Oracle何时索引空列值？
问题描述：
我曾经认为当列值之一为空时，Oracle不会索引一行。Oracle何时索引空列值？
一些简单的实验表明，情况并非如此。即使某些列可以为空（这当然是一个令人惊喜的），我仍然能够意外地运行一些查询。
谷歌搜索导致一些博客相互矛盾的答案：我已阅读，一排被编入索引，除非所有索引列是空的，并且也是一排编入索引，除非索引的前导列值是空值。
那么，在哪些情况下行不进入索引？这个Oracle版本是特定的吗？
答
如果任何索引列包含非空值，该行将被索引。正如你在下面的例子中可以看到的，只有一行没有被索引，并且这是两个索引列都有NULL的行。您还可以看到，当前导索引列具有NULL值时，Oracle肯定会索引行。
SQL> create table big_table as
2 select object_id as pk_col
3 , object_name as col_1
4 , object_name as col_2
5 from all_objects
6/
Table created.
SQL> select count(*) from big_table
2/
COUNT(*)
----------
69238
SQL> insert into big_table values (9999990, null, null)
2/
1 row created.
SQL> insert into big_table values (9999991, 'NEW COL 1', null)
2/
1 row created.
SQL> insert into big_table values (9999992, null, 'NEW COL 2')
2/
1 row created.
SQL> select count(*) from big_table
2/
COUNT(*)
----------
69241
SQL> create index big_i on big_table(col_1, col_2)
2/
Index created.
SQL> exec dbms_stats.gather_table_stats(user, 'BIG_TABLE', cascade=>TRUE)
PL/SQL procedure successfully completed.
SQL> select num_rows from user_indexes where index_name = 'BIG_I'
2/
NUM_ROWS
----------
69240
SQL> set autotrace traceonly exp
SQL>
SQL> select pk_col from big_table
2 where col_1 = 'NEW COL 1'
3/
Execution Plan
----------------------------------------------------------
Plan hash value: 1387873879
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 60 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 2 | 60 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | BIG_I | 2 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_1"='NEW COL 1')
SQL> select pk_col from big_table
2 where col_2 = 'NEW COL 2'
3/
Execution Plan
----------------------------------------------------------
Plan hash value: 3993303771
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 60 | 176 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| BIG_TABLE | 2 | 60 | 176 (1)| 00:00:03 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL_2"='NEW COL 2')
SQL> select pk_col from big_table
2 where col_1 is null
3 and col_2 = 'NEW COL 2'
4/
Execution Plan
----------------------------------------------------------
Plan hash value: 1387873879
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 53 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 1 | 53 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | BIG_I | 2 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_1" IS NULL AND "COL_2"='NEW COL 2')
filter("COL_2"='NEW COL 2')
SQL> select pk_col from big_table
2 where col_1 is null
3 and col_2 is null
4/
Execution Plan
----------------------------------------------------------
Plan hash value: 3993303771
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 53 | 176 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| BIG_TABLE | 1 | 53 | 176 (1)| 00:00:03 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL_1" IS NULL AND "COL_2" IS NULL)
SQL>
本示例在Oracle 11.1.0.6上运行。但我相当确信它适用于所有版本。
答
除了APC的答案，NULLS被索引在位图索引中。
答
除了APC的答案：当你想索引一个NULL值时，你可以给索引添加一个常量表达式。
例子：
SQL> select * from v$version where rownum = 1
2/
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
1 rij is geselecteerd.
SQL> create table t (id,status,fill)
2 as
3 select level
4 , nullif(ceil((level-1)/1000),0)
5 , lpad('*',1000,'*')
6 from dual
7 connect by level <= 10000
8/
Tabel is aangemaakt.
SQL> select status
2 , count(*)
3 from t
4 group by status
5/
STATUS COUNT(*)
---------- ----------
1 1000
2 1000
3 1000
4 1000
5 1000
6 1000
7 1000
8 1000
9 1000
10 999
1
11 rijen zijn geselecteerd.
SQL> create index i_status on t(status)
2/
Index is aangemaakt.
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true)
PL/SQL-procedure is geslaagd.
SQL> set autotrace traceonly
SQL> select *
2 from t
3 where status is null
4/
1 rij is geselecteerd.
Uitvoeringspan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=201 Card=1 Bytes=1007)
1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=201 Card=1 Bytes=1007)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
364 consistent gets
0 physical reads
0 redo size
1265 bytes sent via SQL*Net to client
242 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
请注意全表扫描和364一致获取。
SQL> set autotrace off
SQL> create index i_status2 on t(status,1)
2/
Index is aangemaakt.
SQL> set autotrace traceonly
SQL> select *
2 from t
3 where status is null
4/
1 rij is geselecteerd.
Uitvoeringspan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=1007)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=1 Card=1 Bytes=1007)
2 1 INDEX (RANGE SCAN) OF 'I_STATUS2' (INDEX) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
1 physical reads
0 redo size
1265 bytes sent via SQL*Net to client
242 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
现在它使用索引并且只有3个一致的获取。
Regards， Rob。