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

Not adding to db
Hi, I can't figure out why it won't add the record to the database. It's just a simple form to get

Problem with php's rename function
So, i'm having a problem with the php rename function. Basically my script 'delete_user.php' attempt

Hits this week counter
I have a counter on my site that tracks hits, IP's, etc. into a mySQL database. It also includes the

Opening Multiple Files/Links in Order.
I want to open links in order/one-by-one and check each for a specific string.

Example:
I

Why Are These Functions Causing MASSIVE Memory Problems? Please Help!
Hi,

I have a script with some options.

I use regex to replace patterns in strings, but

SAP FICO learning materials
Hi all,
I am new to this world of SAP FICO. I have taken training on SAP FICO, but was wonderin

All possible combinations of Strings from table in PL/SQL
Hi,

I am trying to figure out how to build a list of all possible string combinations fro

Apple's revamped lineup arrives

I'm personally curious about the magic mouse.. a multi-touch concept sounds like it could be nea

Accessing Infotype data in dialog program
Hi All
In Dialog programs attributes I didn't see any logical database field. How can I access

PHP File Upload Problem
Hi, I have written a very simple file upload script but it fails for reasons I can't explain. Hope

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