10g中function index無法避免排序
SQL> alter session set nls_language=american;
Session altered.
SQL> create table test_sort(name,object_name) as select 'select',object_name from
2 dba_objects;
Table created.
SQL> create index i_test_sort_1 on test_sort(substr(name,0,3),object_name);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'TEST_SORT');
PL/SQL procedure successfully completed.
SQL> set autot trace
SQL> select * from (select * from test_sort where substr(name,0,3)='sel' order by
2 object_name) where rownum<11;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4202652051
--------------------------------------------------------------------------------
-------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU
)| Time |
--------------------------------------------------------------------------------
-------------
| 0 | SELECT STATEMENT | | 10 | 740 | | 504 (3
)| 00:00:07 |
|* 1 | COUNT STOPKEY | | | | |
| |
| 2 | VIEW | | 49902 | 3606K| | 504 (3
)| 00:00:07 |
|* 3 | SORT ORDER BY STOPKEY| | 49902 | 1559K| 3928K| 504 (3
)| 00:00:07 |
|* 4 | TABLE ACCESS FULL | TEST_SORT | 49902 | 1559K| | 60 (5
)| 00:00:01 |
--------------------------------------------------------------------------------
-------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<11)
3 - filter(ROWNUM<11)
4 - filter(SUBSTR("NAME",0,3)='sel')
Statistics
----------------------------------------------------------
139 recursive calls
0 db block gets
279 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
10 rows processed
sql doesn't choose index ,so I use hint to force oracle choose index
SQL> select * from (select /*+ index(test_sort) */ * from test_sort where substr(name,0,3)='sel'
2 order by object_name) where rownum<11;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1978014138
--------------------------------------------------------------------------------
------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc|
Cost (%CPU)| Time |
--------------------------------------------------------------------------------
------------------------
| 0 | SELECT STATEMENT | | 10 | 740 | |
22869 (1)| 00:04:35 |
|* 1 | COUNT STOPKEY | | | | |
| |
| 2 | VIEW | | 49902 | 3606K| |
22869 (1)| 00:04:35 |
|* 3 | SORT ORDER BY STOPKEY | | 49902 | 1559K| 3928K|
22869 (1)| 00:04:35 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST_SORT | 49902 | 1559K| |
22424 (1)| 00:04:30 |
|* 5 | INDEX RANGE SCAN | I_TEST_SORT_1 | 49902 | | |
278 (1)| 00:00:04 |
--------------------------------------------------------------------------------
------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<11)
3 - filter(ROWNUM<11)
5 - access(SUBSTR("NAME",0,3)='sel')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
22393 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
From the explain ,the sql chooses the index,but SORT ORDER BY STOPKEY doesn't avoid,so oracle
must read all rows satisfy substr(name,0,3)='sel' ,the I rewrite the sql as the following,we can see
that sort ORDER BY STOPKEY does't happen,so oracle return 10 rows fast.
SQL> select * from (select /*+ index(test_sort) */ * from test_sort where substr(name,0,3)='sel'
2 order by substr(name,0,3),object_name) where rownum<11;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4154852915
--------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time |
--------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 10 | 740 | 8
(0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | |
| |
| 2 | VIEW | | 10 | 740 | 8
(0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_SORT | 49902 | 1559K| 8
(0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_TEST_SORT_1 | 10 | | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------------
---------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<11)
4 - access(SUBSTR("NAME",0,3)='sel')
filter(SUBSTR("NAME",0,3)='sel')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
以上測試版本:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
11g已經解決這問題了
SQL> create table test_sort(name,object_name) as select 'select',object_name fro
m
2 dba_objects;
表已建立。
SQL> create index i_test_sort_1 on test_sort(substr(name,0,3),object_name);
索引已建立。
SQL> exec dbms_stats.gather_table_stats(user,'TEST_SORT');
PL/SQL 過程已成功完成。
SQL> set autot trace
SQL> select * from (select * from test_sort where substr(name,0,3)='sel' order b
y
2 object_name) where rownum<11;
已選擇10行。
執行計劃
----------------------------------------------------------
Plan hash value: 4154852915
--------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time |
--------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 10 | 740 | 8
(0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | |
| |
| 2 | VIEW | | 10 | 740 | 8
(0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_SORT | 10 | 350 | 8
(0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_TEST_SORT_1 | | | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------------
---------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<11)
4 - access(SUBSTR("NAME",0,3)='sel')
統計資訊
----------------------------------------------------------
140 recursive calls
0 db block gets
372 consistent gets
0 physical reads
0 redo size
696 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select * from v$version;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
itpub原帖子:
http://www.itpub.net/viewthread.php?tid=1148854&pid=13305469&page=1&extra=#pid13305469
Session altered.
SQL> create table test_sort(name,object_name) as select 'select',object_name from
2 dba_objects;
Table created.
SQL> create index i_test_sort_1 on test_sort(substr(name,0,3),object_name);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'TEST_SORT');
PL/SQL procedure successfully completed.
SQL> set autot trace
SQL> select * from (select * from test_sort where substr(name,0,3)='sel' order by
2 object_name) where rownum<11;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4202652051
--------------------------------------------------------------------------------
-------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU
)| Time |
--------------------------------------------------------------------------------
-------------
| 0 | SELECT STATEMENT | | 10 | 740 | | 504 (3
)| 00:00:07 |
|* 1 | COUNT STOPKEY | | | | |
| |
| 2 | VIEW | | 49902 | 3606K| | 504 (3
)| 00:00:07 |
|* 3 | SORT ORDER BY STOPKEY| | 49902 | 1559K| 3928K| 504 (3
)| 00:00:07 |
|* 4 | TABLE ACCESS FULL | TEST_SORT | 49902 | 1559K| | 60 (5
)| 00:00:01 |
--------------------------------------------------------------------------------
-------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<11)
3 - filter(ROWNUM<11)
4 - filter(SUBSTR("NAME",0,3)='sel')
Statistics
----------------------------------------------------------
139 recursive calls
0 db block gets
279 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
10 rows processed
sql doesn't choose index ,so I use hint to force oracle choose index
SQL> select * from (select /*+ index(test_sort) */ * from test_sort where substr(name,0,3)='sel'
2 order by object_name) where rownum<11;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1978014138
--------------------------------------------------------------------------------
------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc|
Cost (%CPU)| Time |
--------------------------------------------------------------------------------
------------------------
| 0 | SELECT STATEMENT | | 10 | 740 | |
22869 (1)| 00:04:35 |
|* 1 | COUNT STOPKEY | | | | |
| |
| 2 | VIEW | | 49902 | 3606K| |
22869 (1)| 00:04:35 |
|* 3 | SORT ORDER BY STOPKEY | | 49902 | 1559K| 3928K|
22869 (1)| 00:04:35 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST_SORT | 49902 | 1559K| |
22424 (1)| 00:04:30 |
|* 5 | INDEX RANGE SCAN | I_TEST_SORT_1 | 49902 | | |
278 (1)| 00:00:04 |
--------------------------------------------------------------------------------
------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<11)
3 - filter(ROWNUM<11)
5 - access(SUBSTR("NAME",0,3)='sel')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
22393 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
From the explain ,the sql chooses the index,but SORT ORDER BY STOPKEY doesn't avoid,so oracle
must read all rows satisfy substr(name,0,3)='sel' ,the I rewrite the sql as the following,we can see
that sort ORDER BY STOPKEY does't happen,so oracle return 10 rows fast.
SQL> select * from (select /*+ index(test_sort) */ * from test_sort where substr(name,0,3)='sel'
2 order by substr(name,0,3),object_name) where rownum<11;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4154852915
--------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time |
--------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 10 | 740 | 8
(0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | |
| |
| 2 | VIEW | | 10 | 740 | 8
(0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_SORT | 49902 | 1559K| 8
(0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_TEST_SORT_1 | 10 | | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------------
---------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<11)
4 - access(SUBSTR("NAME",0,3)='sel')
filter(SUBSTR("NAME",0,3)='sel')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
以上測試版本:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
11g已經解決這問題了
SQL> create table test_sort(name,object_name) as select 'select',object_name fro
m
2 dba_objects;
表已建立。
SQL> create index i_test_sort_1 on test_sort(substr(name,0,3),object_name);
索引已建立。
SQL> exec dbms_stats.gather_table_stats(user,'TEST_SORT');
PL/SQL 過程已成功完成。
SQL> set autot trace
SQL> select * from (select * from test_sort where substr(name,0,3)='sel' order b
y
2 object_name) where rownum<11;
已選擇10行。
執行計劃
----------------------------------------------------------
Plan hash value: 4154852915
--------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time |
--------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 10 | 740 | 8
(0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | |
| |
| 2 | VIEW | | 10 | 740 | 8
(0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_SORT | 10 | 350 | 8
(0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_TEST_SORT_1 | | | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------------
---------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<11)
4 - access(SUBSTR("NAME",0,3)='sel')
統計資訊
----------------------------------------------------------
140 recursive calls
0 db block gets
372 consistent gets
0 physical reads
0 redo size
696 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select * from v$version;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
itpub原帖子:
http://www.itpub.net/viewthread.php?tid=1148854&pid=13305469&page=1&extra=#pid13305469
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8984272/viewspace-619966/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Function-based Index and Or-ExpansionFunctionIndex
- rebuild index 排序RebuildIndex排序
- ORA-28348, encryption column TDE, function indexFunctionIndex
- use the function_based index to access the null valueFunctionIndexNull
- alter index unusable無法起作用的情況Index
- admin\index.jsp中的main.jsp無法顯示？IndexJSAI
- 索引無法消除排序的問題索引排序
- javascript 中function(){},new function(),new Function(),Function 摘錄JavaScriptFunction
- 關於函式索引(function-based index)函式索引FunctionIndex
- alter index unusable 無法起作用的情況 ztIndex
- 避免不必要的排序(二)排序
- 避免不必要的排序(一)排序
- @dbsnake-用合適的函式索引來避免看似無法避免的全表掃描函式索引
- jQuery中$(function(){})jQueryFunction
- ThinkPHP5 + nginx配置(index.php無法訪問404)PHPNginxIndex
- Oracle 10g(10.1.0.2)中的OPTIMIZER_INDEX_COST_ADJ(轉)Oracle 10gIndex
- 排序：交換排序——氣泡排序法排序
- Default behavior of create & rebuild index in 10G (zt)RebuildIndex
- 基於函式的索引（function-based index，FBI）函式索引FunctionIndex
- Oracle 10g EM無法啟動的修復方法Oracle 10g
- 【HINT】使用“NO_INDEX ”Hint提示避免SQL使用特定索引IndexSQL索引
- JavaScript中的 FunctionJavaScriptFunction
- 10g可以通過命令使index unusable！Index
- Oracle 10g安裝程式無法執行的情況Oracle 10g
- 快速排序法排序
- 交換排序法排序
- shell排序法排序
- Intel製造模式漏洞曝光，蘋果公司也無法避免此影響Intel模式蘋果
- 高效的SQL（index range scan優化排序）SQLIndex優化排序
- 建立index時候要用到排序空間Index排序
- android 中ImageView 無法居中AndroidView
- class中的arrow functionFunction
- java.util.function 中的 Function、Predicate、ConsumerJavaFunction
- 10G密碼版本使用者無法登陸問題密碼
- oracle 10g asm windows 無法找到磁碟 的問題解決Oracle 10gASMWindows
- 外掛後臺表單無法按照所寫規則排序排序
- 【Azure 應用服務】Azure Function整合虛擬網路，設定被同在虛擬網路中的Storage Account觸發，遇見Function無法觸發的問題Function
- （四）桶排序法排序