Home » RDBMS Server » Performance Tuning » how to force the sql query to use or not to use index
how to force the sql query to use or not to use index [message #64745] Fri, 16 January 2004 20:12 Go to next message
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
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 Go to previous message
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:
  


Current Time: Mon Sep 28 00:55:55 CDT 2020