NULL与索引
B-Tree索引不会存储全为NULL的列（B-Tree聚簇索引可以存储全为NULL的列），
但复合索引中如果有非NULL的列，就可以存储。
因为B-Tree索引不存储全为NULL的列，有时会导致SQL语句不走索引而影响效率。
点击(此处)折叠或打开
-
DROP TABLE t;
-
-
CREATE TABLE t(a NUMBER, b NUMBER);
-
CREATE UNIQUE INDEX uidx_t_a_b ON t(a, b);
-
INSERT INTO t VALUES(NULL, NULL);
-
INSERT INTO t VALUES(1, NULL);
-
INSERT INTO t VALUES(NULL, 1);
-
COMMIT;
-
-
BEGIN
-
dbms_stats.gather_table_stats(ownname => USER, tabname => \'T\');
-
dbms_stats.gather_index_stats(ownname => USER, indname => \'UIDX_T_A_B\');
-
END;
-
/
-
-
--查看索引中存储的数据的行数. 结论: NULL, NULL所对应的行并没有存储在索引中.(3个记录,索引中只存储了2个)
-
SELECT index_name, num_rows FROM user_indexes WHERE index_name = \'UIDX_T_A_B\';
-
INDEX_NAME NUM_ROWS
-
------------------------------ ----------
-
UIDX_T_A_B 2
-
DROP TABLE t;
-
-
-
--给列b添加NOT NULL约束
-
CREATE TABLE t(a NUMBER, b NUMBER NOT NULL);
-
CREATE UNIQUE INDEX uidx_t_a_b ON t(a, b);
-
-
INSERT INTO t VALUES(1, 1);
-
INSERT INTO t VALUES(NULL, 2);
-
COMMIT;
-
-
BEGIN
-
dbms_stats.gather_table_stats(ownname => USER, tabname => \'T\');
-
dbms_stats.gather_index_stats(ownname => USER, indname => \'UIDX_T_A_B\');
-
-
END;
-
/
-
-
--查看索引中存储的数据的行数
-
SELECT index_name, num_rows FROM user_indexes WHERE index_name = \'UIDX_T_A_B\';
-
INDEX_NAME NUM_ROWS
-
------------------------------ ----------
-
UIDX_T_A_B 2
-
-
--此时执行查询, where条件中包含is null和is not null都可以走索引了
-
SET AUTOTRACE ON
- SELECT * FROM t WHERE a IS NULL;
点击(此处)折叠或打开
- SELECT * FROM t WHERE a IS NOT NULL;
点击(此处)折叠或打开
-
SET AUTOTRACE OFF
-
-
--场景：某列有一些NULL值,但占的比例很少, 希望IS NULL条件可以走索引.
-
-- 但又不想建立复合索引或增加NOT NULL约束.
-
-
DROP TABLE t;
-
CREATE TABLE t(a NUMBER, b NUMBER);
-
INSERT INTO t VALUES(NULL, 1);
-
INSERT INTO t VALUES(NULL, NULL);
-
INSERT INTO t VALUES(NULL, NULL);
-
INSERT INTO t VALUES(NULL, 2);
-
INSERT INTO t VALUES(3, NULL);
-
COMMIT;
-
-
--再多插入一些非NULL值
-
BEGIN
-
FOR i IN 1..10000 LOOP
-
INSERT INTO t VALUES(i, i);
-
END LOOP;
-
COMMIT;
-
END;
-
/
-
-
CREATE INDEX idx_t_a_0 ON t(a, 0);
-
BEGIN
-
dbms_stats.gather_table_stats(ownname => USER, tabname => \'T\');
-
dbms_stats.gather_index_stats(ownname => USER, indname => \'IDX_T_A_0\');
-
END;
-
/
-
-
SELECT index_name, num_rows FROM user_indexes WHERE index_name = \'IDX_T_A_0\';
-
-
INDEX_NAME NUM_ROWS
-
------------------------------ ----------
-
IDX_T_A_0 10005
-
-
SET autotrace ON
-
SELECT * FROM t WHERE a IS NULL;
-
A B
-
---------- ----------
-
1
-
-
- 2
点击(此处)折叠或打开
-
SET autotrace OFF
-
-
--场景: 一个工单系统, 列status只有处理和未处理2种状态. 未处理的很少,最多10%; 已经处理的很多, 90%.
-
-- 经常查询未处理的,对已处理的查询很少.
-
-- 为了节省维护索引的开销、降低索引的存储空间,可以对未处理的记录索引,已处理的不存储在索引中.
-
-
DROP TABLE t;
-
--列a只可以取2个值（0-未处理, 1-已处理)
-
CREATE TABLE t(a NUMBER, b NUMBER);
-
INSERT INTO t VALUES(0, 1)
-
-
--大量插入已经处理的数据
-
BEGIN
-
FOR i IN 1..10000 LOOP
-
INSERT INTO t VALUES(1, i);
-
END LOOP;
-
COMMIT;
-
END;
-
/
-
-
--列a不为0的都转换为NULL,不会存储到索引中(函数索引)
-
CREATE INDEX idx_t_a ON t(DECODE(a, 0, 0, NULL));
-
-
BEGIN
-
dbms_stats.gather_table_stats(ownname => USER, tabname => \'T\');
-
dbms_stats.gather_index_stats(ownname => USER, indname => \'IDX_T_A\');
-
END;
-
/
-
-
--确定索引中存储的行数
-
SELECT index_name, num_rows FROM user_indexes WHERE index_name = \'IDX_T_A\';
-
INDEX_NAME NUM_ROWS
-
------------------------------ ----------
-
IDX_T_A 0
-
-
-
SET autotrace ON EXPLAIN
-
SELECT * FROM t WHERE DECODE(a, 0, 0, NULL) = 0;
-
-
A B
-
---------- ----------
- 0 1
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/17013648/viewspace-1070819/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
用>=替代>
下一篇：
WHERE IN LIST
请登录后发表评论
登录
全部评论
最新文章
- 2015.09.18 网络公开课 《Oracle 11G OCP试题精讲（4）》
- 2015 最新OCM 10g考试资料汇总 Skillset 7（1）
- 2015 最新OCM 10g考试资料汇总 Skillset 6（2）
- 2015 最新OCM 10g考试资料汇总 Skillset 6（1）
- 2015.05.15 网络公开课 《Oracle 11G RAC深入探索之RAC和RAC One Node之间的切换》
- 2015 最新OCM 10g考试资料汇总 Skillset 5（5）
- 2015 最新OCM 10g考试资料汇总 Skillset 5（3）
- 2015 最新OCM 10g考试资料汇总 Skillset 5（2）
- 2015.04.10 网络公开课 《玩转DG系列之-使用RMAN创建物理DG》
- 2015 最新OCM 10g考试资料汇总 Skillset 5（1）
