Hive
  1. Hive
  2. HIVE-7784

Created the needed indexes on Hive.PART_COL_STATS for CBO

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 0.14.0
    • Fix Version/s: 0.14.0
    • Component/s: CBO
    • Labels:
      None

      Description

      With CBO we need the correct set of indexes to provide an efficient Read/Write access.
      These indexes improve performance of Explain plan and Analyzed table by 60% and 300%.

      MySQL 
       CREATE INDEX PART_COL_STATS_N50 ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME) USING BTREE;
      
      MsSQL
      CREATE INDEX PART_COL_STATS_N50 ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME);
      
      Oracle 
      CREATE INDEX PART_COL_STATS_N50 ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME);
      
      Postgres
      CREATE INDEX "PART_COL_STATS_N50" ON "PART_COL_STATS" USING btree ("DB_NAME","TABLE_NAME","COLUMN_NAME");
      
      1. HIVE-7784.2.patch
        8 kB
        Mostafa Mokhtar
      2. HIVE-7784.1.patch
        6 kB
        Mostafa Mokhtar

        Activity

        Mostafa Mokhtar created issue -
        Mostafa Mokhtar made changes -
        Field Original Value New Value
        Description With CBO we need the correct set of indexes to provide an efficient Read/Write access.

        With CBO we need the correct set of indexes to provide an efficient Read/Write access.
        These indexes improve performance of Explain plan and Analyzed table by 60% and 300%.

        {code}
        MySQL
         CREATE INDEX PART_COL_STATS_N50 ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME) USING BTREE;

        MsSQL
        CREATE INDEX PART_COL_STATS_N50 ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME);

        Oracle
        CREATE INDEX PART_COL_STATS_N50 ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME);

        Postgres
        CREATE INDEX "PART_COL_STATS_N50" ON "PART_COL_STATS" USING btree ("DB_NAME","TABLE_NAME","COLUMN_NAME");
        {code}
        Mostafa Mokhtar made changes -
        Attachment HIVE-7784.1.patch [ 12662807 ]
        Mostafa Mokhtar made changes -
        Attachment HIVE-7784.2.patch [ 12662819 ]
        Show
        Mostafa Mokhtar added a comment - Ashutosh Chauhan Code review link https://reviews.apache.org/r/24861/diff/#
        Ashutosh Chauhan made changes -
        Status Open [ 1 ] Patch Available [ 10002 ]
        Hide
        Ashutosh Chauhan added a comment -

        +1

        Show
        Ashutosh Chauhan added a comment - +1
        Hide
        Hive QA added a comment -

        Overall: -1 at least one tests failed

        Here are the results of testing the latest attachment:
        https://issues.apache.org/jira/secure/attachment/12662819/HIVE-7784.2.patch

        ERROR: -1 due to 2 failed/errored test(s), 5819 tests executed
        Failed tests:

        org.apache.hive.hcatalog.pig.TestHCatLoader.testReadDataPrimitiveTypes
        org.apache.hive.hcatalog.pig.TestOrcHCatLoader.testReadDataPrimitiveTypes
        

        Test results: http://ec2-174-129-184-35.compute-1.amazonaws.com/jenkins/job/PreCommit-HIVE-TRUNK-Build/406/testReport
        Console output: http://ec2-174-129-184-35.compute-1.amazonaws.com/jenkins/job/PreCommit-HIVE-TRUNK-Build/406/console
        Test logs: http://ec2-174-129-184-35.compute-1.amazonaws.com/logs/PreCommit-HIVE-TRUNK-Build-406/

        Messages:

        Executing org.apache.hive.ptest.execution.PrepPhase
        Executing org.apache.hive.ptest.execution.ExecutionPhase
        Executing org.apache.hive.ptest.execution.ReportingPhase
        Tests exited with: TestsFailedException: 2 tests failed
        

        This message is automatically generated.

        ATTACHMENT ID: 12662819

        Show
        Hive QA added a comment - Overall : -1 at least one tests failed Here are the results of testing the latest attachment: https://issues.apache.org/jira/secure/attachment/12662819/HIVE-7784.2.patch ERROR: -1 due to 2 failed/errored test(s), 5819 tests executed Failed tests: org.apache.hive.hcatalog.pig.TestHCatLoader.testReadDataPrimitiveTypes org.apache.hive.hcatalog.pig.TestOrcHCatLoader.testReadDataPrimitiveTypes Test results: http://ec2-174-129-184-35.compute-1.amazonaws.com/jenkins/job/PreCommit-HIVE-TRUNK-Build/406/testReport Console output: http://ec2-174-129-184-35.compute-1.amazonaws.com/jenkins/job/PreCommit-HIVE-TRUNK-Build/406/console Test logs: http://ec2-174-129-184-35.compute-1.amazonaws.com/logs/PreCommit-HIVE-TRUNK-Build-406/ Messages: Executing org.apache.hive.ptest.execution.PrepPhase Executing org.apache.hive.ptest.execution.ExecutionPhase Executing org.apache.hive.ptest.execution.ReportingPhase Tests exited with: TestsFailedException: 2 tests failed This message is automatically generated. ATTACHMENT ID: 12662819
        Hide
        Ashutosh Chauhan added a comment -

        Committed to trunk. Thanks, Mostafa!

        Show
        Ashutosh Chauhan added a comment - Committed to trunk. Thanks, Mostafa!
        Ashutosh Chauhan made changes -
        Status Patch Available [ 10002 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        Mostafa Mokhtar made changes -
        Link This issue is a clone of HIVE-7982 [ HIVE-7982 ]
        Mostafa Mokhtar made changes -
        Link This issue is a clone of HIVE-7982 [ HIVE-7982 ]
        Hide
        Thejas M Nair added a comment -

        This has been fixed in 0.14 release. Please open new jira if you see any issues.

        Show
        Thejas M Nair added a comment - This has been fixed in 0.14 release. Please open new jira if you see any issues.
        Thejas M Nair made changes -
        Status Resolved [ 5 ] Closed [ 6 ]

          People

          • Assignee:
            Mostafa Mokhtar
            Reporter:
            Mostafa Mokhtar
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development