with clause


Posted on 16th Feb 2014 07:03 pm by admin

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

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

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

No comments posted yet

Your Answer:

Login to answer
306 Like 47 Dislike
Previous forums Next forums
Other forums

Casting Decimals in Oracle
This code worked as a query in DB2, but I am not sure what the syntax is for casting decimals in Ora

PHP Script runs on CLI but not through web browser
I am running into an issue that I just can't seem to find the answer to. I have a Windows Server 200

pointer 102 question
I read a book
1
2
3
4
5
6
7
8
9
10
11
12
int main() {

Need Successful Return
Hello I am currently working a form here, and I have everything figured out except when I click the

HTML Email rec'd as Source Code
The code does create and send the email successfully. However, the message shows as html code and n

MySQL query problem
When I try to run the following piece of code, I get this error:
QuoteWarning: mysql_query(): sup

natcasesort works on one server but not on another
Hi

I have a problem that I was hoping that someone can help me with.

I'm trying to use

DirectoryIterator and Hacked Website
Hi Everyone.

My problem:
Some one has been sneaking in to my website hidden Iframes. I've

How to show a complete textarea ??
I enter Client Case Notes notes in a textarea field on a PHP form. The field is 5 rows deep and 70 c

help with Get_
ok some how this is not working i am not sure what i am doing wrong here but what this does is when

Sign up to write
Sign up now if you have flare of writing..
Login   |   Register
Follow Us
Indyaspeak @ Facebook Indyaspeak @ Twitter Indyaspeak @ Pinterest RSS



Play Free Quiz and Win Cash