• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8870
  • Last Modified:

Force Index using hints

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
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)

4 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...

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.
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?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

crgary_txAuthor Commented:
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.


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

From your posts....
>>>>1 TABLE ACCESS FULL CISADM.CI_SC_EVT_FT Cost: 2  Bytes: 28  Cardinality: 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.

crgary_txAuthor Commented:
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.

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
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.
crgary_txAuthor Commented:
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..

Mark GeerlingsDatabase AdministratorCommented:
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.
Naveen KumarProduction Manager / Application Support ManagerCommented:
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..

Forced accept.

EE Admin
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now