--lic_policiy, --Count:~100000
--lic_policy_item lpi, --Count:~100000
--lic_policy_group lpg1, -- Count: 400000
--lic_policy_pro lpp --Count: ~80,00000

SELECT COUNT (distinct(sq.policy_id))
FROM
(SELECT *
FROM lic_policiy, 
lic_policy_item lpi, 
lic_policy_group lpg1, 
lic_policy_group lpg2,
lic_policy_pro lpp 
WHERE prod_cde LIKE 'PAMH0D%'
AND policy_allocate_nbr LIKE nvl( null , policy_allocate_nbr)
AND nvl(policy_nbr, '0' ) LIKE nvl( null , nvl(policy_nbr, '0' ))
AND status LIKE nvl( null , status)
AND policy_start_dt >= nvl( null , policy_start_dt)
AND policy_start_dt <= nvl( null , policy_start_dt)
AND lpi_policy_id = policy_id
AND lpi_policy_item_id = lp_map_policy_item_id
AND lpi_policy_end_dt IS NULL
AND lpg1.pg_policy_id = policy_id
AND lpg1.lpg_p_grp_cde = nvl( null , lpg1.lpg_p_grp_cde)
AND lpg1.lpg_stake_holder_id LIKE nvl(:B2, 'SH' )
AND lpg1.lpg_stake_holder_id = 'SH'
AND lpg1.lpg_grp_cd LIKE nvl( null , '%' )
AND lpg1.lpg_eff_end_dt IS NULL
AND lpg2.pg_policy_id = policy_id
AND lpg2.lpg_p_grp_cde = nvl( null , lpg1.lpg_p_grp_cde)
AND lpg2.lpg_stake_holder_id LIKE nvl( null , 'SH' )
AND lpg2.lpg_grp_cd LIKE nvl( null , '%' )
AND lpg2.lpg_eff_end_dt IS NULL
AND lpp_policy_id = policy_id
AND lpp.LPP_P_VAL = 'Rule 214'
AND nvl(lpp.lpp_val_cde, lpp.lpp_val_flag) = 'Y'
AND lpp.lpp_eff_end_dt IS NULL ) sq
,lic_policy_group lpg3
WHERE lpg3.pg_policy_id = sq.policy_id
AND lpg3.lpg_stake_holder_id = 'XXXXX'
AND lpg3.lpg_grp_cd = 'CK001'
AND lpg3.lpg_eff_end_dt IS NULL

Index used:

INDEX_NAME                     TABLE_NAME                     COLUMN_NAME           COLUMN_POSITION
------------------------------ ------------------------------ --------------------- ---------------
INDX_LP01                    lic_policiy                   prod_cde                    3
INDX_LP01                    lic_policiy                   policy_allocate_nbr         4
INDX_LP01                    lic_policiy                   status                      5
INDX_LP01                    lic_policiy                   policy_start_dt             6
INDX_LP01                    lic_policiy                   LP_PG_MAP_GRP_CDE           7
INDX_LP01                    lic_policiy                   lp_map_policy_item_id       8
PK_LP                        lic_policiy                   policy_id                   1
UI_LP01                      lic_policiy                   policy_nbr                  1
IDX_LP02                	 lic_policiy                   prod_cde                    1
IDX_LP02               		 lic_policiy                   status                      2
IDX_LP02               		 lic_policiy                   policy_create_dt            3
IDX_LP_1                     lic_policiy                   status                      1
IDX_LP_2                     lic_policiy                   policy_allocate_nbr         1
INDX_LP01                    lic_policiy                   policy_id                   1
INDX_LP01                    lic_policiy                   LP_PT_PROD_ID               2
PK_LPI                       lic_policy_item               lpi_policy_id               1
PK_LPI                       lic_policy_item               lpi_policy_item_id          2
UK01_LPG                     lic_policy_group              pg_policy_id                1
UK01_LPG                     lic_policy_group              lpg_p_grp_cde               2
UK01_LPG                     lic_policy_group              lp_p_grp_f_cde              3
UK01_LPG                     lic_policy_group            LPG_EFF_BEGIN_DT              4
UK02_LPG                     lic_policy_group            pg_policy_id                  1
UK02_LPG                     lic_policy_group            LPG_EFF_BEGIN_DT              2
UK02_LPG                     lic_policy_group            lpg_grp_cd                    3
UK02_LPG                     lic_policy_group            lpg_stake_holder_id           4
PK_LPG                       lic_policy_group            pg_policy_id                  1
PK_LPG                       lic_policy_group            LP_POLY_GRP_ID                2
IDX_010                      lic_policy_group            lpg_p_grp_cde                 1
IDX_010                      lic_policy_group            lp_p_grp_f_cde                2
IDX_LPP                   	 lic_policy_pro              LPP_P_VAL                     1
IDX_LPP                   	 lic_policy_pro              SYS_NC00029$                  2
PK_LPP                       lic_policy_pro              lpp_policy_id                 1
PK_LPP                       lic_policy_pro              LPP_POLY_PRO_CDE              2
UK01_LPP                     lic_policy_pro              lpp_policy_id                 1
UK01_LPP                     lic_policy_pro              LPP_P_PARM_CDE                2
UK01_LPP                     lic_policy_pro              LPP_EFF_BEGIN_DT              3
UK02_LPP                     lic_policy_pro              lpp_policy_id                 1
UK02_LPP                     lic_policy_pro              LPP_POLY_PRO_CDE              2
UK02_LPP                     lic_policy_pro              LPP_EFF_BEGIN_DT              3
UK02_LPP                     lic_policy_pro              LPP_P_VAL                     4