Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.