Re: Primary indices don't seem to work

"Jan Doggen" <j.doggen_at_qsa.nl> wrote in message news:<29jq7.1199709$ef.36900635_at_Flipper>...
> New facts:
>
> 1) It's OK that records are put in the table in no specific order, we see
> that with MS SQL too.
>
> 2) We just found out that the SQL query issued by the BDE is ORDER BY the
> wrong index!!!
> We created the table like this:
>
> CREATE TABLE TABLES (
> Tbl_ID number(10,0) NOT NULL ,
> Tbl_Source_Module_ID number(10,0) NULL ,
> Tbl_SourceTableName varchar2(255) NULL ,
> Tbl_TargetTableName varchar2(255) NULL ,
> Tbl_Active number(1,0) DEFAULT 1 NOT NULL,
> Tbl_Created date NULL ,
> Tbl_Changed date NULL ,
> PRIMARY KEY (Tbl_ID)
> )
> followed by:
> CREATE INDEX BySource ON TABLES (Tbl_Source_Module_ID, Tbl_SourceTableName)
> followed by:
> CREATE UNIQUE INDEX ByTarget ON TABLES (Tbl_TargetTableName)
>
> And it now turns out the the last index is used instead of the primary key!
>
> How come???
>
> Jan
>
> Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message
> news:tqi1r5kjt71j13_at_news.demon.nl...
> >
> > Records are entered in Oracle tables in no particular physical order.
> > If you don't use an order by or an appropiate where clause in your
> explorer
> > window, you just get what you ask for: the records in physical order.
> >
> > Oracle is NOT mssql.
> >
> > The suggestion about indices not being maintained is only appropiate when
> > you are performing direct loads, or using the append hint in your insert
> > statement.
> > In all other contexts the idea Oracle doesn't maintain indices
> automatically
> > is just completely off the mark.
> >
> > Regards,
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> >
> >

You must be using the rule based optimizer. All things being equal (a primary key and an unique key are equal for RBO) RBO will used the index which was create *last*.

Please stop using the rule based optimizer, it was desupported in and hasn't changed since 1994.

You can switch to the Cost Based optimizer by analyzing your table.

As the lunches in the Oracle office in de Meern are quite good, I can recommend you to book a course in performance tuning (www.oracle.nl/education will provide the details). You won't learn Oracle by just hacking away.

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Thu Sep 20 2001 - 08:10:18 CDT