create table as (select.... takes long time

Database
Enthusiast

create table as (select.... takes long time

hi all

I've got sql query which takes about 20 secs, when I just want to create table as this query(create table as (select ...) I don't have results even after 20minutes...

I've tried to create table first and then insert select... but with the same result - so the problem is with inserting I guess.

Can You explain what is the cause of this issue and how I can fix it?

thanks
4 REPLIES
jpg
Enthusiast

Re: create table as (select.... takes long time

First attempt:

Without seeing yor code and having some knowledge of your data it is difficult to establish the exact cause.

Are you specifying a primary index (PI) for your newly-created table? If not, the command will select the first named column in your select-list as your NUPI. This may cause the data to redistribute and (possibly) skew, which could be the cause of your problem.

In any event, if the new table PI differs from that of the the source table, some redistribution will occur.

HTH
JG
Enthusiast

Re: create table as (select.... takes long time

Hi,

If the target table is a SET table then insertion could take time as SET tables checks for duplicates and eliminates duplicates while insertion. This could take time.
Additional factors might be:
Volume of Data inserted.
Duplicate Rows fetched in the SQL query.

Please check the above in your case.

Thanks,
Toad
Enthusiast

Re: create table as (select.... takes long time

I agree. Mostly it should be due to not specifying primary index explicitly, By default it takes first column in select as PI. I too did the same mistake some time back, specifying primary index resolved the issue.

Please let us know how your issue is fixed finally.
Enthusiast

Re: create table as (select.... takes long time

Hi All

I didn't place query because the problem was with table creation :)
however you're right - omitting primary index during table creation couses long time processing.

I've just added primary index statement and now works perfect.

thanks a lot