通过案例学调优之--Oracle中null使用索引
时间:2022-03-14 02:59
通过案例学调优之--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”方式。
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 processed
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值时,该行才不能被索引。 本文出自 “” 博客,请务必保留此出处