You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Suresh Subbiah (JIRA)" <ji...@apache.org> on 2017/10/03 22:54:00 UTC
[jira] [Resolved] (TRAFODION-2760) hbase cache blocks is OFF for
broad table with narrow index
[ 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)