According to Oracle Doc, Oracle does not index table rows in which all key columns are null except in the case of bitmap indexes (Database SQL Language Reference).
Therefore, in a composite B-tree index, if there exists one column which contains a non-null value, the row will be indexed. Since local prefixed indexes is a composite index which includes partition keys on the leading edge of the index definition, and partition keys are never all null, all rows are always indexed.
For applications, it means that local prefixed indexes is always usable, but not local nonprefixed indexes. In the performance point of view, Column Null Values is a torque on Local prefixed vs. nonprefixed indexes (Number_of_Null_Columns x Number_of_Rows).
Taking the same example from Expert Oracle Database Architecture Page 593, but updating Column b of one row in PART_1 as NULL.
create table partitioned_table
( a int,
b int,
data char(20)
)
partition by range (a)
(
partition part_1 values less than(2) tablespace p1,
partition part_2 values less than(3) tablespace p2
)
/
create index local_prefixed on partitioned_table (a,b) local;
create index local_nonprefixed on partitioned_table (b) local;
insert into partitioned_table
select mod(rownum-1,2)+1, rownum, 'x' from all_objects where rownum <= 1000;
update partitioned_table set b=null where a=1 and b=99;
begin
dbms_stats.gather_table_stats
( user,
'PARTITIONED_TABLE',
cascade=>TRUE );
end;
/
we can see PART_1 having one row with Column b being NULL:
select a, count(*), count(a), count(b)
from partitioned_table group by a;
A COUNT(*) COUNT(A) COUNT(B)
---------- ---------- ---------- ----------
1 500 500 499
2 500 500 500
select index_name, partition_name, num_rows
from dba_ind_partitions
where index_name in ('LOCAL_PREFIXED', 'LOCAL_NONPREFIXED');
INDEX_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
LOCAL_NONPREFIXED PART_1 499
LOCAL_NONPREFIXED PART_2 500
LOCAL_PREFIXED PART_1 500
LOCAL_PREFIXED PART_2 500
Now if we run following queries, all of them will use LOCAL_PREFIXED index.
select /*+ index(t local_prefixed) */ count(a) from partitioned_table t where a = 1 and b is null;
select /*+ index(t local_nonprefixed) */ count(a) from partitioned_table t where a = 1 and b is null;
select /*+ index(t local_prefixed) */ count(a) from partitioned_table t where b is null;
select /*+ index(t local_nonprefixed) */ count(a) from partitioned_table t where b is null;
select /*+ index(t local_prefixed) */ count(a) from partitioned_table t where a is null and b is null;
select /*+ index(t local_nonprefixed) */ count(a)
from partitioned_table t where a is null and b is null;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 1 (0)| | |
| 1 | SORT AGGREGATE | | 1 | 7 | | | |
| 2 | PARTITION RANGE SINGLE| | 1 | 7 | 1 (0)| 1 | 1 |
|* 3 | INDEX RANGE SCAN | LOCAL_PREFIXED | 1 | 7 | 1 (0)| 1 | 1 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"=1 AND "B" IS NULL)
For the queries with partition key being NULL, PARTITION RANGE EMPTY elimination is used.
Even though PART_2 does not contain any rows whose Column b being NULL, LOCAL_NONPREFIXED index can neither be used in the following queries.
select /*+ index(t local_prefixed) */ count(a) from partitioned_table t where a = 2 and b is null;
select /*+ index(t local_nonprefixed) */ count(a) from partitioned_table t where a = 2 and b is null;
select /*+ index(t local_nonprefixed) */ count(a)
from partitioned_table partition (part_2) t where b is null;
