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

need help in mysql_num_rows()
please tell me what i am doing wrong in this query. it displays this error

Code: Warning: mys

Help with syntax
Hi all!

The $bridge variable is not getting passed while it is contained in the line below:

A Few Questions
Hi, I want to ask a few questions that I can't seem to find..

1) I'm creating a "shortcu

How would I convert this into code using an if statement?
ES equals exam score m equals $mean s equals $total. I already coded my mean and total so I'm not g

unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_
Can anyone view my code and tell me why im getting the error:
Parse error: syntax error, unexpect

Displaying values from a SQL count...
Hi,
Easy enough question i just forget the exact method ..
I have ...

Code: //count win

C++ API to Oracle dB
I need to perform a select command to the Oracle dB to obtain information from a table.
What libr

Transform value 0 in no and 1 in yes help needed
Hello,
I have this in the table: "value" type int 0 to represent false and 1 to represe

Notice Undefined index: reset/ Attempting to redirect, works, but get a notice
Full Header.php is:
Code: [Select]<?php
$reset= false;
$reset= strip_tags($_GET[&quo

Reading Most Recent CSV File in Directory
I thought I had wrapped this project up, but found out that the program I use to FTP a csv file to m

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