Home » RDBMS Server » Performance Tuning » Need help to tune the query
| Need help to tune the query [message #417042] |
Wed, 05 August 2009 15:58  |
oraappsfaq
Messages: 9
Registered: August 2009
Location: US
|
Junior Member |
|
|
SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/
hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
hl.location_id location_id,
hcas.cust_acct_site_id address_id, hl.state primary_state,
hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id+0
AND hcsu.site_use_code = 'SHIP_TO'
AND hcsu.primary_flag = 'Y'
AND hcsu.status = 'A'
UNION ALL --WHERE NO PARTY_SITE_ID
SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/
hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account, -1 location_id,
-1 address_id, '' primary_state, '' primary_country
FROM hz_parties hp, hz_cust_accounts hca
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND NOT EXISTS (SELECT 'z'
FROM hz_party_sites hps
WHERE hp.party_id = hps.party_id)
UNION ALL --THERE IS SHIP_TO BUT NO ACTIVE PRIMARY SHIP TO
SELECT hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
hl.location_id location_id, hcas.cust_acct_site_id address_id,
hl.state primary_state, hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsu.site_use_code = 'SHIP_TO'
AND hcsu.primary_flag = 'N'
AND hcsu.cust_acct_site_id =
(SELECT MIN (hcsu2.cust_acct_site_id)
FROM hz_parties hp2,
hz_party_sites hps2,
hz_cust_acct_sites_all hcas2,
hz_cust_site_uses_all hcsu2
WHERE hp2.party_id = hps2.party_id
AND hps2.party_site_id = hcas2.party_site_id
AND hcas2.cust_acct_site_id = hcsu2.cust_acct_site_id
AND hcsu2.site_use_code = 'SHIP_TO'
AND hcsu2.primary_flag = 'N'
AND hp2.party_id = hp.party_id)
AND NOT EXISTS (
SELECT 'z'
FROM hz_parties hp1,
hz_party_sites hps1,
hz_cust_acct_sites_all hcas1,
hz_cust_site_uses_all hcsu1
WHERE hp1.party_id = hps1.party_id
AND hps1.party_site_id = hcas1.party_site_id
AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
AND hcsu1.site_use_code = 'SHIP_TO'
AND hcsu1.primary_flag = 'Y'
AND hcsu1.status = 'A'
AND hp1.party_id = hp.party_id)
UNION ALL -- NO SHIP TO , but BILL_TO
SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/
hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
hl.location_id location_id, hcas.cust_acct_site_id address_id,
hl.state primary_state, hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsu.site_use_id =
(SELECT MIN (hcsu2.site_use_id)
FROM hz_parties hp2,
hz_party_sites hps2,
hz_cust_acct_sites_all hcas2,
hz_cust_site_uses_all hcsu2
WHERE hp2.party_id = hps2.party_id
AND hps2.party_site_id = hcas2.party_site_id
AND hcas2.cust_acct_site_id = hcsu2.cust_acct_site_id
AND hcsu2.site_use_code IN
('BILL_TO', 'INDIVIDUAL RESPONSIBLE SUB')
AND hcas2.cust_account_id = hca.cust_account_id)
AND NOT EXISTS (
SELECT 'z'
FROM hz_parties hp1,
hz_party_sites hps1,
hz_cust_acct_sites_all hcas1,
hz_cust_site_uses_all hcsu1
WHERE hp1.party_id = hps1.party_id
AND hps1.party_site_id = hcas1.party_site_id
AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
AND hcsu1.site_use_code = 'SHIP_TO'
AND hcas1.cust_account_id = hca.cust_account_id)
UNION ALL
--No Site Use (Most of these customers are type PERSON which have some location but no address..confusing..??) )
SELECT hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
hl.location_id location_id, -1 address_id,
hl.state primary_state, hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND NOT EXISTS (
SELECT 'z'
FROM hz_parties hp1,
hz_party_sites hps1,
hz_cust_acct_sites_all hcas1,
hz_cust_site_uses_all hcsu1
WHERE hp1.party_id = hps1.party_id
AND hps1.party_site_id = hcas1.party_site_id
AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
AND hp1.party_id = hp.party_id);
|
|
|
|
|
|
|
|
|
|
| Re: Need help to tune the query [message #417057 is a reply to message #417051] |
Wed, 05 August 2009 18:39  |
oraappsfaq
Messages: 9
Registered: August 2009
Location: US
|
Junior Member |
|
|
The issue with this query is its erratic behavior. The program runs every 2 hours and only sometimes (once in the begging of the month) it gives error "snapshot too old rollback segment is too small."
We have been suspecting that something else goes on in application when this happens but DBA's are insisting that we tune the query.
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
yes all indexes and other stats are current.
|
|
|
|
|
|
|
|
|
|
| Re: Need help to tune the query [message #417235 is a reply to message #417230] |
Thu, 06 August 2009 11:33 |
oraappsfaq
Messages: 9
Registered: August 2009
Location: US
|
Junior Member |
|
|
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=819 Card=5 Bytes=6
42)
1 0 UNION-ALL
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_SITE_USES_ALL'
(TABLE) (Cost=3 Card=1 Bytes=21)
3 2 NESTED LOOPS (Cost=72 Card=1 Bytes=123)
4 3 NESTED LOOPS (Cost=69 Card=1 Bytes=102)
5 4 NESTED LOOPS (Cost=67 Card=1 Bytes=92)
6 5 NESTED LOOPS (Cost=66 Card=1 Bytes=80)
7 6 NESTED LOOPS (Cost=63 Card=1 Bytes=65)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_AC
COUNTS' (TABLE) (Cost=62 Card=1 Bytes=29)
9 8 INDEX (FULL SCAN) OF 'HZ_CUST_ACCOUNTS_U1'
(INDEX (UNIQUE)) (Cost=61 Card=1)
10 7 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTIES
' (TABLE) (Cost=1 Card=1 Bytes=36)
11 10 INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U1' (IN
DEX (UNIQUE)) (Cost=0 Card=1)
12 6 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTY_SIT
ES' (TABLE) (Cost=3 Card=1 Bytes=15)
13 12 INDEX (RANGE SCAN) OF 'HZ_PARTY_SITES_N1' (I
NDEX) (Cost=1 Card=2)
14 5 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_LOCATIONS'
(TABLE) (Cost=1 Card=1 Bytes=12)
15 14 INDEX (UNIQUE SCAN) OF 'HZ_LOCATIONS_U1' (INDE
X (UNIQUE)) (Cost=0 Card=1)
16 4 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_ACCT_SIT
ES_ALL' (TABLE) (Cost=2 Card=1 Bytes=10)
17 16 INDEX (RANGE SCAN) OF 'HZ_CUST_ACCT_SITES_N1' (I
NDEX) (Cost=1 Card=1)
18 3 INDEX (RANGE SCAN) OF 'HZ_CUST_SITE_USES_N1' (INDEX)
(Cost=2 Card=1)
19 1 NESTED LOOPS (ANTI) (Cost=64 Card=1 Bytes=70)
20 19 NESTED LOOPS (Cost=63 Card=1 Bytes=65)
21 20 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_ACCOUNTS'
(TABLE) (Cost=62 Card=1 Bytes=29)
22 21 INDEX (FULL SCAN) OF 'HZ_CUST_ACCOUNTS_U1' (INDEX
(UNIQUE)) (Cost=61 Card=1)
23 20 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTIES' (TABLE
) (Cost=1 Card=1 Bytes=36)
24 23 INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U1' (INDEX (UNI
QUE)) (Cost=0 Card=1)
25 19 INDEX (RANGE SCAN) OF 'HZ_PARTY_SITES_N1' (INDEX) (Cos
t=1 Card=16524 Bytes=82620)
26 1 NESTED LOOPS (Cost=286 Card=1 Bytes=121)
27 26 NESTED LOOPS (Cost=284 Card=1 Bytes=102)
28 27 NESTED LOOPS (Cost=282 Card=1 Bytes=92)
29 28 NESTED LOOPS (Cost=281 Card=1 Bytes=80)
30 29 NESTED LOOPS (Cost=278 Card=1 Bytes=65)
31 30 TABLE ACCESS (FULL) OF 'HZ_CUST_ACCOUNTS' (TAB
LE) (Cost=277 Card=1 Bytes=29)
32 30 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTIES'
(TABLE) (Cost=1 Card=1 Bytes=36)
33 32 INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U1' (INDE
X (UNIQUE)) (Cost=0 Card=1)
34 33 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_
SITE_USES_ALL' (TABLE) (Cost=3 Card=1 Bytes=21)
35 34 NESTED LOOPS (Cost=8 Card=1 Bytes=46)
36 35 NESTED LOOPS (Cost=5 Card=1 Bytes=25)
37 36 NESTED LOOPS (Cost=3 Card=1 Bytes=15
)
38 37 INDEX (UNIQUE SCAN) OF 'HZ_PARTIES
_U1' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=5)
39 37 TABLE ACCESS (BY INDEX ROWID) OF '
HZ_PARTY_SITES' (TABLE) (Cost=2 Card=1 Bytes=10)
40 39 INDEX (RANGE SCAN) OF 'HZ_PARTY_
SITES_N1' (INDEX) (Cost=1 Card=1)
41 36 TABLE ACCESS (BY INDEX ROWID) OF 'HZ
_CUST_ACCT_SITES_ALL' (TABLE) (Cost=2 Card=1 Bytes=10)
42 41 INDEX (RANGE SCAN) OF 'HZ_CUST_ACC
T_SITES_N1' (INDEX) (Cost=1 Card=1)
43 35 INDEX (RANGE SCAN) OF 'HZ_CUST_SITE_US
ES_N1' (INDEX) (Cost=2 Card=1)
44 29 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTY_SITES
' (TABLE) (Cost=3 Card=1 Bytes=15)
45 44 INDEX (RANGE SCAN) OF 'HZ_PARTY_SITES_N1' (IND
EX) (Cost=1 Card=2)
46 28 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_LOCATIONS' (T
ABLE) (Cost=1 Card=1 Bytes=12)
47 46 INDEX (UNIQUE SCAN) OF 'HZ_LOCATIONS_U1' (INDEX
(UNIQUE)) (Cost=0 Card=1)
48 27 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_ACCT_SITES
_ALL' (TABLE) (Cost=2 Card=1 Bytes=10)
49 48 INDEX (RANGE SCAN) OF 'HZ_CUST_ACCT_SITES_N1' (IND
EX) (Cost=1 Card=1)
50 26 INDEX (RANGE SCAN) OF 'HZ_CUST_SITE_USES_N1' (INDEX) (
Cost=2 Card=1 Bytes=19)
51 50 SORT (AGGREGATE)
52 51 NESTED LOOPS (Cost=7 Card=1 Bytes=44)
53 52 NESTED LOOPS (Cost=5 Card=1 Bytes=25)
54 53 NESTED LOOPS (Cost=3 Card=1 Bytes=15)
55 54 INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U1' (INDE
X (UNIQUE)) (Cost=1 Card=1 Bytes=5)
56 54 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTY_S
ITES' (TABLE) (Cost=2 Card=1 Bytes=10)
57 56 INDEX (RANGE SCAN) OF 'HZ_PARTY_SITES_N1'
(INDEX) (Cost=1 Card=1)
58 53 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_ACCT
_SITES_ALL' (TABLE) (Cost=2 Card=1 Bytes=10)
59 58 INDEX (RANGE SCAN) OF 'HZ_CUST_ACCT_SITES_N1
' (INDEX) (Cost=1 Card=1)
60 52 INDEX (RANGE SCAN) OF 'HZ_CUST_SITE_USES_N1' (IN
DEX) (Cost=2 Card=1 Bytes=19)
61 1 FILTER
62 61 HASH (GROUP BY) (Cost=91 Card=1 Bytes=241)
63 62 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_SITE_USES_
ALL' (TABLE) (Cost=4 Card=1 Bytes=22)
64 63 NESTED LOOPS (Cost=80 Card=1 Bytes=241)
65 64 NESTED LOOPS (Cost=76 Card=1 Bytes=219)
66 65 NESTED LOOPS (Cost=76 Card=1 Bytes=214)
67 66 NESTED LOOPS (Cost=75 Card=1 Bytes=204)
68 67 NESTED LOOPS (Cost=72 Card=1 Bytes=184)
69 68 NESTED LOOPS (Cost=69 Card=1 Bytes=162)
70 69 NESTED LOOPS (Cost=67 Card=1 Bytes=140
)
71 70 NESTED LOOPS (Cost=66 Card=1 Bytes=1
16)
72 71 NESTED LOOPS (Cost=63 Card=1 Bytes
=89)
73 72 TABLE ACCESS (BY INDEX ROWID) OF
'HZ_CUST_ACCOUNTS' (TABLE) (Cost=62 Card=1 Bytes=41)
74 73 INDEX (FULL SCAN) OF 'HZ_CUST_
ACCOUNTS_U1' (INDEX (UNIQUE)) (Cost=61 Card=1)
75 74 NESTED LOOPS (Cost=10 Card=2
Bytes=94)
76 75 NESTED LOOPS (Cost=6 Card=
2 Bytes=60)
77 76 NESTED LOOPS (Cost=6 Car
d=2 Bytes=50)
78 77 TABLE ACCESS (BY INDEX
ROWID) OF 'HZ_CUST_ACCT_SITES_ALL' (TABLE) (Cost=4 Card=2 B
ytes=30)
79 78 INDEX (RANGE SCAN) O
F 'HZ_CUST_ACCT_SITES_N2' (INDEX) (Cost=2 Card=2)
80 77 TABLE ACCESS (BY INDEX
ROWID) OF 'HZ_PARTY_SITES' (TABLE) (Cost=1 Card=1 Bytes=10)
81 80 INDEX (UNIQUE SCAN)
OF 'HZ_PARTY_SITES_U1' (INDEX (UNIQUE)) (Cost=0 Card=1)
82 76 INDEX (UNIQUE SCAN) OF '
HZ_PARTIES_U1' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=5)
83 75 INDEX (RANGE SCAN) OF 'HZ_
CUST_SITE_USES_N1' (INDEX) (Cost=2 Card=1 Bytes=17)
84 72 TABLE ACCESS (BY INDEX ROWID) OF
'HZ_PARTIES' (TABLE) (Cost=1 Card=1 Bytes=48)
85 84 INDEX (UNIQUE SCAN) OF 'HZ_PAR
TIES_U1' (INDEX (UNIQUE)) (Cost=0 Card=1)
86 71 TABLE ACCESS (BY INDEX ROWID) OF '
HZ_PARTY_SITES' (TABLE) (Cost=3 Card=1 Bytes=27)
87 86 INDEX (RANGE SCAN) OF 'HZ_PARTY_
SITES_N1' (INDEX) (Cost=1 Card=2)
88 70 TABLE ACCESS (BY INDEX ROWID) OF 'HZ
_LOCATIONS' (TABLE) (Cost=1 Card=1 Bytes=24)
89 88 INDEX (UNIQUE SCAN) OF 'HZ_LOCATIO
NS_U1' (INDEX (UNIQUE)) (Cost=0 Card=1)
90 69 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_C
UST_ACCT_SITES_ALL' (TABLE) (Cost=2 Card=1 Bytes=22)
91 90 INDEX (RANGE SCAN) OF 'HZ_CUST_ACCT_
SITES_N1' (INDEX) (Cost=1 Card=1)
92 68 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUS
T_SITE_USES_ALL' (TABLE) (Cost=3 Card=2 Bytes=44)
93 92 INDEX (RANGE SCAN) OF 'HZ_CUST_SITE_US
ES_N1' (INDEX) (Cost=2 Card=2)
94 67 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_
ACCT_SITES_ALL' (TABLE) (Cost=3 Card=2 Bytes=40)
95 94 INDEX (RANGE SCAN) OF 'HZ_CUST_ACCT_SITE
S_N2' (INDEX) (Cost=1 Card=2)
96 66 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTY_S
ITES' (TABLE) (Cost=1 Card=1 Bytes=10)
97 96 INDEX (UNIQUE SCAN) OF 'HZ_PARTY_SITES_U1'
(INDEX (UNIQUE)) (Cost=0 Card=1)
98 65 INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U1' (INDEX
(UNIQUE)) (Cost=0 Card=1 Bytes=5)
99 64 INLIST ITERATOR
100 99 INDEX (RANGE SCAN) OF 'HZ_CUST_SITE_USES_N1' (
INDEX) (Cost=3 Card=1)
101 1 NESTED LOOPS (Cost=282 Card=1 Bytes=87)
102 101 NESTED LOOPS (Cost=281 Card=1 Bytes=75)
103 102 NESTED LOOPS (Cost=278 Card=1 Bytes=65)
104 103 TABLE ACCESS (FULL) OF 'HZ_CUST_ACCOUNTS' (TABLE)
(Cost=277 Card=1 Bytes=29)
105 103 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTIES' (TAB
LE) (Cost=1 Card=1 Bytes=36)
106 105 INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U1' (INDEX (U
NIQUE)) (Cost=0 Card=1)
107 106 NESTED LOOPS (Cost=7 Card=2 Bytes=60)
108 107 NESTED LOOPS (Cost=5 Card=1 Bytes=25)
109 108 NESTED LOOPS (Cost=3 Card=1 Bytes=15)
110 109 INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U1' (
INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=5)
111 109 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PAR
TY_SITES' (TABLE) (Cost=2 Card=1 Bytes=10)
112 111 INDEX (RANGE SCAN) OF 'HZ_PARTY_SITES_
N1' (INDEX) (Cost=1 Card=1)
113 108 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_
ACCT_SITES_ALL' (TABLE) (Cost=2 Card=1 Bytes=10)
114 113 INDEX (RANGE SCAN) OF 'HZ_CUST_ACCT_SITE
S_N1' (INDEX) (Cost=1 Card=1)
115 107 INDEX (RANGE SCAN) OF 'HZ_CUST_SITE_USES_N1'
(INDEX) (Cost=2 Card=2 Bytes=10)
116 102 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTY_SITES' (T
ABLE) (Cost=3 Card=1 Bytes=10)
117 116 INDEX (RANGE SCAN) OF 'HZ_PARTY_SITES_N1' (INDEX)
(Cost=1 Card=2)
118 101 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_LOCATIONS' (TABLE
) (Cost=1 Card=1 Bytes=12)
119 118 INDEX (UNIQUE SCAN) OF 'HZ_LOCATIONS_U1' (INDEX (UNI
QUE)) (Cost=0 Card=1)
Statistics
----------------------------------------------------------
141 recursive calls
0 db block gets
485998 consistent gets
233 physical reads
0 redo size
78208 bytes sent via SQL*Net to client
7149 bytes received via SQL*Net from client
62 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
906 rows processed
SQL>
|
|
|
|
|
|
| Re: Need help to tune the query [message #417253 is a reply to message #417242] |
Thu, 06 August 2009 14:30 |
oraappsfaq
Messages: 9
Registered: August 2009
Location: US
|
Junior Member |
|
|
Formatted query. Preview does not show any indentation so hoping the actual post will.
SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/
hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
hl.location_id location_id,
hcas.cust_acct_site_id address_id,
hl.state primary_state, hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id + 0
AND hcsu.site_use_code = 'SHIP_TO'
AND hcsu.primary_flag = 'Y'
AND hcsu.status = 'A'
UNION ALL --WHERE NO PARTY_SITE_ID
SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/
hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
-1 location_id,
-1 address_id, '' primary_state, '' primary_country
FROM hz_parties hp, hz_cust_accounts hca
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND NOT EXISTS (SELECT 'z'
FROM hz_party_sites hps
WHERE hp.party_id = hps.party_id)
UNION ALL --THERE IS SHIP_TO BUT NO ACTIVE PRIMARY SHIP TO
SELECT hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
hl.location_id location_id,
hcas.cust_acct_site_id address_id,
hl.state primary_state, hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsu.site_use_code = 'SHIP_TO'
AND hcsu.primary_flag = 'N'
AND hcsu.cust_acct_site_id =
(SELECT MIN (hcsu2.cust_acct_site_id)
FROM hz_parties hp2,
hz_party_sites hps2,
hz_cust_acct_sites_all hcas2,
hz_cust_site_uses_all hcsu2
WHERE hp2.party_id = hps2.party_id
AND hps2.party_site_id = hcas2.party_site_id
AND hcas2.cust_acct_site_id = hcsu2.cust_acct_site_id
AND hcsu2.site_use_code = 'SHIP_TO'
AND hcsu2.primary_flag = 'N'
AND hp2.party_id = hp.party_id)
AND NOT EXISTS (
SELECT 'z'
FROM hz_parties hp1,
hz_party_sites hps1,
hz_cust_acct_sites_all hcas1,
hz_cust_site_uses_all hcsu1
WHERE hp1.party_id = hps1.party_id
AND hps1.party_site_id = hcas1.party_site_id
AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
AND hcsu1.site_use_code = 'SHIP_TO'
AND hcsu1.primary_flag = 'Y'
AND hcsu1.status = 'A'
AND hp1.party_id = hp.party_id)
UNION ALL -- NO SHIP TO , but BILL_TO
SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/
hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
hl.location_id location_id,
hcas.cust_acct_site_id address_id,
hl.state primary_state,
hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsu.site_use_id =
(SELECT MIN (hcsu2.site_use_id)
FROM hz_parties hp2,
hz_party_sites hps2,
hz_cust_acct_sites_all hcas2,
hz_cust_site_uses_all hcsu2
WHERE hp2.party_id = hps2.party_id
AND hps2.party_site_id = hcas2.party_site_id
AND hcas2.cust_acct_site_id = hcsu2.cust_acct_site_id
AND hcsu2.site_use_code IN ('BILL_TO', 'INDIVIDUAL RESPONSIBLE SUB')
AND hcas2.cust_account_id = hca.cust_account_id)
AND NOT EXISTS (
SELECT 'z'
FROM hz_parties hp1,
hz_party_sites hps1,
hz_cust_acct_sites_all hcas1,
hz_cust_site_uses_all hcsu1
WHERE hp1.party_id = hps1.party_id
AND hps1.party_site_id = hcas1.party_site_id
AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
AND hcsu1.site_use_code = 'SHIP_TO'
AND hcas1.cust_account_id = hca.cust_account_id)
UNION ALL
--No Site Use (Most of these customers are type PERSON which have some location but no address..confusing..??)Smile)
SELECT hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
hl.location_id location_id,
-1 address_id,
hl.state primary_state,
hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND NOT EXISTS (
SELECT 'z'
FROM hz_parties hp1,
hz_party_sites hps1,
hz_cust_acct_sites_all hcas1,
hz_cust_site_uses_all hcsu1
WHERE hp1.party_id = hps1.party_id
AND hps1.party_site_id = hcas1.party_site_id
AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
AND hp1.party_id = hp.party_id);
|
|
|
|
| Re: Need help to tune the query [message #417256 is a reply to message #417042] |
Thu, 06 August 2009 14:37 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/ hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
To_number(hca.attribute12) system_code,
Nvl(hca.attribute10,'N') vip_list,
To_number(hca.attribute13) manual_code,
Nvl(hca.attribute14,'N') strategic_account,
hl.location_id location_id,
hcas.cust_acct_site_id address_id,
hl.state primary_state,
hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1','2','3','4')
AND hca.cust_account_id = Nvl(:p_customer_id,hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id + 0
AND hcsu.site_use_code = 'SHIP_TO'
AND hcsu.primary_flag = 'Y'
AND hcsu.status = 'A'
UNION ALL --WHERE NO PARTY_SITE_ID
SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/ hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
To_number(hca.attribute12) system_code,
Nvl(hca.attribute10,'N') vip_list,
To_number(hca.attribute13) manual_code,
Nvl(hca.attribute14,'N') strategic_account,
-1 location_id,
-1 address_id,
'' primary_state,
'' primary_country
FROM hz_parties hp,
hz_cust_accounts hca
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1','2','3','4')
AND hca.cust_account_id = Nvl(:p_customer_id,hca.cust_account_id)
AND NOT EXISTS (SELECT 'z'
FROM hz_party_sites hps
WHERE hp.party_id = hps.party_id)
UNION ALL --THERE IS SHIP_TO BUT NO ACTIVE PRIMARY SHIP TO
SELECT hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
To_number(hca.attribute12) system_code,
Nvl(hca.attribute10,'N') vip_list,
To_number(hca.attribute13) manual_code,
Nvl(hca.attribute14,'N') strategic_account,
hl.location_id location_id,
hcas.cust_acct_site_id address_id,
hl.state primary_state,
hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1','2','3','4')
AND hca.cust_account_id = Nvl(:p_customer_id,hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsu.site_use_code = 'SHIP_TO'
AND hcsu.primary_flag = 'N'
AND hcsu.cust_acct_site_id = (SELECT Min(hcsu2.cust_acct_site_id)
FROM hz_parties hp2,
hz_party_sites hps2,
hz_cust_acct_sites_all hcas2,
hz_cust_site_uses_all hcsu2
WHERE hp2.party_id = hps2.party_id
AND hps2.party_site_id = hcas2.party_site_id
AND hcas2.cust_acct_site_id = hcsu2.cust_acct_site_id
AND hcsu2.site_use_code = 'SHIP_TO'
AND hcsu2.primary_flag = 'N'
AND hp2.party_id = hp.party_id)
AND NOT EXISTS (SELECT 'z'
FROM hz_parties hp1,
hz_party_sites hps1,
hz_cust_acct_sites_all hcas1,
hz_cust_site_uses_all hcsu1
WHERE hp1.party_id = hps1.party_id
AND hps1.party_site_id = hcas1.party_site_id
AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
AND hcsu1.site_use_code = 'SHIP_TO'
AND hcsu1.primary_flag = 'Y'
AND hcsu1.status = 'A'
AND hp1.party_id = hp.party_id)
UNION ALL -- NO SHIP TO , but BILL_TO
SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/ hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
To_number(hca.attribute12) system_code,
Nvl(hca.attribute10,'N') vip_list,
To_number(hca.attribute13) manual_code,
Nvl(hca.attribute14,'N') strategic_account,
hl.location_id location_id,
hcas.cust_acct_site_id address_id,
hl.state primary_state,
hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1','2','3','4')
AND hca.cust_account_id = Nvl(:p_customer_id,hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsu.site_use_id = (SELECT Min(hcsu2.site_use_id)
FROM hz_parties hp2,
hz_party_sites hps2,
hz_cust_acct_sites_all hcas2,
hz_cust_site_uses_all hcsu2
WHERE hp2.party_id = hps2.party_id
AND hps2.party_site_id = hcas2.party_site_id
AND hcas2.cust_acct_site_id = hcsu2.cust_acct_site_id
AND hcsu2.site_use_code IN ('BILL_TO','INDIVIDUAL RESPONSIBLE SUB')
AND hcas2.cust_account_id = hca.cust_account_id)
AND NOT EXISTS (SELECT 'z'
FROM hz_parties hp1,
hz_party_sites hps1,
hz_cust_acct_sites_all hcas1,
hz_cust_site_uses_all hcsu1
WHERE hp1.party_id = hps1.party_id
AND hps1.party_site_id = hcas1.party_site_id
AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
AND hcsu1.site_use_code = 'SHIP_TO'
AND hcas1.cust_account_id = hca.cust_account_id)
UNION ALL
--No Site Use (Most of these customers are type PERSON which have some location but no address..confusing..??)Smile)
SELECT hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
To_number(hca.attribute12) system_code,
Nvl(hca.attribute10,'N') vip_list,
To_number(hca.attribute13) manual_code,
Nvl(hca.attribute14,'N') strategic_account,
hl.location_id location_id,
-1 address_id,
hl.state primary_state,
hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1','2','3','4')
AND hca.cust_account_id = Nvl(:p_customer_id,hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND NOT EXISTS (SELECT 'z'
FROM hz_parties hp1,
hz_party_sites hps1,
hz_cust_acct_sites_all hcas1,
hz_cust_site_uses_all hcsu1
WHERE hp1.party_id = hps1.party_id
AND hps1.party_site_id = hcas1.party_site_id
AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
AND hp1.party_id = hp.party_id);
which is easier to read & understand?
|
|
|
|
|
|
|
|
| Re: Need help to tune the query [message #417259 is a reply to message #417257] |
Thu, 06 August 2009 15:08 |
 |
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
| oraappsfaq wrote on Thu, 06 August 2009 21:46 | Great! How did you do it? Sorry, I am a newbie on this forum.
|
| Michel Cadot wrote on Thu, 06 August 2009 19:45 | Do you really think we can read what you posted?
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.
Regards
Michel
|
Are you able to read?
[Updated on: Thu, 06 August 2009 15:09] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Fri Jun 28 01:04:04 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
