hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Selina Zhang (JIRA)" <j...@apache.org>
Subject [jira] [Created] (HIVE-9447) Metastore: inefficient Oracle query for removing unused column descriptors when add/drop table/partition
Date Fri, 23 Jan 2015 00:39:34 GMT
Selina Zhang created HIVE-9447:
----------------------------------

             Summary: Metastore: inefficient Oracle query for removing unused column descriptors
when add/drop table/partition
                 Key: HIVE-9447
                 URL: https://issues.apache.org/jira/browse/HIVE-9447
             Project: Hive
          Issue Type: Improvement
          Components: Metastore
            Reporter: Selina Zhang
            Assignee: Selina Zhang


Metastore needs removing unused column descriptors when drop/add partitions or tables. For
query the unused column descriptor, the current implementation utilizes datanuleus' range
function, which basically equals LIMIT syntax. However, Oracle does not support LIMIT, the
query is converted as  
{quote}
SQL> SELECT * FROM (SELECT subq.*,ROWNUM rn FROM (SELECT
'org.apache.hadoop.hive.metastore.model.MStorageDescriptor' AS
NUCLEUS_TYPE,A0.INPUT_FORMAT,A0.IS_COMPRESSED,A0.IS_STOREDASSUBDIRECTORIES,A0.LOCATION,
A0.NUM_BUCKETS,A0.OUTPUT_FORMAT,A0.SD_ID FROM drhcat.SDS A0 
WHERE A0.CD_ID = ? ) subq ) WHERE  rn <= 1;
{quote}
Given that CD_ID is not very selective, this query may have to access large amount of rows
(depends how many partitions the table has, millions of rows in our case). Metastore may become
unresponsive because of this. 

Since Metastore only needs to know if the specific CD_ID is referenced in SDS table and does
not need access the whole row. We can use 
{quote}
select count(1) from SDS where SDS.CD_ID=?
{quote}
CD_ID is index column, the above query will do range scan for index, which is faster. 

For other DBs support LIMIT syntax such as MySQL, this problem does not exist. However, the
new query does not hurt.  







--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message