Thanks for the question, ezio.
Asked: December 10, 2015 - 9:41 am UTC
Last updated: December 14, 2015 - 6:50 am UTC
Version: 10g
Viewed 1000+ times
You Asked
Hi:
I know that Oracle CBO will not choose a index which may have null value in it.
But when I give a hint on the select statement ,it used that index ,that is why?
The index is defined as below:
create index DCDM.WIP_LOT_MST_STATUS on DCDM.WIP_LOT_MASTER (LOT_STATUS)
tablespace DCDM_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
The SQL shows below:
SQL> SELECT/*+INDEX(a WIP_LOT_MST_STATUS)*/ *
FROM dcdm.wip_lot_master a
WHERE lot_status NOT IN ('Finished', 'Terminated'); 2 3
18494 rows selected.
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 926K| 511M| 43006
|
| 1 | TABLE ACCESS BY INDEX ROWID| WIP_LOT_MASTER | 926K| 511M| 43006
|
| 2 | INDEX FULL SCAN | WIP_LOT_MST_STATUS | 926K| | 4210
|
-------
SQL> select count(*) from dcdm.wip_lot_master where lot_status is null;
COUNT(*)
----------
185
and Connor said...
WHERE lot_status NOT IN ('Finished', 'Terminated');
is NOT the same as
WHERE lot_status NOT IN ('Finished', 'Terminated') OR lot_status is null.
Your predicate has implied you *only* want the non-null entries, and hence the index can be used.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment