搞不懂的执行计划，请各位帮忙看看

显示全部楼层 · 2010-10-8 09:32:27

SELECT rid FROM ( SELECT rowid rid FROM MID_CHANGELOG WHERE 1 = 1AND ostepnumber != '0' AND osubstepnumber = '0'ORDER BY operationtime DESC )
WHERE rownum between 1 and 5
这个语句其实是一个子查询，外层的查询通过该SQL查出来的rowid直接定位到表中的相应行才是。
该SQL执行计划如下：
已选择5行。
已用时间:00: 00: 00.05
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=5 Bytes=60)
10 COUNT (STOPKEY)
21 FILTER
32 VIEW (Cost=7 Card=5 Bytes=60)
43 TABLE ACCESS (BY INDEX ROWID) OF 'MID_CHANGELOG' (TA

BLE) (Cost=7 Card=9419061 Bytes=235476525)
54
INDEX (FULL SCAN DESCENDING) OF 'I_CHANGELOG_OPERA

TIONTIME' (INDEX) (Cost=3 Card=7)

Statistics
----------------------------------------------------------

0recursive calls

0db block gets

8consistent gets

0physical reads

0redo size
307bytes sent via SQL*Net to client
277bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

5rows processed
加上外查询
SELECT id, parentid, objid, objtype, otypenumber, oapptypenumber, ostepnumber, osubstepnumber, ostatemsg, ostatenumber, o, operator, operationtime, eventtype, manualoperator, times,objectname, changes, bak1, bak2, bak3 FROM MID_CHANGELOG
WHERE rowid in
(SELECT rid FROM ( SELECT rowid rid FROM MID_CHANGELOG WHERE 1 = 1AND ostepnumber != '0' AND osubstepnumber = '0'ORDER BY operationtime DESC )
WHERE rownum between 1 and 5 )
感觉这个语句应该是可以通过直接根据前面子查询出来的rowid得到结果集吧[TABLE ACCESS (BY USER ROWID) OF 'MID_CHANGELOG'],
但结果原来的子查询反而走了FTS了。
执行计划如下：
已用时间:00: 00: 14.06
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=107623 Card=1 Byte

s=100)
10 NESTED LOOPS (Cost=107623 Card=1 Bytes=100)
21 VIEW OF 'VW_NSO_1' (VIEW) (Cost=107621 Card=5 Bytes=60)
32 HASH (UNIQUE)
43 COUNT (STOPKEY)
54 FILTER
65
VIEW (Cost=107621 Card=9419061 Bytes=113028732)
76
SORT (ORDER BY) (Cost=107621 Card=9419061 Byte

s=235476525)
87
TABLE ACCESS (FULL) OF 'MID_CHANGELOG' (TABL

E) (Cost=39585 Card=9419061 Bytes=235476525)
91 TABLE ACCESS (BY USER ROWID) OF 'MID_CHANGELOG' (TABLE)

(Cost=1 Card=1 Bytes=88)

Statistics
----------------------------------------------------------
249recursive calls

6db block gets
179592consistent gets
7307physical reads

0redo size
1335bytes sent via SQL*Net to client
278bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

1sorts (disk)

5rows processed
执行计划中黑体部分为子查询执行变化的地方，
不解为什么会出现这种状况，请各位帮忙看看。
[ 本帖最后由 zhou2324 于 2010-10-16 21:45 编辑 ]

千问 · 2010-10-8 09:32:27

我在自己电脑上测试得出的执行计划。。。
SQL> select *from test2 where rowid in (select *from (select /*+INDEX_DESC(TEST2 IDX_TEST2_I)*/rowid from test2 WHERE I =5) where rownum between 1 and 3);

Execution Plan
----------------------------------------------------------
Plan hash value: 3929559490
------------------------------------------------------------------------------------------------
| Id| Operation
| Name| Rows| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 1 |15 | 3(34)| 00:00:01 |
| 1 |NESTED LOOPS
|
| 1 |15 | 3(34)| 00:00:01 |
| 2 | VIEW
| VW_NSO_1| 1 |12 | 1 (0)| 00:00:01 |
| 3 |HASH UNIQUE
|
| 1 | 3 |
|
|
|*4 | COUNT STOPKEY
|
| | |
|
|
|*5 |FILTER
|
| | |
|
|
|*6 | INDEX RANGE SCAN DESCENDING| IDX_TEST2_I | 1 | 3 | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY USER ROWID | TEST2 | 1 | 3 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(ROWNUM=1)
6 - access("I"=5)
filter("I"=5)

Statistics
----------------------------------------------------------

1recursive calls

0db block gets

2consistent gets

0physical reads

0redo size
456bytes sent via SQL*Net to client
385bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed
SQL>

千问 · 2010-10-8 09:32:27

楼上你是10G 楼主是9i!
只能说9i的optimizer 没有想象的那么智能！外层查询不会通过rowid来定位自己要取的数据！
就是你第1个执行计划也是在里面定位的先VIEW 然后才 FILTER。

千问 · 2010-10-8 09:32:27

我的数据库版本是10.2.0.4

千问 · 2010-10-8 09:32:27

目前通过在子循环加 /*+index_desc(mid_changelog I_CHANGELOG_OPERATIONTIME)*/ 暂时性的在客户那边先交差，
明天上班了再仔细分析下，肯定还是需要调整。

千问 · 2010-10-8 09:32:27

千问 · 2010-10-8 09:32:27

我觉得你就写成
SELECT id, parentid, objid, objtype, otypenumber, oapptypenumber, ostepnumber, osubstepnumber, ostatemsg, ostatenumber, o, operator, operationtime, eventtype, manualoperator, times,objectname, changes, bak1, bak2, bak3 FROM
(select *from CHANGELOGORDER BY operationtime DESC)twhere rownum <=5
看看？

千问 · 2010-10-8 09:32:27

rownum between 1 and 5,虽然没有问题，但是一般不这样写，1改为2那就错了

千问 · 2010-10-8 09:32:27

show parameter optimizer_features_enable

千问 · 2010-10-8 09:32:27

一般来说#7楼的是标准写法，你目前如此写是不是想用index scan来快速取按operationtime DESC后去前5条记录，由于原表的数据量由超过900W条记录。
我建议你先把你的写法和#7楼的写法，把语句的statistics贴出来，另外先对表进行一下分析。