select * from (
select
xxjb801x0_.ID as ID6_0_, xxjb8x1_.ID as ID7_1_, xxjb801x0_.JBID as JBID6_0_, xxjb801x0_.XJXXID as XJXXID6_0_, xxjb801x0_.XM as XM6_0_, xxjb801x0_.XB as XB6_0_, xxjb801x0_.MZ as MZ6_0_, xxjb801x0_.SFXSMZ as SFXSMZ6_0_, xxjb801x0_.CSNY as CSNY6_0_, xxjb801x0_.HJSZD as HJSZD6_0_, xxjb801x0_.HJXZ as HJXZ6_0_, xxjb801x0_.SYD as SYD6_0_, xxjb801x0_.YB as YB6_0_, xxjb801x0_.LXDH as LXDH6_0_, xxjb801x0_.RXSJ as RXSJ6_0_, xxjb801x0_.XZ as XZ6_0_, xxjb801x0_.NJ as NJ6_0_, xxjb801x0_.SFZHM as SFZHM6_0_, xxjb801x0_.DZZCXJHM as DZZCXJHM6_0_, xxjb801x0_.ZZJE as ZZJE6_0_, xxjb801x0_.YHKHM as YHKHM6_0_, xxjb801x0_.BZ as BZ6_0_, xxjb801x0_.VERIFY as VERIFY6_0_, xxjb801x0_.GRADE as GRADE6_0_, xxjb801x0_.ONLYID as ONLYID6_0_
from XX_JB8_01 xxjb801x0_, XX_JB8 xxjb8x1_
where xxjb8x1_.ID=328 and xxjb801x0_.JBID=xxjb8x1_.ID(+)
order by xxjb801x0_.xm desc
) where rownum <= 10
pga:479M
临时表空间:1.5G
这是我的数据
huanghuizz (2008-9-03 12:55:57)
这是我的执行计划
这是我的执行计划
390 rows selected.
Elapsed: 00:00:00.08
Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12118 consistent gets
12103 physical reads
0 redo size
2706 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
这是在分页的情况下
huanghuizz (2008-9-03 13:36:34)
这是语句:
select *
from (select xxjb801x0_.ID as ID6_0_,
xxjb8x1_.ID as ID7_1_,
xxjb801x0_.JBID as JBID6_0_,
xxjb801x0_.XJXXID as XJXXID6_0_,
xxjb801x0_.XM as XM6_0_,
xxjb801x0_.XB as XB6_0_,
xxjb801x0_.MZ as MZ6_0_,
xxjb801x0_.SFXSMZ as SFXSMZ6_0_,
xxjb801x0_.CSNY as CSNY6_0_,
xxjb801x0_.HJSZD as HJSZD6_0_,
xxjb801x0_.HJXZ as HJXZ6_0_,
xxjb801x0_.SYD as SYD6_0_,
xxjb801x0_.YB as YB6_0_,
xxjb801x0_.LXDH as LXDH6_0_,
xxjb801x0_.RXSJ as RXSJ6_0_,
xxjb801x0_.XZ as XZ6_0_,
xxjb801x0_.NJ as NJ6_0_,
xxjb801x0_.SFZHM as SFZHM6_0_,
xxjb801x0_.DZZCXJHM as DZZCXJHM6_0_,
xxjb801x0_.ZZJE as ZZJE6_0_,
xxjb801x0_.YHKHM as YHKHM6_0_,
xxjb801x0_.BZ as BZ6_0_,
xxjb801x0_.VERIFY as VERIFY6_0_,
xxjb801x0_.GRADE as GRADE6_0_,
xxjb801x0_.ONLYID as ONLYID6_0_
from XX_JB8_01 xxjb801x0_, XX_JB8 xxjb8x1_
where xxjb8x1_.ID = 328
and xxjb801x0_.JBID = xxjb8x1_.ID(+)
order by xxjb801x0_.xm desc)
where rownum <= 10;
/
最新回复
huanghuizz (2008-9-03 11:51:07)
select * from (
select
xxjb801x0_.ID as ID6_0_, xxjb8x1_.ID as ID7_1_, xxjb801x0_.JBID as JBID6_0_, xxjb801x0_.XJXXID as XJXXID6_0_, xxjb801x0_.XM as XM6_0_, xxjb801x0_.XB as XB6_0_, xxjb801x0_.MZ as MZ6_0_, xxjb801x0_.SFXSMZ as SFXSMZ6_0_, xxjb801x0_.CSNY as CSNY6_0_, xxjb801x0_.HJSZD as HJSZD6_0_, xxjb801x0_.HJXZ as HJXZ6_0_, xxjb801x0_.SYD as SYD6_0_, xxjb801x0_.YB as YB6_0_, xxjb801x0_.LXDH as LXDH6_0_, xxjb801x0_.RXSJ as RXSJ6_0_, xxjb801x0_.XZ as XZ6_0_, xxjb801x0_.NJ as NJ6_0_, xxjb801x0_.SFZHM as SFZHM6_0_, xxjb801x0_.DZZCXJHM as DZZCXJHM6_0_, xxjb801x0_.ZZJE as ZZJE6_0_, xxjb801x0_.YHKHM as YHKHM6_0_, xxjb801x0_.BZ as BZ6_0_, xxjb801x0_.VERIFY as VERIFY6_0_, xxjb801x0_.GRADE as GRADE6_0_, xxjb801x0_.ONLYID as ONLYID6_0_
from XX_JB8_01 xxjb801x0_, XX_JB8 xxjb8x1_
where xxjb8x1_.ID=328 and xxjb801x0_.JBID=xxjb8x1_.ID(+)
order by xxjb801x0_.xm desc
) where rownum <= 10
pga:479M
临时表空间:1.5G
这是我的数据
huanghuizz (2008-9-03 12:55:57)
这是我的执行计划
390 rows selected.
Elapsed: 00:00:00.08
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1974 | 314K| 289 (2)|
|* 1 | HASH JOIN RIGHT OUTER | | 1974 | 314K| 289 (2)|
| 2 | TABLE ACCESS FULL | SEG$ | 5005 | 55055 | 19 (0)|
|* 3 | HASH JOIN | | 1930 | 286K| 269 (2)|
| 4 | TABLE ACCESS FULL | TS$ | 7 | 21 | 5 (0)|
| 5 | NESTED LOOPS OUTER | | 1930 | 280K| 264 (2)|
| 6 | NESTED LOOPS OUTER | | 1930 | 275K| 262 (2)|
|* 7 | HASH JOIN OUTER | | 1930 | 265K| 262 (2)|
| 8 | NESTED LOOPS | | 1930 | 250K| 184 (2)|
| 9 | MERGE JOIN CARTESIAN| | 2760 | 277K| 79 (3)|
|* 10 | HASH JOIN | | 1 | 68 | 1 (100)|
|* 11 | FIXED TABLE FULL | X$KSPPI | 1 | 55 | 0 (0)|
| 12 | FIXED TABLE FULL | X$KSPPCV | 100 | 1300 | 0 (0)|
| 13 | BUFFER SORT | | 2760 | 96600 | 79 (3)|
|* 14 | TABLE ACCESS FULL | OBJ$ | 2760 | 96600 | 78 (2)|
|* 15 | TABLE ACCESS CLUSTER| TAB$ | 1 | 30 | 1 (0)|
|* 16 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)|
| 17 | TABLE ACCESS FULL | OBJ$ | 52448 | 409K| 77 (0)|
|* 18 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | 5 | 0 (0)|
| 19 | TABLE ACCESS CLUSTER | USER$ | 1 | 3 | 1 (0)|
|* 20 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."FILE#"="S"."FILE#"(+) AND
"T"."BLOCK#"="S"."BLOCK#"(+) AND "T"."TS#"="S"."TS#"(+))
3 - access("T"."TS#"="TS"."TS#")
7 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
10 - access("KSPPI"."INDX"="KSPPCV"."INDX")
11 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
14 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND
BITAND("O"."FLAGS",128)=0)
15 - filter(BITAND("T"."PROPERTY",1)=0)
16 - access("O"."OBJ#"="T"."OBJ#")
18 - access("T"."BOBJ#"="CO"."OBJ#"(+))
20 - access("CX"."OWNER#"="CU"."USER#"(+))
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2419 consistent gets
0 physical reads
0 redo size
9929 bytes sent via SQL*Net to client
744 bytes received via SQL*Net from client
27 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
390 rows processed
yzsind (2008-9-03 12:59:06)
huanghuizz (2008-9-03 13:19:07)
Elapsed: 00:00:11.58
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 949 | 119K| 23463 (1)|
| 1 | SORT ORDER BY | | 949 | 119K| 23463 (1)|
| 2 | NESTED LOOPS | | 949 | 119K| 23462 (1)|
|* 3 | INDEX UNIQUE SCAN| PK_XX_JB8 | 1 | 5 | 1 (0)|
|* 4 | TABLE ACCESS FULL| XX_JB8_01 | 949 | 114K| 23461 (1)|
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("XXJB8X1_"."ID"=328)
4 - filter("XXJB801X0_"."JBID"=328)
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12118 consistent gets
12106 physical reads
0 redo size
103973 bytes sent via SQL*Net to client
1470 bytes received via SQL*Net from client
93 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1375 rows processed
这是在没有分页的情况下
huanghuizz (2008-9-03 13:24:53)
Elapsed: 00:00:10.53
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 24950 | 276 (1)|
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 949 | 2312K| 276 (1)|
|* 3 | SORT ORDER BY STOPKEY| | 949 | 119K| 276 (1)|
| 4 | NESTED LOOPS | | 949 | 119K| 276 (1)|
|* 5 | INDEX UNIQUE SCAN | PK_XX_JB8 | 1 | 5 | 1 (0)|
|* 6 | TABLE ACCESS FULL | XX_JB8_01 | 949 | 114K| 275 (1)|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
5 - access("XXJB8X1_"."ID"=328)
6 - filter("XXJB801X0_"."JBID"=328)
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12118 consistent gets
12103 physical reads
0 redo size
2706 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
这是在分页的情况下
huanghuizz (2008-9-03 13:36:34)
select *
from (select xxjb801x0_.ID as ID6_0_,
xxjb8x1_.ID as ID7_1_,
xxjb801x0_.JBID as JBID6_0_,
xxjb801x0_.XJXXID as XJXXID6_0_,
xxjb801x0_.XM as XM6_0_,
xxjb801x0_.XB as XB6_0_,
xxjb801x0_.MZ as MZ6_0_,
xxjb801x0_.SFXSMZ as SFXSMZ6_0_,
xxjb801x0_.CSNY as CSNY6_0_,
xxjb801x0_.HJSZD as HJSZD6_0_,
xxjb801x0_.HJXZ as HJXZ6_0_,
xxjb801x0_.SYD as SYD6_0_,
xxjb801x0_.YB as YB6_0_,
xxjb801x0_.LXDH as LXDH6_0_,
xxjb801x0_.RXSJ as RXSJ6_0_,
xxjb801x0_.XZ as XZ6_0_,
xxjb801x0_.NJ as NJ6_0_,
xxjb801x0_.SFZHM as SFZHM6_0_,
xxjb801x0_.DZZCXJHM as DZZCXJHM6_0_,
xxjb801x0_.ZZJE as ZZJE6_0_,
xxjb801x0_.YHKHM as YHKHM6_0_,
xxjb801x0_.BZ as BZ6_0_,
xxjb801x0_.VERIFY as VERIFY6_0_,
xxjb801x0_.GRADE as GRADE6_0_,
xxjb801x0_.ONLYID as ONLYID6_0_
from XX_JB8_01 xxjb801x0_, XX_JB8 xxjb8x1_
where xxjb8x1_.ID = 328
and xxjb801x0_.JBID = xxjb8x1_.ID(+)
order by xxjb801x0_.xm desc)
where rownum <= 10;
/
huanghuizz (2008-9-03 13:37:08)
Hopewell_Go (2008-9-03 17:34:20)