--1.
取前
10
行
select * from hr.employees where rownum<=10
--2. 按照first_name升序,取前10位
--正确方法 oracle处理机制: --> hr.employees全表扫描
--> SORT ORDER BY STOPKEY 只排序前10行,作为一个矩阵结构
-->剩下的行与第10行进行比较,合适的进入矩阵,否则抛弃
--优点:RAM中少量排序,速度快(不需要在内存或者temp表空间进行全表排序), 并不真正排序整个结果集,但概念上做了整个结果集的排序
--注意第一,二个rownum的区别
--正确方法 oracle处理机制: --> hr.employees全表扫描
--> SORT ORDER BY STOPKEY 只排序前10行,作为一个矩阵结构
-->剩下的行与第10行进行比较,合适的进入矩阵,否则抛弃
--优点:RAM中少量排序,速度快(不需要在内存或者temp表空间进行全表排序), 并不真正排序整个结果集,但概念上做了整个结果集的排序
--注意第一,二个rownum的区别
select rownum,t.* from (select rownum,employees.* from hr.employees order by first_name) t where rownum<=10
--
执行计划
SELECT STATEMENT, GOAL = CHOOSE Cost=5 Cardinality=10 Bytes=15622 COUNT STOPKEY VIEW Object owner=SCOTT Cost=5 Cardinality=107 Bytes=15622 SORT ORDER BY STOPKEY Cost=5 Cardinality=107 Bytes=7276 COUNT TABLE ACCESS FULL Object owner=HR Object name=EMPLOYEES Cost=2 Cardinality=107 Bytes=7276
--
返回的结果
ROWNUM ROWNUM EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL 1 22 121 Adam Fripp AFRIPP 2 97 196 Alana Walsh AWALSH 3 48 147 Alberto Errazuriz AERRAZUR 4 4 103 Alexander Hunold AHUNOLD 5 16 115 Alexander Khoo AKHOO 6 86 185 Alexis Bull ABULL 7 59 158 Allan McEwen AMCEWEN 8 76 175 Alyssa Hutton AHUTTON 9 68 167 Amit Banda ABANDA 10 88 187 Anthony Cabrio ACABRIO
--3.
取第
11-20
行
--
推荐方法
COUNT STOPKEY -->
当查询到
20
行时
,
执行了查询中止的命令
Cardinality=20
select t.* from (select /*+ First_rows */ rownum rnum,employees.* from hr.employees where rownum<=20) t where rnum>10
SELECT STATEMENT, GOAL = CHOOSE Cost=2 Cardinality=20 Bytes=2920 VIEW Object owner=SCOTT Cost=2 Cardinality=20 Bytes=2920 COUNT STOPKEY TABLE ACCESS FULL Object owner=HR Object name=EMPLOYEES Cost=2 Cardinality=107 Bytes=7276
--
不推荐方法
COUNT -->
建立了整个结果集
Cardinality=107
select t.* from (select rownum rnum,employees.* from hr.employees) t where rnum between 11 and 20
SELECT STATEMENT, GOAL = CHOOSE Cost=2 Cardinality=107 Bytes=15622 VIEW Object owner=SCOTT Cost=2 Cardinality=107 Bytes=15622 COUNT TABLE ACCESS FULL Object owner=HR Object name=EMPLOYEES Cost=2 Cardinality=107 Bytes=7276
SELECT STATEMENT, GOAL = CHOOSE Cost=5 Cardinality=106 Bytes=9328 HASH JOIN Cost=5 Cardinality=106 Bytes=9328 TABLE ACCESS FULL Object owner=HR Object name=DEPARTMENTS Cost=2 Cardinality=27 Bytes=540 TABLE ACCESS FULL Object owner=HR Object name=EMPLOYEES Cost=2 Cardinality=107 Bytes=7276
-
4
.
-
当使用内嵌视图时
, ORACLE
优化程序可能会整合视图
,
看下面那句
sql
语句的
explain
select * from( select employees.* from hr.employees ) emp, (select departments.* from hr.departments ) dept where emp.department_id=dept.department_id
--
如果觉得
ORACLE
所走的
access path
不是你想要或者说执行时间过长
,
可以尝试在内嵌视图中增加
rownum,
这个时候内嵌视图会被实体化
(
当作一个实体表
),
这种情况下
ORACLE
优化程序无法整合视图
!
可能会带来性能上的提升
@!
select * from( select employees.*,rownum from hr.employees ) emp, (select departments.*,rownum from hr.departments ) dept where emp.department_id=dept.department_id
SELECT STATEMENT, GOAL = CHOOSE Cost=5 Cardinality=106 Bytes=22790 HASH JOIN Cost=5 Cardinality=106 Bytes=22790 VIEW Object owner=TEST Cost=2 Cardinality=27 Bytes=1863 COUNT TABLE ACCESS FULL Object owner=HR Object name=DEPARTMENTS Cost=2 Cardinality=27 Bytes=540 VIEW Object owner=TEST Cost=2 Cardinality=107 Bytes=15622 COUNT TABLE ACCESS FULL Object owner=HR Object name=EMPLOYEES Cost=2 Cardinality=107 Bytes=7276