Re: Primary indices don't seem to work

From: Jan Doggen <j.doggen_at_qsa.nl>
Date: Thu, 20 Sep 2001 10:02:38 GMT
Message-ID: <29jq7.1199709$ef.36900635@Flipper>

New facts:

It's OK that records are put in the table in no specific order, we see that with MS SQL too.
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
>
>
>
Received on Thu Sep 20 2001 - 05:02:38 CDT