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.
you need to specify the index name and table_name if you want to use the hint
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...
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. "
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
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?
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!
How about just putting in the table name? (Well the table alias, strictly I guess).
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 ...
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
|