Help! create index question!

Help! create index question!

Post by Cantonese Bo » Sun, 31 Dec 1899 09:00:00



Hi:

    I have a table "TABLE1" with five column "COLUMN_A,

COLUMN_B,

COLUMN_C,

COLUMN_D,

COLUMN_E,

COLUMN_F".
   The first four columns form the primary key and the fifth column is
the unique key.

Here is the create table statement:

create table TABLE1 ("COLUMN_A" DATE NOT NULL,"COLUMN_B" NUMBER(4) NOT
NULL,
"COLUMN_C" NUMBER(4) NOT NULL,"COLUMN_D" NUMBER(2) NOT NULL,"COLUMN_E"
NUMBER
(8),"COLUMN_F" NUMBER)
    PCTFREE 10 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLESPACE TSP_TABLE1
    STORAGE ( INITIAL 500M NEXT 500M MINEXTENTS 1 MAXEXTENTS 99
PCTINCREASE 0
 FREELISTS 1 FREELIST GROUPS 1);

    I have a question , what's the differences if I use method a) or
method b) to create the primary key and unique
key? What 's the impact to the table ?

Method A)

alter table TABLE_1 add constraint PK_ABCD primary key ("COLUMN_A",
    "COLUMN_B", "COLUMN_C", "COLUMN_D") using index tablespace TSP_IX
storage(
    initial 50M next 50M pctincrease 0 minextents 1 maxextents 3)
pctfree 10
     initrans 2 maxtrans 255;

alter table TABLE_1 add constraint UNQ_TABLE1_E unique ("COLUMN_E"
    ) using index tablespace TSP_IX storage(initial 30M next 30M
pctincrease
    0 minextents 1 maxextents 3) pctfree 10 initrans 2 maxtrans 255;

Method B)

create unique index  PK_ABCD on TALBE1 ("COLUMN_A,"COLUMN_B",
"COLUMN_C","COLUMN_E") TABLESPACE TSP_IX INITRANS 2 MAXTRANS 255 PCTFREE
10
    STORAGE (INITIAL 50M NEXT 50M MINEXTENTS 1 MAXEXTENTS 3 PCTINCREASE
0
 FREELISTS 1 FREELIST GROUPS 1);

create unique index UNQ_TABLE1_E on TABLE1 ("COLUMN_E")
 TABLESPACE TSP_IX INITRANS 2 MAXTRANS 255 PCTFREE 10
    STORAGE (INITIAL 30M NEXT 30M MINEXTENTS 1 MAXEXTENTS 3 PCTINCREASE
0
 FREELISTS 1 FREELIST GROUPS 1);

Thanks

W

 
 
 

Help! create index question!

Post by Sybrand Bakke » Sun, 31 Dec 1899 09:00:00


Method B will *NOT* create a constraint, and shouldn't be available anymore.

Hth,

Sybrand Bakker, Oracle DBA



Quote:> Hi:

>     I have a table "TABLE1" with five column "COLUMN_A,

> COLUMN_B,

> COLUMN_C,

> COLUMN_D,

> COLUMN_E,

> COLUMN_F".
>    The first four columns form the primary key and the fifth column is
> the unique key.

> Here is the create table statement:

> create table TABLE1 ("COLUMN_A" DATE NOT NULL,"COLUMN_B" NUMBER(4) NOT
> NULL,
> "COLUMN_C" NUMBER(4) NOT NULL,"COLUMN_D" NUMBER(2) NOT NULL,"COLUMN_E"
> NUMBER
> (8),"COLUMN_F" NUMBER)
>     PCTFREE 10 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLESPACE TSP_TABLE1
>     STORAGE ( INITIAL 500M NEXT 500M MINEXTENTS 1 MAXEXTENTS 99
> PCTINCREASE 0
>  FREELISTS 1 FREELIST GROUPS 1);

>     I have a question , what's the differences if I use method a) or
> method b) to create the primary key and unique
> key? What 's the impact to the table ?

> Method A)

> alter table TABLE_1 add constraint PK_ABCD primary key ("COLUMN_A",
>     "COLUMN_B", "COLUMN_C", "COLUMN_D") using index tablespace TSP_IX
> storage(
>     initial 50M next 50M pctincrease 0 minextents 1 maxextents 3)
> pctfree 10
>      initrans 2 maxtrans 255;

