[
https://issues.apache.org/jira/browse/TRAFODION-2760?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suresh Subbiah resolved TRAFODION-2760.
---------------------------------------
Resolution: Fixed
> hbase cache blocks is OFF for broad table with narrow index
> -----------------------------------------------------------
>
> Key: TRAFODION-2760
> URL: https://issues.apache.org/jira/browse/TRAFODION-2760
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 1.3-incubating
> Reporter: Suresh Subbiah
> Assignee: Suresh Subbiah
> Fix For: 2.3-incubating
>
>
> Enabling use of HBase blockcache is done based on estimates of row size of
> HBase table scanned. When index access is chosen by optimizer we were
> incorrectly using row size of base table to determine if caching should
> enabled.
> create schema wd_business ;
> CREATE TABLE TRAFODION.WD_BUSINESS.WD_USER
> (
> MDP_GUID CHAR(38 BYTES) CHARACTER SET UTF8 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
> , EMPLOYEEID NUMERIC(22, 0) DEFAULT NULL NOT SERIALIZED
> , EMPLOYEECODE VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , EMPLOYEENAME VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , GENDER VARCHAR(2 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , HIREDATE TIMESTAMP(0) DEFAULT NULL NOT SERIALIZED
> , BEGINWORKDATE TIMESTAMP(0) DEFAULT NULL NOT SERIALIZED
> , ORGID NUMERIC(22, 0) DEFAULT NULL NOT SERIALIZED
> , ORGCODE VARCHAR(256 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , ORGNAME VARCHAR(256 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , UNITID NUMERIC(22, 0) DEFAULT NULL NOT SERIALIZED
> , UNITCODE VARCHAR(256 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , UNITNAME VARCHAR(256 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , JOBID NUMERIC(22, 0) DEFAULT NULL NOT SERIALIZED
> , JOBCODE VARCHAR(256 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , JOBNAME VARCHAR(256 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , JOINUNITDATE TIMESTAMP(0) DEFAULT NULL NOT SERIALIZED
> , EMPLOYEESTATUS VARCHAR(2 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , ISTRANSFER VARCHAR(2 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , STATUS VARCHAR(1 CHAR) CHARACTER SET UTF8 COLLATE
> DEFAULT DEFAULT NULL NOT SERIALIZED
> , JOINJOBDATE TIMESTAMP(0) DEFAULT NULL NOT SERIALIZED
> , WEAVETYPE VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , EMPLOYEEORDER NUMERIC(22, 0) DEFAULT NULL NOT SERIALIZED
> , JOBLEVEL VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , ISVERTICALMANGEDTYPE VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , JOINWDFIRSTDATE TIMESTAMP(0) DEFAULT NULL NOT SERIALIZED
> , ADMINISTRATIVERANK VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , ISDIMREPORT VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , ISPROBATION VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , PINYIN VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , USERNAME VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , ISHOTELPROJECT VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , ISINTERN VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , ISCOMPREHENSIVE VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , LEADERSORTNO NUMERIC(22, 0) DEFAULT NULL NOT SERIALIZED
> , CADEMPLYEETYPE VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , REGIN VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , NCENABLED VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , OAENABLED VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , HOTELLEVEL VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , EMPLOYEESTATUSNAME VARCHAR(1024 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , ISVERTICALMANGEDNAME VARCHAR(1024 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , OFFICETEL VARCHAR(128 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , EMPLOYEEMOBILE VARCHAR(128 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , OFFICEEMAIL VARCHAR(128 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , POSTCODE VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , RESHUFFLEDATE TIMESTAMP(0) DEFAULT NULL NOT SERIALIZED
> , UNITFULLPATH VARCHAR(4000 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , ACTUALUNITID VARCHAR(4000 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , OFFICEADDRESS VARCHAR(1024 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , NCORGID VARCHAR(1024 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , NCDEPID VARCHAR(1024 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , NCPOSITIONID VARCHAR(1024 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , JOBCLASS VARCHAR(50 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , HEIGHTESTDEGREETYPE VARCHAR(2000 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , GRADUATEDATE VARCHAR(2000 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , GRADUATESCHOOL VARCHAR(2000 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , SPECIALTY VARCHAR(2000 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , BIRTHDAY TIMESTAMP(0) DEFAULT NULL NOT SERIALIZED
> , C_JISHUXULIE VARCHAR(1 CHAR) CHARACTER SET UTF8 COLLATE
> DEFAULT DEFAULT NULL NOT SERIALIZED
> , FIRSTNAME VARCHAR(128 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , MIDDLENAME VARCHAR(128 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , LASTNAME VARCHAR(128 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , SURNAME VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , GIVENNAME VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , RTXENABLED VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , ADENABLED VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , JOINWDDATE TIMESTAMP(0) DEFAULT NULL NOT SERIALIZED
> , YXSTARLEVEL VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , ISFOREIGN VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , OFFICEFAX VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , OFFICEPOSTCODE VARCHAR(256 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , IDCARD VARCHAR(128 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , PASSPORT VARCHAR(128 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , POLITYTYPE VARCHAR(128 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , HEIGHT NUMERIC(22, 0) DEFAULT NULL NOT SERIALIZED
> , WEIGHT NUMERIC(22, 0) DEFAULT NULL NOT SERIALIZED
> , HOMETEL VARCHAR(128 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , PRIVACYEMAIL VARCHAR(128 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , HOMEADDRESS VARCHAR(512 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , URGENCYLINKMAN VARCHAR(256 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , URGENCYTEL VARCHAR(256 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , CURRENTADDRESS VARCHAR(512 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , CURRENTTEL VARCHAR(256 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , NATIONALITYCODE VARCHAR(128 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , MARITALTYPE VARCHAR(128 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , NATION VARCHAR(128 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , EMPLOYEDPLACE VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , REUNIONPLACE VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , MARITALCODE VARCHAR(64 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , MARITALNAME VARCHAR(256 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , EMPLOYEDPLACECODE VARCHAR(128 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , NATIVEPLACE VARCHAR(128 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , IDTYPE VARCHAR(6 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , MDP_BATCHTIME TIMESTAMP(0) DEFAULT NULL NOT SERIALIZED
> , MDP_OPERATIONTYPE VARCHAR(50 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , MDP_RESULT VARCHAR(50 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , PRIMARY KEY (MDP_GUID ASC)
> )
> SALT USING 4 PARTITIONS
> ATTRIBUTES ALIGNED FORMAT
> HBASE_OPTIONS
> (
> DATA_BLOCK_ENCODING = 'FAST_DIFF',
> COMPRESSION = 'GZ',
> MEMSTORE_FLUSH_SIZE = '1073741824',
> IN_MEMORY = 'true'
> )
> ;
> CREATE INDEX IDX1_WD_USER ON TRAFODION.WD_BUSINESS.WD_USER
> (
> USERNAME ASC
> , EMPLOYEENAME ASC
> , EMPLOYEEMOBILE ASC
> , JOBNAME ASC
> , ORGNAME ASC
> )
> ATTRIBUTES ALIGNED FORMAT
> HBASE_OPTIONS
> (
> DATA_BLOCK_ENCODING = 'FAST_DIFF',
> COMPRESSION = 'GZ',
> MEMSTORE_FLUSH_SIZE = '1073741824',
> IN_MEMORY = 'true'
> )
> SALT LIKE TABLE
> ;
> CREATE INDEX IDX2_WD_USER ON TRAFODION.WD_BUSINESS.WD_USER
> (
> USERNAME ASC
> , EMPLOYEENAME ASC
> , EMPLOYEEID ASC
> )
> ATTRIBUTES ALIGNED FORMAT
> HBASE_OPTIONS
> (
> DATA_BLOCK_ENCODING = 'FAST_DIFF',
> COMPRESSION = 'GZ',
> MEMSTORE_FLUSH_SIZE = '1073741824',
> IN_MEMORY = 'true'
> )
> ;
> cqd HBASE_REGION_SERVER_MAX_HEAP_SIZE '32768' ;
> set schema wd_business ;
> prepare s1 from select * from wd_user <<+ cardinality 4e5>> ;
> prepare s2 from select username, employeename from wd_user <<+ cardinality
> 4e5>> ;
> explain s1 ;
> explain s2 ;
> with bug both s1 and s2 will have this line in full explain. (for scan
> operator)
> cache_blocks ........... OFF
> With fix, s1 will still have this line, but s2 will now have
> cache_blocks ........... ON
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
