Skip to Main Content

help trying to do a better CREATE TABLE statement with SUBQUERY

Christiano_SantosAug 19 2015 — edited Sep 3 2015

Hello everybody.

I am facing a problem with performance with a query like this:

create table a_table as select from tableX

where --(several conditions)

and not in ( --subquery1 -- this query alone executes in 15s

select ID

from tableX, user.tabela@tblink tablelnk

where -- other conditions +

and not exists ( select from tableZ where conditions

            and sorted_Id in (select interest_Id from tableY) ) );

the main query to create a_table, without the subquery1 takes about 2 minutes to perform.

the subquery1 takes only 15s. it requires a db link.

when I put the subquery as the condition to the create table, it´s taking more than one hour (actually, it loses the db link connection, by time out, which is set to 1h)

After some research in this forum, I´ve found that I could change this to:

WITH temp_table as ( subquery1)

create table a_table as select from tableX

where --(several conditions)

and not in temp_table .

I did not try this yet. gonna do tomorrow and compare.

Maybe it´s relevant to say, this is being executed by a shell script, in sqlplus command.

I would like to know if this is a good approach, or better saying, how could I improve this statement performance ?

The constraints to this task are that I still need to use some sort of statement that it´s possible to put in the shell script, I mean, by now, it is out of possibility to divide this in many tasks, or something like. I will have to replace the text of the script and see how it works.

I have tried to find some pl/sql ways, but I am not experienced on that, and my searches on google brought nothing (is this related to subquery unnesting?).

If possible to do with pl/sql, cursors, etc, could show example of code?

Post Details
Added on Aug 19 2015