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

IS this code correct
The reason i ask is everything underneath it appears to be alink as well, tis blimmin annoying

Using Microsoft Exchange Server with PHP
Hi,

I wanted to know if its possible to fetch email attachments from the exchange server usin

redirect standard error and assert (how to?)
How do you redirect standard error and assert?

---

(Why? I am creating an web app and

Transferring session variables into MySQL
I am trying to transfer $_SESSION variables into MySQL. However there is an error in my MySQL statem

PHP Code / Script To check weather the given email exists in a domain
Hi,

I want to implement the following in my web page

in sign up we will ask to enter u

Calling a Procedure with IN & OUT Parameters
Hello,

I usually call my procedures using the following way

declare variable e

asp authentication problem
Hello all, I want to use the asp authentication (from asp.net configuration) in my web site. there

Simple AND question
Hello,

I've got following code:

Code: if(strpos($row[13],"http://")

Inserting multiple records from single form
I've found a number of threads that deal with this issue, but I'm new to php and coding language so

convert PHP array to Javascript array
I have a page that gets a request sent from AJAX, and I am trying to convert a PHP array to a javasc

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