请教各位大侠,
1)有什么思路可以提高sql排序的效率?不一定要一条sql实现,如果用pl/sql实现效率高也行。
2)我们论坛有搜索帖子的功能吗?我想看以前是否有puber贴过类似的帖子。
我遇到的问题情形如下:
workitemhis 为设备作业表,数据量为180万,deviceno为设备号大约为200个左右,需要统计设备最近一段时间内最后N条作业信息。
下面是我写的sql,在pc server oracle10g的库上执行,大概15秒返回第一条记录,最终的结果集为240条记录。
执行计划如下:
SQL> set autotrace traceonly
SQL> SELECT deviceno, starttime, endtime
2 FROM (SELECT t1.PUTCONTAINERCHE deviceno,
3 nvl(t1.PUTTIME, t1.PERFORMDATE) starttime,
4 t1.PERFORMDATE endtime,
5 row_number() over(PARTITION BY t1.PUTCONTAINERCHE ORDER BY t1.PERFORMDATE DESC) seq
6 FROM workitemhis t1
7 WHERE t1.PERFORMDATE >= trunc(SYSDATE) - 30)
8 WHERE seq < 5;
已选择240行。
执行计划
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
(%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------
-----------------------
| 0 | SELECT STATEMENT | | 70566 | 2618K| | 24461
(5)| | |
|* 1 | VIEW | | 70566 | 2618K| | 24461
(5)| | |
|* 2 | WINDOW SORT PUSHED RANK | | 70566 | 1722K| 5544K| 24461
(5)| | |
| 3 | PARTITION RANGE ITERATOR| | 70566 | 1722K| | 23940
(5)| KEY | 8 |
|* 4 | TABLE ACCESS FULL | WORKITEMHIS | 70566 | 1722K| | 23940
(5)| KEY | 8 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SEQ"<5)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "T1"."PUTCONTAINERCHE" ORDER BY
INTERNAL_FUNCTION("T1"."PERFORMDATE") DESC )<5)
4 - filter("T1"."PERFORMDATE">=TRUNC(SYSDATE@!)-30)
Note
-----
- 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
4330 recursive calls
0 db block gets
104144 consistent gets
103236 physical reads
0 redo size
7715 bytes sent via SQL*Net to client
564 bytes received via SQL*Net from client
17 SQL*Net roundtrips to/from client
95 sorts (memory)
0 sorts (disk)
240 rows processed
SQL>