Oracle

with clause

hi all, i have a big query that usually access the same table more than once. for example, i have something like this

Advertisement
select
from table1 where id = 124
union all
select
from table2 a, (select * from table1 where id 123) b
Advertisement
where a.id=b.id
union all
select
from table3 a, (select * from table1 where id = 345) b
and a.id=b.id
Advertisement

as you can see table1 is part of every union. the problem is that table1 is a huge table retrieving about 11 million records. accessing this table multiple time is hurting performance. i tried using With clause when i look a the explain plan the number of bytes is about 15 g

my question is how can i re-write the above query without accessing the table1 multiple times like i am in the code above.
if i use the with clause, the explain plan show big chuck of memory been use. thanks
Advertisement

0 answers

No answers posted yet. Be the first to answer.

Your answer

Login to answer

Trending today: see what people are reading