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

using variables in another page
I have a test database set up on localhost. I have a form that I can type a name into, hit the butto

php calculate
this code echoes correctly the sum but the inserted result is 0??
Code: <?php
$TotalNum

What is SAP Avatar ?
Hi All,

This G.Satish , my boss asked me to explore on SAP Avatar. I searced in internet

PHP error. Need help urgently
Hi,

I am programming a php site and have a problem that i just cant find out how to fix. When

AJAX form submit
I recently built a form that submits data to my database with an ajax function that is called throug

mysql UNION | warning mysql_fetch_array !!
Code: <?php
$i = 0;
$query1="SELECT * FROM `products` WHERE `div

Most basic form question ever?
Hello,

I want to use this snippet to make sure the fields in a form are ok before processing

Executing an exe
Hi. I have some bizarre problem with exec (other any other method) to launch exe's. I am making a ga

mySQL and PHP search
Hello,
I am trying to code a project and ran into a brick wall with one of my pages. I am pretty

How to get variable value on next page
Hello friends
i am working on payentry page ..there i have this code.........

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