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

John Anthony chirayithaj at gmail.com
Wed Oct 2 16:03:13 MST 2019


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/5ac3d832/attachment.html>


More information about the IQUG mailing list