今天测试下,查询语句忽略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 selected
2. 过滤为索引列,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 (%C
PU)| 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 by
3.过滤为索引列,order by为不同索引列
在age列上创建索引
create index ind_testindx_age on testindx(age);
SQL> set autotrace traceonly
SQL> 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 processed
4.全都非索引列
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 processed
4.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了
不走order by的情况
最新推荐文章于 2022-07-01 07:34:30 发布