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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8984272/viewspace-619966/