• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 681
  • Last Modified:

Rewrite the SQL using With Clause (Subquery factoring)

Experts -

Please help me to rewrite this query using oracle With Clause.

Thanks.
SELECT col1
FROM table1 i
WHERE i.i_id IN
  (SELECT im.i_id
  FROM table2 im
  WHERE im.index_code = 'XXX'
  AND im.start_date  <= DATE '2007-03-01'
  AND (im.end_date   IS NULL
  OR im.end_date     >= DATE '2007-03-02')
  )
AND i.start_date <= DATE '2007-03-01'
AND (i.end_date  IS NULL
OR i.end_date    >= DATE '2007-03-02')
AND NOT EXISTS
  (SELECT t1.i_id
  FROM table3 t1
  WHERE t1.i_id = i.i_id
  AND (t1.timeseries_date + t1.timeseries_time) BETWEEN TIMESTAMP '2007-03-01 10:00:00' AND TIMESTAMP '2007-03-02 10:00:00'
  AND t1.avg_pcol1e = 0.00
  )
AND EXISTS
  (SELECT t2.i_id
  FROM table3 t2
  WHERE t2.i_id = i.i_id
  AND (t2.timeseries_date + t2.timeseries_time) BETWEEN TIMESTAMP '2007-03-01 10:00:00' AND TIMESTAMP '2007-03-02 10:00:00'
  )
AND i.col1 NOT IN ('AAA', 'BBB')
ORDER BY col1

Open in new window

0
sventhan
Asked:
sventhan
  • 9
  • 5
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you explain the WHY you want to do that?
0
 
sventhanAuthor Commented:
Trying to fix some performance issue.
Try to eliminate the SubQuries.
0
 
sventhanAuthor Commented:
Please delete the question. I found a solution.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
I hope what you found is that WITH won't help you here.
And that your query needs fixed logically before fixed performance wise.


The  query "as is" looks for i_id  from table1  that both DOES and DOES NOT exist in the same time range of table3.

Obviously, nothing satisfies that so there must be a logic problem
0
 
sventhanAuthor Commented:
thanks SD for your reply.
I've attached the real query and still do you think there is a logic issue.
I'm looking for a way to rewrite the query.
 

pt-sqls.txt
0
 
sdstuberCommented:
oops, I didn't notice the avgerage price clause in the original.   so your exists and your not exists are in fact different.
my mistake.  sorry.

you can still delete the question, but if you found an interesting solution, please post it and then accept your own answer to close the question for a PAQ
0
 
sventhanAuthor Commented:
the difference between exists and not exists is this row
"  AND t1.avg_price = 0.00"
0
 
sventhanAuthor Commented:
Still I'm stuggling with my version of code.
Can you give me your own version/advice if you have time?
0
 
sventhanAuthor Commented:
here is my version of code...
 

pt-sql-myver.txt
0
 
sdstuberCommented:
I wouldn't bother using WITH as a performance tool since your factored-query must be correlated to the instruments table and you have two different conditions to apply to that correlation.
It's not really going to do anything for you.

In fact, if you do an explain plan you should be able to see your refactoring show up as view steps in your plan.

Please post your plan for your original and your modified versions
along with the ddl for the tables and the indexes.
0
 
sventhanAuthor Commented:
Sure SD.
I'll do that as soon as I've access to the system at work.
another question, where can I find your presentation for collaborate 09? You did not post the link at your memeber profile.
Thanks so much for the help.
0
 
sdstuberCommented:
you already figured it out.  :)  just email me if you'd like a copy.
0
 
sventhanAuthor Commented:
Thanks SD.
This the final version of my code.
I'll post the explain plan details later.
 

pt-sql-myver.txt
0
 
sventhanAuthor Commented:
Thanks for the suggestions. I do not see any big performance improvements as you stated. I liked your powerpoint presentation that was super. What I was asking is your URL (blog) or something where you keep all your oracle articles on web. It would be really useful to lots of people.
0
 
sdstuberCommented:
glad I could help, and glad you enjoyed the article and ppt.  Thanks!

I don't currently maintain a blog.  Since I started on EE, I average 15-20 posts per day, so that's all the blogging I can handle.  :)  I am ramping down signficantly on EE though.  So I will be getting more time for other projects.

I have been asked many times to start a blog and when I do, I'll update my EE profile to point to it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now