HINT to use index.
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: HINT to use index.

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    HINT to use index.

    Using 9.2.0.6. - ANALYZED every night. Optimiser set to CHOOSE.
    I don't like HINTS. Never really understood some of them and
    have been warned off them many times; especially by Jonathan
    Lewis's seminar at the UK OUG (Basically "unless you fully
    understand what they are and what they do - DON'T USE THEM!").

    I have a query on a 100,000 row table that I know will only
    retrieve < .05% of the rows. The query is simple:

    Code:
    SELECT 
    FROM   
    WHERE stat_code = 'PICK';
    
    This results in a full table scan.
    
    Heres the TRACE and EXPLAIN...
    
    SQL> 
    SQL> SELECT trl_id, sord_no, ssit_id, invs_id, stat_code 
      2  FROM ship_orders
      3  WHERE stat_code = 'SORD_PICK'
      4  
    SQL> set autotrace traceonly statistics
    SQL> 
    SQL> /
    
    51 rows selected.
    
    
    Statistics
    ----------------------------------------------------------
              7  recursive calls
              0  db block gets
           2241  consistent gets
           2220  physical reads
              0  redo size
           2356  bytes sent via SQL*Net to client
            532  bytes received via SQL*Net from client
              5  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             51  rows processed
    
    SQL> EXPLAIN PLAN
      2  SET statement_id = 'NOHINT'
      3  FOR
      4  SELECT trl_id, sord_no, ssit_id, invs_id, stat_code 
      5  FROM ship_orders
      6  WHERE stat_code = 'SORD_PICK'
      7  /
    
    Explained.
    
    ...
    
    QueryPlan                                   Cost Cardinality
    ---------------------------------------- ------- -----------
      TABLE ACCESS FULL SHIP_ORDERS              216       17871
    
    Originally the search column (STAT_CODE) had no index so I 
    created a simple vanilla index, generated STATS for it and 
    re-ran the query with the same results. 
    
    Ok ... I looked at the Stats, Tree Height is 2 with 431 leaf 
    blocks. But the DISTINCT Keys is only 4... Perhaps the CBO 
    assumes that 25,000 rows will be 'PICK'? And reading 25,000 
    index entries and 25,000 rows might be slower than a FTS. (?)
    
    Seeing as of the 100,000 rows only 50 had a status of 'PICK', 
    I tried to force the CBO to use the available index thus:
    
    
    SQL> set autotrace traceonly statistics
    SQL> 
    SQL> SELECT  /*+ INDEX */ trl_id, sord_no, ssit_id, invs_id, stat_code 
      2  FROM ship_orders
      3  WHERE stat_code = 'SORD_PICK'
      4  /
    
    51 rows selected.
    
    
    Statistics
    ----------------------------------------------------------
              7  recursive calls
              0  db block gets
           2241  consistent gets
           2222  physical reads
              0  redo size
           2356  bytes sent via SQL*Net to client
            532  bytes received via SQL*Net from client
              5  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             51  rows processed
    
    SQL> EXPLAIN PLAN
      2  SET statement_id = 'HINT'
      3  FOR
      4   SELECT  /*+ INDEX */ trl_id, sord_no, ssit_id, invs_id, stat_code 
      5   FROM ship_orders
      6   WHERE stat_code = 'SORD_PICK'
      7  /
    
    Explained.
    
    SQL> @dba\explain_sql
    Enter value for statement_id: HINT
    
    QueryPlan                                   Cost Cardinality
    ---------------------------------------- ------- -----------
      TABLE ACCESS FULL SHIP_ORDERS              216       17871
    
    So it seems the index is not being used. 
    
    The index definitely exists and is VALID:
    
    SQL> SELECT index_name, table_name, column_name
      2  FROM all_ind_columns
      3  WHERE table_name = 'SHIP_ORDERS';
    
    INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------ -----------------------
    SORD_PK                        SHIP_ORDERS                    TRL_ID
    SORD_PK                        SHIP_ORDERS                    SORD_NO
    SORD_INVS_FK_I                 SHIP_ORDERS                    INVS_ID
    SORD_ORDER_NO_I                SHIP_ORDERS                    SORD_NO
    SORD_SCAR_FK_I                 SHIP_ORDERS                    SCAR_ID
    SORD_SSIT_FK_I                 SHIP_ORDERS                    SSIT_ID
    SORD_TRL_FK_I                  SHIP_ORDERS                    TRL_ID
    SORD_STAT_CODE_I               SHIP_ORDERS                    STAT_CODEAny point me in the direction of what might be going on here?
    				
    Last edited by gandolf989; 03-14-2006 at 07:57 AM.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you need to specify the index name and table_name if you want to use the hint

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Dave,
    The doc I was reading says, "The INDEX hint, without a specified index, will not perform a full table scan, even though no indexes have been specified. The optimizer will choose the best index for the query"

    This is from 'Oracle Performance Tuning' by Richard Niemic (Oracle Press) 2000.

    I'll cross-ref this with the on-line docs...

  4. #4
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    From : http://download-west.oracle.com/docs...tsref.htm#5156


    "This hint can optionally specify one or more indexes:

    If this hint specifies a single available index, then the optimizer performs a scan on this index. The optimizer does not consider a full table scan or a scan on another index on the table.
    If this hint specifies a list of available indexes, then the optimizer considers the cost of a scan on each index in the list and then performs the index scan with the lowest cost. The optimizer can also choose to scan multiple indexes from this list and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan or a scan on an index not listed in the hint.
    If this hint specifies no indexes, then the optimizer considers the cost of a scan on each available index on the table and then performs the index scan with the lowest cost. The optimizer can also choose to scan multiple indexes and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan. "

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Code:
    "The INDEX hint, without a specified index, will not perform a full table scan, even though no indexes have been specified. The optimizer will choose the best index for the query"
    I dont think so i agree with that.

    regards
    Hrishy

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    It works if you specify the table name and index name.

    Thus:

    SQL> SELECT /*+ INDEX (ship_orders sord_stat_code_i) */ trl_id, sord_no, ssit_id, invs_id, stat_code
    2 FROM ship_orders
    3 WHERE stat_code = 'SORD_PICK'
    4 /

    51 rows selected.


    Statistics
    ----------------------------------------------------------
    7 recursive calls
    0 db block gets
    22 consistent gets
    0 physical reads
    0 redo size
    2356 bytes sent via SQL*Net to client
    532 bytes received via SQL*Net from client
    5 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    51 rows processed

    SQL>
    SQL> EXPLAIN PLAN
    2 SET STATEMENT_ID = 'HINT_IND_NAME'
    3 FOR
    4 SELECT /*+ INDEX (ship_orders sord_stat_code_i) */ trl_id, sord_no, ssit_id, invs_id, stat_code
    5 FROM ship_orders
    6 WHERE stat_code = 'SORD_PICK'
    7 /

    Explained.

    SQL> @dba\explain_sql
    Enter value for statement_id: HINT_IND_NAME


    QueryPlan Cost Cardinality
    ---------------------------------------- ------- -----------
    TABLE ACCESS BY INDEX ROWID SHIP_ORDER 861 17871
    S

    INDEX RANGE SCAN SORD_STAT_CODE_I 110 17871


    Which is fine (and Thanks Davey!) but why was my initial hint not working? Do the docs lie?

  7. #7
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Quote Originally Posted by hrishy
    Hi

    Code:
    "The INDEX hint, without a specified index, will not perform a full table scan, even though no indexes have been specified. The optimizer will choose the best index for the query"
    I dont think so i agree with that.

    regards
    Hrishy
    I think I've come to doubt it myself!

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How about just putting in the table name? (Well the table alias, strictly I guess).
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    May 2005
    Location
    AZ, USA
    Posts
    131
    May be a candidate for a histogram:

    http://download-west.oracle.com/docs...tats.htm#27065

  10. #10
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Quote Originally Posted by slimdave
    How about just putting in the table name? (Well the table alias, strictly I guess).
    Yes it works with just the table (alias). Docs not entirely clear though are they?

    Thanks Davey.


    Foreign Script Kiddie ...

  11. Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •  


    Click Here to Expand Forum to Full Width