Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Force Index using hints

Posted on 2007-10-08
14
Medium Priority
?
8,855 Views
Last Modified: 2008-01-09
I am forcing the below sql to use the index XM240P0 in the execution. But there is no change the explain plan and the index is not being used. I need to tune the query as it is doing a full table scan. The explain plan is as shown below. Please advice. I will be glad to furnish any other details you may want

 
 select  /*+ index(XM240P0) */
 a.scm_id, a.sc_evt_id, a.cre_dttm, a.SC_EVT_STATUS_FLG,
c.dst_id, c.gl_acct, c.amount
from
ci_sc_evt a,
ci_sc_evt_ft b,
ci_ft_gl c
where a.sc_evt_id = b.SC_EVT_ID
and b.ft_id = c.ft_id
--and d.ft_id = c.ft_id
and a.sc_evt_type_cd = 'EO ALLOC'
and c.dst_id like '2010 EO%'
and  a.scm_id = '0004720000'

explain plan:

SELECT STATEMENT  CHOOSECost: 6  Bytes: 141  Cardinality: 1                                
      7 TABLE ACCESS BY INDEX ROWID CISADM.CI_FT_GL Cost: 3  Bytes: 63  Cardinality: 1                          
            6 NESTED LOOPS  Cost: 6  Bytes: 141  Cardinality: 1                    
                  4 NESTED LOOPS  Cost: 3  Bytes: 78  Cardinality: 1              
                        1 TABLE ACCESS FULL CISADM.CI_SC_EVT_FT Cost: 2  Bytes: 28  Cardinality: 1        
                        3 TABLE ACCESS BY INDEX ROWID CISADM.CI_SC_EVT Cost: 1  Bytes: 50  Cardinality: 1        
                              2 INDEX UNIQUE SCAN UNIQUE CISADM.XT242P0 Cost: 1  Cardinality: 1  
                  5 INDEX RANGE SCAN UNIQUE CISADM.XT113P0 Cost: 3  Cardinality: 2

XT242P0 is created on sc_evt_id
XT113P0 is a composite index (FT_ID, GL_SEQ_NBR)


Thanks,
Gary
0
Comment
Question by:crgary_tx
13 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20034245
the tables seem to contain only few rows, is that possible?
in which case, the full table scan will be "by far" more effective than index seek...

in which case, you need to load the table with a relevant amount of data, update the statistics, and retry to get the explain plan.
note: the index hint will not FORCE the index to be used, just a more strong emphasis to the index to be used...


0
 
LVL 7

Expert Comment

by:Docteur_Z
ID: 20034310
Your cost is low, your cardinalities are very low, it doesn't hurt to have a Full Scan plan.

As angel3 said, try with lager tables. Something reproducing a production load.
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 500 total points
ID: 20034539
There are two MAIN things that could prevent the usage of indexes:
1. The SELECT extracts *!more then ca. 5%!* of the rows of the indexed table

2. There is *! no computed statistics*! over the table and the index.

Which case is yours?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Author Comment

by:crgary_tx
ID: 20034543
Below is the stats for the table. The explain is same irrespective of whether the following filter is present or not:
and  a.scm_id = '0004720000'

With the above filter in place the query takes 2 minutes to run and without this filters it takes more than 10 hours. With the addition of another filter
--and a.sc_evt_id = '000470966141' the query returns one row instantly without full table scan on any tables. I need to run this query without setting any filters ie scm_id or sc_evt_id. Thats the reason I am trying the query to use the index on sc_evt_id which is XM243P0. Below is the statistics of the tables being used.

Thanks,
Gary


TABLE_NAME|TABLESPACE_NAME|CLUSTER_NAME|NEXT_EXTENT|NUM_ROWS|BLOCKS
CI_FT_GL|CISTS_LARGEDATA||134217728|50930149|739277
CI_SC_EVT|CISTS_MEDIUMDATA||4194304|5609745|102162
CI_SC_EVT_FT|CISTS_MEDIUMDATA||4194304|0|1
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20034573
your hint syntax isn't correct either.  

The index hint takes two parameters,  table name then the index name with a space between them.
If you alias the tables (like you did) then you use the table alias in the hint.

So, if your index is on your "b" table then it should look like this...

SELECT /*+ INDEX(b XM240P0) */

having said that though,  I completely agree with the above comments that you don't necessarily need to avoid full table scans.  They can be the most efficient access path in many occasions.  If you don't believe your plan is correct, make sure your statistics are up-to-date.  Because, as mentioned before, with the cardinalities we see in the plan above,  a table scan does seem appropriate

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20034664
From your posts....
>>>>1 TABLE ACCESS FULL CISADM.CI_SC_EVT_FT Cost: 2  Bytes: 28  Cardinality: 1        
>>>> CI_SC_EVT_FT|CISTS_MEDIUMDATA||4194304|0|1

You are full scanning an empty table?    
That seemes like the most efficient access method,
but I'm thinking maybe your stats aren't correct.

Because a 3-way inner join with an empty table should return nothing, regardless of the size of the other two tables.




0
 
LVL 13

Author Comment

by:crgary_tx
ID: 20034742
Thanks for the response. I dont know why it shows Zero for Number of rows when I do select * from all_tables where table_name = ci_sc_evt_ft.

But the table do have data and it has more than 3 million rows. It could contain close to 10 M records.

Thanks,
Gary
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 20034783
Well, that's why your plan is screwy.  Oracle thinks your table is tiny.  Get new statistics and Oracle should figure out that it needs to do something differently.

If it doesn't, then maybe an index hint (with proper syntax) will be warranted
0
 
LVL 7

Expert Comment

by:DiscoNova
ID: 20035175
Furthermore; even with hinting, you can *not* force Oracle to use the index you want. Hint is only a request that you "really, really" wish the query to use the index, but the CBO might *still* decide otherwise.
0
 
LVL 13

Author Comment

by:crgary_tx
ID: 20035448
I have requested the DBA to update the table stats. Let me see if it will have any effect on the explain plan. Thanks for the responses..

Gary
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 500 total points
ID: 20036992
Having out-of-date statistics can cause huge performance problems!  A general rule-of-thumb is 10%, meaning if the volume of data in the table changes by 10% (or more) since the last time the statistics were calculated, they should be recalculated.  But, other factors can also influence the frequency at which statistics should be re-calculated.

And, as other have indicated, an index hint does *NOT* force Oracle to use an index!  It only suggests or recommends to the optimizer that an index be used.  It is easy to force an index to *NOT* be used: just apply an operator to the database column name, like: trunc, upper, to_char, etc. But, the only way I know of to force the use of an index is with a stored outline for the query.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20037628
Firstly we can only give a hint to the optimizer but we do not have the control of forcing it to use whatever we are saying in the hint.

cbo will decide whether the orginial explain plan should be used or the explain plan with the hint which we gave should be used depending on whichever has the lower cost and other factors..

Thanks
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20591421
Forced accept.

Computer101
EE Admin
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month21 days, 6 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question