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

I need to increse 6hours more, and i don't know how???
I need to increse 6hours more, and i don't know how???
<tr>
<t

ALV sum various columns problem
Hi all,
I'm doing a report on ABAP for a timesheet of the current month. The output i want is s

foreach help.
Hello all,

First time I'm attempting to use a foreach statement and was just wondering if my

PHP Form to Variable.
Hi! I have written a HTML-form code in DreamWeaver CS3 (in a PHP doc) and I really need it to work s

MS Access data into a html table
Hi everyone, I am quite a novice at php but I have created some helpful scripts that fetch data
f

Greek characters in php
Hi,

I'm making a script and I m using for first time greek characters.
I started to write

Using mysql_real_escape_string for displayed content
On my website users can input data into a textarea and it will store it in the mysql database. But,

Page Reload
I am having issues getting a page to reload after I submit data to a database. This form should inp

Downloading file (Headers)
I'm trying to make users download a file, but they must wait 60 seconds before it begins.

But

Php - mysql select?
hi, i have this code:

Code: <?php
session_start();
// dBase file
include &quo

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