This discussion is archived
8 Replies Latest reply: Oct 5, 2012 11:16 PM by user10719327 RSS

Index is not getting picked even after providing hint

user10719327 Newbie
Currently Being Moderated
Hi All

The index pk_sku is not getting picked even after providing the hints when executing the query.Please kindly give me such recommendations.I am providing the information.
select  * from
(
(select /*+ index(s pk_sku) */ 
      to_char(t.dstamp, 'YYYYmmDDHH24MI') as sort_1,
      t.client_id as CLIENT_ID ,
      t.site_id as SITE_ID,
      tz.time_zone_name as TIME_ZONE_NAME     ,
      t.owner_id as OWNER_ID,
      t.sku_id as SKU_ID,
      t.reason_id as ADJUST_TYPE,
      to_char(t.dstamp+tz.diff/24, 'DD/mm/YYYY HH24:MI') as ADJUST_DATETIME,
      t.reference_id as RECEIPT_ID,
      t.batch_id as BATCH_ID,
      t.update_qty as UPDATE_QTY,
      t.lock_code as LOCK_CODE,
      s.description as SKU_DESCRIPTION
from  mv_inventory_transaction t
      JOIN timezone tz ON tz.site_id = t.site_id ,
       mv_sku s
where   t.sku_id= s.sku_id
and     t.client_id=s.client_id
and   ( :client is null or :client = t.client_id)
and    ( :site is null or t.site_id  in (:site))
and    ( :owner is null or t.owner_id in (:owner))
and    t.code= 'Adjustment' 
and   (t.dstamp+tz.diff/24) between  to_date(:dateFrom, 'DD/mm/yyyy') and to_date(:dateTo,'DD/mm/yyyy')+1
and      (:sku is null or t.sku_id in (:sku))
)
UNION ALL
(select  /*+ index(s pk_sku) */ 
     to_char(t.dstamp, 'YYYYmmDDHH24MI') as sort_1,
    t.client_id as CLIENT_ID ,
      t.site_id as SITE_ID,
                      tz.time_zone_name as TIME_ZONE_NAME     ,
      t.owner_id as OWNER_ID,
      t.sku_id as SKU_ID,
      t.reason_id as ADJUST_TYPE,
      to_char(t.dstamp+tz.diff/24, 'DD/mm/YYYY HH24:MI') as ADJUST_DATETIME,
      t.reference_id as RECEIPT_ID,
      t.batch_id as BATCH_ID,
      t.update_qty as UPDATE_QTY,
      t.lock_code as LOCK_CODE,
      s.description as SKU_DESCRIPTION
from  mv_inventory_transaction_arch t
      JOIN timezone tz ON tz.site_id = t.site_id,
       mv_sku s
where   t.sku_id= s.sku_id
and     t.client_id=s.client_id
and   ( :client is null or :client = t.client_id)
and    ( :site is null or t.site_id  in (:site))
and    ( :owner is null or t.owner_id in (:owner))
and    t.code= 'Adjustment' 
and   (t.dstamp+tz.diff/24) between  to_date(:dateFrom, 'DD/mm/yyyy') and to_date(:dateTo,'DD/mm/yyyy')+1
and      (:sku is null or t.sku_id in (:sku))
))
order by sort_1,2,3,4,5
version = Release 10.1.0.5.0
SQL> sho parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.1.0.5
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS



sho parameter db_file_multiblock_count_read


sho parameter cursor_sharing


SQL> sho parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.1.0.5
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS

