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_CODE Any point me in the direction of what might be going on here? 03-14-2006, 07:55 AM davey23uk you need to specify the index name and table_name if you want to use the hint 03-14-2006, 08:39 AM JMac 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... 03-14-2006, 08:44 AM JMac 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. " 03-14-2006, 08:46 AM 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 03-14-2006, 08:55 AM JMac 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? 03-14-2006, 08:57 AM JMac 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! :) 03-14-2006, 09:39 AM slimdave How about just putting in the table name? (Well the table alias, strictly I guess). 03-14-2006, 09:47 AM dbtoo May be a candidate for a histogram: http://download-west.oracle.com/docs...tats.htm#27065 03-14-2006, 10:52 AM JMac 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 ... :confused: :) 03-14-2006, 11:05 AM slimdave Have another look at the syntax diagram -- "table" is compulsory. 03-14-2006, 02:00 PM JMac Yup! All times are GMT -4. The time now is 03:45 PM.