Re: Optmization of queries

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: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (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).