SQL>  sho parameter db_file_multi

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16
SQL> sho parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
Explain plan
 
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                               |    56 | 29736 |   345K  (1)| 01:09:07 |
|   1 |  SORT ORDER BY                         |                               |    56 | 29736 |   345K  (1)| 01:09:07 |
|   2 |   VIEW                                 |                               |    56 | 29736 |   345K  (1)| 01:09:07 |
|   3 |    UNION-ALL                           |                               |       |       |            |          |
|   4 |     NESTED LOOPS                       |                               |     2 |   984 | 29686   (1)| 00:05:57 |
|   5 |      HASH JOIN                         |                               |     1 |   326 | 26870   (1)| 00:05:23 |
|   6 |       TABLE ACCESS FULL                | TIMEZONE                      |     4 |    88 |     3   (0)| 00:00:01 |
|   7 |       VIEW                             | MV_INVENTORY_TRANSACTION      |    25 |  7600 | 26866   (1)| 00:05:23 |
|   8 |        UNION-ALL                       |                               |       |       |            |          |
|   9 |         FILTER                         |                               |       |       |            |          |
|  10 |          MAT_VIEW ACCESS BY INDEX ROWID| MV_INVENTORY_TRANSACTION      |     1 |    67 | 24327   (1)| 00:04:52 |
|  11 |           INDEX RANGE SCAN             | IDX_CODE_CLIENTID_690         | 79681 |       |   518   (1)| 00:00:07 |
|  12 |         FILTER                         |                               |       |       |            |          |
|  13 |          MAT_VIEW ACCESS BY INDEX ROWID| MV_INVENTORY_TRANSACTION      |    24 |  2328 |  2539   (1)| 00:00:31 |
|  14 |           INDEX RANGE SCAN             | IDX_CODE_646                  |  9269 |       |    46   (0)| 00:00:01 |
|  15 |      VIEW                              | MV_SKU                        |    37 |  6142 |  2816   (1)| 00:00:34 |
|  16 |       UNION-ALL                        |                               |       |       |            |          |
|  17 |        MAT_VIEW ACCESS FULL            | MV_SKU                        |   335K|    10M|  2535   (1)| 00:00:31 |
|  18 |        MAT_VIEW ACCESS FULL            | MV_SKU                        | 37839 |  1699K|   280   (1)| 00:00:04 |
|  19 |     NESTED LOOPS                       |                               |    54 | 26568 |   315K  (1)| 01:03:10 |
|  20 |      HASH JOIN                         |                               |     1 |   326 |   313K  (1)| 01:02:37 |
|  21 |       TABLE ACCESS FULL                | TIMEZONE                      |     4 |    88 |     3   (0)| 00:00:01 |
|  22 |       VIEW                             | MV_INVENTORY_TRANSACTION_ARCH |   576 |   171K|   313K  (1)| 01:02:37 |
|  23 |        UNION-ALL                       |                               |       |       |            |          |
|  24 |         FILTER                         |                               |       |       |            |          |
|  25 |          MAT_VIEW ACCESS BY INDEX ROWID| MV_INVENTORY_TRANSACTION_ARCH |     1 |    72 | 19258   (1)| 00:03:52 |
|  26 |           INDEX RANGE SCAN             | QUI_IDX_INVTRANS_ARC1         | 36016 |       |   287   (1)| 00:00:04 |
|  27 |         FILTER                         |                               |       |       |            |          |
|  28 |          MAT_VIEW ACCESS BY INDEX ROWID| MV_INVENTORY_TRANSACTION_ARCH |   575 | 55200 |   293K  (1)| 00:58:45 |
|  29 |           INDEX RANGE SCAN             | QUI_IDX_INVTRANS_ARC1         |   367K|       |  3466   (1)| 00:00:42 |
|  30 |      VIEW                              | MV_SKU                        |    37 |  6142 |  2816   (1)| 00:00:34 |
|  31 |       UNION-ALL                        |                               |       |       |            |          |
|  32 |        MAT_VIEW ACCESS FULL            | MV_SKU                        |   335K|    10M|  2535   (1)| 00:00:31 |
|  33 |        MAT_VIEW ACCESS FULL            | MV_SKU                        | 37839 |  1699K|   280   (1)| 00:00:04 |
------------------------------------------------------------------------------------------------------------------------
when put in sql tuning advisor,It recommended with better plan using indexes
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                               |    56 | 29736 |   340K  (1)| 01:08:01 |
|   1 |  SORT ORDER BY                         |                               |    56 | 29736 |   340K  (1)| 01:08:01 |
|   2 |   VIEW                                 |                               |    56 | 29736 |   340K  (1)| 01:08:01 |
|   3 |    UNION-ALL                           |                               |       |       |            |          |
|   4 |     NESTED LOOPS                       |                               |     2 |   848 | 26900   (1)| 00:05:23 |
|   5 |      HASH JOIN                         |                               |     1 |   326 | 26898   (1)| 00:05:23 |
|   6 |       TABLE ACCESS FULL                | TIMEZONE                      |     4 |    88 |     3   (0)| 00:00:01 |
|   7 |       VIEW                             | MV_INVENTORY_TRANSACTION      |    25 |  7600 | 26894   (1)| 00:05:23 |
|   8 |        UNION-ALL                       |                               |       |       |            |          |
|   9 |         FILTER                         |                               |       |       |            |          |
|  10 |          MAT_VIEW ACCESS BY INDEX ROWID| MV_INVENTORY_TRANSACTION      |     1 |    67 | 24352   (1)| 00:04:53 |
|  11 |           INDEX RANGE SCAN             | IDX_CODE_CLIENTID_690         | 79681 |       |   518   (1)| 00:00:07 |
|  12 |         FILTER                         |                               |       |       |            |          |
|  13 |          MAT_VIEW ACCESS BY INDEX ROWID| MV_INVENTORY_TRANSACTION      |    24 |  2328 |  2542   (1)| 00:00:31 |
|  14 |           INDEX RANGE SCAN             | IDX_CODE_646                  |  9269 |       |    46   (0)| 00:00:01 |
|  15 |      VIEW                              | MV_SKU                        |    37 |  3626 |     2   (0)| 00:00:01 |
|  16 |       UNION-ALL PARTITION              |                               |       |       |            |          |
|  17 |        MAT_VIEW ACCESS BY INDEX ROWID  | MV_SKU                        |     1 |    34 |     3   (0)| 00:00:01 |
|  18 |         INDEX UNIQUE SCAN              | PK_SKU                        |     1 |       |     2   (0)| 00:00:01 |
|  19 |        MAT_VIEW ACCESS BY INDEX ROWID  | MV_SKU                        |     1 |    46 |     2   (0)| 00:00:01 |
|  20 |         INDEX UNIQUE SCAN              | PK_SKU                        |     1 |       |     1   (0)| 00:00:01 |
|  21 |     NESTED LOOPS                       |                               |    54 | 22896 |   313K  (1)| 01:02:38 |
|  22 |      HASH JOIN                         |                               |     1 |   326 |   313K  (1)| 01:02:38 |
|  23 |       TABLE ACCESS FULL                | TIMEZONE                      |     4 |    88 |     3   (0)| 00:00:01 |
|  24 |       VIEW                             | MV_INVENTORY_TRANSACTION_ARCH |   576 |   171K|   313K  (1)| 01:02:38 |
|  25 |        UNION-ALL                       |                               |       |       |            |          |
|  26 |         FILTER                         |                               |       |       |            |          |
|  27 |          MAT_VIEW ACCESS BY INDEX ROWID| MV_INVENTORY_TRANSACTION_ARCH |     1 |    72 | 19269   (1)| 00:03:52 |
|  28 |           INDEX RANGE SCAN             | QUI_IDX_INVTRANS_ARC1         | 36016 |       |   287   (1)| 00:00:04 |
|  29 |         FILTER                         |                               |       |       |            |          |
|  30 |          MAT_VIEW ACCESS BY INDEX ROWID| MV_INVENTORY_TRANSACTION_ARCH |   575 | 55200 |   293K  (1)| 00:58:47 |
|  31 |           INDEX RANGE SCAN             | QUI_IDX_INVTRANS_ARC1         |   367K|       |  3466   (1)| 00:00:42 |
|  32 |      VIEW                              | MV_SKU                        |    37 |  3626 |     2   (0)| 00:00:01 |
|  33 |       UNION-ALL PARTITION              |                               |       |       |            |          |
|  34 |        MAT_VIEW ACCESS BY INDEX ROWID  | MV_SKU                        |     1 |    34 |     3   (0)| 00:00:01 |
|  35 |         INDEX UNIQUE SCAN              | PK_SKU                        |     1 |       |     2   (0)| 00:00:01 |
|  36 |        MAT_VIEW ACCESS BY INDEX ROWID  | MV_SKU                        |     1 |    46 |     2   (0)| 00:00:01 |
|  37 |         INDEX UNIQUE SCAN              | PK_SKU                        |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
So ,after checking the recommended plan I tried to execute the query with the hints,even then it is picking up the indexes in the explain plan.So please kindly help in solving the issue

