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 AMdavey23ukyou need to specify the index name and table_name if you want to use the hint 03-14-2006, 08:39 AMJMacDave,
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 AMJMacFrom : 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 AMhrishyHi
I dont think so i agree with that.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"
regards
Hrishy 03-14-2006, 08:55 AMJMacIt 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 AMJMacI think I've come to doubt it myself! :)Quote:
Originally Posted by hrishy
03-14-2006, 09:39 AMslimdaveHow about just putting in the table name? (Well the table alias, strictly I guess). 03-14-2006, 09:47 AMdbtooMay be a candidate for a histogram:
http://download-west.oracle.com/docs...tats.htm#27065 03-14-2006, 10:52 AMJMacYes it works with just the table (alias). Docs not entirely clear though are they?Quote:
Originally Posted by slimdave
Thanks Davey.
Foreign Script Kiddie ... :confused: :)