以前同事的问题，非常隐蔽的问题，一直没有发现，跟大家分析！
SQL> set autotrace on
SQL> SELECT
2 T5.CONFLICT_ID,
3 T5.LAST_UPD,
4 T5.CREATED,
5 T5.LAST_UPD_BY,
6 T5.CREATED_BY,
7 T5.MODIFICATION_NUM,
8 T5.ROW_ID,
9 T33.PROVIDER_FLG,
10 T15.NAME,
11 T33.CURR_PRI_LST_ID,
12 T17.KEY_VALUE,
13 T33.CITIZENSHIP_CD,
14 T33.DEDUP_KEY_UPD_DT,
15 T22.ROW_ID,
16 T31.STATE,
17 T31.ADDR,
18 T33.CON_CD,
19 T31.COUNTRY,
20 T31.CITY,
21 T31.ZIPCODE,
22 T33.CUST_SINCE_DT,
23 T33.PR_REGION_ID,
24 T33.NATIONALITY,
25 T28.CON_ID,
26 T33.PR_SECURITY_ID,
27 T4.NAME,
28 T33.MED_SPEC_ID,
29 T19.PR_EMP_ID,
30 T33.PREF_COMM_METH_CD,
31 T33.PR_OU_ADDR_ID,
32 T30.PR_EMP_ID,
33 T21.LOGIN,
34 T19.PR_EMP_ID,
35 T33.PR_PROD_LN_ID,
36 T33.PR_TERR_ID,
37 T9.PR_SMS_NUM_ID,
38 T33.PR_STATE_LIC_ID,
39 T33.AGENT_FLG,
40 T33.MAIDEN_NAME,
41 T33.MEMBER_FLG,
42 T33.PR_NOTE_ID,
43 T33.PR_INDUST_ID,
44 T26.LOGIN,
45 T9.PR_FAX_NUM_ID,
46 T33.SUPPRESS_MAIL_FLG,
47 T33.EMAIL_ADDR,
48 T33.BIRTH_DT,
49 T33.JOB_TITLE,
50 T33.MID_NAME,
51 T33.PR_DEPT_OU_ID,
52 T33.LAST_NAME,
53 T33.SEX_MF,
54 T33.PR_PER_ADDR_ID,
55 T33.PR_POSTN_ID,
56 T33.COMMENTS,
57 T28.PR_ADDR_ID,
58 T33.HOME_PH_NUM,
59 T33.OWNER_PER_ID,
60 T33.CELL_PH_NUM,
61 T33.WORK_PH_NUM,
62 T33.FAX_PH_NUM,
63 T33.FST_NAME,
64 T33.ASST_PH_NUM,
65 T18.ATTRIB_07,
66 T2.INTEGRATION_ID,
67 T33.PR_PER_PAY_PRFL_ID,
68 T33.INTEGRATION_ID,
69 T33.PRIV_FLG,
70 T33.PR_MKT_SEG_ID,
71 T33.PR_REP_SYS_FLG,
72 T33.PR_REP_MANL_FLG,
73 T33.PR_REP_DNRM_FLG,
74 T33.PR_OPTY_ID,
75 T33.SOC_SECURITY_NUM,
76 T33.PR_GRP_OU_ID,
77 T33.EMP_FLG,
78 T7.OWN_INST_ID,
79 T7.INTEGRATION_ID,
80 T33.PERSON_UID,
81 T5.NAME,
82 T2.NAME,
83 T2.PRTNR_FLG,
84 T33.PR_RESP_ID,
85 T33.BU_ID,
86 T28.STATUS,
87 T33.PR_ALT_PH_NUM_ID,
88 T33.PR_EMAIL_ADDR_ID,
89 T20.SHARE_HOME_PH_FLG,
90 T33.PR_SYNC_USER_ID,
91 T33.CON_CREATED_DT,
92 T33.EYE_COLOR,
93 T33.STOCK_PORTFOLIO,
94 T33.X_ACCNT_ID,
95 T18.ATTRIB_43,
96 T29.LOGIN,
97 T23.LAST_NAME,
98 T32.NAME,
99 T32.X_DEALER_CODE,
100 T33.SEX_MF,
101 T33.X_UPD_FLG,
102 T24.LOGIN,
103 T27.ROW_STATUS,
104 T16.PRIM_MARKET_CD,
105 T3.ROW_ID,
106 T10.OU_NUM,
107 T10.LOC,
108 T10.NAME,
109 T3.ROW_ID,
110 T10.PR_SRV_AGREE_ID,
111 T10.PR_BL_PER_ID,
112 T10.PR_SHIP_PER_ID,
113 T10.PR_BL_ADDR_ID,
114 T10.PR_SHIP_ADDR_ID,
115 T3.ROW_ID,
116 T11.CITY,
117 T11.ADDR,
118 T11.STATE,
119 T11.ZIPCODE,
120 T11.COUNTY,
121 T11.X_COUNTY_CD,
122 T8.LOGIN,
123 T33.ROW_ID,
124 T33.PAR_ROW_ID,
125 T33.MODIFICATION_NUM,
126 T33.CREATED_BY,
127 T33.LAST_UPD_BY,
128 T33.CREATED,
129 T33.LAST_UPD,
130 T33.CONFLICT_ID,
131 T33.PAR_ROW_ID,
132 T18.ROW_ID,
133 T18.PAR_ROW_ID,
134 T18.MODIFICATION_NUM,
135 T18.CREATED_BY,
136 T18.LAST_UPD_BY,
137 T18.CREATED,
138 T18.LAST_UPD,
139 T18.CONFLICT_ID,
140 T18.PAR_ROW_ID,
141 T20.ROW_ID,
142 T20.PAR_ROW_ID,
143 T20.MODIFICATION_NUM,
144 T20.CREATED_BY,
145 T20.LAST_UPD_BY,
146 T20.CREATED,
147 T20.LAST_UPD,
148 T20.CONFLICT_ID,
149 T20.PAR_ROW_ID,
150 T9.ROW_ID,
151 T9.PAR_ROW_ID,
152 T9.MODIFICATION_NUM,
153 T9.CREATED_BY,
154 T9.LAST_UPD_BY,
155 T9.CREATED,
156 T9.LAST_UPD,
157 T9.CONFLICT_ID,
158 T9.PAR_ROW_ID,
159 T7.ROW_ID,
160 T7.PAR_ROW_ID,
161 T7.MODIFICATION_NUM,
162 T7.CREATED_BY,
163 T7.LAST_UPD_BY,
164 T7.CREATED,
165 T7.LAST_UPD,
166 T7.CONFLICT_ID,
167 T7.PAR_ROW_ID,
168 T27.ROW_ID,
169 T25.ROW_ID,
170 T3.ROW_ID,
171 T6.ROW_ID,
172 T11.ROW_ID,
173 T14.ROW_ID
174 FROM
175 SIEBEL.S_CONTACT_BU T1,
176 SIEBEL.S_ORG_EXT T2,
177 SIEBEL.S_PARTY T3,
178 SIEBEL.S_MED_SPEC T4,
179 SIEBEL.S_PARTY T5,
180 SIEBEL.S_CON_ADDR T6,
181 SIEBEL.S_CONTACT_SS T7,
182 SIEBEL.S_USER T8,
183 SIEBEL.S_CONTACT_LOYX T9,
184 SIEBEL.S_ORG_EXT T10,
185 SIEBEL.S_ADDR_PER T11,
186 SIEBEL.S_POSTN T12,
187 SIEBEL.S_PARTY T13,
188 SIEBEL.S_PARTY T14,
189 SIEBEL.S_PRI_LST T15,
190 SIEBEL.S_ORG_EXT_FNX T16,
191 SIEBEL.S_DQ_CON_KEY T17,
192 SIEBEL.S_CONTACT_X T18,
193 SIEBEL.S_POSTN T19,
194 SIEBEL.S_EMP_PER T20,
195 SIEBEL.S_USER T21,
196 SIEBEL.S_CASE T22,
197 SIEBEL.S_USER T23,
198 SIEBEL.S_USER T24,
199 SIEBEL.S_PARTY T25,
200 SIEBEL.S_USER T26,
201 SIEBEL.S_POSTN_CON T27,
202 SIEBEL.S_POSTN_CON T28,
203 SIEBEL.S_USER T29,
204 SIEBEL.S_POSTN T30,
205 SIEBEL.S_ADDR_PER T31,
206 SIEBEL.S_ORG_EXT T32,
207 SIEBEL.S_CONTACT T33
208 WHERE
209 T19.PR_EMP_ID = T26.PAR_ROW_ID (+) AND
210 T2.PR_POSTN_ID = T30.PAR_ROW_ID (+) AND
211 T33.PR_POSTN_ID = T19.PAR_ROW_ID (+) AND
212 T33.PR_DEPT_OU_ID = T2.PAR_ROW_ID (+) AND
213 T5.ROW_ID = T28.CON_ID (+) AND T28.POSTN_ID (+) = '1234' AND
214 T30.PR_EMP_ID = T21.PAR_ROW_ID (+) AND
215 T33.PR_PER_ADDR_ID = T31.ROW_ID (+) AND
216 T5.ROW_ID = T22.PR_SUBJECT_ID (+) AND
217 T33.BU_ID = T32.PAR_ROW_ID (+) AND
218 T33.MED_SPEC_ID = T4.ROW_ID (+) AND
219 T33.CURR_PRI_LST_ID = T15.ROW_ID (+) AND
220 T5.ROW_ID = T17.CONTACT_ID (+) AND
221 T19.PR_EMP_ID = T23.PAR_ROW_ID (+) AND
222 T19.PR_EMP_ID = T29.PAR_ROW_ID (+) AND
223 T5.ROW_ID = T33.PAR_ROW_ID AND
224 T5.ROW_ID = T18.PAR_ROW_ID (+) AND
225 T5.ROW_ID = T20.PAR_ROW_ID (+) AND
226 T5.ROW_ID = T9.PAR_ROW_ID (+) AND
227 T5.ROW_ID = T7.PAR_ROW_ID (+) AND
228 T33.PR_POSTN_ID = T27.POSTN_ID AND T33.ROW_ID = T27.CON_ID AND
229 T27.POSTN_ID = T25.ROW_ID AND
230 T27.POSTN_ID = T12.PAR_ROW_ID (+) AND
231 T12.PR_EMP_ID = T24.PAR_ROW_ID (+) AND
232 T33.PR_DEPT_OU_ID = T3.ROW_ID (+) AND
233 T33.PR_DEPT_OU_ID = T10.PAR_ROW_ID (+) AND
234 T33.PR_DEPT_OU_ID = T16.PAR_ROW_ID (+) AND
235 T33.PR_PER_ADDR_ID = T6.ADDR_PER_ID (+) AND T33.ROW_ID = T6.CONTACT_ID (+) AND
236 T33.PR_PER_ADDR_ID = T11.ROW_ID (+) AND
237 T33.PR_SYNC_USER_ID = T14.ROW_ID (+) AND
238 T33.PR_SYNC_USER_ID = T8.PAR_ROW_ID (+) AND
239 T1.BU_ID = '1234' AND T33.ROW_ID = T1.CONTACT_ID AND
240 T1.BU_ID = T13.ROW_ID AND
241 ((T33.PRIV_FLG = 'N' AND T5.PARTY_TYPE_CD != 'Suspect' AND T1.CON_EMP_FLG = 'N') AND
242 (T1.CON_LAST_NAME >= 'M0808594')) AND
243 (T33.CELL_PH_NUM LIKE '13912345678')
244 ORDER BY
245 T1.BU_ID, T1.CON_LAST_NAME, T1.CON_FST_NAME
246 ;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 4013657437
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 5474 | 36 (6)| 00:00:01 |
| 1 | SORT ORDER BY | | 2 | 5474 | 36 (6)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 2 | 5474 | 35 (3)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 2 | 5434 | 34 (3)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 2 | 5394 | 33 (4)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 2 | 5354 | 32 (4)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 2 | 5314 | 31 (4)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 2 | 5274 | 30 (4)| 00:00:01 |
| 8 | NESTED LOOPS | | 2 | 5234 | 29 (4)| 00:00:01 |
| 9 | NESTED LOOPS OUTER | | 2 | 5212 | 28 (4)| 00:00:01 |
| 10 | NESTED LOOPS OUTER | | 2 | 5190 | 27 (4)| 00:00:01 |
| 11 | NESTED LOOPS OUTER | | 2 | 5168 | 26 (4)| 00:00:01 |
| 12 | NESTED LOOPS OUTER | | 2 | 5042 | 25 (4)| 00:00:01 |
| 13 | NESTED LOOPS OUTER | | 2 | 4916 | 24 (5)| 00:00:01 |
| 14 | NESTED LOOPS OUTER | | 2 | 4866 | 23 (5)| 00:00:01 |
|* 15 | HASH JOIN OUTER | | 2 | 4716 | 22 (5)| 00:00:01 |
| 16 | NESTED LOOPS OUTER | | 2 | 4588 | 19 (0)| 00:00:01 |
| 17 | NESTED LOOPS OUTER | | 2 | 3738 | 18 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 2 | 2950 | 17 (0)| 00:00:01 |
| 19 | NESTED LOOPS OUTER | | 2 | 2812 | 16 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 2 | 2768 | 15 (0)| 00:00:01 |
| 21 | NESTED LOOPS OUTER | | 2 | 2702 | 14 (0)| 00:00:01 |
| 22 | NESTED LOOPS OUTER | | 2 | 2538 | 13 (0)| 00:00:01 |
| 23 | NESTED LOOPS OUTER | | 2 | 2378 | 12 (0)| 00:00:01 |
| 24 | NESTED LOOPS OUTER | | 2 | 2320 | 11 (0)| 00:00:01 |
| 25 | NESTED LOOPS OUTER | | 2 | 2270 | 10 (0)| 00:00:01 |
| 26 | NESTED LOOPS OUTER | | 2 | 2226 | 9 (0)| 00:00:01 |
| 27 | NESTED LOOPS OUTER | | 2 | 2126 | 8 (0)| 00:00:01 |
| 28 | NESTED LOOPS OUTER | | 2 | 1996 | 7 (0)| 00:00:01 |
| 29 | NESTED LOOPS OUTER | | 2 | 1854 | 6 (0)| 00:00:01 |
| 30 | NESTED LOOPS | | 2 | 1810 | 5 (0)| 00:00:01 |
| 31 | NESTED LOOPS OUTER | | 2 | 1732 | 4 (0)| 00:00:01 |
| 32 | NESTED LOOPS OUTER | | 2 | 1464 | 3 (0)| 00:00:01 |
| 33 | NESTED LOOPS | | 2 | 1196 | 2 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | S_PARTY_P1 | 1 | 11 | 1 (0)| 00:00:01 |
|* 35 | TABLE ACCESS BY INDEX ROWID| S_CONTACT | 2 | 1174 | 1 (0)| 00:00:01 |
|* 36 | INDEX RANGE SCAN | S_CONTACT_F68_X | 2 | | 1 (0)| 00:00:01 |
| 37 | TABLE ACCESS BY INDEX ROWID | S_PRI_LST | 1 | 134 | 1 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | S_PRI_LST_P1 | 1 | | 1 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | S_MED_SPEC | 1 | 134 | 1 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | S_MED_SPEC_P1 | 1 | | 1 (0)| 00:00:01 |
|* 41 | TABLE ACCESS BY INDEX ROWID | S_CONTACT_BU | 1 | 39 | 1 (0)| 00:00:01 |
|* 42 | INDEX RANGE SCAN | S_CONTACT_BU_U1 | 1 | | 1 (0)| 00:00:01 |
| 43 | TABLE ACCESS BY INDEX ROWID | S_POSTN | 1 | 22 | 1 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | S_POSTN_U2 | 1 | | 1 (0)| 00:00:01 |
| 45 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT_FNX | 1 | 71 | 1 (0)| 00:00:01 |
|* 46 | INDEX RANGE SCAN | S_ORG_EXT_FNX_U1 | 1 | | 1 (0)| 00:00:01 |
| 47 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 65 | 1 (0)| 00:00:01 |
|* 48 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | 1 (0)| 00:00:01 |
| 49 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 50 | 1 (0)| 00:00:01 |
|* 50 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | 1 (0)| 00:00:01 |
| 51 | TABLE ACCESS BY INDEX ROWID | S_POSTN | 1 | 22 | 1 (0)| 00:00:01 |
|* 52 | INDEX UNIQUE SCAN | S_POSTN_U2 | 1 | | 1 (0)| 00:00:01 |
| 53 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 25 | 1 (0)| 00:00:01 |
|* 54 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | 1 (0)| 00:00:01 |
|* 55 | TABLE ACCESS BY INDEX ROWID | S_CON_ADDR | 1 | 29 | 1 (0)| 00:00:01 |
|* 56 | INDEX RANGE SCAN | S_CON_ADDR_F1 | 2 | | 1 (0)| 00:00:01 |
| 57 | TABLE ACCESS BY INDEX ROWID | S_ADDR_PER | 1 | 80 | 1 (0)| 00:00:01 |
|* 58 | INDEX UNIQUE SCAN | S_ADDR_PER_P1 | 1 | | 1 (0)| 00:00:01 |
| 59 | TABLE ACCESS BY INDEX ROWID | S_ADDR_PER | 1 | 82 | 1 (0)| 00:00:01 |
|* 60 | INDEX UNIQUE SCAN | S_ADDR_PER_P1 | 1 | | 1 (0)| 00:00:01 |
| 61 | TABLE ACCESS BY INDEX ROWID | S_POSTN_CON | 1 | 33 | 1 (0)| 00:00:01 |
|* 62 | INDEX RANGE SCAN | S_POSTN_CON_M3 | 1 | | 1 (0)| 00:00:01 |
| 63 | TABLE ACCESS BY INDEX ROWID | S_POSTN | 1 | 22 | 1 (0)| 00:00:01 |
|* 64 | INDEX UNIQUE SCAN | S_POSTN_U2 | 1 | | 1 (0)| 00:00:01 |
|* 65 | TABLE ACCESS BY INDEX ROWID | S_PARTY | 1 | 69 | 1 (0)| 00:00:01 |
|* 66 | INDEX UNIQUE SCAN | S_PARTY_P1 | 1 | | 1 (0)| 00:00:01 |
|* 67 | INDEX RANGE SCAN | S_DQ_CON_KEY_U1 | 1 | 394 | 1 (0)| 00:00:01 |
| 68 | TABLE ACCESS BY INDEX ROWID | S_CONTACT_SS | 1 | 425 | 1 (0)| 00:00:01 |
|* 69 | INDEX RANGE SCAN | S_CONTACT_SS_U1 | 1 | | 1 (0)| 00:00:01 |
| 70 | TABLE ACCESS FULL | S_CASE | 1 | 64 | 2 (0)| 00:00:01 |
| 71 | TABLE ACCESS BY INDEX ROWID | S_CONTACT_LOYX | 1 | 75 | 1 (0)| 00:00:01 |
|* 72 | INDEX RANGE SCAN | S_CONTACT_LOYX_U1 | 1 | | 1 (0)| 00:00:01 |
| 73 | TABLE ACCESS BY INDEX ROWID | S_POSTN_CON | 1 | 25 | 1 (0)| 00:00:01 |
|* 74 | INDEX RANGE SCAN | S_POSTN_CON_M3 | 1 | | 1 (0)| 00:00:01 |
| 75 | TABLE ACCESS BY INDEX ROWID | S_EMP_PER | 1 | 63 | 1 (0)| 00:00:01 |
|* 76 | INDEX UNIQUE SCAN | S_EMP_PER_U1 | 1 | | 1 (0)| 00:00:01 |
| 77 | TABLE ACCESS BY INDEX ROWID | S_CONTACT_X | 1 | 63 | 1 (0)| 00:00:01 |
|* 78 | INDEX RANGE SCAN | S_CONTACT_X_U1 | 1 | | 1 (0)| 00:00:01 |
|* 79 | INDEX UNIQUE SCAN | S_PARTY_P1 | 1 | 11 | 1 (0)| 00:00:01 |
|* 80 | INDEX UNIQUE SCAN | S_PARTY_P1 | 1 | 11 | 1 (0)| 00:00:01 |
|* 81 | INDEX UNIQUE SCAN | S_PARTY_P1 | 1 | 11 | 1 (0)| 00:00:01 |
| 82 | TABLE ACCESS BY INDEX ROWID | S_USER | 1 | 20 | 1 (0)| 00:00:01 |
|* 83 | INDEX UNIQUE SCAN | S_USER_U2 | 1 | | 1 (0)| 00:00:01 |
| 84 | TABLE ACCESS BY INDEX ROWID | S_USER | 1 | 20 | 1 (0)| 00:00:01 |
|* 85 | INDEX UNIQUE SCAN | S_USER_U2 | 1 | | 1 (0)| 00:00:01 |
| 86 | TABLE ACCESS BY INDEX ROWID | S_USER | 1 | 20 | 1 (0)| 00:00:01 |
|* 87 | INDEX UNIQUE SCAN | S_USER_U2 | 1 | | 1 (0)| 00:00:01 |
| 88 | TABLE ACCESS BY INDEX ROWID | S_USER | 1 | 20 | 1 (0)| 00:00:01 |
|* 89 | INDEX UNIQUE SCAN | S_USER_U2 | 1 | | 1 (0)| 00:00:01 |
| 90 | TABLE ACCESS BY INDEX ROWID | S_USER | 1 | 20 | 1 (0)| 00:00:01 |
|* 91 | INDEX UNIQUE SCAN | S_USER_U2 | 1 | | 1 (0)| 00:00:01 |
| 92 | TABLE ACCESS BY INDEX ROWID | S_USER | 1 | 20 | 1 (0)| 00:00:01 |
|* 93 | INDEX UNIQUE SCAN | S_USER_U2 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
15 - access("T5"."ROW_ID"="T22"."PR_SUBJECT_ID"(+))
34 - access("T13"."ROW_ID"='1234')
35 - filter("T33"."PRIV_FLG"='N')
36 - access("T33"."CELL_PH_NUM"='13912345678')
38 - access("T33"."CURR_PRI_LST_ID"="T15"."ROW_ID"(+))
40 - access("T33"."MED_SPEC_ID"="T4"."ROW_ID"(+))
41 - filter("T1"."CON_LAST_NAME">='M0808594' AND "T1"."CON_EMP_FLG"='N')
42 - access("T33"."ROW_ID"="T1"."CONTACT_ID" AND "T1"."BU_ID"='1234')
44 - access("T33"."PR_POSTN_ID"="T19"."PAR_ROW_ID"(+))
46 - access("T33"."PR_DEPT_OU_ID"="T16"."PAR_ROW_ID"(+))
48 - access("T33"."PR_DEPT_OU_ID"="T10"."PAR_ROW_ID"(+))
50 - access("T33"."PR_DEPT_OU_ID"="T2"."PAR_ROW_ID"(+))
52 - access("T2"."PR_POSTN_ID"="T30"."PAR_ROW_ID"(+))
54 - access("T33"."BU_ID"="T32"."PAR_ROW_ID"(+))
55 - filter("T33"."PR_PER_ADDR_ID"="T6"."ADDR_PER_ID"(+))
56 - access("T33"."ROW_ID"="T6"."CONTACT_ID"(+))
filter("T6"."CONTACT_ID"(+) IS NOT NULL)
58 - access("T33"."PR_PER_ADDR_ID"="T31"."ROW_ID"(+))
60 - access("T33"."PR_PER_ADDR_ID"="T11"."ROW_ID"(+))
62 - access("T33"."PR_POSTN_ID"="T27"."POSTN_ID" AND "T33"."ROW_ID"="T27"."CON_ID")
64 - access("T27"."POSTN_ID"="T12"."PAR_ROW_ID"(+))
65 - filter("T5"."PARTY_TYPE_CD"<>'Suspect')
66 - access("T5"."ROW_ID"="T33"."PAR_ROW_ID")
67 - access("T5"."ROW_ID"="T17"."CONTACT_ID"(+))
69 - access("T5"."ROW_ID"="T7"."PAR_ROW_ID"(+))
72 - access("T5"."ROW_ID"="T9"."PAR_ROW_ID"(+))
74 - access("T28"."POSTN_ID"(+)='1234' AND "T5"."ROW_ID"="T28"."CON_ID"(+))
76 - access("T5"."ROW_ID"="T20"."PAR_ROW_ID"(+))
78 - access("T5"."ROW_ID"="T18"."PAR_ROW_ID"(+))
79 - access("T33"."PR_SYNC_USER_ID"="T14"."ROW_ID"(+))
80 - access("T33"."PR_DEPT_OU_ID"="T3"."ROW_ID"(+))
81 - access("T27"."POSTN_ID"="T25"."ROW_ID")
83 - access("T33"."PR_SYNC_USER_ID"="T8"."PAR_ROW_ID"(+))
85 - access("T19"."PR_EMP_ID"="T29"."PAR_ROW_ID"(+))
87 - access("T19"."PR_EMP_ID"="T26"."PAR_ROW_ID"(+))
89 - access("T19"."PR_EMP_ID"="T23"."PAR_ROW_ID"(+))
91 - access("T30"."PR_EMP_ID"="T21"."PAR_ROW_ID"(+))
93 - access("T12"."PR_EMP_ID"="T24"."PAR_ROW_ID"(+))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
12616 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
反映说这个sql非常慢，但看执行计划，老实说基本上都是正常的，最开始就发现有个全表scan的：
| 70 | TABLE ACCESS FULL | S_CASE | 1 | 64 | 2 (0)| 00:00:01 |
问了下同事说这个表记录为0，问题不是关键。比较怪异的地方是：
sql 条件里面：
(T33.CELL_PH_NUM LIKE '13912345678')
而对于的执行计划是下面这样的：
36 - access("T33"."CELL_PH_NUM"='13912345678')
最开始没引起重视，确实没想到，还让同事做了10046和10053都没看出来。最后以前的技术总监看出来了，
当然最后我是恍然大悟。
原因是 oracle 这里把 like 当成 = 去执行了。最后把参数 _like_with_bind_as_equality 调整为true后解决问题。
补充：这个参数在10g，11g中默认都为false。
发表回复