Ask the Expert

Sub-query speed, part 2

Which one of the following statements take less time?

Query 1

SELECT  CONCAT ( CONCAT ( '

', r.name ), CONCAT ( '

', r.description ) ) AS named, r.room_id FROM rooms r WHERE NOT EXISTS ( SELECT 1 FROM room_res rr WHERE TO_DATE ( :upto_date, 'YYYY-MM-DD HH24:MI:SS' ) > rr.res_from AND TO_DATE ( :from_date, 'YYYY-MM-DD HH24:MI:SS' ) < rr.res_upto AND rr.room_id = r.room_id ) AND :ppl <= r.capacity;

Query 2

SELECT  CONCAT ( CONCAT ( '

', r.name ), CONCAT ( '

', r.description ) ) AS named, r.room_id FROM rooms r WHERE NOT EXISTS ( SELECT 1 FROM room_res rr, rooms x -- Changed WHERE TO_DATE ( :upto_date, 'YYYY-MM-DD HH24:MI:SS' ) > rr.res_from AND TO_DATE ( :from_date, 'YYYY-MM-DD HH24:MI:SS' ) < rr.res_upto AND rr.room_id = x.room_id -- Changed ) AND :ppl <= r.capacity;

    Requires Free Membership to View

Continued from page 1.

Perhaps this is what you meant to do when you wrote Query 2:

Query 3

SELECT  '<h2>'
        ||  name
        ||  '</h2>'
        ||  description  AS  named,
        room_id
FROM    rooms
WHERE   room_id NOT IN ( SELECT DISTINCT room_id
                         FROM    room_res rr
                         WHERE   :upto_dt > rr.res_from
                           AND   :from_dt < rr.res_upto
                       )
  AND  :ppl <= capacity;

(The use of || operators rather than CONCAT does not affect the results or the speed; I just find multiple ||'s easier to read and debug than nested CONCATs.)

Again, I can only guess if Query 1 will be faster than Query 3 on your system with your data.

If most of your queries (or at least the ones you need to run quickly) involve dates in the future, but most of the database involves dates in the past (e.g., if you keep historical data in the room_res table for five years), then the most selective condition in the sub-query is going to be ":upto_dt > rr.res_from". Make sure there's an index on res_from and check the EXPLAIN PLAN output to make sure the query is using it.

If Oracle insists on using an index on res_upto instead, then change the WHERE clause of the sub-query to say ":from_dt < rr.res_upto + 0" to trick Oracle into not using the index on res_upto (or you can drop the index).

This was first published in November 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: