[IQUG] HG index increased in size after varchar columns increased in length

Mark Mumy markdmumy at gmail.com
Wed Oct 2 16:24:32 MST 2019


No.  Not that I’m aware of.   That would make them variable in size which can be bad for the index structures from a performance perspective.  Imaging having to widen the structure when the tokens change size.  Could be quite bad.  

Mark
 
========================

Sent from my mobile device

> On Oct 2, 2019, at 19:03, John Anthony <chirayithaj at gmail.com> wrote:
> 
> 
> Mark,
> 
> Are HGs not based on N-bit tokens ?
> 
> Is why there is great space saving as you align nbits to cardinality!
> 
> -JA
> 
>> On Wed, Oct 2, 2019 at 5:56 PM Mark Mumy <markdmumy at gmail.com> wrote:
>> This makes total sense.  An HG index is fixed width at the widest possible allowed by each column.  A varchar(250) will take 2x the storage of a varchar(125). 
>> 
>> It is only in the FP and N-bit where we are variable with those types.  
>> 
>> Mark
>>  
>> ========================
>> 
>> Sent from my mobile device
>> 
>>>> On Oct 1, 2019, at 20:15, Karl Cashin <karlcashin at hotmail.com> wrote:
>>>> 
>>> 
>> 
>>> Hello IQUG,
>>> 
>>> we recently increased the size of some varchar(128) columns in a table by creating a new table with varchar(250) columns, inserting the data from the old table to the new table and then renaming the new table in place of the old table. The data was identical in the old and new table, the only change was the increase in length of the varchar columns. What we observed after this process was an HG index on these changed columns increased in size from 60 GB to 90 GB. Is this expected behaviour for an IQ database?
>>> 
>>> Below is a cut down example of the above scenario where a 1 GB HG index increased to 1.7 GB when copying data from a varchar(128) columns to varchar(250) columns.
>>> 
>>> Any advice would be greatly appreciated. 
>>> 
>>> Regards, Karl 
>>> 
>>> Original varchar(128) table definition:
>>> 
>>> CREATE TABLE "DBA"."VARCHAR128" (
>>> "cola" varchar(128) NOT NULL
>>> ,"colb" varchar(128) NOT NULL
>>> ) IN "DBS_USR_Data_001"
>>> go
>>> 
>>> CREATE HG INDEX "IDX_VARCHAR128_HG" ON "DBA"."VARCHAR128" (
>>> "cola","colb"
>>>  ) IN "DBS_USR_Data_001"
>>> go
>>> 
>>> Creating the new varchar(250) table:
>>> 
>>> READ "test.sql"
>>> --
>>> -- Start of test.sql
>>> --
>>> describe "DBA"."VARCHAR128"
>>> Column Type         Nullable Primary Key
>>> ----------------------------------------
>>> cola   varchar(128)        0           0
>>> colb   varchar(128)        0           0
>>> 
>>> (2 rows)
>>> DROP TABLE "DBA"."VARCHAR250";
>>> select count(*) from "DBA"."VARCHAR128"
>>> 
>>>  count()
>>> --------
>>> 17534938
>>> 
>>> (1 rows)
>>> 
>>> select avg(length(cola)),avg(length(colb)) from "DBA"."VARCHAR128";
>>> 
>>> avg(length(VARCHAR128.cola)) avg(length(VARCHAR128.colb))
>>> ---------------------------------------------------------
>>>           5.2850253020569562           9.6375112361389586
>>> 
>>> (1 rows)
>>> 
>>> sp_iqindexsize 'DBA.VARCHAR128.IDX_VARCHAR128_HG';
>>> Username Indexname                        Type Info       KBytes  Pages CompressedPages
>>> ---------------------------------------------------------------------------------------
>>> DBA      DBA.VARCHAR128.IDX_VARCHAR128_HG HG   Total      1080888 39860 39854
>>> DBA      DBA.VARCHAR128.IDX_VARCHAR128_HG HG     bt       903888  37622 37618
>>> DBA      DBA.VARCHAR128.IDX_VARCHAR128_HG HG     garray   176864  2236  2235
>>> DBA      DBA.VARCHAR128.IDX_VARCHAR128_HG HG     bm       136     2     1
>>> DBA      DBA.VARCHAR128.IDX_VARCHAR128_HG HG     barray   0       0     0
>>> DBA      DBA.VARCHAR128.IDX_VARCHAR128_HG HG     dpstore  0       0     0
>>> DBA      DBA.VARCHAR128.IDX_VARCHAR128_HG HG     largelob 0       0     0
>>> DBA      DBA.VARCHAR128.IDX_VARCHAR128_HG HG     txtPst   0       0     0
>>> 
>>> (8 rows)
>>> 
>>> CREATE TABLE "DBA"."VARCHAR250" (
>>> "cola" varchar(250) NOT NULL
>>> ,"colb" varchar(250) NOT NULL
>>> ) IN "DBS_USR_Data_001"
>>> ;
>>> CREATE HG INDEX "IDX_VARCHAR250_HG" ON "DBA"."VARCHAR250" (
>>> "cola","colb"
>>>  ) IN "DBS_USR_Data_001"
>>> ;
>>> INSERT INTO "DBA"."VARCHAR250" SELECT * FROM  "DBA"."VARCHAR128";
>>> INTO "DBA"."VARCHAR250" SELECT * FROM  "DBA"."VARCHAR128"
>>> 17,534,938 row(s) inserted
>>> 
>>> commit;
>>> 
>>> select avg(length(cola)),avg(length(colb)) from "DBA"."VARCHAR250"
>>> 
>>> avg(length(VARCHAR250.cola)) avg(length(VARCHAR250.colb))
>>> ---------------------------------------------------------
>>>           5.2850253020569562           9.6375112361389586
>>> 
>>> (1 rows)
>>> 
>>> -- Execution time: 21.93 seconds
>>> --
>>> -- End of test.sql
>>> --
>>> 
>>> Checking the size of the varchar(250) HG index:
>>> 
>>> sp_iqindexsize 'DBA.VARCHAR250.IDX_VARCHAR250_HG';
>>> Username Indexname                        Type Info       KBytes  Pages CompressedPages
>>> ---------------------------------------------------------------------------------------
>>> DBA      DBA.VARCHAR250.IDX_VARCHAR250_HG HG   Total      1857144 72170 72160
>>> DBA      DBA.VARCHAR250.IDX_VARCHAR250_HG HG     bt       1679264 69934 69926
>>> DBA      DBA.VARCHAR250.IDX_VARCHAR250_HG HG     garray   177744  2234  2233
>>> DBA      DBA.VARCHAR250.IDX_VARCHAR250_HG HG     bm       136     2     1
>>> DBA      DBA.VARCHAR250.IDX_VARCHAR250_HG HG     barray   0       0     0
>>> DBA      DBA.VARCHAR250.IDX_VARCHAR250_HG HG     dpstore  0       0     0
>>> DBA      DBA.VARCHAR250.IDX_VARCHAR250_HG HG     largelob 0       0     0
>>> DBA      DBA.VARCHAR250.IDX_VARCHAR250_HG HG     txtPst   0       0     0
>>> 
>>> (8 rows)
>>> 
>>> -- Execution time: 0.057 seconds
>>> 
>>> 
>>> 
>>> 
>>> _______________________________________________
>>> IQUG mailing list
>>> IQUG at iqug.org
>>> http://iqug.org/mailman/listinfo/iqug
>> _______________________________________________
>> IQUG mailing list
>> IQUG at iqug.org
>> http://iqug.org/mailman/listinfo/iqug
> -- 
> Sent from mobile device
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20191002/0748a76f/attachment.html>


More information about the IQUG mailing list