-
Notifications
You must be signed in to change notification settings - Fork 849
/
Copy pathexpression.lst
124 lines (86 loc) · 5.24 KB
/
expression.lst
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
Table created.
COUNT(*)
----------
1773
PL/SQL procedure successfully completed.
COUNT(*)
----------
1773
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 8caz15dwj8w8k, child number 0
-------------------------------------
SELECT /* MYQUERY */ COUNT(*) FROM t WHERE ( "DELIVERY_DATE" >=
trunc(sysdate@!, 'fmyear') AND ( coalesce("CONTROL_FLAG", 1) = 1
OR coalesce("CONTROL_FLAG", 1) = 3 ) AND (
coalesce(substr(to_char("ACCOUNT_ID"), 4, 1), '1') = '1' OR
coalesce(substr(to_char("ACCOUNT_ID"), 4, 1), '1') = '3' ) AND
"DELIVERY_DATE" <= trunc(sysdate@! - 1) )
Plan hash value: 1010173228
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL| T | 2 | 30 | 9 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
2 - filter(TRUNC(SYSDATE@!-1)>=TRUNC(SYSDATE@!,'fmyear'))
3 - filter(((COALESCE("CONTROL_FLAG",1)=1 OR
COALESCE("CONTROL_FLAG",1)=3) AND "DELIVERY_DATE"<=TRUNC(SYSDATE@!-1)
AND (COALESCE(SUBSTR(TO_CHAR("ACCOUNT_ID"),4,1),'1')='1' OR
COALESCE(SUBSTR(TO_CHAR("ACCOUNT_ID"),4,1),'1')='3') AND
"DELIVERY_DATE">=TRUNC(SYSDATE@!,'fmyear')))
30 rows selected.
TABLE_NAME OWNER EXPRESSION_TEXT
---------------------------------------- ---------------------------------------- ----------------------------------------------------------------------------------------------------
T ADHOC COALESCE(SUBSTR(TO_CHAR("ACCOUNT_ID"),4,1),'1')
T ADHOC "DELIVERY_DATE"
T ADHOC COALESCE("CONTROL_FLAG",1)
DBMS_STATS.CREATE_EXTENDED_STATS(USER,'T',Q'[(COALESCE("CONTROL_FLAG",1))]')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS_STUARLGZBSAG4YYL$Q$14300KT
DBMS_STATS.CREATE_EXTENDED_STATS(USER,'T',Q'[(COALESCE(SUBSTR(TO_CHAR("ACCOUNT_ID"),4,1),'1'))]')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS_STU2K568KT2T5R6S37VQUN63#V
PL/SQL procedure successfully completed.
COUNT(*)
----------
1773
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 8caz15dwj8w8k, child number 0
-------------------------------------
SELECT /* MYQUERY */ COUNT(*) FROM t WHERE ( "DELIVERY_DATE" >=
trunc(sysdate@!, 'fmyear') AND ( coalesce("CONTROL_FLAG", 1) = 1
OR coalesce("CONTROL_FLAG", 1) = 3 ) AND (
coalesce(substr(to_char("ACCOUNT_ID"), 4, 1), '1') = '1' OR
coalesce(substr(to_char("ACCOUNT_ID"), 4, 1), '1') = '3' ) AND
"DELIVERY_DATE" <= trunc(sysdate@! - 1) )
Plan hash value: 1010173228
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL| T | 1363 | 17719 | 10 (10)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
2 - filter(TRUNC(SYSDATE@!-1)>=TRUNC(SYSDATE@!,'fmyear'))
3 - filter(("DELIVERY_DATE"<=TRUNC(SYSDATE@!-1) AND
COALESCE(SUBSTR(TO_CHAR("ACCOUNT_ID"),4,1),'1') AND
"DELIVERY_DATE">=TRUNC(SYSDATE@!,'fmyear') AND
COALESCE("CONTROL_FLAG",1)))
29 rows selected.