Re: Primary Key and indexes

Hi.

 There is a BUG of Oracle ( from 8.0.3 and up ).  Oracle decided to apply "we know better" policy to our applications  and implemented following feature:
 Before creation of PK Oracle check if ANY index containing PK columns  as leading columns ( column order and uniqueness are NOT important )  exists.
 If it exists - PK index is NOT created and uniqueness is checked by  querying an existing index.
 They forgot that uniquenes is only part of the problem and PK may be  used by SQL statements.

 The problem arises in following situations:

1. Oracle Designer is generating table in following order:
2. CREATE TABLE
3. CREATE INDEX ...
4. ALTER TABLE ADD CONSTRAINT ... PRIMARY KEY ... So if you have a PK on columns Col1, Col2 and an index on Col2, Col1 - no index for PK will be created and all your SQL statements with "... WHERE Col1 = :p1 " will use FULL table scan.
5. Even worse - the same sequence CREATE TABLE -> CREATE INDEX -> ALTER TABLE is generated by EXPORT utility, so if you make an EXPORT, drop yout table(s)/schema and do IMPORT - you are going to be surprized ( I was after most selects on 5 biggest tables in my application started to perform FULL table scans on millions of rows).

  There 2 bugs exist in Oracle bug database, but I'm home now and can   not give you the numbers.

  If you are interested - send me e-mail.

  HTH. Michael.

In article <39bfc4e8.7568082_at_news.news-ituk.to>,   See Message body for real address wrote:
> I am a little confused ( an understatement! )
>
> When creating a Primary Key constraint, the docs indicate that an
 implicit index
> is created...how can I verify that this is the case...I cannot find
 the index
> anywhere with DBA Studio's Schema Manager or TOAD or any other
 tool...Not that I
> doubt Oracle, but it would be nice to actually see the index...( is
 it under the
> same schema as the table ?, some odd name maybe? ...)
>
> Also, most of our Primary Keys were generated by Designer, and it
 seems some
> have indexes ( named the same as the constraint name) and some do
 not...however,
> it may be that the ones that have indexes had the primary key
 created AFTER the
> unique index was built and just used the existing index...
>
> any info appreciated...
>
> John Greco
>
> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----== Over 80,000 Newsgroups - 16 Different Servers! =-----
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Sep 13 2000 - 14:37:31 CDT