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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top