Help with performance tuning of query with multiple left joins

user8170666

Below query cost is very high. Unable to identify which part requires tuning based on attached query execution plan.

Even if 'Order by' it is not there, cost remains same.

Appreciate any help from members.

Query plan is attached for reference.

select count(*) from policy_register; -- 8 million

select count(*) from ENTITY_ADDRESS_INFO; --13 million

select count(*) from FLAGS_REGISTER; --10 million

select count(*) from ENTITY_REGISTER; --11 million

select count(*) from SEGMENTATION_MASTER; --11 records smallest table

QueryPlan.zip

Query:

SELECT CPLX_742.LEGAL_STATUS,

CPLX_742.POLICY_TERM_ID

FROM (POLICY_REGISTER CPLX_742

LEFT JOIN FLAGS_REGISTER CPLX_747

ON ( ( CPLX_742.POLICY_TERM_ID=CPLX_747.POLICY_TERM_ID ) )

LEFT JOIN (POLICY_ENTITY_REGISTER CPLX_748

LEFT JOIN (ENTITY_REGISTER CPLX_749

LEFT JOIN ENTITY_ADDRESS_INFO CPLX_750

ON ( ( CPLX_749.SYSTEM_ENTITY_CODE=CPLX_750.SYSTEM_ENTITY_CODE

AND CPLX_750.PRIMARY_ADDRESS_YN  ='Y' ) ) )

ON ( ( CPLX_748.SYSTEM_ENTITY_CODE=CPLX_749.SYSTEM_ENTITY_CODE ) ) )

ON ( ( CPLX_742.POLICY_TERM_ID  =CPLX_748.POLICY_TERM_ID ) )

LEFT JOIN (ENTITY_REGISTER CPLX_743

LEFT JOIN ENTITY_ADDRESS_INFO CPLX_752

ON ( ( CPLX_743.SYSTEM_ENTITY_CODE=CPLX_752.SYSTEM_ENTITY_CODE

AND CPLX_752.PRIMARY_ADDRESS_YN  ='Y' ) )

LEFT JOIN (POLICY_REGISTER CPLX_753

LEFT JOIN FLAGS_REGISTER CPLX_754

ON ( ( CPLX_753.POLICY_TERM_ID     =CPLX_754.POLICY_TERM_ID ) ) )

ON ( ( CPLX_743.GOVERNING_POLICY_TERM_ID=CPLX_753.POLICY_TERM_ID ) )

LEFT JOIN FLAGS_REGISTER CPLX_755

ON ( ( CPLX_743.SYSTEM_ENTITY_CODE =CPLX_755.SYSTEM_ENTITY_CODE ) ) )

ON ( ( CPLX_742.ACCOUNT_SYSTEM_CODE=CPLX_743.SYSTEM_ENTITY_CODE )

AND ( CPLX_743.ENTITY_TYPE     ='ACCOUNT' ) )

LEFT JOIN (ENTITY_REGISTER CPLX_745

LEFT JOIN ENTITY_ADDRESS_INFO CPLX_757

ON ( ( CPLX_745.SYSTEM_ENTITY_CODE=CPLX_757.SYSTEM_ENTITY_CODE

AND CPLX_757.PRIMARY_ADDRESS_YN  ='Y' ) ) )

ON ( ( CPLX_742.BROKER_SYSTEM_CODE=CPLX_745.SYSTEM_ENTITY_CODE )

AND ( CPLX_745.ENTITY_TYPE    ='BROKER' ) )

LEFT JOIN (ENTITY_REGISTER CPLX_744

LEFT JOIN ENTITY_ADDRESS_INFO CPLX_756

ON ( ( CPLX_744.SYSTEM_ENTITY_CODE =CPLX_756.SYSTEM_ENTITY_CODE

AND CPLX_756.PRIMARY_ADDRESS_YN  ='Y' ) ) )

ON ( ( CPLX_742.INSURED_SYSTEM_CODE=CPLX_744.SYSTEM_ENTITY_CODE )

AND ( CPLX_744.ENTITY_TYPE     ='INSURED' ) )

LEFT JOIN (ENTITY_REGISTER CPLX_746

LEFT JOIN ENTITY_ADDRESS_INFO CPLX_758

ON ( ( CPLX_746.SYSTEM_ENTITY_CODE =CPLX_758.SYSTEM_ENTITY_CODE

AND CPLX_758.PRIMARY_ADDRESS_YN   ='Y' ) ) )

ON ( ( CPLX_742.CUSTOMER_SYSTEM_CODE=CPLX_746.SYSTEM_ENTITY_CODE ) ) 

)

