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?
Last edited by gandolf989; 03-14-2006 at 06:57 AM.
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.
"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. "
"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"
"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"
Originally Posted by hrishy
Posting Permissions
Reply With Quote
Bookmarks