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

PHP - HTML
Could anyone give me some GOOD sample links for php - html email tutorial.

Thanks!

couldn't connect to your database
Hello I am new to php mysql

Actually i have read A tutorial on nettuts
"http://net.tu

How to give the privillege for triggers?
Hi,

How to give the privillage for triggers to particular user?

Please let me

Dynamic Data + Sql Server 2005 Enterprise?
Hi! I have just started to learn ASP.NET, and it looks like it is quite a lot to learn. Im not reall

need help in update query
hi
i have a dynamic form. i need help in how can i use update query when values from dynamic for

Help adding strtolower( to my in_array
strtolower i need to add it to this to work, iuno how.

Code: $moderators = array('Admin', 'ba

Alternate messaging
I have 4 strings in MySQL db1

$string1 : Hello
$string2 : Hi
$string3 : Great
$strin

SQl num_rows problem
when i try to count rows from an SQL select i get an warning

Code: [Select]$countviews = mysq

php multiple action on submit
I have a form which says:

<form enctype='multipart/form-data' method='post' action='pr

records between 2 dates
Hello all,

I have a date tracked table which contains effective_start_date and effective

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