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

Count of reciepients in php mailer
How can i sent a mail to large no of reciepients say 10,00,00 Using PHP MAILER

str_replace help
Hey there,

I'm a PHP newb, I'm having troubles with the str_replace function. I want to clean

Multiple arrays inside data
Hi,

Simple question. I have a column called "array" in my database, and inserted in

using explode() to fill in checkboxes
Hi

I have a field stored in a table that contains regions in the UK separated by commas. Ther

calculator
I can't figure out why this code doesn't work. No error messages. Page loads.

Code: <

Multi Level Array Problem
hi all,

For example I have array like below:

$temp = array(array('north america', 'us'

Help on query replacing the date
Hi Pals,

i want to replace the current date in a column to some value say C or D or any n

Placing and array within an array then sorting it!
I have a page that runs two large mysql queries and saves the results into arrays, in php I then per

php and downloading
I want to use a php script to create two files. Then I want it to redirect (or whatever) to have tho

Solution Manager BPR
Does the SolMan-BPR-ARIS-Integration (http://www.ids-scheer.com/en/ARIS/ARIS_Platform/ARIS_Business_

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