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

mysqli_fetch_assoc returns multiple arrays, how to return a single array
Hello, I am using mysqli_fetch_assoc which is returning multiple rows, so it looks like:

Code

Need a Timecode Class...
Not a Time Stamp, Time Code. Format is a bit different. HH:MM:SS:FF where FF is Frames. I dont re

Finding digits in variable containing text, and IDing them
$romanstock = "http://www.remoteprice.com/data.asp?storeid=123&itemcode=456&typ

Take info from one coloum and move to another
Hi all,

I have this:
Code: [Select]$array = "SELECT stock_id FROM stocks WHERE stock_

Not "Just Another" MMORPG - text based.
Hello,

I've been learning MySQL and PHP for the last 2-3 years.
Though, I've had long paus

PHP Upload issue
Hi guys,

I have stumble across an interesting issue with my script and is doing my head in.

Writting a script to arrange images........ need some help
Ok so here is the link

http://hmtotc.com/dev/projects/vrassociates/jeweler_dev/admin/index.ph

DELETE FROM not working deletes wrong row
Hello

I have the following code which i found but it doesnt work properly.. it comes up with

Need help with basic sql
Hi,

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

Can we convert non uni code system into unicode
Hi All,

Presently i am using non-unicode system and the sap version is 4.7.
Can i c

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