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

the problem with str_replace
$str="hahahahahahahahahahahahahaha";
$nn=1;
$str=str_replace('ha','MyGod',$str,$nn);

path to include folder
Hi everyone,

When I'm testing locally, I have to add the name of the site folder as part of t

Creating a custom API
I'm creating a site, and I need to create a basic API. Unfortunately I have no idea where to start.

Renaming a file that a user uploads to site?
My site allows for registered users to upload images to the site under their own gallery. Currently

Help Ordering Arrays
Hi, I have 3 arrays as shown below.

Code: $users = $this->get_latest_users();
$flir

How to form a xml form table with a single sql statement..?
Hi everyone,
I don't know if this is going to be a duplicate thread but i couldn't

how do i display data on a page from mysql
1 - do i use this code at the top of each of my page i wish to only alow access if there as been a s

SESSION CHECK WORKS "MOST" of the time... whats going on?
i have a sign in check thats included in every page that is a members only page which is basically a

Small problem with image resize script
Hi!

I am a little bit new to php and I have an issue installing an image resize script I down

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

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