Re: Primary indices don't seem to work

"Jan Doggen" <massoft_at_tref.nl> wrote in message news:9oaq3c$4ba$1_at_reader05.wxs.nl...
> Hi everyone,
>
> in Delphi 4 we're creating and accessing tables through the BDE and TCP/IP
> connections to an Oracle 8 database.
> These tables have single-field primary keys, either created with
>
> > CREATE TABLE
> > ...
> > CONSTRAINT PK_INDX PRIMARY KEY (fldname)
> or
> > CREATE TABLE
> > followed by
> > ALTER TABLE ADD PRIMARY KEY(fldname)
>
> Sometimes the records are *not* ordered by their primary key:
> - if we use a Delphi TTable and jump to the last record it's not the last
ID
> - if we use Delphi SQL Explorer to look at the data, the records are not
> ordered by the primary key field
>
> The app is doing a lot of record appends, truncate tables etc.
> The app also runs on MS-SQL without problems, the code logic seems fine.
>
> What could be going on here?
> Is there such a thing as the index not being 'maintained' in Oracle?
>
> Thanks in advance for all suggestions
> Jan
>
>
>

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 Wed Sep 19 2001 - 15:49:27 CDT