Index usage questions

[khussain]

Table in question: Orders
Index A on Table Orders (order_number, issue_symbol, firm_symbol)

Query in question:
select * from orders
where firm_symbol = 'ABCD'
and issue_symbol = 'IBM'
and order_side = 'B'
and msg_key_time > '100423';

After doing a trace on this query, it does a full table scan. Index hint also does not work. Table/indexes are analyzed.
Why does it not use index A, should it ? since 2 of the 3 columns are in the index.

Is there anything else I can try, other than creating another index ?

[chrisrlong]

In 8i, it will only use an index if a left-leading sub-set of the columns are used in the predicates. Since you are not using order_number, the index will not be used.

9i has new capabilities in this area.

- Chris

[khussain]

Thanks Chris, can u give some more details on 9i capabilities that u refered to, that is what we are using.

Should this query not use index skip scan ?

[slimdave]

index skip-scan requires a low number of distinct values in the leading columns of the index. I'm guessing from the table and column names that order_number would not meet that restriction.

[tamilselvan]

Try this:

select * from orders
where order_number = order_number
and firm_symbol = 'ABCD'
and issue_symbol = 'IBM'
and order_side = 'B'
and msg_key_time > '100423';

I am not sure the above sql will use index.
Give a shot.

[slimdave]

Originally posted by tamilselvan
Try this:

select * from orders
where order_number = order_number
and firm_symbol = 'ABCD'
and issue_symbol = 'IBM'
and order_side = 'B'
and msg_key_time > '100423';

I am not sure the above sql will use index.
Give a shot.

I hope it doesn't, and I don't think it will.

[Partha]

HI

Use this query, it uses the index and i have checked it in the plan_table.

--------------------------------------------------------
select /*+ INDEX(ORDERS A) */ * from orders
where
order_number=order_number
and issue_symbol = 'IBM'
and firm_symbol = 'ABCD'
and order_side = 'B'
and msg_key_time > '100423';

--------------------------------------------------------

Actually the where clause column order should be in the same order as specified during the creation of the index. i have used the same concept and the hint(index) to force the usage of the index.

I am attaching the execution plan and the statistics for ur reference.

Regards

Partha

[pando]

if you use a hint of course it uses index but of course if will go much sloswer

also, since when the order of predicate has to be the same as index....? any reference for that?

[slimdave]

Originally posted by pando
if you use a hint of course it uses index but of course if will go much sloswer

also, since when the order of predicate has to be the same as index....? any reference for that?

Excellent points. Partha is fired.

[abhaysk]

Originally posted by chrisrlong
In 8i, it will only use an index if a left-leading sub-set of the columns are used in the predicates. Since you are not using order_number, the index will not be used.

9i has new capabilities in this area.

- Chris

Chris :

Not sure i follow you...take a look.

SQL*Plus: Release 8.1.7.0.0 - Production on Sun May 18 07:04:28 2003

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Enter password: ********

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
JServer Release 8.1.7.4.1 - Production

SCS_DBA@U144> set lines 10000
SCS_DBA@U144> select material_no from ship_fcst where FCST_PERIOD_DT = '01-may-03' and rownum < 2;

MATERIAL_NO
------------------
110936-B21


Execution Plan
----------------------------------------------------------
          0                    SELECT STATEMENT Optimizer=CHOOSE (Cost=2948 Card=100967 Bytes=1716439)
          1                  0   COUNT (STOPKEY)
          2                  1     INDEX (FAST FULL SCAN) OF 'PK_SHIP_FCST' (UNIQUE) (Cost=2948 Card=100967 Bytes=1716439)


-- Note Index (PK_SHIP_FCST) Being Used...

now see the index postion of FCST_PERIOD_DT

SCS_DBA@U144> ed
Wrote file afiedt.buf

  1  select column_position, index_name, table_name, column_name from dba_ind_columns where
  2* index_name='PK_SHIP_FCST' order by  column_position
SCS_DBA@U144> /

COLUMN_POSITION INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
--------------- ------------------------------ ------------------------------ -----------------------------------
              1 PK_SHIP_FCST                   SHIP_FCST                      PLANT_CD
              2 PK_SHIP_FCST                   SHIP_FCST                      MATERIAL_NO
              3 PK_SHIP_FCST                   SHIP_FCST                      SALES_ORGANIZATION_CD
              4 PK_SHIP_FCST                   SHIP_FCST                      FCST_BASE_PERIOD_DT   
              5 PK_SHIP_FCST                   SHIP_FCST                      FCST_PERIOD_DT
              6 PK_SHIP_FCST                   SHIP_FCST                      VERSION_STAT_NM

6 rows selected.