Create new table as select * from hugetable

Dear all,

 

Some users of mine use the SQL below to create SAS tables inside Vertica :

 

create table sasview.Agregats_GR as

select a.*

from hugetable a

where...

 

How can I limit the overuse of the RAM and DISK with this sort of statement ?

For your information, the hugetable contains 500 millions of records.

i was told that with Oracle, you can force the commit after n records.

Do we have a similar option with Vertica or other tips to solve this issue ?

Thanks for your help

Regards

Ty

 

 

 

 

Comments

  •  

     To be able to limit user RAM you need to create Specific Resource Pools for them in the limits you want them.

     

     For disk usage is kind of hard becouse there is no quota in Vertica for now.

     

    But you can help them improve their queryes by: 

     

    If your source table is partitioned you can use partion pruning to make you statement faster.

     

     Also you can use /*+direct*/ hint on you  select statement 

    eg:

    select /*+direct*/ col.* from tbl

    - this will avoid going to WOS.

     

    500 mill should be quite fast. 

     

      Also if you wana replicate the table try to use COPY_PARTITIONS_TO_TABLE.This lightweight partition copy increases performance by initially sharing the same storage between two tables.

     

      Is your where predicate part of the order of segmentation ? - in most cases the create as select is not restricted by the write on the new DB containers but by the select performance.

     

    Can you put the entire sql, to have a look ! ?

     

     

  • Dear;

    This a real query, I know it's awesome,  may be it has another way of writing this SQL ?

    I'm afraid that this SQL treats line by line ?

     

     

    create table lk1asasview.Agregats_GR as
    select a.*,
    case a.SEGMENT_NOTATION
    when '01' then
                        case when a.EPARGNE_TOTALE is null then
                                (

                                 select b.N_valeur
                                 from lk1asasview.formats as b
                                 where b.fmtname='EPARGNE_TOTALE_M2A_N'  and b.mini is null

                                 )
                                 else

                                 (

                                 select b.N_valeur
                                 from lk1asasview.formats as b
                                 where b.fmtname='EPARGNE_TOTALE_M2A_N'
                                and a.EPARGNE_TOTALE >= b.mini and a.EPARGNE_TOTALE < b.maxi

                                 )
                                end
    when '02' then
                       case when a.EPARGNE_LIQUIDE is null then
                      (

                       select b.N_valeur
                      from lk1asasview.formats as b
                       where b.fmtname='EPARGNE_LIQUIDE_M2B_N'  and b.mini is null

                      )
                     else  case when a.EPARGNE_LIQUIDE = 0 then
                     (

                       select b.N_valeur
                      from lk1asasview.formats as b
                      where b.fmtname='EPARGNE_LIQUIDE_M2B_N'
                     and b.maxi = 0

                     )
                    else
                     (

                       select b.N_valeur
                      from lk1asasview.formats as b
                      where b.fmtname='EPARGNE_LIQUIDE_M2B_N'
                      and a.EPARGNE_LIQUIDE >= b.mini and a.EPARGNE_LIQUIDE < b.maxi

                      )
                  end /* case */
    end

    end as note1,
    from lk1asasview.Agregats_GR_2 as a
    order by a.ID_CR, a.ID_GROUPE_RISQUE, a.DATE_ARRETE ;

     

    Thanks for your help

     

    Ty

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.