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

Mysql error message
help me find out what this error message means:

"Duplicate entry '0' for key 'PRIMARY'&q

Why use OOP?
Can someone explain to me why I should use OOP instead of procedure based code.

Im building a

Retrieving the 25 most recently added entries from all tables in a MySQL databas
Hello,

The code below works great. It creates a table that shows the 25 most recently added t

Having problemswith multithreading and prime numbers
I have an assignment when I'm suppose to do the following:

Write a multithreaded Java, Pt

Forms Authentication and Refresh at Login page
Hello, When I try to press the Login button in my webapplication at my login.aspx page nothing h

Trying to add functions to table
I have this list of characters and I'm trying to figure out what I have to do to put the results of

Where am I going wrong
Been trying to work this out for hours

I have two tables called 'Genres' and 'Films'. Genre

strtotime issue
Hey all,

I'm playing around with some code, and basically the idea is:

Person changes

Getting Subdomain Name With PHP?
I want to grab the subdomain name with PHP so I can generate database queries.

for example my

Mail functionality from localhost to server
Hi

I am facing problem of mail functionality.

When i tested mail functionality in my

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