oracle正确使用索引,通过案例学调优之--Oracle中null使用索引
通過案例學調優之--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?processed
4、建立復合索引(其他列為 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值時，該行才不能被索引。
總結
以上是生活随笔為你收集整理的oracle正确使用索引,通过案例学调优之--Oracle中null使用索引的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： php取指定长度,php截取指定长度的简
- 下一篇： oracle删除无效归档日志,求助：rm