asked on

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Docteur_Z

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.

SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

crgary_tx

ASKER

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 Stuber

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 Stuber

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.

crgary_tx

ASKER

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

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DiscoNova

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_tx

ASKER

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

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Naveen Kumar

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

Computer101

Forced accept.

Computer101
EE Admin