t_workflow:add primary key (EVENTID, ORD)
T_EventMainadd primary key (EVENTID)select *
from T_Workflow t,T_EventMain a
where t.eventid = a.eventid
and t.Ord =
(select min(ord) from T_Workflow where eventid = a.eventid);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Co
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12041 | 86M| | 1
| 1 | NESTED LOOPS | | 12041 | 86M| | 1
|* 2 | HASH JOIN | | 1241K| 271M| 55M| 17
| 3 | VIEW | VW_SQ_1 | 1241K| 41M| |
| 4 | SORT GROUP BY | | 1241K| 41M| |
| 5 | INDEX FULL SCAN | SYS_C003463 | 1241K| 41M| |
| 6 | TABLE ACCESS FULL | T_EVENTMAIN | 1279K| 236M| | 3
| 7 | TABLE ACCESS BY INDEX ROWID| T_WORKFLOW | 1 | 7301 | |
|* 8 | INDEX UNIQUE SCAN | SYS_C003463 | 1 | | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("VW_SQ_1"."EVENTID"="A"."EVENTID")
8 - access("T"."EVENTID"="A"."EVENTID" AND "T"."ORD"="VW_SQ_1"."VW_COL_1")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note: cpu costing is off
另外t_workflow中同eventid最多有4条记录
很慢!!!!!!!!!!!
T_EventMainadd primary key (EVENTID)select *
from T_Workflow t,T_EventMain a
where t.eventid = a.eventid
and t.Ord =
(select min(ord) from T_Workflow where eventid = a.eventid);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Co
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12041 | 86M| | 1
| 1 | NESTED LOOPS | | 12041 | 86M| | 1
|* 2 | HASH JOIN | | 1241K| 271M| 55M| 17
| 3 | VIEW | VW_SQ_1 | 1241K| 41M| |
| 4 | SORT GROUP BY | | 1241K| 41M| |
| 5 | INDEX FULL SCAN | SYS_C003463 | 1241K| 41M| |
| 6 | TABLE ACCESS FULL | T_EVENTMAIN | 1279K| 236M| | 3
| 7 | TABLE ACCESS BY INDEX ROWID| T_WORKFLOW | 1 | 7301 | |
|* 8 | INDEX UNIQUE SCAN | SYS_C003463 | 1 | | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("VW_SQ_1"."EVENTID"="A"."EVENTID")
8 - access("T"."EVENTID"="A"."EVENTID" AND "T"."ORD"="VW_SQ_1"."VW_COL_1")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note: cpu costing is off
另外t_workflow中同eventid最多有4条记录
很慢!!!!!!!!!!!
解决方案 »
- ORACLE如何实现RANGE子分区,能否通过嵌套表实现
- 谁能给我一个oracle论坛的邀请码呀
- 请教一个oracle循环更新问题
- ORACLE 存储过程 在此 SELECT 语句中缺少 INTO 子句,怎么办?
- 这个SQL不用别名怎么写,有没有类似MSSql中的[]呀,高分(急)
- 关于select for update 游标的问题,非常疑惑!!
- ERROR:ORA-12541: TNS: 没有监听器?!
- 请问sql语句文件打开路径名的格式应该是怎么样
- 这段SQL语句错在哪里?
- 关于一个长SQL的优化,有没有大神能提点一下...毫无头绪...
- 创建间隔分区表报错
- oracle中存储过程里for循环如何每一次循环都从表中取出相邻的两条记录
from (
select t.*, row_number() over(partition by eventid order by ord asc) rn from t_workflow t
) t, T_EventMain a
where t.rn = 1
and t.eventid = a.eventid
;
作为一个子表,再进行关联
from T_Workflow t,T_EventMain a
where t.eventid = a.eventid
and (t.eventid ,t.Ord) in
(select EVENTID,min(ORD)
from T_Workflow
group by eventid)
试试
只是先取得先返回,对于非界面显示的没有什么实质性的意义
from T_Workflow t,T_EventMain a
where t.eventid = a.eventid
and (t.eventid ,t.Ord) in
(select EVENTID,min(ORD)
from T_Workflow where eventid = a.eventid
group by eventid)
-----------------------------------------------
这个效果是最好的!@
select *
from nn911.t_workflow t, nn911.t_eventmain a
where t.eventid = a.eventid
and (t.eventid, t.ord) in
(select eventid, min(ord) from nn911.t_workflow group by eventid);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| C
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12413 | 89M| | 6
| 1 | NESTED LOOPS | | 12413 | 89M| | 6
| 2 | MERGE JOIN SEMI | | 12413 | 86M| | 3
| 3 | TABLE ACCESS BY INDEX ROWID| T_WORKFLOW | 1241K| 8642M| |
| 4 | INDEX FULL SCAN | SYS_C003463 | 1241K| | |
|* 5 | SORT UNIQUE | | 1241K| 41M| 104M| 3
| 6 | VIEW | VW_NSO_1 | 1241K| 41M| |
| 7 | SORT GROUP BY | | 1241K| 41M| |
| 8 | INDEX FULL SCAN | SYS_C003463 | 1241K| 41M| |
| 9 | TABLE ACCESS BY INDEX ROWID | T_EVENTMAIN | 1279K| 236M| |
|* 10 | INDEX UNIQUE SCAN | SYS_C003258 | 1 | | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
5 - access("T"."EVENTID"="VW_NSO_1"."$nso_col_1")
filter("T"."ORD"="VW_NSO_1"."$nso_col_2" AND "T"."EVENTID"="VW_NSO_1"."$n
col_1")
10 - access("T"."EVENTID"="A"."EVENTID")
Note: cpu costing is off
****************************************************************************************
select *
from nn911.t_workflow t, nn911.t_eventmain a
where t.eventid = a.eventid
and (t.eventid, t.ord) in (select eventid, min(ord)
from nn911.t_workflow
where eventid = a.eventid
group by eventid);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 62065 | 443M| 2484K|
| 1 | NESTED LOOPS | | 62065 | 443M| 2484K|
| 2 | TABLE ACCESS FULL | T_WORKFLOW | 1241K| 8642M| 1463 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_EVENTMAIN | 1 | 194 | 2 |
|* 4 | INDEX UNIQUE SCAN | SYS_C003258 | 1 | | 1 |
|* 5 | FILTER | | | | |
| 6 | SORT GROUP BY NOSORT | | 12413 | 424K| 2 |
|* 7 | INDEX RANGE SCAN | SYS_C003463 | 12413 | 424K| 2 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("SYS_ALIAS_2"."EVENTID"="SYS_ALIAS_3"."EVENTID")
filter( EXISTS (SELECT /*+ */ 0 FROM "NN911"."T_WORKFLOW" "T_WORKFL
OW" WHERE "T_WORKFLOW"."EVENTID"=:B1 GROUP BY "T_WORKFLOW"."
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EVENTID" HAVING "T_WORKFLOW"."EVENTID"=:B2 AND MIN("T_WORKFL
OW"."ORD")=:B3))
5 - filter("T_WORKFLOW"."EVENTID"=:B1 AND MIN("T_WORKFLOW"."ORD")=:B2)
7 - access("T_WORKFLOW"."EVENTID"=:B1)
filter("T_WORKFLOW"."EVENTID"=:B1)
Note: cpu costing is off****************************************************************************************
select *
from nn911.T_Workflow t, nn911.T_EventMain a
where t.eventid = a.eventid
and t.Ord = (select min(ord) from nn911.T_Workflow);PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7495 | 1465 |
| 1 | NESTED LOOPS | | 1 | 7495 | 1465 |
|* 2 | TABLE ACCESS FULL | T_WORKFLOW | 1 | 7301 | 1463 |
| 3 | SORT AGGREGATE | | 1 | 13 | |
| 4 | INDEX FAST FULL SCAN | SYS_C003463 | 1241K| 15M| 4 |
| 5 | TABLE ACCESS BY INDEX ROWID| T_EVENTMAIN | 1 | 194 | 2 |
|* 6 | INDEX UNIQUE SCAN | SYS_C003258 | 1 | | 1 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."ORD"= (SELECT /*+ */ MIN("T_WORKFLOW"."ORD") FROM "NN91
1"."T_WORKFLOW" "T_WORKFLOW"))
6 - access("T"."EVENTID"="A"."EVENTID")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note: cpu costing is off
select * from
(
select *
from T_Workflow t where not exists
(select 1 from T_Workflow where eventid = t.eventid and Ord<t.Ord);
) t ,T_EventMain a
where t.eventid = a.eventid
from nn911.T_Workflow t,nn911.T_EventMain a
where t.eventid = a.eventid
and t.Ord =
(select min(ord) from nn911.T_Workflow where eventid = a.eventid)
and rownum<10;
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7137 Card=9 Bytes=11
67977) 1 0 COUNT (STOPKEY)
2 1 NESTED LOOPS (Cost=7137 Card=12041 Bytes=1167977)
3 2 HASH JOIN (Cost=7137 Card=1241291 Bytes=76960042)
4 3 TABLE ACCESS (FULL) OF 'T_EVENTMAIN' (Cost=3400 Card
=1279674 Bytes=34551198) 5 3 VIEW OF 'VW_SQ_1' (Cost=26 Card=1241291 Bytes=434451
85) 6 5 SORT (GROUP BY) (Cost=26 Card=1241291 Bytes=434451
85) 7 6 INDEX (FULL SCAN) OF 'SYS_C003463' (UNIQUE) (Cos
t=26 Card=1241291 Bytes=43445185) 8 2 INDEX (UNIQUE SCAN) OF 'SYS_C003463' (UNIQUE)
统计信息
----------------------------------------------------------
0 recursive calls
2 db block gets
42051 consistent gets
50304 physical reads
0 redo size
512 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
9 rows processed
**************************************************************************************
select a.calltime
from nn911.T_Workflow t, nn911.T_EventMain a
where t.eventid = a.eventid
and exists (select 1
from (select eventid, min(ord) ord
from nn911.T_Workflow
group by eventid) x
where x.eventid = a.eventid
and x.ord = t.ord)
and rownum<10;
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7133 Card=9 Bytes=38
48030) 1 0 COUNT (STOPKEY)
2 1 FILTER
3 2 HASH JOIN (Cost=7115 Card=62065 Bytes=3848030)
4 3 TABLE ACCESS (FULL) OF 'T_EVENTMAIN' (Cost=3400 Card
=1279674 Bytes=34551198) 5 3 INDEX (FAST FULL SCAN) OF 'SYS_C003463' (UNIQUE) (Co
st=4 Card=1241291 Bytes=43445185) 6 2 FILTER
7 6 SORT (GROUP BY NOSORT) (Cost=2 Card=12413 Bytes=4344
55) 8 7 INDEX (RANGE SCAN) OF 'SYS_C003463' (UNIQUE) (Cost
=2 Card=12413 Bytes=434455)统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
42133 consistent gets
42125 physical reads
0 redo size
512 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
****************************************************************************
select /*+ first_rows*/ a.calltime
from nn911.T_Workflow t, nn911.T_EventMain a
where t.eventid = a.eventid
and exists (select 1
from (select eventid, min(ord) ord
from nn911.T_Workflow
group by eventid) x
where x.eventid = a.eventid
and x.ord = t.ord)
and rownum < 10执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1246875 Ca
rd=9 Bytes=3848030) 1 0 COUNT (STOPKEY)
2 1 NESTED LOOPS (Cost=1246875 Card=62065 Bytes=3848030)
3 2 INDEX (FULL SCAN) OF 'SYS_C003463' (UNIQUE) (Cost=26 C
ard=1241291 Bytes=43445185) 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'T_EVENTMAIN' (Cost=2
Card=1 Bytes=27) 5 4 INDEX (UNIQUE SCAN) OF 'SYS_C003258' (UNIQUE) (Cost=
1 Card=1) 6 5 FILTER
7 6 SORT (GROUP BY NOSORT) (Cost=2 Card=12413 Bytes=
434455) 8 7 INDEX (RANGE SCAN) OF 'SYS_C003463' (UNIQUE) (
Cost=2 Card=12413 Bytes=434455)统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
60 consistent gets
0 physical reads
0 redo size
512 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed