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

returning data from an ssh2_exec()
here's what i got.


$conn = ssh2_connect($this->_host);
ssh2_auth_password($c

Form submissing with PHP and JQuery/Ajax
I have searched everywhere, but cannot find a solution for this... I have worked all day trying to g

replacements
I have a variable in my PHP script like

ASSFDDDDDDDDDDDDDASDDDDDDDDARYTRHKKHHHHHHHHHHH and p

Mail form doesn't send Russian/Cyrillic characters correctly?
When someone enters foreign characters (like Cyrillic text, and Japanese/Chinese probably gives the

How to read CSS message data
Hi,
I have a requirement here. When working as a Dev angel for multiple customers its really di

CURL XML Request [From C to PHP]
Hi,

I need some help converting this into something that will work with PHP.

Code: #

Form a inline view based on the results of the previous query?
Hi Experts,
Can we form a inline view on the results of a previous query (another inline view)?

Display search result
Hi!

I have a SQL database with information about albums and track (music).

This is wh

Casting Decimals in Oracle
This code worked as a query in DB2, but I am not sure what the syntax is for casting decimals in Ora

Select Rows as Columns..
is there a way to select COLUMN_NAME from user_tab_columns where table_name='TABLENAME';
and ha

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