OraFAQ Forum: Performance Tuning » Why the Index is not used, even if its forced?

Home » RDBMS Server » Performance Tuning » Why the Index is not used, even if its forced?

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

Why the Index is not used, even if its forced? [message #125119]

Thu, 23 June 2005 06:47

nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait

Member

SELECT ALL /*+ index (pt_policy, POL_PDS_CODE_01) */ a.DIVN_BL_NAME, b.POL_NO,b.pol_pds_code,
b.POL_CUST_CODE, b.POL_ASSURED_NAME,
c.PSC_FIELD_2, c.PSC_FIELD_4,
c.PSC_FIELD_7, c.PSC_FIELD_8,
c.PSC_FIELD_9, c.PSC_FIELD_3,
b.POL_NL_PERIOD_DESC
FROM PM_DIVISION a, PT_POLICY b, PT_POLICY_SCHEDULE c
WHERE (b.POL_PDS_CODE IN ('080', '088', '089')
 AND c.PSC_FIELD_3 LIKE '&CIVIL_ID%')
 AND  ((c.PSC_POL_SYS_ID=b.POL_SYS_ID)
 AND (a.DIVN_CODE=b.POL_DIVN_CODE))
ORDER BY a.DIVN_BL_NAME ASC,
b.POL_NO ASC, b.POL_CUST_CODE ASC;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=2 Bytes=258)
   1    0   SORT (ORDER BY) (Cost=14 Card=2 Bytes=258)
   2    1     NESTED LOOPS (Cost=12 Card=2 Bytes=258)
   3    2       NESTED LOOPS (Cost=10 Card=2 Bytes=232)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'PT_POLICY_SCHEDULE
          ' (Cost=4 Card=2 Bytes=110)

   5    4           INDEX (RANGE SCAN) OF 'PSC_FIELD_3' (NON-UNIQUE) (
          Cost=3 Card=2)

   6    3         TABLE ACCESS (BY INDEX ROWID) OF 'PT_POLICY' (Cost=3
           Card=1265 Bytes=77165)

   7    6           INDEX (RANGE SCAN) OF 'IDX_PIX_POL_6' (UNIQUE) (Co
          st=2 Card=1)

   8    2       TABLE ACCESS (BY INDEX ROWID) OF 'PM_DIVISION' (Cost=1
           Card=1 Bytes=13)

   9    8         INDEX (UNIQUE SCAN) OF 'PK_PM_DIVISION' (UNIQUE)




Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        757  bytes sent via SQL*Net to client
        328  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)

Even used the table alias

  1  SELECT ALL /*+ index (b, POL_PDS_CODE_01) */ a.DIVN_BL_NAME, b.POL_NO,b.pol_pds_code,
  2  b.POL_CUST_CODE, b.POL_ASSURED_NAME,
  3  c.PSC_FIELD_2, c.PSC_FIELD_4,
  4  c.PSC_FIELD_7, c.PSC_FIELD_8,
  5  c.PSC_FIELD_9, c.PSC_FIELD_3,
  6  b.POL_NL_PERIOD_DESC
  7  FROM PM_DIVISION a, PT_POLICY b, PT_POLICY_SCHEDULE c
  8  WHERE (b.POL_PDS_CODE IN ('080', '088', '089')
  9   AND c.PSC_FIELD_3 LIKE '&CIVIL_ID%')
 10   AND  ((c.PSC_POL_SYS_ID=b.POL_SYS_ID)
 11   AND (a.DIVN_CODE=b.POL_DIVN_CODE))
 12  ORDER BY a.DIVN_BL_NAME ASC,
 13* b.POL_NO ASC, b.POL_CUST_CODE ASC
SQL> /
Enter value for civil_id: 27809176546
old   9:  AND c.PSC_FIELD_3 LIKE '&CIVIL_ID%')
new   9:  AND c.PSC_FIELD_3 LIKE '27809176546%')

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=2 Bytes=258)
   1    0   SORT (ORDER BY) (Cost=14 Card=2 Bytes=258)
   2    1     NESTED LOOPS (Cost=12 Card=2 Bytes=258)
   3    2       NESTED LOOPS (Cost=10 Card=2 Bytes=232)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'PT_POLICY_SCHEDULE
          ' (Cost=4 Card=2 Bytes=110)

   5    4           INDEX (RANGE SCAN) OF 'PSC_FIELD_3' (NON-UNIQUE) (
          Cost=3 Card=2)

   6    3         TABLE ACCESS (BY INDEX ROWID) OF 'PT_POLICY' (Cost=3
           Card=1265 Bytes=77165)

   7    6           INDEX (RANGE SCAN) OF 'IDX_PIX_POL_6' (UNIQUE) (Co
          st=2 Card=1)

   8    2       TABLE ACCESS (BY INDEX ROWID) OF 'PM_DIVISION' (Cost=1
           Card=1 Bytes=13)

   9    8         INDEX (UNIQUE SCAN) OF 'PK_PM_DIVISION' (UNIQUE)




Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        757  bytes sent via SQL*Net to client
        328  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

