--************Problem View*************************

CREATE OR REPLACE FORCE VIEW FCCI_PROD.ENTITY_DETAILS
(ED_POLICY_NO, ED_POLICY_RENEW_NO, ED_GROUP_CODE,
ED_ENTY_CODE, ED_ENTY_NAME,ED_ENTY_TAXIDNO, ED_SEQUENCE_NO)
AS 
(SELECT preg_policy_no ed_policy_no, 
preg_policy_renew_no ed_policy_renew_no, 
enty_group_code ed_group_code, 
enty_code ed_enty_code, 
enty_name ed_enty_name, 
enty_taxidno ed_enty_taxidno, 
0 ed_sequence_no 
FROM POLICY_REGISTER, ENTITY_ADDRESS_MASTER 
WHERE ( (enty_group_code = 'BROKER' 
                   AND 
                  enty_code = preg_broker
                 ) 
OR 
                (enty_group_code = 'ACCOUNT' 
                  AND 
                 enty_code = preg_insured_acc_no
               ) 
             ) 
AND enty_status = 'Y' 
UNION ALL 
SELECT per_policy_no ed_policy_no, 
per_policy_renew_no ed_policy_renew_no, 
per_group_code ed_group_code, 
per_entity_code ed_enty_code, 
enty_name ed_enty_name, 
enty_taxidno ed_enty_taxidno, 
per_sequence_no ed_sequence_no 
FROM POLICY_REGISTER, ENTITY_ADDRESS_MASTER, 
POLICY_ENTITY_REGISTER 
WHERE preg_policy_no = per_policy_no 
AND preg_policy_renew_no = per_policy_renew_no 
AND enty_group_code = per_group_code 
AND enty_code = per_entity_code 
AND enty_status = 'Y' 
);


Select Count(*),Count(Distinct enty_group_code),Count(Distinct enty_code),
Count(Distinct enty_status)
From ENTITY_ADDRESS_MASTER 

1518459 12 207491 2 

Select Count(*),Count(Distinct preg_broker),Count(Distinct preg_insured_acc_no),
Count(Distinct preg_policy_renew_no),Count(Distinct preg_policy_no) 
From POLICY_REGISTER

67866 593 13566 8 64834

Select Count(*),Count(Distinct per_policy_renew_no),Count(Distinct per_policy_no),
Count(Distinct per_group_code),Count(Distinct per_entity_code)
From POLICY_ENTITY_REGISTER 

128238 8 64834 6 63762

Indexes

These indexes appear in Execution plan shown above

CREATE INDEX I_EAM_IND6 ON ENTITY_ADDRESS_MASTER
(ENTY_LAST_NAME_5_CHAR, ENTY_GROUP_CODE, ENTY_STATUS);

CREATE UNIQUE INDEX UK_PREG ON POLICY_REGISTER
(PREG_POLICY_NO, PREG_POLICY_RENEW_NO); 

CREATE UNIQUE INDEX IND_PER_1 ON POLICY_ENTITY_REGISTER
(per_policy_no ,per_policy_renew_no );



Please tell me how tune it, whether i should change query structure or use indexes, please guide me



Explain plan for View query

Operation Object Name                                            Rows Bytes   Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS                         1M   1164    5 
UNION-ALL 
CONCATENATION 
HASH JOIN                                                        93K  7M      4639 
[u][i][b]TABLE ACCESS FULL FCCI_PROD.POLICY_REGISTER[/b][/i][/u] 67K  2M      706 
TABLE ACCESS BY INDEX ROWID FCCI_PROD.ENTITY_ADDRESS_MASTER      155K 7M      3330 
INDEX SKIP SCAN [i][b]FCCI_PROD.I_EAM_IND6[/b][/i]               155K 807 
HASH JOIN                                                        1M   98M     1695 
TABLE ACCESS BY INDEX ROWID FCCI_PROD.ENTITY_ADDRESS_MASTER      10K  514K    981 
INDEX SKIP SCAN [b][i]FCCI_PROD.I_EAM_IND6[/i][/b]               10K  807 
[u][i][b]TABLE ACCESS FULL FCCI_PROD.POLICY_REGISTER[/b][/i][/u] 67K  2M      706 
HASH JOIN                                                        17K  1M      5310 
TABLE ACCESS BY INDEX ROWID FCCI_PROD.POLICY_ENTITY_REGISTER      2   70      1 
NESTED LOOPS                                                     126K 6M      159 
INDEX FULL SCAN FCCI_PROD.UK_PREG                                67K  1M      57 
INDEX RANGE SCAN FCCI_PROD.IND_PER_1                             1    1 
TABLE ACCESS BY INDEX ROWID FCCI_PROD.ENTITY_ADDRESS_MASTER      207K 9M      4171 
INDEX SKIP SCAN [i][b]FCCI_PROD.I_EAM_IND6[/b][/i]               207K 807