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

onclick problem
I have <input type="text" name="message">
<a href=page2.

PHP webpage & array print issue
I have this code running, and it works perfectly … however, see my bottom bit about what I see

Calander layout
Hi i know this sounds like a simple question but i cant find the answer to it anywhere i have added

difference between datetimes
($row['totime']-$row['fromtime'])/60

this is giving me 0

example of totime and fromtim

SuperCali PHP Event Calendar
does anyone have any experience with the SuperCali PHP Event Calendar? I could use some assistance

need help in dynamic select menu in php
hi i have created a dynamic select menu using php. i have a problem in that which is when the user s

Pls help with PHP
Pls can anyone direct me on how to create a user account for a customer. For example i want know if

Can't shake the "Warning: include()" error
I've just uploaded my site to a new server and where I have PHP include tags in my HTML, the browser

mysql VARCHAR acting like INT
Hi, All.

I have a table that contains a varchar(10) column named weird_field. In this column

private constructor
Hello,

Can we create a constructor as private? If yes, what is the use of it? If no, why can'

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