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

Doubles are giving me problems
Ok so, first of all i made a double = 0.05, but when running the debugger it shows up as 0.04999. I

Values disappear from my array :( HELP!
hey all, I have a lil mysql/php/apache script that queries a database
and pulls put 5 integers.

Libraries in C++
Hi all,

I have two libraries. one is based targeted on linux platform and uses another li

Cannot Display Array from Select Statement + Login question
Hi,
I'm new to PHP but so far so goog. I was assigend a project and I'm very close to completion.

This is driving me nuts!
This insert query looks to be alright, however I get this error:

QuoteYou have an error in yo

C++ Http request?
Code: string Response = "LOGIN_UNSUCCESSFUL"; System.Net.WebRequest request = Sy

socket makes browser hang...
I have a socket server, and I am having a problem at the moment...

A browser sends a http hea

Fetching array then reversing it
Hi, I have a simple problem, I'll try to explain it as best I can:
News entries in my database ar

need help in dynamic select menu in php
hi i have created a dynamic select menu using php. i have a problem in that which is when the user s

Saving data from a form into a file
Hey everybody,

Sorry, I am really new to PHP coding and such but a project kind of got thrust

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