Re: primary key indexes - where ???

From: Brian P. Mac Lean <brian.maclean_at_teldta.com>
Date: 1996/09/22
Message-ID: <3244E97D.641F_at_teldta.com>

seci_at_ludens.elte.hu wrote:
>
> hello,
>
> if i have a table with a primary key it is obvious that oracle will
> create an index (of course a unique one) to maintain this constraint
> (but if i look at user_indexes i cannot see this index).
>
> is it possible to define the name and the TABLESPACE and STORAGE of this
> kind of indexes or not?
>
> TIA
> peter

This is my response to another posting in this news group. The origional question is included first, followed by the (one of many possible) answer:

>Chuck Hamilton wrote:
>
> Is it possible to build a primary key index in parallel? If so, what's
> the syntax?
> --
> Chuck Hamilton
> chuckh_at_dvol.com
>

SQL> !cat /tmp/new.sql
CREATE TABLE "OPS$ORACLE"."ADJ_CODE"

( "ADJ_TYPE"                       VARCHAR2(2)     NOT NULL
, "ADJ_DESC"                       VARCHAR2(25)    NOT NULL

) PCTFREE 20 PCTUSED 70 INITRANS 2 MAXTRANS 255 TABLESPACE "DAT01"
STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 121          PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) PARALLEL(DEGREE 2) NOCACHE;   CREATE UNIQUE INDEX "OPS$ORACLE"."ADJ_CODE_PK" ON "OPS$ORACLE"."ADJ_CODE" ( "ADJ_TYPE") PCTFREE 20 INITRANS 2 MAXTRANS 255
TABLESPACE "IDX01"
STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 121          PCTINCREASE 0 FREELISTS 1)
UNRECOVERABLE
PARALLEL(DEGREE 2);   ALTER TABLE "OPS$ORACLE"."ADJ_CODE"
ADD CONSTRAINT "ADJ_CODE_PK" PRIMARY KEY ( "ADJ_TYPE" ) EXCEPTIONS INTO "OPS$ORACLE"."EXCEPTIONS";     SQL> _at_/tmp/new  

Table created.    

Index created.    

Table altered.  

SQL> set pagesize 24 linesize 132
SQL> select * from dba_segments where segment_name like '%ADJ%';  

OWNER                          SEGMENT_NAME                                                                      

SEGMENT_TYPE

------------------------------ ---------------------------------------------------------------------------------
-----------------
TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT

MIN_EXTENTS

------------------------------ ----------- ------------ ---------- ---------- ---------- -------------- -----------

---

MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS ----------- ------------ ---------- ---------------

OPS$ORACLE                     ADJ_CODE                                                                          TABLE
DAT01                                    3        16407    1064960        260          1        1048576     1048576
 1
        121            0          1               1
 
OPS$ORACLE                     ADJ_CODE_PK                                                                       INDEX
IDX01                                    4         3462    1064960        260          1        1048576     1048576
 1
        121            0          1               1
 
 

SQL> select * from dba_tables where table_name = 'ADJ_CODE';  

OWNER                          TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------

  PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- FREELIST_GROUPS B NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN DEGREE INSTANCES CACHE TABLE_LO --------------- - ---------- ---------- ------------ ---------- ---------- ----------- ---------- ---------- ----- --------

OPS$ORACLE                     ADJ_CODE                       DAT01
        20         70          2        255        1048576     1048576           1         121            0          1
              1 N                                                                               2          1     N ENABLED
 
 

SQL> select * from dba_indexes where index_name = 'ADJ_CODE_PK';  

OWNER                          INDEX_NAME                     TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
TABLE_TYPE  UNIQUENES TABLESPACE_NAME                 INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS 

MAX_EXTENTS

----------- --------- ------------------------------ ---------- ---------- -------------- ----------- ----------- 
-----------
PCT_INCREASE  FREELISTS FREELIST_GROUPS   PCT_FREE     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY
------------ ---------- --------------- ---------- ---------- ----------- ------------- -----------------------

AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS

----------------------- ----------------- -----------
OPS$ORACLE                     ADJ_CODE_PK                    OPS$ORACLE                     ADJ_CODE
TABLE       UNIQUE    IDX01                                   2        255        1048576     1048576           1         
121
           0          1               1         20
                                          VALID
 
 

SQL> select * from dba_constraints where CONSTRAINT_NAME = 'ADJ_CODE_PK';  

OWNER                          CONSTRAINT_NAME                C TABLE_NAME
------------------------------ ------------------------------ - ------------------------------
SEARCH_CONDITION                                                                 R_OWNER
-------------------------------------------------------------------------------- ------------------------------
R_CONSTRAINT_NAME              DELETE_RU STATUS
------------------------------ --------- --------
OPS$ORACLE                     ADJ_CODE_PK                    P ADJ_CODE
 
                                         ENABLED
 
 

SQL> select * from dba_cons_columns where CONSTRAINT_NAME = 'ADJ_CODE_PK';  

OWNER                          CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------ --------------------------

  POSITION

---

OPS$ORACLE                     ADJ_CODE_PK                    ADJ_CODE                       ADJ_TYPE
         1
 
 

SQL>

--
                       \\|//
                       (0-0)
           +-----oOO----(_)-----------+
           | Brian P. Mac Lean        |
           | Database Analyst         |
           | brian.maclean_at_teldta.com |
           | http://www.teldta.com    |
           +-------------------oOO----+
                      |__|__|
                       || ||
                      ooO Ooo

Received on Sun Sep 22 1996 - 00:00:00 CEST