| To tune query [message #115099] |
Thu, 07 April 2005 08:21 |
anmol1947
Messages: 34
Registered: February 2005
Location: Gurgaon
|
Member |
|
|
Hi
i need help to tune this query
1 SELECT DISTINCT table_name, policy_nbr, asl_cd
2 FROM (SELECT /* distinct */ 'Unearned Premium' table_name,
3 policy_nbr, asl_cd
4 FROM cra.unearned_premium_reserve
5 WHERE TO_CHAR (orig_book_date, 'YYYYMM') = '200501'
6 AND company_nbr IN (80, 90)
7 AND NVL (asl_cd, '.') NOT IN (SELECT aslob_cd
8 FROM cra.peril_code)
9 UNION ALL
10 SELECT /* distinct */ 'Premium Detail' table_name,
11 policy_nbr, asl_cd
12 FROM cra.premium_detail
13 WHERE gl_reporting_period_desc = '200504'
14 AND company_nbr IN (80, 90)
15 AND NVL (asl_cd, '.') NOT IN (SELECT aslob_cd
16 FROM cra.peril_code)
17 UNION ALL
18 SELECT /* distinct */ 'Loss Detail' table_name, policy_nbr,
19 asl_cd
20 FROM cra.loss_detail_qs
21 WHERE gl_reporting_period_desc = '200504'
22 AND company_nbr IN (80, 90)
23 AND NVL (asl_cd, '.') NOT IN (SELECT aslob_cd
24* FROM cra.peril_code))
SQL> /
TABLE_NAME POLICY_NBR ASL_CD
---------------- ------------------------------- ------
Unearned Premium 0000000000000000000000490276000 56
Unearned Premium 0000000000000000000000490276000 76
Elapsed: 00:00:13.37
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3555 Card=389 Bytes=
12448)
1 0 SORT (UNIQUE) (Cost=3555 Card=389 Bytes=12448)
2 1 VIEW (Cost=4327 Card=389 Bytes=12448)
3 2 UNION-ALL
4 3 FILTER
5 4 INLIST ITERATOR
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'UNEARNED_PREMI
UM_RESERVE' (Cost=2264 Card=98 Bytes=4214)
7 6 BITMAP CONVERSION (TO ROWIDS)
8 7 BITMAP INDEX (SINGLE VALUE) OF 'UNEARNED_PRE
MIUM_RESERVE_BMP1'
9 4 TABLE ACCESS (FULL) OF 'PERIL_CODE' (Cost=2 Card=1
Bytes=3)
10 3 FILTER
11 10 TABLE ACCESS (BY INDEX ROWID) OF 'PREMIUM_DETAIL'
(Cost=498 Card=287 Bytes=10906)
12 11 BITMAP CONVERSION (TO ROWIDS)
13 12 BITMAP INDEX (RANGE SCAN) OF 'PREMINUM_DETAIL_
BMP1'
14 10 TABLE ACCESS (FULL) OF 'PERIL_CODE' (Cost=2 Card=1
Bytes=3)
15 3 FILTER
16 15 TABLE ACCESS (BY INDEX ROWID) OF 'LOSS_DETAIL_QS'
(Cost=786 Card=4 Bytes=88)
17 16 BITMAP CONVERSION (TO ROWIDS)
18 17 BITMAP INDEX (FULL SCAN) OF 'LOSS_DETAIL_QS_BM
P1'
19 15 TABLE ACCESS (FULL) OF 'PERIL_CODE' (Cost=2 Card=1
Bytes=3)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
