Skip to main content

Create new table as select * from hugetable

  • December 11, 2016
  • 1 reply
  • 8 views
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

 

 

 

 

    1 reply

    Adrian_Oprea_1
    Forum|alt.badge.img+1
    • Adrian_Oprea_1
    • Participating Frequently
    • December 11, 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 ! ?

     

     

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement