Re: Unique Indexes - OraDoc contradictory?

"Marc Blum" <marc_at_marcblum.de> wrote in message news:qg9rjuoggcpdaso2caq52varpluidmnkph_at_4ax.com...
> Hi all,
>
> in the Oracle 8i Documentation I found the following two passages:
>
> Concepts
>
> Unique and Nonunique Indexes
> Indexes can be unique or nonunique. Unique indexes guarantee that no two
rows of
> a table have duplicate values in the columns that define the index.
Nonunique
> indexes do not impose this restriction on the column values.
> Oracle recommends that you do not explicitly define unique indexes on
tables.
> Uniqueness is strictly a logical concept and should be associated with the
> definition of a table. Alternatively, define UNIQUE integrity constraints
on the
> desired columns. Oracle enforces UNIQUE integrity constraints by
automatically
> defining a unique index on the unique key.
>
>
> Application Developer's Guide - Fundamentals
>
> Creating Indexes for Use with Constraints
> All enabled unique and primary keys require indexes, and foreign keys
should
> almost always be indexed. Although unique and primary keys can create
unique
> indexes for you, when you need an index for performance reasons, you
should not
> rely on an index that is automatically created for key columns. Instead,
create
> the index first, by hand.
>
>
> I'm a bit unsure, if there's a contradiction or if I'm missing the point.
>
> To me, the first excerpt says:
>
> You shall not define unique indexes on tables. You shall an integrity
constraint
> and the index will build implicitely.
>
> The second excerpt says:
>
> You shall create first the index to support a constraint. Then you shall
define
> constraint, which will use the index.
>
>
> Maybe some guru can shed some light an this? Many thanks in advance
>

I think you missed the subtle significance of certain key words in the second paragraph:

"Although unique and primary keys can create unique indexes for you, *when you need an index for performance reasons*, you should not rely on an index that is automatically created for key columns..."

In other words, if the columns you need to be indexed for performance reasons are not part of a primary or a unique key, then clearly there won't be any automatically created indexes, and therefore you need to create your own. Or, consider a column which is *part* of a primary key: the automatically created index may have included it as a non-leading column, but your queries reference that value a lot, and so it would be nice to have an index containing *only* that column. So 'don't rely' on the automatically created index, because it might not match what you need 'for performance reasons'.

It's badly written, I agree. But the author is (as far as I can tell) trying to contrast index creation done because you want PK or Unique keys, and indexes which you want to help your SQL run faster.

Regards
HJR
>
>
> Marc Blum
> mailto:marc_at_marcblum.de
> http://www.marcblum.de
Received on Tue Jul 23 2002 - 16:50:11 CDT