Create new table as select * from hugetable

Ty

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

 

 

 

 

Adrian_Oprea_1

 

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

 

 

Ty

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