Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Silly SQL Question

Re: Silly SQL Question

From: Vladimir Begun <>
Date: Tue, 18 Nov 2003 10:04:39 -0800
Message-ID: <>


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: -- Author: Vladimir Begun INET: Fat City Network Services -- 858-538-5051 San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: (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).
Received on Tue Nov 18 2003 - 12:04:39 CST

Original text of this message