I checked your example, I think there are some issues here:
1. Original queries provided below do use merge join.
2. We could have missing indexes which can exist on real system.
3. Timings below is not a criteria -- after gathering statistics and creation an index on val this both queries take about 1,3 seconds. So it means on your system you checked the *speed of sort operation* only -- because, most probably, merge was used. Even w/o index but with hash join it works much more faster -- 11.87 vs 1.25 (figures are not precise).
4. It'a all for nothing -- life is cruel and real-life examples are much more complex :)
If you do not mind I would not continue this discussion.
Thank you. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation.
Jacques Kilchoer wrote:
Mr. Begun, I didn't answer your comment about making the query work with the decode vs. using a user-written PL/SQL function because I wanted to compare timings for both. I created a table with 200,000 rows and compared the VB (Vladimir Begun) query to the JRK (my) query. The runtimes were identical (using SET TIMING ON in SQL*Plus). I agree that PL/SQL isn't necessary but I think it's easier to read, and the performance is the same. So there!
In any case Ms. Bellows had a correct solution, which I mistakenly thought was false, so all this was just an intellectual exercise.
With a 4-element list execute :list := '3,4,5,6,' SET TIMING ON results: VB query 11.87 JRK query: 11.67 (each query was run twice)
If you don't "hardcode" the number of elements in the list in the 'AND cnt = ' clause, the times were slightly longer but still the same: 14.71 vs. 14.32
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
