溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
通過案例學調優之--Oracle中null使用索引
默認情況下，Oracle數據庫，null在Index上是不被存儲的，當在索引列以“is null”的方式訪問時，無法使用索引；本案例，主要向大家演示如何在存在null的索引列上，使用“is null”訪問索引。
案例分析：
1、建立表和普通索引
13:52:23 SCOTT@ prod >create table t2 (x int,y int);
Table created.
14:00:11 SCOTT@ prod >insert into t2 values (1,1);
1 row created.
Elapsed: 00:00:00.04
14:00:21 SCOTT@ prod >insert into t2 values (1,null);
1 row created.
Elapsed: 00:00:00.00
14:00:31 SCOTT@ prod >insert into t2 values (null,1);
1 row created.
Elapsed: 00:00:00.00
14:00:37 SCOTT@ prod >insert into t2 values (null,null);
1 row created.
Elapsed: 00:00:00.00
14:00:44 SCOTT@ prod >commit;
Commit complete.
Elapsed: 00:00:00.04
14:06:41 SCOTT@ prod >select * from t2;
X Y
---------- ----------
1 1
1
1
14:36:12 SCOTT@ prod >create index t2_ind on t2(x);
Index created.
14:49:38 SCOTT@ prod >select index_name,table_name,num_rows from user_indexes where index_name='T2_IND';
INDEX_NAME TABLE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
T2_IND T2 3
在索引中只有3行，在最后一行字段全為null值，沒有被存儲！
14:36:27 SCOTT@ prod >exec dbms_stats.gather_index_stats(user,'T2_IND');
PL/SQL procedure successfully completed.
14:37:29 SCOTT@ prod >select * from t2 where x=1;
X Y
---------- ----------
1 1
1
Execution Plan
----------------------------------------------------------
Plan hash value: 1173409066
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 2 | 8 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T2_IND | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
519 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
14:37:45 SCOTT@ prod >select * from t2 where x is not null;
X Y
---------- ----------
1 1
1
Execution Plan
----------------------------------------------------------
Plan hash value: 463061910
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 2 | 8 | 2 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | T2_IND | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("X" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
519 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
14:38:00 SCOTT@ prod >select * from t2 where x is null;
X Y
---------- ----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 2 | 8 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
508 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
當x通過“is null”訪問時，Oracle選擇了“full table scan”方式。2、通過建立常量復合索引
14:38:55 SCOTT@ prod >create index t2_ind on t2(x,0);
Index created.
14:49:38 SCOTT@ prod >select index_name,table_name,num_rows from user_indexes where index_name='T2_IND';
INDEX_NAME TABLE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
T2_IND T2 4
索引塊上存儲了表中所用的行。
14:39:50 SCOTT@ prod >select * from t2 where x is null;
X Y
---------- ----------
1
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1173409066
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 2 | 8 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T2_IND | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
508 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
對于x通過“is null”訪問時，也能通過索引訪問了！3、建立復合索引（其他列為null）
13:59:40 SCOTT@ prod >create index x_ind on t2(x,y);
Index created.
14:08:29 SCOTT@ prod >EXEC dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T2');
PL/SQL procedure successfully completed.
14:09:22 SCOTT@ prod >EXEC dbms_stats.gather_index_stats(ownname=>USER,indname=>'X_IND');
PL/SQL procedure successfully completed.
14:09:58 SCOTT@ prod >select index_name,num_rows from user_indexes where index_name='X_IND';
INDEX_NAME NUM_ROWS
------------------------------ ----------
X_IND 3
14:10:50 SCOTT@ prod >select count(*) from t2;
COUNT(*)
----------
4
14:11:28 SCOTT@ prod >set autotrace on
14:12:33 SCOTT@ prod >select * from t2 where x=1;
X Y
---------- ----------
1 1
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3708139238
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| X_IND | 2 | 8 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
512 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
14:12:47 SCOTT@ prod >select * from t2 where x is not null;
X Y
---------- ----------
1 1
1
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3776680409
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | X_IND | 2 | 8 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
512 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
14:13:08 SCOTT@ prod >select * from t2 where x is null;
X Y
---------- ----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 2 | 8 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
508 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
如果，復合索引列其他列也為null，在查詢使用’is null‘條件時，仍然為“full table scan”。
14:13:52 SCOTT@ prod >select * from t2 where x=1 and y is null;
X Y
---------- ----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3708139238
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| X_IND | 1 | 4 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X"=1 AND "Y" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
471 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
14:16:16 SCOTT@ prod >select * from t2 where x is null and y=1;
X Y
---------- ----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3708139238
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| X_IND | 1 | 4 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X" IS NULL AND "Y"=1)
filter("Y"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
471 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed4、建立復合索引（其他列為 not null）
15:13:38 SCOTT@ prod >desc t2;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
X NUMBER(38)
Y NUMBER(38)
15:13:43 SCOTT@ prod >alter table t2 modify (y NUMBER(38) not null);
Table altered.
15:14:01 SCOTT@ prod >desc t2;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
X NUMBER(38)
Y NOT NULL NUMBER(38)
15:12:54 SCOTT@ prod >insert into t2 values (1,1);
1 row created.
Elapsed: 00:00:00.02
15:13:02 SCOTT@ prod >insert into t2 values (null,1);
1 row created.
Elapsed: 00:00:00.00
15:13:12 SCOTT@ prod >insert into t2 values (null,2);
1 row created.
Elapsed: 00:00:00.00
15:13:36 SCOTT@ prod >commit;
Commit complete.
15:15:00 SCOTT@ prod >create index t2_ind on t2 (x,y);
Index created.
15:15:29 SCOTT@ prod >exec dbms_stats.gather_table_stats(user,'T2',cascade=>true);
PL/SQL procedure successfully completed.
15:16:09 SCOTT@ prod >select index_name,table_name,num_rows from user_indexes where index_name='T2_IND';
INDEX_NAME TABLE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
T2_IND T2 3
15:17:20 SCOTT@ prod >set autotrace trace
15:17:26 SCOTT@ prod >SELECT * from t2 where x is null
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2876512201
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 10 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| T2_IND | 2 | 10 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X" IS NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
510 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
在復合索引中，如果其他列為not null，則在“is null”條件下，仍然可以使用索引訪問。結論：
對于普通的索引，null值不能進行索引的正確理解應該是，對于某一行，索引的所有列的值都是null值時，該行才不能被索引。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
