Re: Creating Table as select...

Hi Daniel,
  I read the following in oracle 10g concepts manual:

http://download-west.oracle.com/docs/cd/B12037_01/server.101/b10743/schema.htm#sthref893

  <Oracle10gConcepts>

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 key column (or columns). Nonunique indexes do not impose this restriction on the column values.

Oracle recommends that unique indexes be created explicitly, and not through enabling a unique constraint on a table.

  </Oracle10gConcepts>

   Can you please comment on this!

--KDB Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1076121020.197412_at_yasure>...
> Jon wrote:
>
> > Hi All,
> > I'm trying to create a table with an 'as select' clause, but I would
> > also like to specify a table constraint on the new table - a unique
> > index. One of the versions that I have tried without success is
> > below. Can this be done in one statment or do I have to do an alter
> > table afterwords? If it can be done in one statement what would that
> > statement be?
> >
> > Thanks & Regards
> > Jon
> >
> >
> > create table obs_last_logon
> > tablespace bv_data
> > CONSTRAINT obs_last_logon_unique UNIQUE (user_id)
> > USING INDEX TABLESPACE bv_index
> > as
> > select distinct a.user_id, c.user_alias, max(timestamp) "timestamp"
> > from obs1 a, obs_bv_user_profile b, obs_bv_user c
> > where not a.user_id in (1301,984,3408,1262)
> > and a.user_id = c.user_id
> > and c.user_id = b.user_id
> > group by a.user_id, c.user_alias
>
> You should never build unique indexes ... rather unique constraints.
> Unique indexes lack many of the capabilities of unique constraints while
> creating the same functionality.
>
> Create the table then create the index.
Received on Sun Feb 08 2004 - 23:21:48 CST