Q

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;

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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close