Optmization of queries

[Naveen Nahata]

Hi all,I have a question on optimization of queries. <BR>I'm attaching 3 different ways for fetching the reqd. data.My question is not which one is better rather, <BR>under what all circumstances one wud be better than the other. This is to gain useful insight into looking at optimization problem <BR>and the way to go about it. And yes, I've done RTFM ;-), but i want to get as many ideas as <BR>possible(kinda brainstorming), and see how experts go about tackling <BR>such problem. And moreover I dont have the data populated bcoz right now we are in <BR>the design stage itself and i'm trying to tune queries for the application <BR>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 <BR>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 <BR>for an indexed column bcoz the optimizer will go for FULL TABLE SCAN rather than <BR>INDEX SCAN. But what if i have a Bitmap Index? Will an INDEX SCAN still be avoided? <BR>If yes, why? Regards, Naveen

---

Do You Yahoo!?
Yahoo! Auctions Great stuff seeking new owners! Bid now!