Cheers,
My understanding was it used the where clause to determine what index
to choose, regardless of what is selected in select clause.
"Anurag Varma" <avdbi_at_nospam.hotmail.com> wrote in message news:<%7pn7.1293$f7.318336_at_news02.optonline.net>...
> In first case you select a column which is not present in the index. So
> Oracle decides that
> doing one full table scan is cheaper than doing a full index scan and table
> lookup.
> In second case you select a column which is present in the index. So Oracle
> decides that one
> full index scan will suffice. Since the selected column is the index itself,
> it won't need to do a
> table lookup.
>
> Is this what you were asking for?
>
> Cheers,
> Anurag
>
> "fran keenan" <fran.keenan_at_yac.com> wrote in message
> news:8b7972b3.0109110340.116a7d1_at_posting.google.com...
> > Hi,
> >
> > I have the following table:
> > SQL> desc conferencebookings;
> > Name Null? Type
> > ----------------------------------------- -------- --------------
> > F_BOOKINGID NOT NULL NUMBER(10)
> > F_CONFERENCEDETID NOT NULL NUMBER(10)
> > F_PATTERNID NOT NULL NUMBER(10)
> > F_BOOKERTYPE NOT NULL NUMBER(2)
> > F_BOOKER NOT NULL NUMBER(10)
> > F_SOURCEIP VARCHAR2(32)
> > F_ROOMNUMBER NOT NULL NUMBER(9)
> > F_AUTOEXTEND NOT NULL NUMBER(2)
> > F_OWNERID NUMBER(10)
> > F_DTCREATED NOT NULL DATE
> > F_DTSAVED NOT NULL DATE
> >
> > f_bookingid is the index.
> >
> >
> > I run the following sql:
> >
> > SELECT F_ROOMNUMBER
> > FROM CONFERENCEBOOKINGS B,CONFERENCES C
> > WHERE B.F_BOOKINGID = C.F_BOOKINGID
> >
> > explain plan is:
> > SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=91 Card=90 Bytes=1080)
> > NESTED LOOPS (Cost=91 Card=90 Bytes=1080)
> > TABLE ACCESS (FULL) OF CONFERENCEBOOKINGS (Cost=1 Card=90 Bytes=720)
> > INDEX (RANGE SCAN) OF CF_I3 (NON-UNIQUE) (Cost=1 Card=90 Bytes=360)
> > It uses full table scan, where I would expect it to use the index on the
> pk.
> >
> > Now if I change the select to :
> > SELECT B.F_BOOKINGID
> > FROM CONFERENCEBOOKINGS B,CONFERENCES C
> > WHERE B.F_BOOKINGID = C.F_BOOKINGID
> >
> > The explain plan now picks up the pk index:
> > SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=26 Card=90 Bytes=720)
> > NESTED LOOPS (Cost=26 Card=90 Bytes=720)
> > INDEX (FULL SCAN) OF CF_I3 (NON-UNIQUE) (Cost=26 Card=90 Bytes=360)
> > INDEX (UNIQUE SCAN) OF CFB_PK (UNIQUE)
> >
> > Any ideas ??
Received on Wed Sep 12 2001 - 05:01:24 CDT