WHERE 

( CPLX_742.DATA_SEGMENT   IN

 (SELECT polic751.DATA_SEGMENT

 FROM SEGMENTATION_MASTER polic751

 WHERE (( CPLX_742.BROKER_SYSTEM_CODE = polic751.BROKER_SYSTEM_CODE

 OR polic751.AGENT_NO         = 'ALL')

 AND ( CPLX_742.BILL_TYPE       = polic751.BILL_TYPE

 OR polic751.BILL_TYPE        = 'ALL')

 AND ( CPLX_742.PRODUCT_CODE     = polic751.PRODUCT_CODE

 OR polic751.PRODUCT_CODE       = 'ALL')

 AND ( CPLX_742.STATE_CODE      = polic751.STATE

 OR polic751.STATE          = 'ALL')

 AND ( CPLX_742.OPERATING_COMPANY   = polic751.OPERATING_COMPANY

 OR polic751.OPERATING_COMPANY    = 'ALL'))

 AND ( polic751.DISPLAY_USER_ID    ='admin'

 OR 1                 =1 )

 ) )

AND ( ( CPLX_743.SOURCE_SYSTEM_ENTITY_CODE= 'ACC123' )

AND CPLX_748.ENTITY_TYPE         = 'ACCOUNT' )

ORDER BY CPLX_756.FULL_NAME ASC ,

 CPLX_742.POLICY_NO ASC ,

 CPLX_742.POLICY_EFFECTIVE_DATE DESC ,

 CPLX_742.POLICY_TERM_ID DESC;

Paulzip

Post plan in thread, most volunteers won't open a *.zip file from a random source.

user8170666

QueryPlan.txt

Large Query Plan in attached text file.

