Skip to Content

While defining a columnar table, what is the purpose of column store type

Hi folks

I have two questions related to columnar table definition.

1. What is the purpose of column store type.

While defining a columnar table, what is the purpose of column store type (STRING ,CS_FIXEDSTRING,CS_INT etc) , when I define a table using the UI I see that the column is showing STRING but when I goto EXPORT SQL it does not show.  Is this mandatory or optional ?

2.VARCHAR Vs. CHAR - In the UI when I create the table I do not see the CHAR option , but I do see lot of discussion where people are using CHAR for defining the columnar table. Not sure why UI dropdown does not show it. I also read that we should avoid using VARCHAR as those columns are not compressed, is that true, I thought the column store gives compression for all the columns. Are there certain columns which cannot be compressed .

Please let me know where I can find more information about these two questions.

Poonam

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Mar 19, 2014 at 07:56 AM

    Hi Poonam

    the CS_-data types are the data types that are used internally in the column store. They can be supplied but it is not at all required or recommended to do so.

    SAP HANA will automatically use the correct CS_-data type for every SQL data type in your table definitions.

    To be very clear about this: don't use the CS_-data types directly. Just stick to the SQL data types.

    Concerning VARCHAR vs CHAR: fixed character data types are not supported anymore and don't show up anymore in the documentation.

    I have no idea why you believe that VARCHAR columns are not compressed but this is just a myth.

    create column table charcompr (fchar char(20), vchar varchar(20));

    insert into charcompr (

        select lpad ('x', to_int (rand()*20), 'y'), null from objects cross join objects);


    -- same data into both columns

    update charcompr set vchar = fchar;

    -- perform the delta merge and force a compression optimization

    merge delta of charcompr;

    update charcompr with parameters ('OPTIMIZE_COMPRESSION' ='FORCE');

    -- check the memory requirements

    select COLUMN_NAME, MEMORY_SIZE_IN_TOTAL, UNCOMPRESSED_SIZE, COUNT, DISTINCT_COUNT, COMPRESSION_TYPE

    from m_cs_columns where table_name ='CHARCOMPR'

    COLUMN_NAME    MEMORY_SIZE_IN_TOTAL    UNCOMPRESSED_SIZE   COUNT   DISTINCT_COUNT  COMPRESSION_TYPE

    FCHAR       3661                    70285738            6692569 20              RLE

    VCHAR       3661                    70285738            6692569 20              RLE

    We see: compression and memory requirements are the same for both fixed and variable character sizes.

    - Lars

              

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Poonam


      Yes I know this blog post.

      And this is a good example for that one should be really cautious with what to believe on the web.

      As long as you don't find version information and steps to reproduce the claimed facts you should be suspicious.

      Even if you do have these information, it always pays out to double check (just as we've seen in this thread) if the claimed facts are actually true.  That's regardless of who the author of the article/blog post is.

      - Lars