OraFAQ Forum: Performance Tuning » how to force the sql query to use or not to use index

Home » RDBMS Server » Performance Tuning » how to force the sql query to use or not to use index

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to force the sql query to use or not to use index [message #64745]

Fri, 16 January 2004 20:12

Punet Sachar
Messages: 6
Registered: January 2004

Junior Member

how can i force the sql query not to use index or other way round i.e how can i force the query to use index.

and how will i'm asure that its using index

Report message to a moderator

Re: how to force the sql query to use or not to use index [message #64746 is a reply to message #64745]

Sat, 17 January 2004 06:37

Thiru
Messages: 1089
Registered: May 2002

Senior Member

To disable the use of index in a particular query,you could use FULL,NO_INDEX hints,adding expressions/functions to the indexed columns etc

thiru@9.2.0:SQL>alter session set optimizer_goal=CHOOSE;

Session altered.

thiru@9.2.0:SQL>select count(*) from t where empno=1000;

  COUNT(*)
----------
         1

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=3)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=1 Bytes=3)

-- Index is used here. No problem. 

-- Lets suppress this index 

thiru@9.2.0:SQL>select count(*) from t where empno<B>+0</B>=1000;

  COUNT(*)
----------
         1

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
   1    0   SORT (AGGREGATE)
   2    1     <B>TABLE ACCESS (FULL)</B> OF 'T' (Cost=2 Card=1 Bytes=3)

thiru@9.2.0:SQL>select <B>/*+ FULL(t) */</B> * from t where empno=1000;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO GRADE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      1000 Victor     DBA             7839 20-MAY-03      11000          0         10 JUNIOR

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=41)
   1    0   <B>TABLE ACCESS (FULL)</B> OF 'T' (Cost=2 Card=1 Bytes=41)

thiru@9.2.0:SQL>select <B>/*+ NO_INDEX(T) */</B> count(*) from t where empno=1000;

  COUNT(*)
----------
         1

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
   1    0   SORT (AGGREGATE)
   2    1     <B>TABLE ACCESS (FULL) OF 'T'</B> (Cost=2 Card=1 Bytes=3)

thiru@9.2.0:SQL>select count(*) from t where <B>to_number(empno)</B>=1000;

  COUNT(*)
----------
         1

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
   1    0   SORT (AGGREGATE)
   2    1     <B>TABLE ACCESS (FULL)</B> OF 'T' (Cost=2 Card=1 Bytes=3)

Well, to make use of the available index,make sure you update statistics when you create new indexes and leave it to the CBO. It again depends on your OPTIMIZER* settings,Index selectivity,Clustering factor,size of the table,DB_FILE_MULTIBLOCK_READ_COUNT,etc etc. Take care not to accidentally suppress the indexes by adding expressions,functions etc. Also you could hint the optimizer to use the index using INDEX hint etc.
RBO,by default loves indexes.

Check out these thread also
Using Index
Index?

HTH
Thiru

[Updated on: Fri, 18 February 2005 23:32]

Report message to a moderator

Previous Topic:	Select .... from and performance issues
Next Topic:	purging of data

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 15:56:01 CDT 2024