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
Did you know?Explore Trending and Topic pages for more stories like this.
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

Include ("Absolute Path") of a file?
I'm not sure what I'm doing wrong. I am writing my php code in the same directory of the file that

Having a problem get the selected item from a drop down menu
I have been looking at this for days now.

I have a drop down menu that get it's values from a

weird problem
last time, i did post a topic concerning why my page sometime will load as blank page when using IE.

rookie looking for help coding a CSS form with PHP
I'm trying to figure out how to add PHP code to my xhtml form so that it is a working form embedded

Please help with code for SMTP authenticated PHP email form
Hello, I'm creating a PHP email form, and for this particular server, I have to use SMTP Authenticat

list files from folder, only one for each date
I have many files in a folder and wish to only list one of each date

these are the file names

Need help with basic sql
Hi,

I am using oracle 10g, and having some small doubts related to sql , here goes the si

Why is this query failing?
Why is this not working?
$query = "SELECT * FROM `users` WHERE `userid` = " . $USERID

PHP doesn't send my mail
Hi all, I found this and used it to send mail. Simple contact form with some required fields. The va

Problem executing bash script using shell_exec
Hi there,
I created a bash script file using following code to convert doc documents to pdf using

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