Why adding "dual" in the "from" clause speeds this query up?

From: Vitalis <vitalopedrotti_at_yahoo.fr>
Date: 20 Feb 2004 04:56:18 -0800
Message-ID: <4e6dd3ba.0402200456.56c741bb@posting.google.com>

Hi,

A friend of mine told me that one of his complicated queries was wondrously speed up when he added "dual" to the "from" clause. The query looks like:

SELECT v.col1, v.col2, NVL( v.col3, ' '), ' ', TO_CHAR( r.col1,:CST_MY_FORMAT), v.col4, NVL( v.col5, ' '), NVL( v.col6, ' '), ' ', r.col2, r.col3, TO_CHAR(c.col1,:CST_MY_FORMAT) FROM table_r r, table_v v, table_c c
WHERE NOT EXISTS (SELECT col1 FROM table_y WHERE col1 = r.col4)

AND r.col5 = :my_string1
AND r.col6 = :my_string2
AND r.col7 = v.col4
AND c.col1 = :my_string2
AND c.col2 = r.col7
AND c.col3 = r.col8
AND c.col4 = r.col9
AND r.col1 BETWEEN TO_DATE(:my_string3,:CST_MY_FORMAT)

AND TO_DATE(:my_string3,:CST_MY_FORMAT)

Unfortunately I don't have access to his system (8i database) and I don't have any information about the database objects involved. Nonetheless do you see what could produce such results? The timing is 30 seconds vs. less than 1 second and it is reproducible. Received on Fri Feb 20 2004 - 06:56:18 CST