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 item.id 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 item.id 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?