Home » RDBMS Server » Performance Tuning » To tune query
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)

Previous Topic: no_index optimizer hint
Next Topic: Oracle 9i Explian Plan output- TOAD
Goto Forum:
  


Current Time: Wed Sep 30 10:44:37 CDT 2020