17,377
社区成员
发帖
与我相关
我的任务
分享
SQL> explain plan for( SELECT compid,compname,pcompid from companyinfo c
2 where vpd_year =(select max(vpd_year) from companyinfo ) and stopflag = 0 AND ACCBOOKID = 0
3 start with c.COMPID ='0600'
4 connect by prior c.compid = c.pcompid );
已解释。
SQL> select * from TABLE(DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1273742929
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 93 | 4836 | 252 (1)| 00:00:04 |
|* 1 | FILTER | | | | | |
|* 2 | CONNECT BY WITH FILTERING| | | | | |
|* 3 | TABLE ACCESS FULL | COMPANYINFO | 10 | 690 | 252 (1)| 00:00:04 |
|* 4 | HASH JOIN | | | | | |
| 5 | CONNECT BY PUMP | | | | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
| 6 | TABLE ACCESS FULL | COMPANYINFO | 93 | 4836 | 252 (1)| 00:00:04 |
| 7 | SORT AGGREGATE | | 1 | 4 | | |
| 8 | TABLE ACCESS FULL | COMPANYINFO | 66199 | 258K| 252 (1)| 00:00:04 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("VPD_YEAR"= (SELECT MAX("VPD_YEAR") FROM "COMPANYINFO"
"COMPANYINFO") AND "STOPFLAG"=0 AND "ACCBOOKID"=0)
2 - access("C"."PCOMPID"=PRIOR "C"."COMPID")
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
3 - filter("C"."COMPID"='0600')
4 - access("C"."PCOMPID"=PRIOR "C"."COMPID")
已选择24行。
SQL> explain plan for( SELECT compid,compname,pcompid FROM (
2 SELECT compid,compname,pcompid from companyinfo c
3 where vpd_year =(select max(vpd_year) from companyinfo ) and stopflag = 0 AND ACCBOOKID = 0 )
4 start with COMPID ='0600'
5 connect by prior compid = pcompid);
已解释。
SQL> select * from TABLE(DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1904047111
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 416 | 505 (1)| 00:00:07 |
|* 1 | CONNECT BY WITH FILTERING| | | | | |
|* 2 | TABLE ACCESS FULL | COMPANYINFO | 1 | 69 | 253 (2)| 00:00:04 |
| 3 | SORT AGGREGATE | | 1 | 4 | | |
| 4 | TABLE ACCESS FULL | COMPANYINFO | 66199 | 258K| 252 (1)| 00:00:04 |
|* 5 | HASH JOIN | | | | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
| 6 | CONNECT BY PUMP | | | | | |
|* 7 | TABLE ACCESS FULL | COMPANYINFO | 8 | 416 | 253 (2)| 00:00:04 |
| 8 | SORT AGGREGATE | | 1 | 4 | | |
| 9 | TABLE ACCESS FULL | COMPANYINFO | 66199 | 258K| 252 (1)| 00:00:04 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PCOMPID"=PRIOR "COMPID")
2 - filter("COMPID"='0600' AND "STOPFLAG"=0 AND "ACCBOOKID"=0 AND "VPD_YEAR"=
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
(SELECT /*+ */ MAX("VPD_YEAR") FROM "COMPANYINFO" "COMPANYINFO"))
5 - access("PCOMPID"=PRIOR "COMPID")
7 - filter("STOPFLAG"=0 AND "ACCBOOKID"=0 AND "VPD_YEAR"= (SELECT
MAX("VPD_YEAR") FROM "COMPANYINFO" "COMPANYINFO"))
已选择26行。