两种字符汇聚方法的性能对比

最新推荐文章于 2022-02-26 23:33:48 发布

cnfs3521

最新推荐文章于 2022-02-26 23:33:48 发布

阅读量144

点赞数

文章标签： ui

对比结果（逻辑读块数）
f_List2str：第一次：5311,第二次：5200
Sys_Connect_By_Path：第一次：15598,第二次：15590

结论：自定义函数f_List2str比树形查询Sys_Connect_By_Path方式更快

SQL> set linesize 5000
SQL> Select d.Table_Name, d.Constraint_Name, f_List2str(Cast(Collect(d.Column_Name Order By d.Position) As t_Strlist)) Colstr
2 From User_Cons_Columns D,
3 (Select a.Table_Name, a.Constraint_Name
4 From User_Constraints A, zlBakTables T
5 Where a.Table_Name = t.表名 And t.直接转出 = 1 And t.系统 = 100 And a.Constraint_Type In ('P', 'U')) A
6 Where a.Constraint_Name = d.Constraint_Name And a.Table_Name = d.Table_Name
7 Group By d.Table_Name, d.Constraint_Name
8 Order By Constraint_Name;

已选择131行。

执行计划
----------------------------------------------------------
Plan hash value: 2192633783

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 283 | 18 (6)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 283 | 18 (6)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 283 | 17 (0)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 1 | 279 | 16 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 275 | 15 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 271 | 14 (0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 267 | 13 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 220 | 12 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 201 | 11 (0)| 00:00:01 |
| 9 | NESTED LOOPS OUTER | | 1 | 172 | 10 (0)| 00:00:01 |
| 10 | NESTED LOOPS OUTER | | 1 | 164 | 9 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 159 | 8 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 142 | 7 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 134 | 6 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 110 | 5 (0)| 00:00:01 |
| 15 | NESTED LOOPS OUTER | | 1 | 86 | 4 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 78 | 3 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 57 | 2 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID| ZLBAKTABLES | 1 | 25 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | ZLBAKTABLES_PK | 101 | | 1 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 32 | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | I_OBJ2 | 1 | | 1 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 21 | 1 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | I_CDEF2 | 3 | | 1 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 1 (0)| 00:00:01 |
| 26 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 24 | 1 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 1 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 24 | 1 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | I_CON1 | 1 | | 1 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | 1 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | CCOL$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | I_CCOL2 | 1 | | 1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
|* 37 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 29 | 1 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 19 | 1 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | I_COL3 | 1 | | 1 (0)| 00:00:01 |
| 41 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 47 | 1 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | 1 (0)| 00:00:01 |
| 43 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 45 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 46 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 47 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 48 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 49 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 50 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

18 - filter("T"."直接转出"=1)
19 - access("T"."系统"=100)
21 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"="T"."表名")
22 - filter((DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C','?')='P' OR
DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C','?')='U') AND "C"."TYPE#"<>8 AND
"C"."TYPE#"<>12)
23 - access("C"."OBJ#"="O"."OBJ#")
25 - access("C"."RCON#"="RC"."CON#"(+))
27 - access("OC"."CON#"="C"."CON#")
29 - access("C"."OWNER#"=USERENV('SCHEMAID') AND "OC"."NAME"="C"."NAME")
30 - filter("CD"."TYPE#"<>12)
31 - access("C"."CON#"="CD"."CON#")
33 - access("CD"."CON#"="CC"."CON#")
34 - access("C"."ROBJ#"="RO"."OBJ#"(+))
36 - access("C"."ENABLED"="OI"."OBJ#"(+))
37 - filter("O"."NAME"="O"."NAME")
38 - access("CC"."OBJ#"="O"."OBJ#")
40 - access("CC"."OBJ#"="COL"."OBJ#" AND "CC"."INTCOL#"="COL"."INTCOL#")
42 - access("COL"."OBJ#"="AC"."OBJ#"(+) AND "COL"."INTCOL#"="AC"."INTCOL#"(+))
44 - access("U"."USER#"=USERENV('SCHEMAID'))
46 - access("OI"."OWNER#"="UI"."USER#"(+))
48 - access("RC"."OWNER#"="RU"."USER#"(+))
50 - access("OC"."OWNER#"="OU"."USER#")

统计信息
----------------------------------------------------------
26 recursive calls
0 db block gets
5311 consistent gets
0 physical reads
0 redo size
7908 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
131 rows processed

SQL> Select d.Table_Name, d.Constraint_Name, LTrim(Sys_Connect_By_Path(d.Column_Name, ','), ',') Colstr
2 From (Select Rownum, d.Table_Name, d.Constraint_Name, d.Column_Name, d.Position
3 From User_Cons_Columns D,
4 (Select a.Table_Name, a.Constraint_Name
5 From User_Constraints A, zlBakTables T
6 Where a.Table_Name = t.表名 And t.直接转出 = 1 And t.系统 = 100 And
7 a.Constraint_Type In ('P', 'U')) A
8 Where a.Constraint_Name = d.Constraint_Name And a.Table_Name = d.Table_Name) D
9 Where Connect_By_Isleaf = 1
10 Start With d.Position = 1
11 Connect By Prior d.Position + 1 = d.Position And Prior d.Constraint_Name = d.Constraint_Name
12 Order By Constraint_Name;

已选择131行。

执行计划
----------------------------------------------------------
Plan hash value: 2726543698

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2049 | 17 (0)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 2049 | 17 (0)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | CONNECT BY WITH FILTERING | | | | | |
|* 4 | VIEW | | 1 | 2126 | 17 (0)| 00:00:01 |
| 5 | COUNT | | | | | |
| 6 | NESTED LOOPS | | 1 | 357 | 17 (0)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 1 | 353 | 16 (0)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 1 | 349 | 15 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 345 | 14 (0)| 00:00:01 |
| 10 | NESTED LOOPS OUTER | | 1 | 341 | 13 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 294 | 12 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 266 | 11 (0)| 00:00:01 |
| 13 | NESTED LOOPS OUTER | | 1 | 213 | 10 (0)| 00:00:01 |
| 14 | NESTED LOOPS OUTER | | 1 | 205 | 9 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 200 | 8 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 180 | 7 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 167 | 6 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 143 | 5 (0)| 00:00:01 |
| 19 | NESTED LOOPS OUTER | | 1 | 119 | 4 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 111 | 3 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 81 | 2 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID | ZLBAKTABLES | 1 | 25 | 1 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | ZLBAKTABLES_PK | 101 | | 1 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 56 | 1 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | I_OBJ2 | 1 | | 1 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 30 | 1 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | I_CDEF2 | 3 | | 1 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 1 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 24 | 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 1 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 24 | 1 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | I_CON1 | 1 | | 1 (0)| 00:00:01 |
|* 34 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 13 | 1 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | 1 (0)| 00:00:01 |
| 36 | TABLE ACCESS BY INDEX ROWID | CCOL$ | 1 | 20 | 1 (0)| 00:00:01 |
|* 37 | INDEX RANGE SCAN | I_CCOL2 | 1 | | 1 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
|* 41 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 53 | 1 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
| 43 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 28 | 1 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | I_COL3 | 1 | | 1 (0)| 00:00:01 |
| 45 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 47 | 1 (0)| 00:00:01 |
|* 46 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | 1 (0)| 00:00:01 |
| 47 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 48 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 49 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 50 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 51 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 52 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 53 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 54 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
|* 55 | HASH JOIN | | | | | |
| 56 | CONNECT BY PUMP | | | | | |
| 57 | VIEW | | 1 | 2049 | 17 (0)| 00:00:01 |
| 58 | COUNT | | | | | |
| 59 | NESTED LOOPS | | 1 | 283 | 17 (0)| 00:00:01 |
| 60 | NESTED LOOPS | | 1 | 279 | 16 (0)| 00:00:01 |
| 61 | NESTED LOOPS OUTER | | 1 | 275 | 15 (0)| 00:00:01 |
| 62 | NESTED LOOPS OUTER | | 1 | 271 | 14 (0)| 00:00:01 |
| 63 | NESTED LOOPS OUTER | | 1 | 267 | 13 (0)| 00:00:01 |
| 64 | NESTED LOOPS | | 1 | 220 | 12 (0)| 00:00:01 |
| 65 | NESTED LOOPS | | 1 | 201 | 11 (0)| 00:00:01 |
| 66 | NESTED LOOPS OUTER | | 1 | 172 | 10 (0)| 00:00:01 |
| 67 | NESTED LOOPS OUTER | | 1 | 164 | 9 (0)| 00:00:01 |
| 68 | NESTED LOOPS | | 1 | 159 | 8 (0)| 00:00:01 |
| 69 | NESTED LOOPS | | 1 | 142 | 7 (0)| 00:00:01 |
| 70 | NESTED LOOPS | | 1 | 134 | 6 (0)| 00:00:01 |
| 71 | NESTED LOOPS | | 1 | 110 | 5 (0)| 00:00:01 |
| 72 | NESTED LOOPS OUTER | | 1 | 86 | 4 (0)| 00:00:01 |
| 73 | NESTED LOOPS | | 1 | 78 | 3 (0)| 00:00:01 |
| 74 | NESTED LOOPS | | 1 | 57 | 2 (0)| 00:00:01 |
|* 75 | TABLE ACCESS BY INDEX ROWID| ZLBAKTABLES | 1 | 25 | 1 (0)| 00:00:01 |
|* 76 | INDEX RANGE SCAN | ZLBAKTABLES_PK | 101 | | 1 (0)| 00:00:01 |
| 77 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 32 | 1 (0)| 00:00:01 |
|* 78 | INDEX RANGE SCAN | I_OBJ2 | 1 | | 1 (0)| 00:00:01 |
|* 79 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 21 | 1 (0)| 00:00:01 |
|* 80 | INDEX RANGE SCAN | I_CDEF2 | 3 | | 1 (0)| 00:00:01 |
| 81 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 82 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 1 (0)| 00:00:01 |
| 83 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 24 | 1 (0)| 00:00:01 |
|* 84 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 1 (0)| 00:00:01 |
| 85 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 24 | 1 (0)| 00:00:01 |
|* 86 | INDEX UNIQUE SCAN | I_CON1 | 1 | | 1 (0)| 00:00:01 |
|* 87 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 88 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | 1 (0)| 00:00:01 |
| 89 | TABLE ACCESS BY INDEX ROWID | CCOL$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 90 | INDEX RANGE SCAN | I_CCOL2 | 1 | | 1 (0)| 00:00:01 |
|* 91 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 |
| 92 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 93 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
|* 94 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 29 | 1 (0)| 00:00:01 |
|* 95 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
| 96 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 19 | 1 (0)| 00:00:01 |
|* 97 | INDEX UNIQUE SCAN | I_COL3 | 1 | | 1 (0)| 00:00:01 |
| 98 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 47 | 1 (0)| 00:00:01 |
|* 99 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | 1 (0)| 00:00:01 |
| 100 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|*101 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 102 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|*103 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 104 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|*105 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 106 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|*107 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 108 | VIEW | | 1 | 2113 | 17 (0)| 00:00:01 |
| 109 | COUNT | | | | | |
| 110 | NESTED LOOPS | | 1 | 357 | 17 (0)| 00:00:01 |
| 111 | NESTED LOOPS OUTER | | 1 | 353 | 16 (0)| 00:00:01 |
| 112 | NESTED LOOPS OUTER | | 1 | 349 | 15 (0)| 00:00:01 |
| 113 | NESTED LOOPS | | 1 | 345 | 14 (0)| 00:00:01 |
| 114 | NESTED LOOPS OUTER | | 1 | 341 | 13 (0)| 00:00:01 |
| 115 | NESTED LOOPS | | 1 | 294 | 12 (0)| 00:00:01 |
| 116 | NESTED LOOPS | | 1 | 266 | 11 (0)| 00:00:01 |
| 117 | NESTED LOOPS OUTER | | 1 | 213 | 10 (0)| 00:00:01 |
| 118 | NESTED LOOPS OUTER | | 1 | 205 | 9 (0)| 00:00:01 |
| 119 | NESTED LOOPS | | 1 | 200 | 8 (0)| 00:00:01 |
| 120 | NESTED LOOPS | | 1 | 180 | 7 (0)| 00:00:01 |
| 121 | NESTED LOOPS | | 1 | 167 | 6 (0)| 00:00:01 |
| 122 | NESTED LOOPS | | 1 | 143 | 5 (0)| 00:00:01 |
| 123 | NESTED LOOPS OUTER | | 1 | 119 | 4 (0)| 00:00:01 |
| 124 | NESTED LOOPS | | 1 | 111 | 3 (0)| 00:00:01 |
| 125 | NESTED LOOPS | | 1 | 81 | 2 (0)| 00:00:01 |
|*126 | TABLE ACCESS BY INDEX ROWID | ZLBAKTABLES | 1 | 25 | 1 (0)| 00:00:01 |
|*127 | INDEX RANGE SCAN | ZLBAKTABLES_PK | 101 | | 1 (0)| 00:00:01 |
| 128 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 56 | 1 (0)| 00:00:01 |
|*129 | INDEX RANGE SCAN | I_OBJ2 | 1 | | 1 (0)| 00:00:01 |
|*130 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 30 | 1 (0)| 00:00:01 |
|*131 | INDEX RANGE SCAN | I_CDEF2 | 3 | | 1 (0)| 00:00:01 |
| 132 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 8 | 1 (0)| 00:00:01 |
|*133 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 1 (0)| 00:00:01 |
| 134 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 24 | 1 (0)| 00:00:01 |
|*135 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 1 (0)| 00:00:01 |
| 136 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 24 | 1 (0)| 00:00:01 |
|*137 | INDEX UNIQUE SCAN | I_CON1 | 1 | | 1 (0)| 00:00:01 |
|*138 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 13 | 1 (0)| 00:00:01 |
|*139 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | 1 (0)| 00:00:01 |
| 140 | TABLE ACCESS BY INDEX ROWID | CCOL$ | 1 | 20 | 1 (0)| 00:00:01 |
|*141 | INDEX RANGE SCAN | I_CCOL2 | 1 | | 1 (0)| 00:00:01 |
|*142 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 |
| 143 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 8 | 1 (0)| 00:00:01 |
|*144 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
|*145 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 53 | 1 (0)| 00:00:01 |
|*146 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
| 147 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 28 | 1 (0)| 00:00:01 |
|*148 | INDEX UNIQUE SCAN | I_COL3 | 1 | | 1 (0)| 00:00:01 |
| 149 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 47 | 1 (0)| 00:00:01 |
|*150 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | 1 (0)| 00:00:01 |
| 151 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|*152 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 153 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|*154 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 155 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|*156 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 157 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|*158 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(CONNECT_BY_ISLEAF=1)
3 - access("D"."CONSTRAINT_NAME"=PRIOR "D"."CONSTRAINT_NAME")
filter("D"."POSITION"=PRIOR "D"."POSITION"+1)
4 - filter("D"."POSITION"=1)
22 - filter("T"."直接转出"=1)
23 - access("T"."系统"=100)
25 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"="T"."表名")
26 - filter((DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C','?')='P' OR
DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C','?')='U') AND "C"."TYPE#"<>8 AND
"C"."TYPE#"<>12)
27 - access("C"."OBJ#"="O"."OBJ#")
29 - access("C"."RCON#"="RC"."CON#"(+))
31 - access("OC"."CON#"="C"."CON#")
33 - access("C"."OWNER#"=USERENV('SCHEMAID') AND "OC"."NAME"="C"."NAME")
34 - filter("CD"."TYPE#"<>12)
35 - access("C"."CON#"="CD"."CON#")
37 - access("CD"."CON#"="CC"."CON#")
38 - access("C"."ROBJ#"="RO"."OBJ#"(+))
40 - access("C"."ENABLED"="OI"."OBJ#"(+))
41 - filter("O"."NAME"="O"."NAME")
42 - access("CC"."OBJ#"="O"."OBJ#")
44 - access("CC"."OBJ#"="COL"."OBJ#" AND "CC"."INTCOL#"="COL"."INTCOL#")
46 - access("COL"."OBJ#"="AC"."OBJ#"(+) AND "COL"."INTCOL#"="AC"."INTCOL#"(+))
48 - access("U"."USER#"=USERENV('SCHEMAID'))
50 - access("OI"."OWNER#"="UI"."USER#"(+))
52 - access("RC"."OWNER#"="RU"."USER#"(+))
54 - access("OC"."OWNER#"="OU"."USER#")
55 - access("D"."CONSTRAINT_NAME"=PRIOR "D"."CONSTRAINT_NAME")
75 - filter("T"."直接转出"=1)
76 - access("T"."系统"=100)
78 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"="T"."表名")
79 - filter((DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C','?')='P' OR
DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C','?')='U') AND "C"."TYPE#"<>8 AND
"C"."TYPE#"<>12)
80 - access("C"."OBJ#"="O"."OBJ#")
82 - access("C"."RCON#"="RC"."CON#"(+))
84 - access("OC"."CON#"="C"."CON#")
86 - access("C"."OWNER#"=USERENV('SCHEMAID') AND "OC"."NAME"="C"."NAME")
87 - filter("CD"."TYPE#"<>12)
88 - access("C"."CON#"="CD"."CON#")
90 - access("CD"."CON#"="CC"."CON#")
91 - access("C"."ROBJ#"="RO"."OBJ#"(+))
93 - access("C"."ENABLED"="OI"."OBJ#"(+))
94 - filter("O"."NAME"="O"."NAME")
95 - access("CC"."OBJ#"="O"."OBJ#")
97 - access("CC"."OBJ#"="COL"."OBJ#" AND "CC"."INTCOL#"="COL"."INTCOL#")
99 - access("COL"."OBJ#"="AC"."OBJ#"(+) AND "COL"."INTCOL#"="AC"."INTCOL#"(+))
101 - access("OI"."OWNER#"="UI"."USER#"(+))
103 - access("RC"."OWNER#"="RU"."USER#"(+))
105 - access("OC"."OWNER#"="OU"."USER#")
107 - access("C"."OWNER#"="U"."USER#")
126 - filter("T"."直接转出"=1)
127 - access("T"."系统"=100)
129 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"="T"."表名")
130 - filter((DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C','?')='P' OR
DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C','?')='U') AND "C"."TYPE#"<>8 AND
"C"."TYPE#"<>12)
131 - access("C"."OBJ#"="O"."OBJ#")
133 - access("C"."RCON#"="RC"."CON#"(+))
135 - access("OC"."CON#"="C"."CON#")
137 - access("C"."OWNER#"=USERENV('SCHEMAID') AND "OC"."NAME"="C"."NAME")
138 - filter("CD"."TYPE#"<>12)
139 - access("C"."CON#"="CD"."CON#")
141 - access("CD"."CON#"="CC"."CON#")
142 - access("C"."ROBJ#"="RO"."OBJ#"(+))
144 - access("C"."ENABLED"="OI"."OBJ#"(+))
145 - filter("O"."NAME"="O"."NAME")
146 - access("CC"."OBJ#"="O"."OBJ#")
148 - access("CC"."OBJ#"="COL"."OBJ#" AND "CC"."INTCOL#"="COL"."INTCOL#")
150 - access("COL"."OBJ#"="AC"."OBJ#"(+) AND "COL"."INTCOL#"="AC"."INTCOL#"(+))
152 - access("U"."USER#"=USERENV('SCHEMAID'))
154 - access("OI"."OWNER#"="UI"."USER#"(+))
156 - access("RC"."OWNER#"="RU"."USER#"(+))
158 - access("OC"."OWNER#"="OU"."USER#")

统计信息
----------------------------------------------------------
29 recursive calls
0 db block gets
15598 consistent gets
0 physical reads
0 redo size
7908 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
131 rows processed

SQL> /

已选择131行。

执行计划
----------------------------------------------------------
Plan hash value: 2726543698

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2049 | 17 (0)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 2049 | 17 (0)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | CONNECT BY WITH FILTERING | | | | | |
|* 4 | VIEW | | 1 | 2126 | 17 (0)| 00:00:01 |
| 5 | COUNT | | | | | |
| 6 | NESTED LOOPS | | 1 | 357 | 17 (0)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 1 | 353 | 16 (0)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 1 | 349 | 15 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 345 | 14 (0)| 00:00:01 |
| 10 | NESTED LOOPS OUTER | | 1 | 341 | 13 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 294 | 12 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 266 | 11 (0)| 00:00:01 |
| 13 | NESTED LOOPS OUTER | | 1 | 213 | 10 (0)| 00:00:01 |
| 14 | NESTED LOOPS OUTER | | 1 | 205 | 9 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 200 | 8 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 180 | 7 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 167 | 6 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 143 | 5 (0)| 00:00:01 |
| 19 | NESTED LOOPS OUTER | | 1 | 119 | 4 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 111 | 3 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 81 | 2 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID | ZLBAKTABLES | 1 | 25 | 1 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | ZLBAKTABLES_PK | 101 | | 1 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 56 | 1 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | I_OBJ2 | 1 | | 1 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 30 | 1 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | I_CDEF2 | 3 | | 1 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 1 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 24 | 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 1 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 24 | 1 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | I_CON1 | 1 | | 1 (0)| 00:00:01 |
|* 34 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 13 | 1 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | 1 (0)| 00:00:01 |
| 36 | TABLE ACCESS BY INDEX ROWID | CCOL$ | 1 | 20 | 1 (0)| 00:00:01 |
|* 37 | INDEX RANGE SCAN | I_CCOL2 | 1 | | 1 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
|* 41 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 53 | 1 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
| 43 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 28 | 1 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | I_COL3 | 1 | | 1 (0)| 00:00:01 |
| 45 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 47 | 1 (0)| 00:00:01 |
|* 46 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | 1 (0)| 00:00:01 |
| 47 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 48 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 49 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 50 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 51 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 52 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 53 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 54 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
|* 55 | HASH JOIN | | | | | |
| 56 | CONNECT BY PUMP | | | | | |
| 57 | VIEW | | 1 | 2049 | 17 (0)| 00:00:01 |
| 58 | COUNT | | | | | |
| 59 | NESTED LOOPS | | 1 | 283 | 17 (0)| 00:00:01 |
| 60 | NESTED LOOPS | | 1 | 279 | 16 (0)| 00:00:01 |
| 61 | NESTED LOOPS OUTER | | 1 | 275 | 15 (0)| 00:00:01 |
| 62 | NESTED LOOPS OUTER | | 1 | 271 | 14 (0)| 00:00:01 |
| 63 | NESTED LOOPS OUTER | | 1 | 267 | 13 (0)| 00:00:01 |
| 64 | NESTED LOOPS | | 1 | 220 | 12 (0)| 00:00:01 |
| 65 | NESTED LOOPS | | 1 | 201 | 11 (0)| 00:00:01 |
| 66 | NESTED LOOPS OUTER | | 1 | 172 | 10 (0)| 00:00:01 |
| 67 | NESTED LOOPS OUTER | | 1 | 164 | 9 (0)| 00:00:01 |
| 68 | NESTED LOOPS | | 1 | 159 | 8 (0)| 00:00:01 |
| 69 | NESTED LOOPS | | 1 | 142 | 7 (0)| 00:00:01 |
| 70 | NESTED LOOPS | | 1 | 134 | 6 (0)| 00:00:01 |
| 71 | NESTED LOOPS | | 1 | 110 | 5 (0)| 00:00:01 |
| 72 | NESTED LOOPS OUTER | | 1 | 86 | 4 (0)| 00:00:01 |
| 73 | NESTED LOOPS | | 1 | 78 | 3 (0)| 00:00:01 |
| 74 | NESTED LOOPS | | 1 | 57 | 2 (0)| 00:00:01 |
|* 75 | TABLE ACCESS BY INDEX ROWID| ZLBAKTABLES | 1 | 25 | 1 (0)| 00:00:01 |
|* 76 | INDEX RANGE SCAN | ZLBAKTABLES_PK | 101 | | 1 (0)| 00:00:01 |
| 77 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 32 | 1 (0)| 00:00:01 |
|* 78 | INDEX RANGE SCAN | I_OBJ2 | 1 | | 1 (0)| 00:00:01 |
|* 79 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 21 | 1 (0)| 00:00:01 |
|* 80 | INDEX RANGE SCAN | I_CDEF2 | 3 | | 1 (0)| 00:00:01 |
| 81 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 82 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 1 (0)| 00:00:01 |
| 83 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 24 | 1 (0)| 00:00:01 |
|* 84 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 1 (0)| 00:00:01 |
| 85 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 24 | 1 (0)| 00:00:01 |
|* 86 | INDEX UNIQUE SCAN | I_CON1 | 1 | | 1 (0)| 00:00:01 |
|* 87 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 88 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | 1 (0)| 00:00:01 |
| 89 | TABLE ACCESS BY INDEX ROWID | CCOL$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 90 | INDEX RANGE SCAN | I_CCOL2 | 1 | | 1 (0)| 00:00:01 |
|* 91 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 |
| 92 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 93 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
|* 94 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 29 | 1 (0)| 00:00:01 |
|* 95 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
| 96 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 19 | 1 (0)| 00:00:01 |
|* 97 | INDEX UNIQUE SCAN | I_COL3 | 1 | | 1 (0)| 00:00:01 |
| 98 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 47 | 1 (0)| 00:00:01 |
|* 99 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | 1 (0)| 00:00:01 |
| 100 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|*101 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 102 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|*103 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 104 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|*105 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 106 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|*107 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 108 | VIEW | | 1 | 2113 | 17 (0)| 00:00:01 |
| 109 | COUNT | | | | | |
| 110 | NESTED LOOPS | | 1 | 357 | 17 (0)| 00:00:01 |
| 111 | NESTED LOOPS OUTER | | 1 | 353 | 16 (0)| 00:00:01 |
| 112 | NESTED LOOPS OUTER | | 1 | 349 | 15 (0)| 00:00:01 |
| 113 | NESTED LOOPS | | 1 | 345 | 14 (0)| 00:00:01 |
| 114 | NESTED LOOPS OUTER | | 1 | 341 | 13 (0)| 00:00:01 |
| 115 | NESTED LOOPS | | 1 | 294 | 12 (0)| 00:00:01 |
| 116 | NESTED LOOPS | | 1 | 266 | 11 (0)| 00:00:01 |
| 117 | NESTED LOOPS OUTER | | 1 | 213 | 10 (0)| 00:00:01 |
| 118 | NESTED LOOPS OUTER | | 1 | 205 | 9 (0)| 00:00:01 |
| 119 | NESTED LOOPS | | 1 | 200 | 8 (0)| 00:00:01 |
| 120 | NESTED LOOPS | | 1 | 180 | 7 (0)| 00:00:01 |
| 121 | NESTED LOOPS | | 1 | 167 | 6 (0)| 00:00:01 |
| 122 | NESTED LOOPS | | 1 | 143 | 5 (0)| 00:00:01 |
| 123 | NESTED LOOPS OUTER | | 1 | 119 | 4 (0)| 00:00:01 |
| 124 | NESTED LOOPS | | 1 | 111 | 3 (0)| 00:00:01 |
| 125 | NESTED LOOPS | | 1 | 81 | 2 (0)| 00:00:01 |
|*126 | TABLE ACCESS BY INDEX ROWID | ZLBAKTABLES | 1 | 25 | 1 (0)| 00:00:01 |
|*127 | INDEX RANGE SCAN | ZLBAKTABLES_PK | 101 | | 1 (0)| 00:00:01 |
| 128 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 56 | 1 (0)| 00:00:01 |
|*129 | INDEX RANGE SCAN | I_OBJ2 | 1 | | 1 (0)| 00:00:01 |
|*130 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 30 | 1 (0)| 00:00:01 |
|*131 | INDEX RANGE SCAN | I_CDEF2 | 3 | | 1 (0)| 00:00:01 |
| 132 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 8 | 1 (0)| 00:00:01 |
|*133 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 1 (0)| 00:00:01 |
| 134 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 24 | 1 (0)| 00:00:01 |
|*135 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 1 (0)| 00:00:01 |
| 136 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 24 | 1 (0)| 00:00:01 |
|*137 | INDEX UNIQUE SCAN | I_CON1 | 1 | | 1 (0)| 00:00:01 |
|*138 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 13 | 1 (0)| 00:00:01 |
|*139 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | 1 (0)| 00:00:01 |
| 140 | TABLE ACCESS BY INDEX ROWID | CCOL$ | 1 | 20 | 1 (0)| 00:00:01 |
|*141 | INDEX RANGE SCAN | I_CCOL2 | 1 | | 1 (0)| 00:00:01 |
|*142 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 |
| 143 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 8 | 1 (0)| 00:00:01 |
|*144 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
|*145 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 53 | 1 (0)| 00:00:01 |
|*146 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
| 147 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 28 | 1 (0)| 00:00:01 |
|*148 | INDEX UNIQUE SCAN | I_COL3 | 1 | | 1 (0)| 00:00:01 |
| 149 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 47 | 1 (0)| 00:00:01 |
|*150 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | 1 (0)| 00:00:01 |
| 151 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|*152 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 153 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|*154 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 155 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|*156 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 157 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|*158 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(CONNECT_BY_ISLEAF=1)
3 - access("D"."CONSTRAINT_NAME"=PRIOR "D"."CONSTRAINT_NAME")
filter("D"."POSITION"=PRIOR "D"."POSITION"+1)
4 - filter("D"."POSITION"=1)
22 - filter("T"."直接转出"=1)
23 - access("T"."系统"=100)
25 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"="T"."表名")
26 - filter((DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C','?')='P' OR
DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C','?')='U') AND "C"."TYPE#"<>8 AND
"C"."TYPE#"<>12)
27 - access("C"."OBJ#"="O"."OBJ#")
29 - access("C"."RCON#"="RC"."CON#"(+))
31 - access("OC"."CON#"="C"."CON#")
33 - access("C"."OWNER#"=USERENV('SCHEMAID') AND "OC"."NAME"="C"."NAME")
34 - filter("CD"."TYPE#"<>12)
35 - access("C"."CON#"="CD"."CON#")
37 - access("CD"."CON#"="CC"."CON#")
38 - access("C"."ROBJ#"="RO"."OBJ#"(+))
40 - access("C"."ENABLED"="OI"."OBJ#"(+))
41 - filter("O"."NAME"="O"."NAME")
42 - access("CC"."OBJ#"="O"."OBJ#")
44 - access("CC"."OBJ#"="COL"."OBJ#" AND "CC"."INTCOL#"="COL"."INTCOL#")
46 - access("COL"."OBJ#"="AC"."OBJ#"(+) AND "COL"."INTCOL#"="AC"."INTCOL#"(+))
48 - access("U"."USER#"=USERENV('SCHEMAID'))
50 - access("OI"."OWNER#"="UI"."USER#"(+))
52 - access("RC"."OWNER#"="RU"."USER#"(+))
54 - access("OC"."OWNER#"="OU"."USER#")
55 - access("D"."CONSTRAINT_NAME"=PRIOR "D"."CONSTRAINT_NAME")
75 - filter("T"."直接转出"=1)
76 - access("T"."系统"=100)
78 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"="T"."表名")
79 - filter((DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C','?')='P' OR
DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C','?')='U') AND "C"."TYPE#"<>8 AND
"C"."TYPE#"<>12)
80 - access("C"."OBJ#"="O"."OBJ#")
82 - access("C"."RCON#"="RC"."CON#"(+))
84 - access("OC"."CON#"="C"."CON#")
86 - access("C"."OWNER#"=USERENV('SCHEMAID') AND "OC"."NAME"="C"."NAME")
87 - filter("CD"."TYPE#"<>12)
88 - access("C"."CON#"="CD"."CON#")
90 - access("CD"."CON#"="CC"."CON#")
91 - access("C"."ROBJ#"="RO"."OBJ#"(+))
93 - access("C"."ENABLED"="OI"."OBJ#"(+))
94 - filter("O"."NAME"="O"."NAME")
95 - access("CC"."OBJ#"="O"."OBJ#")
97 - access("CC"."OBJ#"="COL"."OBJ#" AND "CC"."INTCOL#"="COL"."INTCOL#")
99 - access("COL"."OBJ#"="AC"."OBJ#"(+) AND "COL"."INTCOL#"="AC"."INTCOL#"(+))
101 - access("OI"."OWNER#"="UI"."USER#"(+))
103 - access("RC"."OWNER#"="RU"."USER#"(+))
105 - access("OC"."OWNER#"="OU"."USER#")
107 - access("C"."OWNER#"="U"."USER#")
126 - filter("T"."直接转出"=1)
127 - access("T"."系统"=100)
129 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"="T"."表名")
130 - filter((DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C','?')='P' OR
DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C','?')='U') AND "C"."TYPE#"<>8 AND
"C"."TYPE#"<>12)
131 - access("C"."OBJ#"="O"."OBJ#")
133 - access("C"."RCON#"="RC"."CON#"(+))
135 - access("OC"."CON#"="C"."CON#")
137 - access("C"."OWNER#"=USERENV('SCHEMAID') AND "OC"."NAME"="C"."NAME")
138 - filter("CD"."TYPE#"<>12)
139 - access("C"."CON#"="CD"."CON#")
141 - access("CD"."CON#"="CC"."CON#")
142 - access("C"."ROBJ#"="RO"."OBJ#"(+))
144 - access("C"."ENABLED"="OI"."OBJ#"(+))
145 - filter("O"."NAME"="O"."NAME")
146 - access("CC"."OBJ#"="O"."OBJ#")
148 - access("CC"."OBJ#"="COL"."OBJ#" AND "CC"."INTCOL#"="COL"."INTCOL#")
150 - access("COL"."OBJ#"="AC"."OBJ#"(+) AND "COL"."INTCOL#"="AC"."INTCOL#"(+))
152 - access("U"."USER#"=USERENV('SCHEMAID'))
154 - access("OI"."OWNER#"="UI"."USER#"(+))
156 - access("RC"."OWNER#"="RU"."USER#"(+))
158 - access("OC"."OWNER#"="OU"."USER#")

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
15590 consistent gets
0 physical reads
0 redo size
7908 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
131 rows processed

SQL> Select d.Table_Name, d.Constraint_Name, f_List2str(Cast(Collect(d.Column_Name Order By d.Position) As t_Strlist)) Colstr
2 From User_Cons_Columns D,
3 (Select a.Table_Name, a.Constraint_Name
4 From User_Constraints A, zlBakTables T
5 Where a.Table_Name = t.表名 And t.直接转出 = 1 And t.系统 = 100 And a.Constraint_Type In ('P', 'U')) A
6 Where a.Constraint_Name = d.Constraint_Name And a.Table_Name = d.Table_Name
7 Group By d.Table_Name, d.Constraint_Name
8 Order By Constraint_Name;

已选择131行。

执行计划
----------------------------------------------------------
Plan hash value: 2192633783

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 283 | 18 (6)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 283 | 18 (6)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 283 | 17 (0)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 1 | 279 | 16 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 275 | 15 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 271 | 14 (0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 267 | 13 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 220 | 12 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 201 | 11 (0)| 00:00:01 |
| 9 | NESTED LOOPS OUTER | | 1 | 172 | 10 (0)| 00:00:01 |
| 10 | NESTED LOOPS OUTER | | 1 | 164 | 9 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 159 | 8 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 142 | 7 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 134 | 6 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 110 | 5 (0)| 00:00:01 |
| 15 | NESTED LOOPS OUTER | | 1 | 86 | 4 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 78 | 3 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 57 | 2 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID| ZLBAKTABLES | 1 | 25 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | ZLBAKTABLES_PK | 101 | | 1 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 32 | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | I_OBJ2 | 1 | | 1 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 21 | 1 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | I_CDEF2 | 3 | | 1 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 1 (0)| 00:00:01 |
| 26 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 24 | 1 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 1 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 24 | 1 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | I_CON1 | 1 | | 1 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | 1 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | CCOL$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | I_CCOL2 | 1 | | 1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
|* 37 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 29 | 1 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 19 | 1 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | I_COL3 | 1 | | 1 (0)| 00:00:01 |
| 41 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 47 | 1 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | 1 (0)| 00:00:01 |
| 43 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 45 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 46 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 47 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 48 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 49 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 50 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

18 - filter("T"."直接转出"=1)
19 - access("T"."系统"=100)
21 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"="T"."表名")
22 - filter((DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C','?')='P' OR
DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C','?')='U') AND "C"."TYPE#"<>8 AND
"C"."TYPE#"<>12)
23 - access("C"."OBJ#"="O"."OBJ#")
25 - access("C"."RCON#"="RC"."CON#"(+))
27 - access("OC"."CON#"="C"."CON#")
29 - access("C"."OWNER#"=USERENV('SCHEMAID') AND "OC"."NAME"="C"."NAME")
30 - filter("CD"."TYPE#"<>12)
31 - access("C"."CON#"="CD"."CON#")
33 - access("CD"."CON#"="CC"."CON#")
34 - access("C"."ROBJ#"="RO"."OBJ#"(+))
36 - access("C"."ENABLED"="OI"."OBJ#"(+))
37 - filter("O"."NAME"="O"."NAME")
38 - access("CC"."OBJ#"="O"."OBJ#")
40 - access("CC"."OBJ#"="COL"."OBJ#" AND "CC"."INTCOL#"="COL"."INTCOL#")
42 - access("COL"."OBJ#"="AC"."OBJ#"(+) AND "COL"."INTCOL#"="AC"."INTCOL#"(+))
44 - access("U"."USER#"=USERENV('SCHEMAID'))
46 - access("OI"."OWNER#"="UI"."USER#"(+))
48 - access("RC"."OWNER#"="RU"."USER#"(+))
50 - access("OC"."OWNER#"="OU"."USER#")

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5200 consistent gets
0 physical reads
0 redo size
7908 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
131 rows processed

SQL> spool off

附：
Create Or Replace Function f_List2str
(
p_Strlist In t_Strlist,
p_Delimiter In Varchar2 Default ',',
p_Distinct In Number Default 1
) Return Varchar2 Is
l_String Long;
l_Add Number;
--功能：将一个列表集合转换为一个缺省以逗号分隔的字符串。
--例：
--Select 科室, f_List2str(Cast(Collect(人员 Order By 编号) As t_Strlist)) 人员列表
--From (Select a.名称 As 科室, c.姓名 As 人员,c.编号
-- From 部门表 A, 部门人员 B, 人员表 C
-- Where a.Id = b.部门id And b.人员id = c.Id
-- Order By 科室, 人员)
--Group By 科室

--此函数不支持with方式构造的临时内存表，这将会报错：ORA-00932: 数据类型不一致: 应为 -, 但却获得 -。
--例如：With Test As (Select '内科' As 科室,'张三' As 人员 From Dual Union All......)
-- Select 科室,f_List2str(cast(COLLECT(人员) as t_Strlist)) tt From Test Group By 科室
Begin
If p_Strlist.Count > 0 Then
For I In p_Strlist.First .. p_Strlist.Last Loop
l_Add := 0;
If p_Distinct = 1 Then
If Instr(',' || l_String || ',', ',' || p_Strlist(I) || ',') = 0 Then
l_Add := 1;
End If;
Else
l_Add := 1;
End If;
If l_Add = 1 Then
If I != p_Strlist.First Then
l_String := l_String || p_Delimiter;
End If;
l_String := l_String || p_Strlist(I);
End If;
End Loop;
End If;
Return l_String;
End f_List2str;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/117319/viewspace-1839906/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/117319/viewspace-1839906/

cnfs3521

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
两种字符汇聚方法的性能对比

对比结果（逻辑读块数）f_List2str：第一次：5311,第二次：5200Sys_Connect_By_Path：第一次：15598,第二次：15590结论：自定义函数f_List2str比树形查询Sys...
复制链接

扫一扫