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

Retrieving Data from Two Different Databases
Hello All,

Ran into another problem and would appreciate some help! I have two datab

C - Reading a file into a byte array
Hi,

I'm trying to read a file into a byte array in C. I have to use C as this is for a loadru

Storing user data help?
Hey Guys,

I'm not use if this question is to broad but I can always give you more informatio

retrieving more than one max key from an array?
so i have an array of 20 numerical values (0-100) that i need to order from highest to lowest and th

How to change Time Zone
HI
I want to change the time zone of the server to another country.How can do that?

Thanks

Libraries in C++
Hi all,

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

check how many commas the variable containing the query has between [i]select[/i
Hi...

iv made an sql table that contains id number - name of sql query and an sql query.
T

Passing PHP variables from one page to another
Hello-

I'm having trouble figuring out how to transport a variable from one php file to anoth

Deleted Delivery but material wont return
We deleted a delivery but the material its still pending.

We made a reverse goods movemen

PHP, jAVASCRIPT setting input values, why isn't it working?
Hi:

I am calling this javascript:
<script language="javascript">

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