Create new table as select * from hugetable

Comments

• Options

  Adrian_Oprea_1

  December 2016

   

   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 ! ?

   

   

  0
• Options

  Ty

  December 2016

  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

  0