Join inner did not fit in memory for small data
I have query it is giving Join inner did not fit in memory for small data even for very small data. Only few hundred rows max in some tables and even less in other tables.
I tried to convert Hash joins to Merge joins wherever possible. But still running into this issue. Problem seems to be with sa_audit_result_summary_f table. If I exclude that table from the query, it executes instantly. How can resolve this issue ?
Access Path:Sort Key: (sa_platform_base_d.platform_name, sa_facility_base_d.facility_name, sa_server_group_base_d.group_name, sa_server_management_d.host_name, sa_compliance_status_type_d.status_name, sa_audit_compliance_details_f.rule_item, sa_audit_compliance_details_f.rule_details, sa_audit_compliance_details_f.rule_description, sa_audit_compliance_details_f.expected_value, sa_audit_compliance_details_f.actual_value, sa_audit_compliance_details_f.test_date, V(3,4), V(6,1), V(5,4), V(3,5), sa_compliance_status_type_d.status_name, sa_audit_compliance_daily_prejoin_projection_b0.scan_date, V(3,6), sa_audit_compliance_details_f.compliance_detail_id)
LDISTRIB_SEGMENTED
+-GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 54909.000000, Rows: 485.000000 Disk(B): 0.000000 CPU(B): 24077340.000000 Memory(B): 47627000.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 49092] (PATH ID: 1)
| Aggregates: sum(sa_audit_result_summary_f.warning_checks), sum(sa_audit_result_summary_f.total_non_compliant_objects), sum(sa_audit_result_summary_f.compliant_checks), sum(sa_audit_result_summary_f.non_compliant_checks), sum(sa_audit_result_summary_f.scan_failed_checks), sum(sa_audit_result_summary_f.excepted_checks)
| Group By: sa_platform_base_d.platform_name, sa_facility_base_d.facility_name, sa_server_group_base_d.group_name, sa_server_management_d.host_name, cbi_data.sa_compliance_status_type_d.status_name, sa_audit_compliance_details_f.rule_item, sa_audit_compliance_details_f.rule_details, sa_audit_compliance_details_f.rule_description, sa_audit_compliance_details_f.expected_value, sa_audit_compliance_details_f.actual_value, sa_audit_compliance_details_f.test_date, "<subquery>".audit_name, "<subquery>".server_key, "<subquery>".audit_policy_name, "<subquery>".created_by, cbi_data.sa_compliance_status_type_d.status_name, sa_audit_compliance_daily_prejoin_projection_b0.scan_date, "<subquery>".effective_date, sa_audit_compliance_details_f.compliance_detail_id
| Sort Key: (sa_platform_base_d.platform_name, sa_facility_base_d.facility_name, sa_server_group_base_d.group_name, sa_server_management_d.host_name, sa_compliance_status_type_d.status_name, sa_audit_compliance_details_f.rule_item, sa_audit_compliance_details_f.rule_details, sa_audit_compliance_details_f.rule_description, sa_audit_compliance_details_f.expected_value, sa_audit_compliance_details_f.actual_value, sa_audit_compliance_details_f.test_date, V(3,4), V(6,1), V(5,4), V(3,5), sa_compliance_status_type_d.status_name, sa_audit_compliance_daily_prejoin_projection_b0.scan_date, V(3,6), sa_audit_compliance_details_f.compliance_detail_id)
| LDISTRIB_SEGMENTED
| +---> JOIN HASH [Cost: 51995.000000, Rows: 485.000000 Disk(B): 0.000000 CPU(B): 16253720.000000 Memory(B): 23280.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 49644] (PATH ID: 2)
| | Join Cond: (sa_audit_compliance_daily_prejoin_projection_b0.server_key = "<subquery>".server_key) AND ("<subquery>".server_key = sa_server_management_d.server_key) AND (sa_server_facility_d.server_key = "<subquery>".server_key) AND ("<subquery>".server_key = sa_server_platform_d.server_key) AND ("<subquery>".server_key = sa_server_group_member_d.server_key)
| | Join Filter: (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= "<subquery>".server_effective_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= "<subquery>".end_of_life_date)
| | Materialize at Input: sa_server_group_member_d.server_key
| | Sort Key: (sa_server_group_member_d.server_group_key, sa_server_group_member_d.server_key, sa_server_group_member_d.end_date, sa_server_group_member_d.start_date)
| | LDISTRIB_SEGMENTED
| | +-- Outer -> JOIN HASH [Cost: 51193.000000, Rows: 326.000000 (251.000000 RLE) Disk(B): 0.000000 CPU(B): 592360.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 49604] (PATH ID: 3)
| | | Join Filter: (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= sa_server_group_member_d.start_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= sa_server_group_member_d.end_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= sa_server_group_ld.effective_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= sa_server_group_ld.end_of_life_date)
| | | Materialize at Input: sa_server_group_member_d.start_date, sa_server_group_member_d.end_date, sa_server_facility_d.facility_key, sa_server_facility_d.server_key, sa_server_facility_d.end_date, sa_server_facility_d.start_date
| | | Sort Key: (sa_server_group_member_d.server_group_key, sa_server_group_member_d.server_key, sa_server_group_member_d.end_date, sa_server_group_member_d.start_date)
| | | LDISTRIB_SEGMENTED
| | | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [Cost: 264.000000, Rows: 326.000000 (59.000000 RLE) Disk(B): 0.000000 CPU(B): 464397.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 821] (PATH ID: 4)
| | | | Join Cond: (sa_server_group_ld.server_group_key = sa_server_group_base_d.server_group_key)
| | | | Sort Key: (sa_server_group_member_d.server_group_key, sa_server_group_member_d.server_key, sa_server_group_member_d.end_date, sa_server_group_member_d.start_date)
| | | | LDISTRIB_SEGMENTED
| | | | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [Cost: 69.000000, Rows: 326.000000 (59.000000 RLE) Disk(B): 0.000000 CPU(B): 20400.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 48] (PATH ID: 5)
| | | | | Join Cond: (sa_server_group_member_d.server_group_key = sa_server_group_ld.server_group_key)
| | | | | Sort Key: (sa_server_group_member_d.server_group_key, sa_server_group_member_d.server_key, sa_server_group_member_d.end_date, sa_server_group_member_d.start_date)
| | | | | LDISTRIB_SEGMENTED
| | | | | +-- Outer -> STORAGE ACCESS for <No Alias> [Cost: 17.000000, Rows: 326.000000 (59.000000 RLE) Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 16] (PATH ID: 6)
| | | | | | Column Cost Aspects: [ Disk(B): 65536.000000 CPU(B): 0.000000 Memory(B): 944.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | Projection: cbi_data.sa_server_group_member_d_unsegmented_node0001
| | | | | | Materialize: sa_server_group_member_d.server_group_key
| | | | | | Runtime Filters: (SIP11(MergeJoin): sa_server_group_member_d.server_group_key), (SIP8(HashJoin): sa_server_group_member_d.server_key)
| | | | | | Sort Key: (sa_server_group_member_d.server_group_key, sa_server_group_member_d.server_key, sa_server_group_member_d.end_date, sa_server_group_member_d.start_date)
| | | | | | LDISTRIB_SEGMENTED
| | | | | +-- Inner -> STORAGE ACCESS for <No Alias> [Cost: 51.000000, Rows: 608.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 32] (PATH ID: 7)
| | | | | | Column Cost Aspects: [ Disk(B): 196608.000000 CPU(B): 0.000000 Memory(B): 19456.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | Projection: cbi_data.sa_server_group_ld_unsegmented_node0001
| | | | | | Materialize: sa_server_group_ld.server_group_key, sa_server_group_ld.effective_date, sa_server_group_ld.end_of_life_date
| | | | | | Runtime Filter: (SIP10(MergeJoin): sa_server_group_ld.server_group_key)
| | | | | | Sort Key: (sa_server_group_ld.server_group_key, sa_server_group_ld.source_item_id)
| | | | | | LDISTRIB_SEGMENTED
| | | | +-- Inner -> STORAGE ACCESS for <No Alias> [Cost: 183.000000, Rows: 585.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 789] (PATH ID: 8)
| | | | | Column Cost Aspects: [ Disk(B): 655360.000000 CPU(B): 0.000000 Memory(B): 456885.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | Projection: cbi_data.sa_server_group_base_d_node0001
| | | | | Materialize: sa_server_group_base_d.server_group_key, sa_server_group_base_d.group_name
| | | | | Sort Key: (sa_server_group_base_d.server_group_key)
| | | | | LDISTRIB_SEGMENTED
| | | +-- Inner -> JOIN HASH [Cost: 50914.000000, Rows: 251.000000 Disk(B): 0.000000 CPU(B): 2128480.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 48751] (PATH ID: 9)
| | | | Join Filter: (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= sa_server_platform_d.start_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= sa_server_platform_d.end_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= sa_platform_ld.effective_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= sa_platform_ld.end_of_life_date)
| | | | Materialize at Input: sa_platform_ld.platform_key, sa_platform_ld.effective_date, sa_platform_ld.end_of_life_date
| | | | Sort Key: (sa_server_facility_d.facility_key, sa_server_facility_d.server_key, sa_server_facility_d.end_date, sa_server_facility_d.start_date)
| | | | LDISTRIB_SEGMENTED
| | | | +-- Outer -> JOIN HASH [Cost: 50487.000000, Rows: 251.000000 Disk(B): 0.000000 CPU(B): 70280.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 47179] (PATH ID: 10)
| | | | | Join Filter: (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= sa_server_facility_d.start_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= sa_server_facility_d.end_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= sa_facility_ld.effective_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= sa_facility_ld.end_of_life_date)
| | | | | Materialize at Input: sa_server_facility_d.start_date, sa_server_facility_d.end_date, sa_server_management_d.server_key, sa_server_management_d.end_date, sa_server_management_d.start_date, sa_server_management_d.host_name
| | | | | Sort Key: (sa_server_facility_d.facility_key, sa_server_facility_d.server_key, sa_server_facility_d.end_date, sa_server_facility_d.start_date)
| | | | | LDISTRIB_SEGMENTED
| | | | | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [Cost: 344.000000, Rows: 212.000000 (7.000000 RLE) Disk(B): 0.000000 CPU(B): 1902.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 206] (PATH ID: 11)
| | | | | | Join Cond: (sa_facility_ld.facility_key = sa_facility_base_d.facility_key)
| | | | | | Sort Key: (sa_server_facility_d.facility_key, sa_server_facility_d.server_key, sa_server_facility_d.end_date, sa_server_facility_d.start_date)
| | | | | | LDISTRIB_SEGMENTED
| | | | | | +-- Outer -> JOIN HASH [Cost: 213.000000, Rows: 212.000000 (7.000000 RLE) Disk(B): 0.000000 CPU(B): 528.000000 Memory(B): 432.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 48] (PATH ID: 12)
| | | | | | | Join Cond: (sa_server_facility_d.facility_key = sa_facility_ld.facility_key)
| | | | | | | Sort Key: (sa_server_facility_d.facility_key, sa_server_facility_d.server_key, sa_server_facility_d.end_date, sa_server_facility_d.start_date)
| | | | | | | LDISTRIB_SEGMENTED
| | | | | | | +-- Outer -> STORAGE ACCESS for <No Alias> [Cost: 17.000000, Rows: 212.000000 (7.000000 RLE) Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 16] (PATH ID: 13)
| | | | | | | | Column Cost Aspects: [ Disk(B): 65536.000000 CPU(B): 0.000000 Memory(B): 112.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | | Projection: cbi_data.sa_server_facility_d_unsegmented_keys_node0001
| | | | | | | | Materialize: sa_server_facility_d.facility_key
| | | | | | | | Runtime Filter: (SIP13(HashJoin): sa_server_facility_d.facility_key)
| | | | | | | | Sort Key: (sa_server_facility_d.facility_key, sa_server_facility_d.server_key, sa_server_facility_d.end_date, sa_server_facility_d.start_date)
| | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | +-- Inner -> STORAGE ACCESS for <No Alias> [Cost: 195.000000, Rows: 9.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 32] (PATH ID: 14)
| | | | | | | | Column Cost Aspects: [ Disk(B): 786432.000000 CPU(B): 0.000000 Memory(B): 288.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | | Projection: cbi_data.sa_facility_ld_node0001
| | | | | | | | Materialize: sa_facility_ld.facility_key, sa_facility_ld.effective_date, sa_facility_ld.end_of_life_date
| | | | | | | | Runtime Filter: (SIP12(MergeJoin): sa_facility_ld.facility_key)
| | | | | | | | Sort Key: (sa_facility_ld.source_key, sa_facility_ld.source_item_id)
| | | | | | | | LDISTRIB_SEGMENTED
| | | | | | +-- Inner -> STORAGE ACCESS for <No Alias> [Cost: 130.000000, Rows: 9.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 174] (PATH ID: 15)
| | | | | | | Column Cost Aspects: [ Disk(B): 524288.000000 CPU(B): 0.000000 Memory(B): 1494.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | Projection: cbi_data.sa_facility_base_d_node0001
| | | | | | | Materialize: sa_facility_base_d.facility_key, sa_facility_base_d.facility_name
| | | | | | | Sort Key: (sa_facility_base_d.facility_key)
| | | | | | | LDISTRIB_SEGMENTED
| | | | | +-- Inner -> JOIN HASH [Cost: 50141.000000, Rows: 251.000000 Disk(B): 0.000000 CPU(B): 1361424.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 46184] (PATH ID: 16)
| | | | | | Join Filter: (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= sa_server_management_d.start_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= sa_server_management_d.end_date)
| | | | | | Sort Key: (sa_server_management_d.server_key, sa_server_management_d.end_date)
| | | | | | LDISTRIB_SEGMENTED
| | | | | | +-- Outer -> STORAGE ACCESS for <No Alias> [Cost: 866.000000, Rows: 226.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 16] (PATH ID: 17)
| | | | | | | Column Cost Aspects: [ Disk(B): 3538944.000000 CPU(B): 0.000000 Memory(B): 3616.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | Projection: cbi_data.sa_server_management_d_super
| | | | | | | Materialize: sa_server_management_d.start_date, sa_server_management_d.end_date
| | | | | | | Sort Key: (sa_server_management_d.server_key, sa_server_management_d.end_date)
| | | | | | | LDISTRIB_SEGMENTED
| | | | | | +-- Inner -> JOIN HASH [Cost: 49241.000000, Rows: 251.000000 Disk(B): 0.000000 CPU(B): 11024873.000000 Memory(B): 4734.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 46176] (PATH ID: 18)
| | | | | | | Join Cond: ("<subquery>".audit_policy_key = sa_audit_lnkd_policies_d.audit_policy_key)
| | | | | | | Join Filter: (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= "<subquery>".start_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= "<subquery>".end_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= "<subquery>".effective_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= "<subquery>".end_of_life_date)
| | | | | | | Sort Key: (V(3,1), V(3,3))
| | | | | | | LDISTRIB_UNSEGMENTED
| | | | | | | +-- Outer -> JOIN HASH [Cost: 47631.000000, Rows: 251.000000 Disk(B): 0.000000 CPU(B): 11020875.000000 Memory(B): 14592.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 43825] (PATH ID: 19)
| | | | | | | | Join Cond: (sa_audit_lnkd_policies_d.audit_key = "<subquery>".audit_key)
| | | | | | | | Join Filter: (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= sa_audit_lnkd_policies_d.start_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= sa_audit_lnkd_policies_d.end_date)
| | | | | | | | Sort Key: (V(3,1), V(3,3))
| | | | | | | | LDISTRIB_UNSEGMENTED
| | | | | | | | +-- Outer -> JOIN HASH [Cost: 47293.000000, Rows: 251.000000 Disk(B): 0.000000 CPU(B): 941631.000000 Memory(B): 40298.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 43793] (PATH ID: 20)
| | | | | | | | | Join Cond: (sa_audit_compliance_daily_prejoin_projection_b0.audit_key = "<subquery>".audit_key)
| | | | | | | | | Join Filter: (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= "<subquery>".effective_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= "<subquery>".end_of_life_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= "<subquery>".start_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= "<subquery>".end_date)
| | | | | | | | | Materialize at Input: sa_audit_result_summary_f.server_key, sa_audit_result_summary_f.audit_result_key, sa_audit_result_summary_f.compliant_checks, sa_audit_result_summary_f.scan_failed_checks, sa_audit_result_summary_f.warning_checks, sa_audit_result_summary_f.excepted_checks, sa_audit_result_summary_f.non_compliant_checks, sa_audit_result_summary_f.total_non_compliant_objects
| | | | | | | | | Sort Key: (V(3,1), V(3,3))
| | | | | | | | | LDISTRIB_UNSEGMENTED
| | | | | | | | | +-- Outer -> SELECT [Cost: 2053.000000, Rows: 251.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 3511] (PATH ID: 21)
| | | | | | | | | | Sort Key: (sa_audit_d.audit_key, sa_audit_d.end_date)
| | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | +---> JOIN MERGEJOIN(inputs presorted) [Cost: 2053.000000, Rows: 251.000000 Disk(B): 6553600.000000 CPU(B): 11504.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 3511] (PATH ID: 22)
| | | | | | | | | | | Join Cond: (sa_audit_d.audit_key = sa_audit_ld.audit_key)
| | | | | | | | | | | Materialize at Output: sa_audit_d.start_date, sa_audit_d.end_date, sa_audit_d.audit_name, sa_audit_d.created_by
| | | | | | | | | | | Sort Key: (sa_audit_d.audit_key, sa_audit_d.end_date)
| | | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | | +-- Outer -> STORAGE ACCESS for sa_audit_d [Cost: 401.000000, Rows: 251.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 16] (PATH ID: 23)
| | | | | | | | | | | | Column Cost Aspects: [ Disk(B): 1638400.000000 CPU(B): 0.000000 Memory(B): 4016.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | | | | | | Projection: cbi_data.sa_audit_d_super
| | | | | | | | | | | | Materialize: sa_audit_d.audit_key
| | | | | | | | | | | | Runtime Filters: (SIP1(MergeJoin): sa_audit_d.audit_key), (SIP16(HashJoin): "<subquery>".audit_key), (SIP15(HashJoin): "<subquery>".audit_key), (SIP44(HashJoin): "<subquery>".audit_key), (SIP43(HashJoin): "<subquery>".audit_key)
| | | | | | | | | | | | Sort Key: (sa_audit_d.audit_key, sa_audit_d.end_date)
| | | | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | | +-- Inner -> STORAGE ACCESS for sa_audit_ld [Cost: 51.000000, Rows: 234.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 32] (PATH ID: 24)
| | | | | | | | | | | | Column Cost Aspects: [ Disk(B): 196608.000000 CPU(B): 0.000000 Memory(B): 7488.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | | | | | | Projection: cbi_data.sa_audit_ld_unsegmented_node0001
| | | | | | | | | | | | Materialize: sa_audit_ld.audit_key, sa_audit_ld.effective_date, sa_audit_ld.end_of_life_date
| | | | | | | | | | | | Sort Key: (sa_audit_ld.audit_key, sa_audit_ld.source_item_id)
| | | | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | +-- Inner -> JOIN HASH [Cost: 45215.000000, Rows: 1.000000 Disk(B): 0.000000 CPU(B): 400444.728554 Memory(B): 40170.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 40170] (PATH ID: 25)
| | | | | | | | | | Join Cond: (sa_audit_compliance_summary_f.audit_result_key = sa_audit_result_summary_f.audit_result_key) AND (sa_audit_compliance_summary_f.server_key = sa_audit_result_summary_f.server_key)
| | | | | | | | | | Sort Key: (sa_audit_result_summary_f.server_key, sa_audit_result_summary_f.audit_result_key, sa_audit_result_summary_f.compliance_status)
| | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | +-- Outer -> STORAGE ACCESS for <No Alias> [Cost: 182.000000, Rows: 9007.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 32] (PATH ID: 26)
| | | | | | | | | | | Column Cost Aspects: [ Disk(B): 655291.919491 CPU(B): 131072.000000 Memory(B): 333218.156603 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | | | | | Projection: cbi_data.sa_audit_result_summary_f_segmented_result_b0
| | | | | | | | | | | Materialize: sa_audit_result_summary_f.audit_result_key, sa_audit_result_summary_f.server_key
| | | | | | | | | | | Filter: (sa_audit_result_summary_f.compliance_status <> 'UNKNOWN')/* sel=0.999870 ndv= 4 */
| | | | | | | | | | | Runtime Filters: (SIP17(HashJoin): sa_audit_result_summary_f.audit_result_key), (SIP18(HashJoin): sa_audit_result_summary_f.server_key), (SIP19(HashJoin): sa_audit_result_summary_f.audit_result_key, sa_audit_result_summary_f.server_key)
| | | | | | | | | | | Sort Key: (sa_audit_result_summary_f.server_key, sa_audit_result_summary_f.audit_result_key, sa_audit_result_summary_f.compliance_status)
| | | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | +-- Inner -> JOIN HASH [RightOuter] [Cost: 45021.000000, Rows: 1.000000 Disk(B): 9306112.000000 CPU(B): 1675941234.000000 Memory(B): 236.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 40154] (PATH ID: 27)
| | | | | | | | | | | Join Cond: (sa_audit_compliance_daily_prejoin_projection_b0.server_key = sa_audit_compliance_details_f.server_key) AND (sa_audit_compliance_daily_prejoin_projection_b0.audit_key = sa_audit_compliance_details_f.audit_key) AND (sa_audit_compliance_daily_prejoin_projection_b0.compliance_summary_id = sa_audit_compliance_details_f.compliance_summary_id)
| | | | | | | | | | | Materialize at Input: sa_audit_compliance_details_f.server_key, sa_audit_compliance_details_f.audit_key, sa_audit_compliance_details_f.compliance_summary_id, sa_audit_compliance_details_f.compliance_status_key, sa_audit_compliance_details_f.test_date, sa_audit_compliance_details_f.rule_item, sa_audit_compliance_details_f.rule_description, sa_audit_compliance_details_f.rule_details, sa_audit_compliance_details_f.actual_value, sa_audit_compliance_details_f.expected_value, sa_audit_compliance_details_f.compliance_detail_id, sa_audit_compliance_summary_f.server_key, sa_audit_compliance_summary_f.audit_result_key, sa_audit_compliance_summary_f.job_key, sa_audit_compliance_summary_f.audit_key, sa_audit_compliance_summary_f.test_date
| | | | | | | | | | | Runtime Filter: (SIP4(HashJoin): sa_audit_compliance_daily_prejoin_projection_b0.server_key)
| | | | | | | | | | | LDISTRIB_UNSEGMENTED
| | | | | | | | | | | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 205.000000, Rows: 41909.000000 (6561.000000 RLE) Disk(B): 0.000000 CPU(B): 160408.000000 Memory(B): 3200.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 84] (PATH ID: 28)
| | | | | | | | | | | | Join Cond: (sa_audit_compliance_details_f.compliance_status_key = cbi_data.sa_compliance_status_type_d.compliance_status_key)
| | | | | | | | | | | | Runtime Filters: (SIP20(HashJoin): sa_audit_compliance_details_f.server_key), (SIP21(HashJoin): sa_audit_compliance_details_f.audit_key), (SIP22(HashJoin): sa_audit_compliance_details_f.compliance_summary_id), (SIP23(HashJoin): sa_audit_compliance_details_f.server_key, sa_audit_compliance_details_f.audit_key, sa_audit_compliance_details_f.compliance_summary_id)
| | | | | | | | | | | | Sort Key: (sa_audit_compliance_details_f.server_key, sa_audit_compliance_details_f.audit_key, sa_audit_compliance_details_f.compliance_summary_id, sa_audit_compliance_details_f.compliance_status_key, sa_audit_compliance_details_f.transaction_key, sa_audit_compliance_details_f.source_key)
| | | | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | | | +-- Outer -> STORAGE ACCESS for <No Alias> [Cost: 70.000000, Rows: 41909.000000 (6561.000000 RLE) Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 16] (PATH ID: 29)
| | | | | | | | | | | | | Column Cost Aspects: [ Disk(B): 262144.000000 CPU(B): 0.000000 Memory(B): 104976.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | | | | | | | Projection: cbi_data.sa_audit_compliance_details_f_segmented1_b0
| | | | | | | | | | | | | Materialize: sa_audit_compliance_details_f.compliance_status_key
| | | | | | | | | | | | | Sort Key: (sa_audit_compliance_details_f.server_key, sa_audit_compliance_details_f.audit_key, sa_audit_compliance_details_f.compliance_summary_id, sa_audit_compliance_details_f.compliance_status_key, sa_audit_compliance_details_f.transaction_key, sa_audit_compliance_details_f.source_key)
| | | | | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | | | +-- Inner -> STORAGE ACCESS for <No Alias> [Cost: 130.000000, Rows: 32.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 84] (PATH ID: 30)
| | | | | | | | | | | | | Column Cost Aspects: [ Disk(B): 524288.000000 CPU(B): 0.000000 Memory(B): 2432.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | | | | | | | Projection: cbi_data.sa_compliance_status_type_d_node0001
| | | | | | | | | | | | | Materialize: cbi_data.sa_compliance_status_type_d.compliance_status_key, cbi_data.sa_compliance_status_type_d.status_name
| | | | | | | | | | | | | Sort Key: (sa_compliance_status_type_d.source_key, sa_compliance_status_type_d.source_item_id)
| | | | | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | | +-- Inner -> JOIN HASH [Cost: 1627.000000, Rows: 1.000000 Disk(B): 706418.685891 CPU(B): 768344.107252 Memory(B): 749160.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 156] (PATH ID: 31)
| | | | | | | | | | | | Join Cond: (sa_audit_compliance_daily_prejoin_projection_b0.server_key = sa_audit_compliance_summary_f.server_key) AND (sa_audit_compliance_daily_prejoin_projection_b0.audit_key = sa_audit_compliance_summary_f.audit_key) AND (sa_audit_compliance_daily_prejoin_projection_b0.sa_job_key = sa_audit_compliance_summary_f.job_key) AND (sa_audit_compliance_daily_prejoin_projection_b0.scan_date = sa_audit_compliance_summary_f.test_date)
| | | | | | | | | | | | Materialize at Input: sa_audit_compliance_daily_prejoin_projection_b0.server_key, sa_audit_compliance_daily_prejoin_projection_b0.audit_key, sa_audit_compliance_daily_prejoin_projection_b0.compliance_summary_id, sa_audit_compliance_daily_prejoin_projection_b0.sa_job_key, sa_audit_compliance_daily_prejoin_projection_b0.effective_date, sa_audit_compliance_daily_prejoin_projection_b0.scan_date, sa_audit_compliance_daily_prejoin_projection_b0.compliance_status_key
| | | | | | | | | | | | Sort Key: (sa_audit_compliance_summary_f.server_key, sa_audit_compliance_summary_f.audit_result_key, sa_audit_compliance_summary_f.job_key, sa_audit_compliance_summary_f.audit_key, sa_audit_compliance_summary_f.test_date)
| | | | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | | | +-- Outer -> STORAGE ACCESS for <No Alias> [Cost: 1156.000000, Rows: 820.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 56] (PATH ID: 32)
| | | | | | | | | | | | | Column Cost Aspects: [ Disk(B): 4718592.000000 CPU(B): 0.000000 Memory(B): 45920.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | | | | | | | Projection: cbi_data.sa_audit_compliance_summary_result_based_b0
| | | | | | | | | | | | | Materialize: sa_audit_compliance_summary_f.server_key, sa_audit_compliance_summary_f.audit_key, sa_audit_compliance_summary_f.job_key, sa_audit_compliance_summary_f.test_date
| | | | | | | | | | | | | Runtime Filters: (SIP24(HashJoin): sa_audit_compliance_summary_f.server_key), (SIP25(HashJoin): sa_audit_compliance_summary_f.audit_key), (SIP26(HashJoin): sa_audit_compliance_summary_f.job_key), (SIP27(HashJoin): sa_audit_compliance_summary_f.test_date), (SIP28(HashJoin): sa_audit_compliance_summary_f.server_key, sa_audit_compliance_summary_f.audit_key, sa_audit_compliance_summary_f.job_key, sa_audit_compliance_summary_f.test_date)
| | | | | | | | | | | | | Sort Key: (sa_audit_compliance_summary_f.server_key, sa_audit_compliance_su
I tried to convert Hash joins to Merge joins wherever possible. But still running into this issue. Problem seems to be with sa_audit_result_summary_f table. If I exclude that table from the query, it executes instantly. How can resolve this issue ?
Access Path:Sort Key: (sa_platform_base_d.platform_name, sa_facility_base_d.facility_name, sa_server_group_base_d.group_name, sa_server_management_d.host_name, sa_compliance_status_type_d.status_name, sa_audit_compliance_details_f.rule_item, sa_audit_compliance_details_f.rule_details, sa_audit_compliance_details_f.rule_description, sa_audit_compliance_details_f.expected_value, sa_audit_compliance_details_f.actual_value, sa_audit_compliance_details_f.test_date, V(3,4), V(6,1), V(5,4), V(3,5), sa_compliance_status_type_d.status_name, sa_audit_compliance_daily_prejoin_projection_b0.scan_date, V(3,6), sa_audit_compliance_details_f.compliance_detail_id)
LDISTRIB_SEGMENTED
+-GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 54909.000000, Rows: 485.000000 Disk(B): 0.000000 CPU(B): 24077340.000000 Memory(B): 47627000.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 49092] (PATH ID: 1)
| Aggregates: sum(sa_audit_result_summary_f.warning_checks), sum(sa_audit_result_summary_f.total_non_compliant_objects), sum(sa_audit_result_summary_f.compliant_checks), sum(sa_audit_result_summary_f.non_compliant_checks), sum(sa_audit_result_summary_f.scan_failed_checks), sum(sa_audit_result_summary_f.excepted_checks)
| Group By: sa_platform_base_d.platform_name, sa_facility_base_d.facility_name, sa_server_group_base_d.group_name, sa_server_management_d.host_name, cbi_data.sa_compliance_status_type_d.status_name, sa_audit_compliance_details_f.rule_item, sa_audit_compliance_details_f.rule_details, sa_audit_compliance_details_f.rule_description, sa_audit_compliance_details_f.expected_value, sa_audit_compliance_details_f.actual_value, sa_audit_compliance_details_f.test_date, "<subquery>".audit_name, "<subquery>".server_key, "<subquery>".audit_policy_name, "<subquery>".created_by, cbi_data.sa_compliance_status_type_d.status_name, sa_audit_compliance_daily_prejoin_projection_b0.scan_date, "<subquery>".effective_date, sa_audit_compliance_details_f.compliance_detail_id
| Sort Key: (sa_platform_base_d.platform_name, sa_facility_base_d.facility_name, sa_server_group_base_d.group_name, sa_server_management_d.host_name, sa_compliance_status_type_d.status_name, sa_audit_compliance_details_f.rule_item, sa_audit_compliance_details_f.rule_details, sa_audit_compliance_details_f.rule_description, sa_audit_compliance_details_f.expected_value, sa_audit_compliance_details_f.actual_value, sa_audit_compliance_details_f.test_date, V(3,4), V(6,1), V(5,4), V(3,5), sa_compliance_status_type_d.status_name, sa_audit_compliance_daily_prejoin_projection_b0.scan_date, V(3,6), sa_audit_compliance_details_f.compliance_detail_id)
| LDISTRIB_SEGMENTED
| +---> JOIN HASH [Cost: 51995.000000, Rows: 485.000000 Disk(B): 0.000000 CPU(B): 16253720.000000 Memory(B): 23280.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 49644] (PATH ID: 2)
| | Join Cond: (sa_audit_compliance_daily_prejoin_projection_b0.server_key = "<subquery>".server_key) AND ("<subquery>".server_key = sa_server_management_d.server_key) AND (sa_server_facility_d.server_key = "<subquery>".server_key) AND ("<subquery>".server_key = sa_server_platform_d.server_key) AND ("<subquery>".server_key = sa_server_group_member_d.server_key)
| | Join Filter: (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= "<subquery>".server_effective_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= "<subquery>".end_of_life_date)
| | Materialize at Input: sa_server_group_member_d.server_key
| | Sort Key: (sa_server_group_member_d.server_group_key, sa_server_group_member_d.server_key, sa_server_group_member_d.end_date, sa_server_group_member_d.start_date)
| | LDISTRIB_SEGMENTED
| | +-- Outer -> JOIN HASH [Cost: 51193.000000, Rows: 326.000000 (251.000000 RLE) Disk(B): 0.000000 CPU(B): 592360.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 49604] (PATH ID: 3)
| | | Join Filter: (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= sa_server_group_member_d.start_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= sa_server_group_member_d.end_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= sa_server_group_ld.effective_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= sa_server_group_ld.end_of_life_date)
| | | Materialize at Input: sa_server_group_member_d.start_date, sa_server_group_member_d.end_date, sa_server_facility_d.facility_key, sa_server_facility_d.server_key, sa_server_facility_d.end_date, sa_server_facility_d.start_date
| | | Sort Key: (sa_server_group_member_d.server_group_key, sa_server_group_member_d.server_key, sa_server_group_member_d.end_date, sa_server_group_member_d.start_date)
| | | LDISTRIB_SEGMENTED
| | | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [Cost: 264.000000, Rows: 326.000000 (59.000000 RLE) Disk(B): 0.000000 CPU(B): 464397.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 821] (PATH ID: 4)
| | | | Join Cond: (sa_server_group_ld.server_group_key = sa_server_group_base_d.server_group_key)
| | | | Sort Key: (sa_server_group_member_d.server_group_key, sa_server_group_member_d.server_key, sa_server_group_member_d.end_date, sa_server_group_member_d.start_date)
| | | | LDISTRIB_SEGMENTED
| | | | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [Cost: 69.000000, Rows: 326.000000 (59.000000 RLE) Disk(B): 0.000000 CPU(B): 20400.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 48] (PATH ID: 5)
| | | | | Join Cond: (sa_server_group_member_d.server_group_key = sa_server_group_ld.server_group_key)
| | | | | Sort Key: (sa_server_group_member_d.server_group_key, sa_server_group_member_d.server_key, sa_server_group_member_d.end_date, sa_server_group_member_d.start_date)
| | | | | LDISTRIB_SEGMENTED
| | | | | +-- Outer -> STORAGE ACCESS for <No Alias> [Cost: 17.000000, Rows: 326.000000 (59.000000 RLE) Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 16] (PATH ID: 6)
| | | | | | Column Cost Aspects: [ Disk(B): 65536.000000 CPU(B): 0.000000 Memory(B): 944.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | Projection: cbi_data.sa_server_group_member_d_unsegmented_node0001
| | | | | | Materialize: sa_server_group_member_d.server_group_key
| | | | | | Runtime Filters: (SIP11(MergeJoin): sa_server_group_member_d.server_group_key), (SIP8(HashJoin): sa_server_group_member_d.server_key)
| | | | | | Sort Key: (sa_server_group_member_d.server_group_key, sa_server_group_member_d.server_key, sa_server_group_member_d.end_date, sa_server_group_member_d.start_date)
| | | | | | LDISTRIB_SEGMENTED
| | | | | +-- Inner -> STORAGE ACCESS for <No Alias> [Cost: 51.000000, Rows: 608.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 32] (PATH ID: 7)
| | | | | | Column Cost Aspects: [ Disk(B): 196608.000000 CPU(B): 0.000000 Memory(B): 19456.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | Projection: cbi_data.sa_server_group_ld_unsegmented_node0001
| | | | | | Materialize: sa_server_group_ld.server_group_key, sa_server_group_ld.effective_date, sa_server_group_ld.end_of_life_date
| | | | | | Runtime Filter: (SIP10(MergeJoin): sa_server_group_ld.server_group_key)
| | | | | | Sort Key: (sa_server_group_ld.server_group_key, sa_server_group_ld.source_item_id)
| | | | | | LDISTRIB_SEGMENTED
| | | | +-- Inner -> STORAGE ACCESS for <No Alias> [Cost: 183.000000, Rows: 585.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 789] (PATH ID: 8)
| | | | | Column Cost Aspects: [ Disk(B): 655360.000000 CPU(B): 0.000000 Memory(B): 456885.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | Projection: cbi_data.sa_server_group_base_d_node0001
| | | | | Materialize: sa_server_group_base_d.server_group_key, sa_server_group_base_d.group_name
| | | | | Sort Key: (sa_server_group_base_d.server_group_key)
| | | | | LDISTRIB_SEGMENTED
| | | +-- Inner -> JOIN HASH [Cost: 50914.000000, Rows: 251.000000 Disk(B): 0.000000 CPU(B): 2128480.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 48751] (PATH ID: 9)
| | | | Join Filter: (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= sa_server_platform_d.start_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= sa_server_platform_d.end_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= sa_platform_ld.effective_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= sa_platform_ld.end_of_life_date)
| | | | Materialize at Input: sa_platform_ld.platform_key, sa_platform_ld.effective_date, sa_platform_ld.end_of_life_date
| | | | Sort Key: (sa_server_facility_d.facility_key, sa_server_facility_d.server_key, sa_server_facility_d.end_date, sa_server_facility_d.start_date)
| | | | LDISTRIB_SEGMENTED
| | | | +-- Outer -> JOIN HASH [Cost: 50487.000000, Rows: 251.000000 Disk(B): 0.000000 CPU(B): 70280.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 47179] (PATH ID: 10)
| | | | | Join Filter: (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= sa_server_facility_d.start_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= sa_server_facility_d.end_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= sa_facility_ld.effective_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= sa_facility_ld.end_of_life_date)
| | | | | Materialize at Input: sa_server_facility_d.start_date, sa_server_facility_d.end_date, sa_server_management_d.server_key, sa_server_management_d.end_date, sa_server_management_d.start_date, sa_server_management_d.host_name
| | | | | Sort Key: (sa_server_facility_d.facility_key, sa_server_facility_d.server_key, sa_server_facility_d.end_date, sa_server_facility_d.start_date)
| | | | | LDISTRIB_SEGMENTED
| | | | | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [Cost: 344.000000, Rows: 212.000000 (7.000000 RLE) Disk(B): 0.000000 CPU(B): 1902.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 206] (PATH ID: 11)
| | | | | | Join Cond: (sa_facility_ld.facility_key = sa_facility_base_d.facility_key)
| | | | | | Sort Key: (sa_server_facility_d.facility_key, sa_server_facility_d.server_key, sa_server_facility_d.end_date, sa_server_facility_d.start_date)
| | | | | | LDISTRIB_SEGMENTED
| | | | | | +-- Outer -> JOIN HASH [Cost: 213.000000, Rows: 212.000000 (7.000000 RLE) Disk(B): 0.000000 CPU(B): 528.000000 Memory(B): 432.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 48] (PATH ID: 12)
| | | | | | | Join Cond: (sa_server_facility_d.facility_key = sa_facility_ld.facility_key)
| | | | | | | Sort Key: (sa_server_facility_d.facility_key, sa_server_facility_d.server_key, sa_server_facility_d.end_date, sa_server_facility_d.start_date)
| | | | | | | LDISTRIB_SEGMENTED
| | | | | | | +-- Outer -> STORAGE ACCESS for <No Alias> [Cost: 17.000000, Rows: 212.000000 (7.000000 RLE) Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 16] (PATH ID: 13)
| | | | | | | | Column Cost Aspects: [ Disk(B): 65536.000000 CPU(B): 0.000000 Memory(B): 112.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | | Projection: cbi_data.sa_server_facility_d_unsegmented_keys_node0001
| | | | | | | | Materialize: sa_server_facility_d.facility_key
| | | | | | | | Runtime Filter: (SIP13(HashJoin): sa_server_facility_d.facility_key)
| | | | | | | | Sort Key: (sa_server_facility_d.facility_key, sa_server_facility_d.server_key, sa_server_facility_d.end_date, sa_server_facility_d.start_date)
| | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | +-- Inner -> STORAGE ACCESS for <No Alias> [Cost: 195.000000, Rows: 9.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 32] (PATH ID: 14)
| | | | | | | | Column Cost Aspects: [ Disk(B): 786432.000000 CPU(B): 0.000000 Memory(B): 288.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | | Projection: cbi_data.sa_facility_ld_node0001
| | | | | | | | Materialize: sa_facility_ld.facility_key, sa_facility_ld.effective_date, sa_facility_ld.end_of_life_date
| | | | | | | | Runtime Filter: (SIP12(MergeJoin): sa_facility_ld.facility_key)
| | | | | | | | Sort Key: (sa_facility_ld.source_key, sa_facility_ld.source_item_id)
| | | | | | | | LDISTRIB_SEGMENTED
| | | | | | +-- Inner -> STORAGE ACCESS for <No Alias> [Cost: 130.000000, Rows: 9.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 174] (PATH ID: 15)
| | | | | | | Column Cost Aspects: [ Disk(B): 524288.000000 CPU(B): 0.000000 Memory(B): 1494.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | Projection: cbi_data.sa_facility_base_d_node0001
| | | | | | | Materialize: sa_facility_base_d.facility_key, sa_facility_base_d.facility_name
| | | | | | | Sort Key: (sa_facility_base_d.facility_key)
| | | | | | | LDISTRIB_SEGMENTED
| | | | | +-- Inner -> JOIN HASH [Cost: 50141.000000, Rows: 251.000000 Disk(B): 0.000000 CPU(B): 1361424.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 46184] (PATH ID: 16)
| | | | | | Join Filter: (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= sa_server_management_d.start_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= sa_server_management_d.end_date)
| | | | | | Sort Key: (sa_server_management_d.server_key, sa_server_management_d.end_date)
| | | | | | LDISTRIB_SEGMENTED
| | | | | | +-- Outer -> STORAGE ACCESS for <No Alias> [Cost: 866.000000, Rows: 226.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 16] (PATH ID: 17)
| | | | | | | Column Cost Aspects: [ Disk(B): 3538944.000000 CPU(B): 0.000000 Memory(B): 3616.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | Projection: cbi_data.sa_server_management_d_super
| | | | | | | Materialize: sa_server_management_d.start_date, sa_server_management_d.end_date
| | | | | | | Sort Key: (sa_server_management_d.server_key, sa_server_management_d.end_date)
| | | | | | | LDISTRIB_SEGMENTED
| | | | | | +-- Inner -> JOIN HASH [Cost: 49241.000000, Rows: 251.000000 Disk(B): 0.000000 CPU(B): 11024873.000000 Memory(B): 4734.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 46176] (PATH ID: 18)
| | | | | | | Join Cond: ("<subquery>".audit_policy_key = sa_audit_lnkd_policies_d.audit_policy_key)
| | | | | | | Join Filter: (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= "<subquery>".start_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= "<subquery>".end_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= "<subquery>".effective_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= "<subquery>".end_of_life_date)
| | | | | | | Sort Key: (V(3,1), V(3,3))
| | | | | | | LDISTRIB_UNSEGMENTED
| | | | | | | +-- Outer -> JOIN HASH [Cost: 47631.000000, Rows: 251.000000 Disk(B): 0.000000 CPU(B): 11020875.000000 Memory(B): 14592.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 43825] (PATH ID: 19)
| | | | | | | | Join Cond: (sa_audit_lnkd_policies_d.audit_key = "<subquery>".audit_key)
| | | | | | | | Join Filter: (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= sa_audit_lnkd_policies_d.start_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= sa_audit_lnkd_policies_d.end_date)
| | | | | | | | Sort Key: (V(3,1), V(3,3))
| | | | | | | | LDISTRIB_UNSEGMENTED
| | | | | | | | +-- Outer -> JOIN HASH [Cost: 47293.000000, Rows: 251.000000 Disk(B): 0.000000 CPU(B): 941631.000000 Memory(B): 40298.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 43793] (PATH ID: 20)
| | | | | | | | | Join Cond: (sa_audit_compliance_daily_prejoin_projection_b0.audit_key = "<subquery>".audit_key)
| | | | | | | | | Join Filter: (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= "<subquery>".effective_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= "<subquery>".end_of_life_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date >= "<subquery>".start_date) AND (sa_audit_compliance_daily_prejoin_projection_b0.effective_date <= "<subquery>".end_date)
| | | | | | | | | Materialize at Input: sa_audit_result_summary_f.server_key, sa_audit_result_summary_f.audit_result_key, sa_audit_result_summary_f.compliant_checks, sa_audit_result_summary_f.scan_failed_checks, sa_audit_result_summary_f.warning_checks, sa_audit_result_summary_f.excepted_checks, sa_audit_result_summary_f.non_compliant_checks, sa_audit_result_summary_f.total_non_compliant_objects
| | | | | | | | | Sort Key: (V(3,1), V(3,3))
| | | | | | | | | LDISTRIB_UNSEGMENTED
| | | | | | | | | +-- Outer -> SELECT [Cost: 2053.000000, Rows: 251.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 3511] (PATH ID: 21)
| | | | | | | | | | Sort Key: (sa_audit_d.audit_key, sa_audit_d.end_date)
| | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | +---> JOIN MERGEJOIN(inputs presorted) [Cost: 2053.000000, Rows: 251.000000 Disk(B): 6553600.000000 CPU(B): 11504.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 3511] (PATH ID: 22)
| | | | | | | | | | | Join Cond: (sa_audit_d.audit_key = sa_audit_ld.audit_key)
| | | | | | | | | | | Materialize at Output: sa_audit_d.start_date, sa_audit_d.end_date, sa_audit_d.audit_name, sa_audit_d.created_by
| | | | | | | | | | | Sort Key: (sa_audit_d.audit_key, sa_audit_d.end_date)
| | | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | | +-- Outer -> STORAGE ACCESS for sa_audit_d [Cost: 401.000000, Rows: 251.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 16] (PATH ID: 23)
| | | | | | | | | | | | Column Cost Aspects: [ Disk(B): 1638400.000000 CPU(B): 0.000000 Memory(B): 4016.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | | | | | | Projection: cbi_data.sa_audit_d_super
| | | | | | | | | | | | Materialize: sa_audit_d.audit_key
| | | | | | | | | | | | Runtime Filters: (SIP1(MergeJoin): sa_audit_d.audit_key), (SIP16(HashJoin): "<subquery>".audit_key), (SIP15(HashJoin): "<subquery>".audit_key), (SIP44(HashJoin): "<subquery>".audit_key), (SIP43(HashJoin): "<subquery>".audit_key)
| | | | | | | | | | | | Sort Key: (sa_audit_d.audit_key, sa_audit_d.end_date)
| | | | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | | +-- Inner -> STORAGE ACCESS for sa_audit_ld [Cost: 51.000000, Rows: 234.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 32] (PATH ID: 24)
| | | | | | | | | | | | Column Cost Aspects: [ Disk(B): 196608.000000 CPU(B): 0.000000 Memory(B): 7488.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | | | | | | Projection: cbi_data.sa_audit_ld_unsegmented_node0001
| | | | | | | | | | | | Materialize: sa_audit_ld.audit_key, sa_audit_ld.effective_date, sa_audit_ld.end_of_life_date
| | | | | | | | | | | | Sort Key: (sa_audit_ld.audit_key, sa_audit_ld.source_item_id)
| | | | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | +-- Inner -> JOIN HASH [Cost: 45215.000000, Rows: 1.000000 Disk(B): 0.000000 CPU(B): 400444.728554 Memory(B): 40170.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 40170] (PATH ID: 25)
| | | | | | | | | | Join Cond: (sa_audit_compliance_summary_f.audit_result_key = sa_audit_result_summary_f.audit_result_key) AND (sa_audit_compliance_summary_f.server_key = sa_audit_result_summary_f.server_key)
| | | | | | | | | | Sort Key: (sa_audit_result_summary_f.server_key, sa_audit_result_summary_f.audit_result_key, sa_audit_result_summary_f.compliance_status)
| | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | +-- Outer -> STORAGE ACCESS for <No Alias> [Cost: 182.000000, Rows: 9007.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 32] (PATH ID: 26)
| | | | | | | | | | | Column Cost Aspects: [ Disk(B): 655291.919491 CPU(B): 131072.000000 Memory(B): 333218.156603 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | | | | | Projection: cbi_data.sa_audit_result_summary_f_segmented_result_b0
| | | | | | | | | | | Materialize: sa_audit_result_summary_f.audit_result_key, sa_audit_result_summary_f.server_key
| | | | | | | | | | | Filter: (sa_audit_result_summary_f.compliance_status <> 'UNKNOWN')/* sel=0.999870 ndv= 4 */
| | | | | | | | | | | Runtime Filters: (SIP17(HashJoin): sa_audit_result_summary_f.audit_result_key), (SIP18(HashJoin): sa_audit_result_summary_f.server_key), (SIP19(HashJoin): sa_audit_result_summary_f.audit_result_key, sa_audit_result_summary_f.server_key)
| | | | | | | | | | | Sort Key: (sa_audit_result_summary_f.server_key, sa_audit_result_summary_f.audit_result_key, sa_audit_result_summary_f.compliance_status)
| | | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | +-- Inner -> JOIN HASH [RightOuter] [Cost: 45021.000000, Rows: 1.000000 Disk(B): 9306112.000000 CPU(B): 1675941234.000000 Memory(B): 236.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 40154] (PATH ID: 27)
| | | | | | | | | | | Join Cond: (sa_audit_compliance_daily_prejoin_projection_b0.server_key = sa_audit_compliance_details_f.server_key) AND (sa_audit_compliance_daily_prejoin_projection_b0.audit_key = sa_audit_compliance_details_f.audit_key) AND (sa_audit_compliance_daily_prejoin_projection_b0.compliance_summary_id = sa_audit_compliance_details_f.compliance_summary_id)
| | | | | | | | | | | Materialize at Input: sa_audit_compliance_details_f.server_key, sa_audit_compliance_details_f.audit_key, sa_audit_compliance_details_f.compliance_summary_id, sa_audit_compliance_details_f.compliance_status_key, sa_audit_compliance_details_f.test_date, sa_audit_compliance_details_f.rule_item, sa_audit_compliance_details_f.rule_description, sa_audit_compliance_details_f.rule_details, sa_audit_compliance_details_f.actual_value, sa_audit_compliance_details_f.expected_value, sa_audit_compliance_details_f.compliance_detail_id, sa_audit_compliance_summary_f.server_key, sa_audit_compliance_summary_f.audit_result_key, sa_audit_compliance_summary_f.job_key, sa_audit_compliance_summary_f.audit_key, sa_audit_compliance_summary_f.test_date
| | | | | | | | | | | Runtime Filter: (SIP4(HashJoin): sa_audit_compliance_daily_prejoin_projection_b0.server_key)
| | | | | | | | | | | LDISTRIB_UNSEGMENTED
| | | | | | | | | | | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 205.000000, Rows: 41909.000000 (6561.000000 RLE) Disk(B): 0.000000 CPU(B): 160408.000000 Memory(B): 3200.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 84] (PATH ID: 28)
| | | | | | | | | | | | Join Cond: (sa_audit_compliance_details_f.compliance_status_key = cbi_data.sa_compliance_status_type_d.compliance_status_key)
| | | | | | | | | | | | Runtime Filters: (SIP20(HashJoin): sa_audit_compliance_details_f.server_key), (SIP21(HashJoin): sa_audit_compliance_details_f.audit_key), (SIP22(HashJoin): sa_audit_compliance_details_f.compliance_summary_id), (SIP23(HashJoin): sa_audit_compliance_details_f.server_key, sa_audit_compliance_details_f.audit_key, sa_audit_compliance_details_f.compliance_summary_id)
| | | | | | | | | | | | Sort Key: (sa_audit_compliance_details_f.server_key, sa_audit_compliance_details_f.audit_key, sa_audit_compliance_details_f.compliance_summary_id, sa_audit_compliance_details_f.compliance_status_key, sa_audit_compliance_details_f.transaction_key, sa_audit_compliance_details_f.source_key)
| | | | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | | | +-- Outer -> STORAGE ACCESS for <No Alias> [Cost: 70.000000, Rows: 41909.000000 (6561.000000 RLE) Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 16] (PATH ID: 29)
| | | | | | | | | | | | | Column Cost Aspects: [ Disk(B): 262144.000000 CPU(B): 0.000000 Memory(B): 104976.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | | | | | | | Projection: cbi_data.sa_audit_compliance_details_f_segmented1_b0
| | | | | | | | | | | | | Materialize: sa_audit_compliance_details_f.compliance_status_key
| | | | | | | | | | | | | Sort Key: (sa_audit_compliance_details_f.server_key, sa_audit_compliance_details_f.audit_key, sa_audit_compliance_details_f.compliance_summary_id, sa_audit_compliance_details_f.compliance_status_key, sa_audit_compliance_details_f.transaction_key, sa_audit_compliance_details_f.source_key)
| | | | | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | | | +-- Inner -> STORAGE ACCESS for <No Alias> [Cost: 130.000000, Rows: 32.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 84] (PATH ID: 30)
| | | | | | | | | | | | | Column Cost Aspects: [ Disk(B): 524288.000000 CPU(B): 0.000000 Memory(B): 2432.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | | | | | | | Projection: cbi_data.sa_compliance_status_type_d_node0001
| | | | | | | | | | | | | Materialize: cbi_data.sa_compliance_status_type_d.compliance_status_key, cbi_data.sa_compliance_status_type_d.status_name
| | | | | | | | | | | | | Sort Key: (sa_compliance_status_type_d.source_key, sa_compliance_status_type_d.source_item_id)
| | | | | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | | +-- Inner -> JOIN HASH [Cost: 1627.000000, Rows: 1.000000 Disk(B): 706418.685891 CPU(B): 768344.107252 Memory(B): 749160.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 156] (PATH ID: 31)
| | | | | | | | | | | | Join Cond: (sa_audit_compliance_daily_prejoin_projection_b0.server_key = sa_audit_compliance_summary_f.server_key) AND (sa_audit_compliance_daily_prejoin_projection_b0.audit_key = sa_audit_compliance_summary_f.audit_key) AND (sa_audit_compliance_daily_prejoin_projection_b0.sa_job_key = sa_audit_compliance_summary_f.job_key) AND (sa_audit_compliance_daily_prejoin_projection_b0.scan_date = sa_audit_compliance_summary_f.test_date)
| | | | | | | | | | | | Materialize at Input: sa_audit_compliance_daily_prejoin_projection_b0.server_key, sa_audit_compliance_daily_prejoin_projection_b0.audit_key, sa_audit_compliance_daily_prejoin_projection_b0.compliance_summary_id, sa_audit_compliance_daily_prejoin_projection_b0.sa_job_key, sa_audit_compliance_daily_prejoin_projection_b0.effective_date, sa_audit_compliance_daily_prejoin_projection_b0.scan_date, sa_audit_compliance_daily_prejoin_projection_b0.compliance_status_key
| | | | | | | | | | | | Sort Key: (sa_audit_compliance_summary_f.server_key, sa_audit_compliance_summary_f.audit_result_key, sa_audit_compliance_summary_f.job_key, sa_audit_compliance_summary_f.audit_key, sa_audit_compliance_summary_f.test_date)
| | | | | | | | | | | | LDISTRIB_SEGMENTED
| | | | | | | | | | | | +-- Outer -> STORAGE ACCESS for <No Alias> [Cost: 1156.000000, Rows: 820.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 56] (PATH ID: 32)
| | | | | | | | | | | | | Column Cost Aspects: [ Disk(B): 4718592.000000 CPU(B): 0.000000 Memory(B): 45920.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 ]
| | | | | | | | | | | | | Projection: cbi_data.sa_audit_compliance_summary_result_based_b0
| | | | | | | | | | | | | Materialize: sa_audit_compliance_summary_f.server_key, sa_audit_compliance_summary_f.audit_key, sa_audit_compliance_summary_f.job_key, sa_audit_compliance_summary_f.test_date
| | | | | | | | | | | | | Runtime Filters: (SIP24(HashJoin): sa_audit_compliance_summary_f.server_key), (SIP25(HashJoin): sa_audit_compliance_summary_f.audit_key), (SIP26(HashJoin): sa_audit_compliance_summary_f.job_key), (SIP27(HashJoin): sa_audit_compliance_summary_f.test_date), (SIP28(HashJoin): sa_audit_compliance_summary_f.server_key, sa_audit_compliance_summary_f.audit_key, sa_audit_compliance_summary_f.job_key, sa_audit_compliance_summary_f.test_date)
| | | | | | | | | | | | | Sort Key: (sa_audit_compliance_summary_f.server_key, sa_audit_compliance_su
0