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

multiple recipients + dbuser mail recall
Hello,

I am building a php login script.

When the user registers the script will send

Retail terminologys not been altered after activing Retail System
Hi, All

After I active Retail System via TCode SWF5, the terms does not been changed, th

Uploading Filetypes and placing them in seperate folders.
Hello,

first post , and asking for help im afraid. Very new to PHP, was making good progress

Help me with some material on Open Text Overview.
Hi Experts,
Please help me out with some material on Open Text. I have been supporting AP Workf

Operating System
How in PHP or other language can I detect Operating system, ie Windows XP Home, Windows 7 Ultimate,

Record count of entire database
Does anyone know if there is a way to obtain the number of records in each table of the database usi

dynamic table with forms
I have a table that is populated with mysql data and in teh first column there is a raido button tha

how to populate a drop down box
Hi buddies!

PROCEDURE DOWNLOAD_REPORT_FORM (report_name varchar) is begin DOWNLO

How to generate a text file using php...?
Hi,
Can anyone give me code to generate a text file using php


Thanks in advance

PHP switch function help
Hi, I am trying to create a search function for a directory website. On the search function on html

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