> alter table TABLE_1 add constraint UNQ_TABLE1_E unique ("COLUMN_E"
>     ) using index tablespace TSP_IX storage(initial 30M next 30M
> pctincrease
>     0 minextents 1 maxextents 3) pctfree 10 initrans 2 maxtrans 255;

> Method B)

> create unique index  PK_ABCD on TALBE1 ("COLUMN_A,"COLUMN_B",
> "COLUMN_C","COLUMN_E") TABLESPACE TSP_IX INITRANS 2 MAXTRANS 255 PCTFREE
> 10
>     STORAGE (INITIAL 50M NEXT 50M MINEXTENTS 1 MAXEXTENTS 3 PCTINCREASE
> 0
>  FREELISTS 1 FREELIST GROUPS 1);

> create unique index UNQ_TABLE1_E on TABLE1 ("COLUMN_E")
>  TABLESPACE TSP_IX INITRANS 2 MAXTRANS 255 PCTFREE 10
>     STORAGE (INITIAL 30M NEXT 30M MINEXTENTS 1 MAXEXTENTS 3 PCTINCREASE
> 0
>  FREELISTS 1 FREELIST GROUPS 1);

> Thanks

> W


 
 
 

Help! create index question!

Post by Paul Schluc » Sun, 31 Dec 1899 09:00:00


I agree with Sybrand and would at, that although a primary key by definition
is unique, a unique key is not a primary key and therefore cannot  - at
least as far as I know - be used in a foreign key relationship.

An additional question: why user 3 commands (1 create table and 2 create
constraint)  instead of 1 create table statement in which you include the
definition of primary and unique key?

Paul Schluck, OCP DBA



> Method B will *NOT* create a constraint, and shouldn't be available
anymore.

> Hth,

> Sybrand Bakker, Oracle DBA



> > Hi:

> >     I have a table "TABLE1" with five column "COLUMN_A,

> > COLUMN_B,

> > COLUMN_C,

> > COLUMN_D,

> > COLUMN_E,

> > COLUMN_F".
> >    The first four columns form the primary key and the fifth column is
> > the unique key.

> > Here is the create table statement:

> > create table TABLE1 ("COLUMN_A" DATE NOT NULL,"COLUMN_B" NUMBER(4) NOT
> > NULL,
> > "COLUMN_C" NUMBER(4) NOT NULL,"COLUMN_D" NUMBER(2) NOT NULL,"COLUMN_E"
> > NUMBER
> > (8),"COLUMN_F" NUMBER)
> >     PCTFREE 10 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLESPACE TSP_TABLE1
> >     STORAGE ( INITIAL 500M NEXT 500M MINEXTENTS 1 MAXEXTENTS 99
> > PCTINCREASE 0
> >  FREELISTS 1 FREELIST GROUPS 1);

> >     I have a question , what's the differences if I use method a) or
> > method b) to create the primary key and unique
> > key? What 's the impact to the table ?

> > Method A)

> > alter table TABLE_1 add constraint PK_ABCD primary key ("COLUMN_A",
> >     "COLUMN_B", "COLUMN_C", "COLUMN_D") using index tablespace TSP_IX
> > storage(
> >     initial 50M next 50M pctincrease 0 minextents 1 maxextents 3)
> > pctfree 10
> >      initrans 2 maxtrans 255;

> > alter table TABLE_1 add constraint UNQ_TABLE1_E unique ("COLUMN_E"
> >     ) using index tablespace TSP_IX storage(initial 30M next 30M
> > pctincrease
> >     0 minextents 1 maxextents 3) pctfree 10 initrans 2 maxtrans 255;

> > Method B)

> > create unique index  PK_ABCD on TALBE1 ("COLUMN_A,"COLUMN_B",
> > "COLUMN_C","COLUMN_E") TABLESPACE TSP_IX INITRANS 2 MAXTRANS 255 PCTFREE
> > 10
> >     STORAGE (INITIAL 50M NEXT 50M MINEXTENTS 1 MAXEXTENTS 3 PCTINCREASE
> > 0
> >  FREELISTS 1 FREELIST GROUPS 1);

> > create unique index UNQ_TABLE1_E on TABLE1 ("COLUMN_E")
> >  TABLESPACE TSP_IX INITRANS 2 MAXTRANS 255 PCTFREE 10
> >     STORAGE (INITIAL 30M NEXT 30M MINEXTENTS 1 MAXEXTENTS 3 PCTINCREASE
> > 0
> >  FREELISTS 1 FREELIST GROUPS 1);

> > Thanks

> > W