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 07:57 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
Click Here to Expand Forum to Full Width
|
Reply With Quote