概述

由於業務的需要：查看某列值為NULL的記錄有多少。這是一個十分簡單的需求，同時也很容易實現。無非就是使用如下語句：

SELECT * FROM table WHERE xx IS NULL; 

結果得出的結果只有幾十條記錄,但是執行的時間比我想象中的長了許多。一般這樣的查詢只是要秒秒鐘的問題，但是卻花了十幾秒，而平時使用IS NOT NULL也沒有那麼慢啊。所以後面抽空就做了個實驗來簡單測試下。

基礎數據準備

DROP TABLE t PURGE; 
CREATE TABLE t(a NUMBER); 
CREATE INDEX idx_t ON t(a); 
INSERT INTO t VALUES(NULL); 
INSERT INTO t VALUES(NULL); 
INSERT INTO t VALUES(1); 
INSERT INTO t VALUES(1); 
INSERT INTO t VALUES(1); 
INSERT INTO t VALUES(1); 
INSERT INTO t VALUES(1); 
INSERT INTO t VALUES(1); 
INSERT INTO t VALUES(1); 
COMMIT; 
--收集表統計信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'T'); 
EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname=>'SCOTT', indname=>'IDX_T'); 

對比執行計劃

SET NULL UNKOWN --設置如果只為NULL就以UNKOWN顯示 
SET AUTOTRACE ON EXPLAIN 
SELECT * FROM t WHERE a IS NULL; 
SELECT * FROM t WHERE a IS NOT NULL; 

可以看出使用IS NULL時候沒有使用索引，而使用IS NOT NULL有使用索引。

查看IDX_T索引的所在的列

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME FROM USER_IND_COLUMNS WHERE INDEX_NAME = 'IDX_T'; 

很明顯的展示了在表T的a列上是有索引的

查看索引統計信息

SELECT index_name, num_rows from user_indexes where index_name = 'IDX_T'; 

從上面可以看出索引的統計信息的NUM_ROWS字段只有7行，而實際的數據總量是9行。按道理應該索引的NUM_ROWS應該是9才對缺少了2，很明顯少了值為NULL的那2行。原來索引是不將NULL值的行記錄在索引中的。

創建一個偽列的索引

SET AUTOTRACE OFF 
DROP INDEX idx_t; 
CREATE INDEX idx_t ON t(a, 0); 
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'T'); 
EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname=>'SCOTT', indname=>'IDX_T'); 
SET AUTOTRACE ON EXPLAIN 
SELECT * FROM t WHERE a IS NULL; 
SELECT * FROM t WHERE a IS NOT NULL; 

從上面可以看到IS NULL 走索引了，IS NOT NULL 也走索引。

查看索引統計信息

SELECT index_name, num_rows from user_indexes where index_name = 'IDX_T'; 

索引的統計信息是9和表的行數是一樣的。

篇幅有限，就寫到這裡了，這裡大家可以抽空自己用函數索引來測試下，看到的num_rows也會不一樣的，後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注一下~

閱讀更多 波波說運維 的文章

關鍵字: 結果 平板電腦 幾十條