Re: Silly SQL Question

Jacques,

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: Vladimir.Begun_at_oracle.com

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: ListGuru_at_fatcity.com (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).