Naveen Nahata wrote:
>
> 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);
You don't need DISTINCT.
> 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);
You don't need DISTINCT.
> Query 3:
> SELECT DISTINCT description
> FROM cdl_documents
> MINUS
> SELECT DISTINCT description
> FROM cdl_documents
> WHERE cdl_cdl_id = 5;
You don't need DISTINCT.
> 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
>
Since you still are at a very early stage, what is DESCRIPTION refering
to? DOCUMENT_LINE or CDL_CDL_ID? From your own description (and your
queries) I think that it refers to DOCUMENT_LINE, otherwise you would
just need to write
SELECT DESCRIPTION
FROM ...
WHERE CDL_CDL_ID != 5
If you have very few distinct DESCRIPTION values, perhaps, before
jumping on bitmap indexes, would it be wise to refine a little bit the
normalisation process, assign them a sequence-generated id (say DESC_ID)
and move them to a different table. Then
SELECT D.DESCRIPTION
FROM CDL_DESC D
WHERE NOT EXISTS (SELECT NULL
FROM CDL_DOCUMENTS X
WHERE X.CDL_CDL_ID = 5
AND X.DESC_ID = D.DESC_ID)
will mean a full scan of CDL_DESC but may fly if it's relatively small
and if CDL_DOCUMENTS is properly indexed on (CDL_CDL_ID, DESC_ID).
--
Regards,
Stephane Faroult
Oriole Ltd
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
