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

Random Number Generation And Probability
Hello, I am working on a function which has to insert data into a SQL table.

we have to suppl

IF Statement & Two Tables With Different Echoes
I'm retrieving two tables in a single query using UNION ALL, like so:

Code: $query = "SE

Error in SQL Syntax HELP!!!
I have this page:

Code: <?php

session_start();

//connect to server and s

Help with file_get_html
The following code gives the expected results:

<html>
<head&g

Remove values in array2 from array1
I have two arrays.

Array 1 is where the array key holds various different numbers. For exampl

Question handling xml data
Hello,

I have sucessfull followed this tutorial
http://www.phpfreaks.com/tutorial/handlin

Pass sql into pl/sql and create RMAN duplicate script.
Hi,

I'm new to pl/sql and I'm trying to write a script that will generate some RMAN comma

error with opendir
when i do this

if ($handle = opendir($dir)) {
while (false !== ($file = readdir($han

how to easy edit text, with box? Help.
Hi.
I have a little problem. I'm doing a webpage for my aunt and I would like to make it as easy

PHP Directory Listing Not working
Hey Guys,

I need help, I tried a ton of directory listing scripts and they all don't work. Al

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