Re: create index when add primary key

Bob Yeh wrote:
>
> Hi,
>
> Oracle create a unique index when the primary key is created.
> Is there a way to tell Oracle to create this index in certain
> tablespace when creating primary key?
>
> Thanks. BobBob,

Yes there is a way. If you are creating the constraint as you create the table, try something similar to the following:

	CREATE TABLE new_table
		(column1  number,
      		 column2  varchar2(20),
		 column3  date,
                 CONSTRAINT pk_new_table
		 PRIMARY KEY(column1)
		 USING INDEX PCTFREE 20 PCTUSED 50 
		 TABLESPACE index_tablespace
		 STORAGE(INITIAL 100K NEXT 10K PCTINCREASE 0));

If you are adding a primary key constraint to an existing table try something like the following:

	ALTER TABLE old_table
		ADD CONSTRAINT pk_old_table
		PRIMARY KEY(column1)
		USING INDEX ...
		TABLESPACE index_tablespace
		STORAGE(...);

hope this helps,

regards,
Chuck

-- 
*****************************************************************************
*
*   Chuck Wolfe                         Phone: (703)838-9720
*   Manager                             e-mail:  cwolfe_at_ix.netcom.com (Home)
*   NDC Group, Inc                               cwolfe_at_ndcinc.com    (Work)
*   625 Slaters Lane, 
*   Suite 102
*   Alexandria, VA 22314
* 
*   All the usual discalimers apply, except where otherwise indicated...
****************************************************************************