What is the sql query in order to check the size of the particular sql query in hp vertica ?

Hi

What is the query to check the size of the particular sql query in  hp vertica i.e mentioned below ? Please advise.

 

Say like i wanted to know the table size of the following query :-

 

select * from test.example where batch_load_date between date(sysdate-30) and date(sysdate);

 

 

PLEASE HELP

Comments

  • 1 - Create the table (segmented vs replicated - will decide also the size of the ocupied space)

     

    2 - Optimize encodings

    SELECT distinct 
    'select DESIGNER_DESIGN_PROJECTION_ENCODINGS ('''|| projection_schema || '.'|| projection_basename||
    ''',''/tmp/'|| projection_schema|| '.'|| projection_basename|| '.sql'','|| '''$depoly true/false$'');'
    FROM
    projections
    WHERE
    anchor_table_name='$table name$'
    and projection_schema = '$schema name$';

    - just execute the optput (also : replace the $ valiables with your tbl name schema, true/false)

     

    3 - Get size of the table

    SELECT
    anchor_table_name,
    ROUND(SUM(used_bytes)/1024^2,3.0) AS 'Size in Mb'
    FROM
    projection_storage
    where anchor_table_name='$table_name$'
    GROUP BY
    1
    ORDER BY
    2 DESC

     

     

  • Hi Adrian

    My confusion here is where do i define the above mentioned query (sysdate-30)

     

    With Regards

     

    Ujjwal

  • i am not sure i understand you question, can you add more to it.

     

  • How can i see the size of the following query---->select * from test.example where batch_load_date between date(sysdate-30) and date(sysdate);

     

    again like i wanted to see one months size only which is sysdate -  30

     

    Assume like i wanted to see the size of this query ---->select * from test.example where batch_load_date between date(sysdate-30) and date(sysdate);

  •    I am at point blank :) hehehehe

     

     

       When you mean size , do you refer to

    1 - the size of the data contained in the  where batch_load_date between date(sysdate-30) and date(sysdate); predicate ?

    2 - how much memory you are required for this query to run ? 

     

    3 - Or maybe you have concerns around you predicate build ? 

    I would re write the query like this 

    select * from test.example where batch_load_date 
    between date_trunc('month', getdate()) and getdate()

    -this is becouse not all months have 30 days so you do not want to roll into another month , this will not give you the right results. 

     

    So what of those is ? 

     

     

      

     

  • Please find my answer below in line

     

    1 - the size of the data contained in the  where batch_load_date between date(sysdate-30) and date(sysdate); predicate ?

     

    Answer : Yes. Via etl one of the job runs only on monthly basis and monthly records once in a month. Therefore , I wanted to check size / space occupied by one moths of data

    2 - how much memory you are required for this query to run ? 

    Answer : There is no issue with memory, again i only need to know what is the query to trigger or to find out the space occupied by 1 months of date as mentioned on your point 1.

     

    3 - Or maybe you have concerns around you predicate build ? 

    I would re write the query like this 

    select * from test.example where batch_load_date 
    between date_trunc('month', getdate()) and getdate()

    -this is becouse not all months have 30 days so you do not want to roll into another month , this will not give you the right results. 

     

    Answer :  My concerns is related with point 1 above onlye. Need to know the query to find the space occupied/disk usage by one months of data

     

     

     

  • You can use select count(1) from... 

    to get the number of lines for this query if that is what you mean

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file