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

2 things: enter doesn't work in IE & empty form
i have this search form that works well except for 2 things.

1) on IE when i hit enter instea

Losing 'page' data
I have this code that allows me to update my database. But after updating, I lose the $_GET['page']

str_replace help
Hey there,

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

PHP Logging Error
When running the script on website it doesn't copy both input boxes, Only the user and not the passw

query help
Hi experts.

i have a table rep2 like this
PROD_COD ACCT_NO DUE_DAYS BALANCE

tell csv import script to ignore blank rows?
Hi i have the below script but i get an error if a row is blank ie a return in the csv file at the e

Random date selection
i want to select random date with time(hour+minutes+sec) where date is specified(10/22/2009) and tim

PHP and Javascript
Hi Guys,

Im trying to load the contents of an html file into a text area for editing, this is

Calculus Help (and by help I mean homework)
Hrmmm, I hate posting about math homework, partly because it's homework, and partly because I hate w

php/xmlrpc class issue
I am working on xml-rpc in php to start with.

I have a class that only has variables defined

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