文章出處
不走orderby的情況，今天測試下，查詢語句忽略order by的情況。創建測試表并插入數據：create table testindx(id number,name varchar2(10),sex varchar2(2),age int,high float,weight float);begin for i in 1..100 loop insert into testindx values(i,i||'name','男',i,i,i); end loop;end;在id列上創建索引：create index ind_testindx_id on testindx(id);查詢并查看執行計劃：1. 過濾為索引列，order by為非索引列SQL> explain plan for select count(1) from testindx where id=3 order by name; Explained SQL> select * from table(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 2764140301--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00| 1 | SORT AGGREGATE | | 1 | 20 | ||* 2 | INDEX RANGE SCAN| IND_TESTINDX_ID | 1 | | 1 (0)| 00:00--------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("ID"=3)Note----- - dynamic sampling used for this statement (level=2) 18 rows selected2. 過濾為索引列，order by為相同索引列SQL> set autot on;SQL> select * from testindx where id=3 order by id; ID NAME SE AGE HIGH WEIGHT---------- ---------- -- ---------- ---------- ---------- 3 3name 男 3 3 3執行計劃----------------------------------------------------------Plan hash value: 899881125-----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 62 | 2(0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TESTINDX | 1 | 62 | 2(0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IND_TESTINDX_ID | 1 | | 1(0)| 00:00:01 |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("ID"=3)Note----- - dynamic sampling used for this statement (level=2)統計信息---------------------------------------------------------- 7 recursive calls 0 db block gets 13 consistent gets 0 physical reads 0 redo size 873 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed忽略了order by3.過濾為索引列，order by為不同索引列在age列上創建索引create index ind_testindx_age on testindx(age);SQL> set autotrace traceonlySQL> select * from testindx where id=3 order by age;執行計劃----------------------------------------------------------Plan hash value: 1192112165------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 62 | 3(34)| 00:00:01 || 1 | SORT ORDER BY | | 1 | 62 | 3(34)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID| TESTINDX | 1 | 62 | 2 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | IND_TESTINDX_ID | 1 | | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("ID"=3)Note----- - dynamic sampling used for this statement (level=2)統計信息---------------------------------------------------------- 9 recursive calls 0 db block gets 13 consistent gets 0 physical reads 0 redo size 869 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed4.全都非索引列SQL> select * from testindx where name='3name' order by name; ID NAME SE AGE HIGH WEIGHT---------- ---------- -- ---------- ---------- ---------- 3 3name 男 3 3 3執行計劃----------------------------------------------------------Plan hash value: 1210584362------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 62 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TESTINDX | 1 | 62 | 3 (0)| 00:00:01 |------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("NAME"='3name')Note----- - dynamic sampling used for this statement (level=2)統計信息---------------------------------------------------------- 0 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 869 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed沒走索引，忽略了order by。那么又想起了一個情況，做語句優化的時候，會發現count(1)+order by的情況，那么在2,4下，會走order by么？2.select count(1) from testindx where id=3 order by id;SQL> select count(1) from testindx where id=3 order by id; COUNT(1)---------- 1執行計劃----------------------------------------------------------Plan hash value: 2764140301-------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 13 | | ||* 2 | INDEX RANGE SCAN| IND_TESTINDX_ID | 1 | 13 | 1 (0)| 00:00:01 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("ID"=3)Note----- - dynamic sampling used for this statement (level=2)統計信息---------------------------------------------------------- 0 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 527 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed4.select count(1) from testindx where name='3name' order by name;SQL> select count(1) from testindx where name='3name' order by name; COUNT(1)---------- 1執行計劃----------------------------------------------------------Plan hash value: 2984520020-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 7 | | ||* 2 | TABLE ACCESS FULL| TESTINDX | 1 | 7 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("NAME"='3name')Note----- - dynamic sampling used for this statement (level=2)統計信息---------------------------------------------------------- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 527 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed那么，建議來了：1. 如果過濾列與排序列相同，建議在該列創建索引2. 如果count(1)就不要寫order by了看文倉www.kanwencang.com網友整理上傳,為您提供最全的知識大全,期待您的分享，轉載請注明出處。
歡迎轉載：http://www.kanwencang.com/bangong/20170216/102232.html
文章列表
全站熱搜
留言列表
