Re: Sql not picking up primary key index

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 Tue Sep 11 2001 - 09:23:55 CDT