Optmization of queries

Hi all,I have a question on optimization of queries. I'm attaching 3 different ways for fetching the reqd. data.My question is not which one is better rather, under what all circumstances one wud be better than the other. This is to gain useful insight into looking at optimization problem and the way to go about it.

And yes, I've done RTFM ;-), but i want to get as many ideas as possible(kinda brainstorming), and see how experts go about tackling such problem. And moreover I dont have the data populated bcoz right now we are in the design stage itself and i'm trying to tune queries for the application developers to put in the application.

SQL> desc cdl_documents

 Name                          Null?    Type
 ----------------------------- -------- -----------------
 CDL_CDL_ID                    NOT NULL NUMBER(10)
 DESCRIPTION                   NOT NULL VARCHAR2(50)
 DOCUMENT_LINE                 NOT NULL NUMBER(2)
 DATE_RECEIVED                 NOT NULL DATE
 DATE_RETURNED                          DATE
 INSERTED_BY                   NOT NULL VARCHAR2(30)
 INSERT_DATE                   NOT NULL DATE
 LAST_CHANGED_BY               NOT NULL VARCHAR2(30)
 LAST_CHANGE_DATE              NOT NULL DATE

DESCRIPTION column will have relatively few distinct values compared to the number of rows so a Bitmap Index will be considered.

Following are the 3 queries which immediately come to my mind ...

Query 1:
SELECT DISTINCT cdl.description
  FROM cdl_documents cdl
 WHERE description NOT IN (SELECT description

			     FROM cdl_documents
			    WHERE cdl_cdl_id = 5);

Query 2:
SELECT DISTINCT cdl.description
  FROM cdl_documents cdl
 WHERE NOT EXISTS (SELECT 1

		     FROM cdl_documents
		    WHERE cdl_cdl_id = 5
			  AND
			  cdl.description = description);

Query 3:
SELECT DISTINCT description
  FROM cdl_documents
MINUS
SELECT DISTINCT description
  FROM cdl_documents
 WHERE cdl_cdl_id = 5;

One more thing -
It is generally not advisable to use a NOT IN or NOT EQUAL TO clause for an indexed column bcoz the optimizer will go for FULL TABLE SCAN rather than INDEX SCAN. But what if i have a Bitmap Index? Will an INDEX SCAN still be avoided? If yes, why?

Regards, NaveenDo You Yahoo!?
Yahoo! Auctions Great stuff seeking new owners! Bid now! Received on Sat Feb 02 2002 - 05:00:58 CST