user8170666

 OPERATION OBJECT_NAME CARDINALITY COST 

 SELECT STATEMENT 

   1 11861 

  

 SORT 

   1 11861 

     

 FILTER 

        

        

 Filter Predicates 

           

 EXISTS (SELECT 0 FROM SEGMENTATION_MASTER POLIC751 WHERE (POLIC751.BILL_TYPE=:B1 OR POLIC751.BILL_TYPE=U'ALL') AND (POLIC751.OPERATING_COMPANY=:B2 OR POLIC751.OPERATING_COMPANY=U'ALL') AND (POLIC751.PRODUCT_CODE=:B3 OR POLIC751.PRODUCT_CODE=U'ALL') AND (POLIC751.STATE=:B4 OR POLIC751.STATE=U'ALL') AND POLIC751.DATA_SEGMENT=:B5 AND (POLIC751.AGENT_NO=U'ALL' OR POLIC751.BROKER_SYSTEM_CODE=:B6)) 

        

 NESTED LOOPS 

   291 38 

           

 NESTED LOOPS 

   1 33 

              

 NESTED LOOPS 

   1 29 

                 

 NESTED LOOPS 

   1 25 

                    

 NESTED LOOPS 

   1 20 

                       

 HASH JOIN 

   1 16 

                          

 Access Predicates 

                             

 CPLX_742.POLICY_TERM_ID=CPLX_747.POLICY_TERM_ID(+) 

                          

 NESTED LOOPS 

   1 16 

                             

 STATISTICS COLLECTOR 

        

                                

 NESTED LOOPS 

   1 14 

                                   

 NESTED LOOPS 

   1 12 

                                      

 HASH JOIN 

   1 8 

                                         

 Access Predicates 

                                            

 CPLX_743.SYSTEM_ENTITY_CODE=CPLX_755.SYSTEM_ENTITY_CODE(+) 

                                         

 NESTED LOOPS 

   1 8 

                                            

 STATISTICS COLLECTOR 

        

                                               

 HASH JOIN 

   1 6 

                                                  

 Access Predicates 

                                                     

 CPLX_743.SYSTEM_ENTITY_CODE=CPLX_752.SYSTEM_ENTITY_CODE(+) 

                                                  

 NESTED LOOPS 

   1 6 

                                                     

 STATISTICS COLLECTOR 

        

                                                        

 TABLE ACCESS 

 ENTITY_REGISTER 1 4 

                                                           

 INDEX 

 IDX_ENTITY_REG_PT_3 1 3 

                                                              

 Access Predicates 

                                                                 

 AND 

                                                                    

 CPLX_743.ENTITY_TYPE=U'ACCOUNT' 

                                                                    

 CPLX_743.SOURCE_SYSTEM_ENTITY_CODE=U'ACCRT001-1-1' 

                                                     

 INDEX 

 ENTITY_ADDRESS_INFO_PTI2 1 2 

                                                        

 Access Predicates 

                                                           

 AND 

                                                              

 CPLX_743.SYSTEM_ENTITY_CODE=CPLX_752.SYSTEM_ENTITY_CODE(+) 

                                                              

 CPLX_752.PRIMARY_ADDRESS_YN(+)=U'Y' 

                                                  

 INDEX 

 ENTITY_ADDRESS_INFO_PTI2 1 2 

                                                     

 Filter Predicates 

                                                        

 CPLX_752.PRIMARY_ADDRESS_YN(+)=U'Y' 

                                            

 INDEX 

 IND_FR_SYSTEM_ENTITY_CODE 1 2 

                                               

 Access Predicates 

                                                  

 CPLX_743.SYSTEM_ENTITY_CODE=CPLX_755.SYSTEM_ENTITY_CODE(+) 

                                               

 Filter Predicates 

                                                  

 CPLX_755.SYSTEM_ENTITY_CODE(+) IS NOT NULL 

                                         

 INDEX 

 IND_FR_SYSTEM_ENTITY_CODE 1 2 

                                            

 Filter Predicates 

                                               

 CPLX_755.SYSTEM_ENTITY_CODE(+) IS NOT NULL 

                                      

 TABLE ACCESS 

 POLICY_REGISTER 3 4 

                                         

 INDEX 

 POLICY_REGISTER_PTI2 3 2 

                                            

 Access Predicates 

                                               

 CPLX_742.ACCOUNT_SYSTEM_CODE=CPLX_743.SYSTEM_ENTITY_CODE 

                                            

 Filter Predicates 

                                               

 CPLX_742.ACCOUNT_SYSTEM_CODE IS NOT NULL 

                                   

 INDEX 

 POLICY_ENTITY_REGISTER_PK 1 2 

                                      

 Access Predicates 

                                         

 AND 

                                            

 CPLX_742.POLICY_TERM_ID=CPLX_748.POLICY_TERM_ID 

                                            

 CPLX_748.ENTITY_TYPE=U'ACCOUNT' 

                                      

 Filter Predicates 

                                         

 CPLX_748.ENTITY_TYPE=U'ACCOUNT' 

                             

 INDEX 

 IDX_FLAGS_REGISTER_FK1 1 2 

                                

 Access Predicates 

                                   

 CPLX_742.POLICY_TERM_ID=CPLX_747.POLICY_TERM_ID(+) 

                                

 Filter Predicates 

                                   

 CPLX_747.POLICY_TERM_ID(+) IS NOT NULL 

                          

 INDEX 

 IDX_FLAGS_REGISTER_FK1 1 2 

                             

 Filter Predicates 

                                

 CPLX_747.POLICY_TERM_ID(+) IS NOT NULL 

                       

 VIEW PUSHED PREDICATE 

   1 4 

                          

 NESTED LOOPS 

   2211951387 4 

                             

 INDEX 

 SYSTEM_ENTITY_CODE_PK 1 2 

                                

 Access Predicates 

                                   

 CPLX_746.SYSTEM_ENTITY_CODE=CPLX_742.CUSTOMER_SYSTEM_CODE 

                             

 INDEX 

 ENTITY_ADDRESS_INFO_PTI2 2207695152 2 

                                

 Access Predicates 

                                   

 AND 

                                      

 CPLX_758.SYSTEM_ENTITY_CODE(+)=CPLX_742.CUSTOMER_SYSTEM_CODE 

                                      

 CPLX_758.PRIMARY_ADDRESS_YN(+)=U'Y' 

                                

 Filter Predicates 

                                   

 CPLX_746.SYSTEM_ENTITY_CODE=CPLX_758.SYSTEM_ENTITY_CODE(+) 

                    

 VIEW PUSHED PREDICATE 

   1 5 

                       

 NESTED LOOPS 

   1115 5 

                          

 INDEX 

 IDX_ER_ENTTYPE_SEC 1 3 

                             

 Access Predicates 

                                

 AND 

                                   

 CPLX_744.ENTITY_TYPE=U'INSURED' 

                                   

 CPLX_744.SYSTEM_ENTITY_CODE=CPLX_742.INSURED_SYSTEM_CODE 

                          

 INDEX 

 IDX_EAI_SEC_PRIMADD_FULLNAME 1485 2 

                             

 Access Predicates 

                                

 AND 

                                   

 CPLX_756.SYSTEM_ENTITY_CODE(+)=CPLX_742.INSURED_SYSTEM_CODE 

                                   

 CPLX_756.PRIMARY_ADDRESS_YN(+)=U'Y' 

                             

 Filter Predicates 

                                

 CPLX_744.SYSTEM_ENTITY_CODE=CPLX_756.SYSTEM_ENTITY_CODE(+) 

                 

 VIEW PUSHED PREDICATE 

   1 4 

                    

 NESTED LOOPS 

   1 4 

                       

 INDEX 

 POLICY_REGISTER_PK 1 2 

                          

 Access Predicates 

                             

 CPLX_753.POLICY_TERM_ID=CPLX_743.GOVERNING_POLICY_TERM_ID 

                       

 INDEX 

 IDX_FLAGS_REGISTER_FK1 1 2 

                          

 Access Predicates 

                             

 CPLX_754.POLICY_TERM_ID(+)=CPLX_743.GOVERNING_POLICY_TERM_ID 

                          

 Filter Predicates 

                             

 CPLX_753.POLICY_TERM_ID=CPLX_754.POLICY_TERM_ID(+) 

              

 VIEW PUSHED PREDICATE 

   132 4 

                 

 NESTED LOOPS 

   1540398345 4 

                    

 INDEX 

 SYSTEM_ENTITY_CODE_PK 1 2 

                       

 Access Predicates 

                          

 CPLX_749.SYSTEM_ENTITY_CODE=CPLX_748.SYSTEM_ENTITY_CODE 

                    

 INDEX 

 ENTITY_ADDRESS_INFO_PTI2 1537434312 2 

                       

 Access Predicates 

                          

 AND 

                             

 CPLX_750.SYSTEM_ENTITY_CODE(+)=CPLX_748.SYSTEM_ENTITY_CODE 

                             

 CPLX_750.PRIMARY_ADDRESS_YN(+)=U'Y' 

                       

 Filter Predicates 

                          

 CPLX_749.SYSTEM_ENTITY_CODE=CPLX_750.SYSTEM_ENTITY_CODE(+) 

           

 VIEW PUSHED PREDICATE 

   892 5 

              

 NESTED LOOPS 

   1045094350 5 

                 

 INDEX 

 IDX_ER_ENTTYPE_SEC 1 3 

                    

 Access Predicates 

                       

 AND 

                          

 CPLX_745.ENTITY_TYPE=U'BROKER' 

                          

 CPLX_745.SYSTEM_ENTITY_CODE=CPLX_742.BROKER_SYSTEM_CODE 

                 

 INDEX 

 ENTITY_ADDRESS_INFO_PTI2 10397748153 2 

                    

 Access Predicates 

                       

 AND 

                          

 CPLX_757.SYSTEM_ENTITY_CODE(+)=CPLX_742.BROKER_SYSTEM_CODE 

                          

 CPLX_757.PRIMARY_ADDRESS_YN(+)=U'Y' 

                    

 Filter Predicates 

                       

 CPLX_745.SYSTEM_ENTITY_CODE=CPLX_757.SYSTEM_ENTITY_CODE(+) 

        

 INLIST ITERATOR 

        

           

 TABLE ACCESS 

 SEGMENTATION_MASTER 2 81 

              

 Filter Predicates 

                 

 POLIC751.DATA_SEGMENT=:B1 

              

 INDEX 

 IDX_SEGMENTATION_MASTER_COMB 11 1 

                 

 Access Predicates 

                    

 AND 

                       

 OR 

                          

 POLIC751.STATE=:B1 

                          

 POLIC751.STATE=U'ALL' 

                       

 OR 

                          

 POLIC751.PRODUCT_CODE=:B2 

                          

 POLIC751.PRODUCT_CODE=U'ALL' 

                       

 OR 

                          

 POLIC751.OPERATING_COMPANY=:B3 

                          

 POLIC751.OPERATING_COMPANY=U'ALL' 

                       

 OR 

                          

 POLIC751.BILL_TYPE=:B4 

                          

 POLIC751.BILL_TYPE=U'ALL' 

                 

 Filter Predicates 

                    

 OR 

                       

 POLIC751.AGENT_NO=U'ALL' 

                       

 POLIC751.BROKER_SYSTEM_CODE=:B1