Forum Stats

  • 3,840,319 Users
  • 2,262,585 Discussions
  • 7,901,216 Comments

Discussions

Query Performance Issue

2»

Answers

  • User_OCZ1T
    User_OCZ1T Member Posts: 1,923 Gold Badge
    edited Jul 9, 2020 7:59AM

    You are spot on.

    There does exists frequency histogram on column NAME(having 14 distinct value) and STAT(having 7 distinct values) of table STAGE. Also there already exists a frequency histogram on column PUSER and height balanced histogram on column NAME of table REF1. But still they were not helping the existing query.

    By creating a column group on both on ref1 (puser, name) and stage(name, stat)  with histogram for both i.e. 'FOR COLUMNS SIZE 254'. The merge Cartesian removed automatically from the path by optimizer and its doing a hash join now with TAB1 finishing the query in quick time.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,013 Blue Diamond
    edited Jul 9, 2020 12:05PM

    I've written up a blog note giving some explanation of the suggestions I made: https://jonathanlewis.wordpress.com/2020/07/09/execution-plans-6/

    Regards

    Jonathan Lewis

    User_OCZ1T