We help IT Professionals succeed at work.

Force Index using hints

crgary_tx
crgary_tx asked
on
8,902 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
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Docteur_ZDatabase Engineer
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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.

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
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

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

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

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




Author

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.

Thanks,
Gary
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
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.

Author

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

Gary
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
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
Forced accept.

Computer101
EE Admin

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.