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 (3.56 KB)
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;