Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Rewrite the SQL using With Clause (Subquery factoring)

Posted on 2009-05-18
15
Medium Priority
?
674 Views
Last Modified: 2013-12-19
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
Comment
Question by:sventhan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
15 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24417165
can you explain the WHY you want to do that?
0
 
LVL 18

Author Comment

by:sventhan
ID: 24417244
Trying to fix some performance issue.
Try to eliminate the SubQuries.
0
 
LVL 18

Author Comment

by:sventhan
ID: 24417433
Please delete the question. I found a solution.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 74

Expert Comment

by:sdstuber
ID: 24418054
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
 
LVL 18

Author Comment

by:sventhan
ID: 24418097
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 24418109
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
 
LVL 18

Author Comment

by:sventhan
ID: 24418110
the difference between exists and not exists is this row
"  AND t1.avg_price = 0.00"
0
 
LVL 18

Author Comment

by:sventhan
ID: 24418117
Still I'm stuggling with my version of code.
Can you give me your own version/advice if you have time?
0
 
LVL 18

Author Comment

by:sventhan
ID: 24418153
here is my version of code...
 

pt-sql-myver.txt
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 24418198
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
 
LVL 18

Author Comment

by:sventhan
ID: 24418228
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 24418321
you already figured it out.  :)  just email me if you'd like a copy.
0
 
LVL 18

Author Comment

by:sventhan
ID: 24418385
Thanks SD.
This the final version of my code.
I'll post the explain plan details later.
 

pt-sql-myver.txt
0
 
LVL 18

Author Closing Comment

by:sventhan
ID: 31582807
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 24432923
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question