But in this query its been used

SQL> select  /*+ index (pt_policy, POL_PDS_CODE_01) */ pol_no, pol_pds_code  from pt_policy
  2  WHERE POL_NO LIKE '01/351/2005/201%'
  3  /

POL_NO               POL
-------------------- ---
01/351/2005/201      351
01/351/2005/2010     351
01/351/2005/2011     351
......

11 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=306419 Card=1 Bytes=
          19)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'PT_POLICY' (Cost=306419
          Card=1 Bytes=19)

   2    1     INDEX (FULL SCAN) OF 'POL_PDS_CODE_01' (NON-UNIQUE) (Cos
          t=2980 Card=765260)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     327836  consistent gets
        414  physical reads
        472  redo size
        654  bytes sent via SQL*Net to client
        426  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

All tables used are analyzed and stats are collected. Im using 9iR2

Report message to a moderator

Re: Why the Index is not used, even if its forced? [message #125137 is a reply to message #125119]

Thu, 23 June 2005 08:31

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> WHERE (b.POL_PDS_CODE IN ('080', '088', '089')
***IN LIST*** is the reason.
When IN cluase is used, the query might be transformed into an union all.
Please trace the session and post the output.

Report message to a moderator

Re: Why the Index is not used, even if its forced? [message #125146 is a reply to message #125137]

Thu, 23 June 2005 09:46

girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)

Senior Member

Hi Mahesh

But the index is being passed on in HINT as well, shouldn't it be used then.

Regds
Girish

Report message to a moderator

Re: Why the Index is not used, even if its forced? [message #125148 is a reply to message #125146]

Thu, 23 June 2005 09:54

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

A hint is a suggestion to the optimizer, not a directive.

Report message to a moderator

Re: Why the Index is not used, even if its forced? [message #125253 is a reply to message #125148]

Fri, 24 June 2005 01:18

girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)

Senior Member

HI Art

Checked both the links
On http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96540/sql_elements7a.htm#8477

its mentioned that
"The INDEX hint explicitly chooses an index scan for the specified table."

On http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:7038986332061#41628238596404
It says
"if the hint is being ignored, it is most likely *(MOST likely)* because it cannot be followed."

Now, Art since in these queries thge index hinted is not getting used, so reasoning by Asktom appears to be true.
My query here is how do we ascertain that why this hint can't be followed for this query as in posting by Nabeel, he has mentioned other query as well, in which he is putting hint and that hinted index is getting used.

Regds
Girish

Report message to a moderator

----->>> WRONG SYNTAX [message #125290 is a reply to message #125253]

Fri, 24 June 2005 04:43

MauriceM
Messages: 7
Registered: June 2005

Junior Member

check the correct systax of the Hint:

/*+ INDEX ( table [index [index]...] ) */

there is no comma between the table and the index...

HTH
Maurice

[Updated on: Fri, 24 June 2005 04:44]

Report message to a moderator

Re: ----->>> WRONG SYNTAX [message #125292 is a reply to message #125290]

Fri, 24 June 2005 04:46

girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)

Senior Member

Hi Maurice

Checked that syntax alongwith Nabeel, but that didn't made the query use this index.

Regds
Girish

[Updated on: Fri, 24 June 2005 04:46]

Report message to a moderator

Re: Why the Index is not used, even if its forced? [message #125683 is a reply to message #125119]

Tue, 28 June 2005 11:34

MauriceM
Messages: 7
Registered: June 2005

Junior Member

for which column did you create this index?
where have you defined a WHERE condition against this column?

Report message to a moderator

Re: Why the Index is not used, even if its forced? [message #125769 is a reply to message #125119]

Wed, 29 June 2005 03:09

nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait

Member

Index is on POL_PDS_CODE and the index was used in 3rd example

Report message to a moderator

Re: Why the Index is not used, even if its forced? [message #125776 is a reply to message #125119]

Wed, 29 June 2005 03:49

MauriceM
Messages: 7
Registered: June 2005

Junior Member

in your 3rd query you use the following where condition:
WHERE POL_NO LIKE '01/351/2005/201%'

So I'm suprised to see that Oracle uses the index when you set a where condition against a column which is not contained in this index.

could you please provide the SQL statment used to create the index POL_PDS_CODE_01.

Report message to a moderator

Re: Why the Index is not used, even if its forced? [message #125782 is a reply to message #125119]

Wed, 29 June 2005 04:20

nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait

Member

create index POL_PDS_CODE_01 on pt_policy(pol_pds_code);

Report message to a moderator

Re: Why the Index is not used, even if its forced? [message #125784 is a reply to message #125782]

Wed, 29 June 2005 04:28

MauriceM
Messages: 7
Registered: June 2005

Junior Member

where do you have this sql from? Toad, Designer?

what do you get if you execute an:

select *
from all_indexes
where index_name = 'POL_PDS_CODE_01'

select *
from all_ind_columns
where index_name = 'POL_PDS_CODE_01'

[Updated on: Wed, 29 June 2005 04:31]

Report message to a moderator

Re: Why the Index is not used, even if its forced? [message #125791 is a reply to message #125119]

Wed, 29 June 2005 04:43

nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait

Member

-I used this sql to creat the index, Do I have to use Toad, Designer to get this sql?

-First query gives

SQL> select OWNER   ,
  2  INDEX_NAME     ,
  3  INDEX_TYPE     ,
  4  TABLE_OWNER    ,
  5  TABLE_NAME     ,
  6  TABLE_TYPE     ,
  7  UNIQUENESS     ,
  8  COMPRESSION    ,
  9  PREFIX_LENGTH  ,
 10  TABLESPACE_NAME
 11  from all_indexes
 12* where index_name = 'POL_PDS_CODE_01'
SQL> /

OWNER                          INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER
------------------------------ ------------------------------ --------------------------- ----------
TABLE_NAME                     TABLE UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME
------------------------------ ----- --------- -------- ------------- ------------------------------
PREMPROD                       POL_PDS_CODE_01                NORMAL                      PREMPROD
PT_POLICY                      TABLE NONUNIQUE DISABLED               PREMIDXTAB


1 row selected.

-Second query gives

SQL> select * 
  2  from all_ind_columns 
  3  where index_name = 'POL_PDS_CODE_01' 
  4  /

INDEX_OWNER                    INDEX_NAME                     TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------ -------
COLUMN_NAME
----------------------------------------------------------------------------------------------------
COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
--------------- ------------- ----------- ----
PREMPROD                       POL_PDS_CODE_01                PREMPROD                       PT_POLICY
POL_PDS_CODE
              1             3           3 ASC


1 row selected.

Report message to a moderator

Re: Why the Index is not used, even if its forced? [message #125807 is a reply to message #125791]

Wed, 29 June 2005 05:31

MauriceM
Messages: 7
Registered: June 2005

Junior Member

I still don't understand the execuion plan of the 3rd query.
So I got one last question: could you please execute the following SQLs.

explain plan for
select /*+ index (pt_policy, POL_PDS_CODE_01) */
pol_no,
pol_pds_code
from pt_policy
WHERE POL_NO LIKE '01/351/2005/201%';

select * from table(dbms_xplan.display);

Report message to a moderator

Re: Why the Index is not used, even if its forced? [message #125813 is a reply to message #125119]

Wed, 29 June 2005 06:16

nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait

Member

SQL> conn premprod/******@PROD
Connected.
SQL> explain plan for select  /*+ index (pt_policy, POL_PDS_CODE_01) */ pol_no, pol_pds_code  from p
t_policy 
  2  WHERE POL_NO LIKE '01/351/2005/201%'; 

Explained.

SQL> select * from table(dbms_xplan.display); 

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
| Id  | Operation                   |  Name            | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    19 |   306K|
|*  1 |  TABLE ACCESS BY INDEX ROWID| PT_POLICY        |     1 |    19 |   306K|
|   2 |   INDEX FULL SCAN           | POL_PDS_CODE_01  |   765K|       |  2980 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   1 - filter("PT_POLICY"."POL_NO" LIKE '01/351/2005/201%')

Note: cpu costing is off

15 rows selected.

SQL>

If you need something else let me know... by the way its there in my 3rd example first post

Report message to a moderator

Re: Why the Index is not used, even if its forced? [message #126227 is a reply to message #125813]

Fri, 01 July 2005 13:01

ziggy
Messages: 27
Registered: July 2005
Location: Moscow, Russia

Junior Member

Hi Nabeelkhan!

I agree with MauriceM that it is a mistake of course.
Using the index POL_PDS_CODE_01 without column POL_NO absolutly unreal. I hope you message isn't joke Smile

)

It may be a different schemes with different column in indexes with same name, different servers or something like this.

Pleas give us script for creation table PT_POLICY and all indexes for this table.

Also I hope that you gather statistics for table and indexes so please.

Please execute the folowing statement after gather statistics:

select table_name, index_name, index_type,
blevel, distinct_keys, clustering_factor
from user_indexes
where table_name = 'PT_POLICY'

As trick try to drop index POL_PDS_CODE_01 and create a new one on the same table with same column and different name.

Don't give up,
Oleg.

Report message to a moderator

Re: Why the Index is not used, even if its forced? [message #140022 is a reply to message #126227]

Fri, 30 September 2005 09:00

ziggy
Messages: 27
Registered: July 2005
Location: Moscow, Russia

Junior Member

By the way, you can read this:
http://www.dbazine.com/oracle/or-articles/jlewis12

Report message to a moderator

Previous Topic:	Last n records based on date
Next Topic:	sizing SGA...???

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 16:30:09 CST 2024