select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL
SELECT message_id, message_version, message_location, message_type, event_date, match_set_type, match_set_subtype, transaction_id, transaction_id_type, item_id, item_id_type, inventory_owner_group_id, warehouse_id, country_code, quantity, unit_cost, currency_code, match_state, reason, adjusted_by
FROM FINANCIALS.MOE_DATA moe_data
WHERE transaction_id = 7024793115 AND
transaction_id_type = 3
AND item_id_type = 5
AND item_id = 7024793115
ORDER BY message_id, message_version DESC FOR UPDATE NOWAIT
/
Plan hash value: 236610634
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 402 | 4 (25)| 00:00:01 | | |
| 1 | FOR UPDATE | | | | | | | |
| 2 | SORT ORDER BY | | 1 | 402 | 4 (25)| 00:00:01 | | |
| 3 | PARTITION HASH ALL | | 1 | 402 | 3 (0)| 00:00:01 | 1 | 64 |
| 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| MOE_DATA | 1 | 402 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 5 | INDEX SKIP SCAN | MOE_DATA_STATUS | 1 | | 3 (0)| 00:00:01 | 1 | 64 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("TRANSACTION_ID_TYPE"=3 AND "ITEM_ID_TYPE"=5)
filter("TRANSACTION_ID_TYPE"=3 AND "ITEM_ID_TYPE"=5 AND TO_NUMBER("TRANSACTION_ID")=7024793115 AND
TO_NUMBER("ITEM_ID")=7024793115)
Here is indexes on MOE_DATA Table.
INDEX_NAME LAST_ANALYZ COLUMN_NAME Col_Pos TABLESPACE_NAME STATUS INDEX_TYPE NUM_ROWS BLEVEL CF
----------------------------------- ----------- -------------------- ---------- ------------------------------ -------- ---------- ---------- ---------- ---------------
MOE_DATA_STATUS 20-sep-2011 TRANSACTION_ID 1 N/A NORMAL 42268387 2 42218748
TRANSACTION_ID_TYPE 2 N/A NORMAL 2 42218748
ITEM_ID_TYPE 3 N/A NORMAL 2 42218748
ITEM_ID 4 N/A NORMAL 2 42218748
PK_MOE_DATA 01-sep-2011 MESSAGE_ID 1 N/A NORMAL 0 0 0
MESSAGE_VERSION 2 N/A NORMAL 0 0
I want to understand why my query is using INDEX SKIP SCAN? Index 'MOE_DATA_STATUS' is a global hash partitioned(TRANSACTION_ID) index.
Index DDL
CREATE INDEX FINANCIALS.MOE_DATA_STATUS ON FINANCIALS.MOE_DATA (TRANSACTION_ID, TRANSACTION_ID_TYPE, ITEM_ID_TYPE, ITEM_ID)
GLOBAL PARTITION BY HASH (TRANSACTION_ID)
Here is NDV for each column in composite index
TRANSACTION_ID=~51Million (Though this is not a pk)
TRANSACTION_ID_TYPE=7
ITEM_ID_TYPE=5
ITEM_ID =~51Million
Table got 51232733 rows.
I am providing all columns in 'where' predicates which are present in index composition.
Should not i expect 'Index Range scan' or an 'index full scan' ?
I tried putting index_rs_asc,index_rrs and index_rs_desc hints but it could not give me index range scan !
PS: Table is a large hash partitioned table having 64 partitions with one blob column. Blob column is stored in the same tablespace with other columns.