The pk_sku is the index created on client_id and sku_id columns



Thanks
GV
  • 1. Re: Index is not getting picked even after providing hint
    riedelme Expert
    Currently Being Moderated
    user10719327 wrote:
    Hi All

    The index pk_sku is not getting picked even after providing the hints when executing the query.Please kindly give me such recommendations.I am providing the information.
    What version of the database?

    There are OTN contributors - really smart people with lots of experience - who insist that "hints" are really "directives" that WILL be followed when used if at all possible barring optimizer bugs. My experience is differernt - hints will be followed if the optimizer determines they will be beneficial and not if they will not, including my current project on 11gR2. I have been through the exact situation you are describing several times on different versions of the database - usually 10g and above. The database simply will not use the hint for any discernable reason (its possible that a 10053 trace could reveal why but these things generally happen when there is a backlog of things to do and intense exploration of the issue is not convenient). Optimizer bugs may account for the issue of hints not being but bugs are elusive; you can search My Oracle Support if you have the time.

    The optimizer is probably not using the index because it determines another access path will be more efficient (cost).

    Things to look for:
    1) If another index is being used and your version supports the functionality alter the index to be invisible (and not used) to see if the index you want to be used is used then
    2) Analyze the tables and the index to make sure the statistics are current
    3) If you are doing full table scans change the session parameter optimzer_index_cost_adj to favor using indexes and see what happens
    4) You are already using the SQL tuning advisor. Did it reccoment profies or just indexes?
    5) check the syntax to make sure the hint is specified correctly (make sure you're using the correct table alias and hint names) :)

    Good luck!

    Edited by: riedelme on Oct 5, 2012 8:20 AM
  • 2. Re: Index is not getting picked even after providing hint
    user10719327 Newbie
    Currently Being Moderated
    Hi

    Its already mentioned .the db version is 10.1.0.5.0

    Thanks
    Gv
  • 3. Re: Index is not getting picked even after providing hint
    chris227 Guru
    Currently Being Moderated
    Dont know if this plays a role , but why are you mixing ansii and oracle join syntax?
  • 4. Re: Index is not getting picked even after providing hint
    6363 Guru
    Currently Being Moderated
    riedelme wrote:

    My experience is differernt - hints will be followed if the optimizer determines they will be beneficial and not if they will not, including my current project on 11gR2.
    The example below (unfortunately only 11gR1) disproves your theory, showing the index hint is followed and that the cost increases by a factor of five.

    The most common cases of the optimizer apparently ignoring a hint are due to operator error, the hint is not well formed, or cannot be followed. There are a couple of documented cases where a hint will be ignored - first_rows used in CTAS is one.

    Jonathan Lewis writes a lot about it on his blog showing that the optimizer always acts on a well formed hint if it is at all possible.
    SQL> create table t (n number not null, c varchar2(1000));
    
    Table created.
    
    SQL> insert into t select 1, rpad('x',1000,'x')
      2  from dual connect by level <= 50000;
    
    50000 rows created.
    
    SQL> create index t_ix on t (n);
    
    Index created.
    
    SQL> exec dbms_stats.gather_table_stats(user, 't', cascade => true)
    
    PL/SQL procedure successfully completed.
    
    SQL> explain plan for
      2  select max(c) from t where n =1;
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------
    
    Plan hash value: 2966233522
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     1 |  1004 |  1574   (1)| 00:00:19 |
    |   1 |  SORT AGGREGATE    |      |     1 |  1004 |            |          |
    |*  2 |   TABLE ACCESS FULL| T    | 50000 |    47M|  1574   (1)| 00:00:19 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------
    
    
       2 - filter("N"=1)
    
    14 rows selected.
    
    SQL> explain plan for
      2  select /*+ index (t t_ix) */  max(c) from t where n = 1;
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------
    
    Plan hash value: 3231292748
    
    -------------------------------------------------------------------------------------
    | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |      |     1 |  1004 |  7244   (1)| 00:01:27 |
    |   1 |  SORT AGGREGATE              |      |     1 |  1004 |            |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID| T    | 50000 |    47M|  7244   (1)| 00:01:27 |
    |*  3 |    INDEX RANGE SCAN          | T_IX | 50000 |       |    98   (0)| 00:00:02 |
    -------------------------------------------------------------------------------------
    
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("N"=1)
    
    15 rows selected.
  • 5. Re: Index is not getting picked even after providing hint
    user10719327 Newbie
    Currently Being Moderated
    Hi

    can you help me in rewriting the query so that it gives much better execution plan .
    As if now it is taking 18 mins.Please kindly guide me in rewriting the query to pick the better execution plan


    Thanks
    Gv
  • 6. Re: Index is not getting picked even after providing hint
    rp0428 Guru
    Currently Being Moderated
    >
    can you help me in rewriting the query so that it gives much better execution plan .
    As if now it is taking 18 mins.Please kindly guide me in rewriting the query to pick the better execution plan
    >
    Try changing your predicate aliases to use the table with the index. Your code now is - see the comments I added
           mv_sku s
    where   t.sku_id= s.sku_id
    and     t.client_id=s.client_id
    and   ( :client is null or :client = t.client_id) -- USE 's' instead of 't' for the alias to put the filter on the mv_sku table
    and    ( :site is null or t.site_id  in (:site))
    and    ( :owner is null or t.owner_id in (:owner))
    and    t.code= 'Adjustment' 
    and   (t.dstamp+tz.diff/24) between  to_date(:dateFrom, 'DD/mm/yyyy') and to_date(:dateTo,'DD/mm/yyyy')+1
    and      (:sku is null or t.sku_id in (:sku)) -- USE 's' instead of 't' for the alias to put the filter on the mv_ske table
    The pk_sku is the index created on client_id and sku_id columns
    >
    What is the order of columns in that index?

    Are both columns specified as NOT NULL? If not the index can't be used since it would give the wrong results (you specify :client is null and :sku is null in the predicates).

    Also - did you notice that the plan showed by 3360 shows the filter predicates but your plans dont' show them? Please show the rest of the plans.
  • 7. Re: Index is not getting picked even after providing hint
    user10719327 Newbie
    Currently Being Moderated
    Hi

    Please find the below info

    What is the order of columns in that index?

    sku_id is 1 and client_id is 2

    Are both columns specified as NOT NULL? If not the index can't be used since it would give the wrong results (you specify :client is null and :sku is null in the predicates).

    Both of them are not null

    Also - did you notice that the plan showed by 3360 shows the filter predicates but your plans dont' show them? Please show the rest of the plans.
    when ever I am executing this and taking explain plan ,I am getting the same plan always what I posted above

    Thanks
    Gv
  • 8. Re: Index is not getting picked even after providing hint
    user10719327 Newbie
    Currently Being Moderated
    even after changing the coode as per ur instrucions,i am not able to get the good plan.

    please can u look into the issue


    thanks
    gv

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points