Notes pour OCP 9i DBA1

Date de publication : 01/09/2004 , Date de mise a jour : 01/09/2004

8. Gestion des tables, index et contraintes

8.1. Création des tables

• Relationnelle. Ce sont les tables ordinaires. Elles sont du type par défaut ORGANIZATION HEAP.
• Temporaire : se créent par CREATE GLOBAL TEMPORARY TABLE
• IOT (tables organisées en index). Ce sont des tables qui sont prétriées dans l'ordre de la clé primaire. On les crée par CREATE TABLE ORGANIZATION INDEX.
• Externe. Ces tables sont en fait des fichiers plats stockés hors de la base. Ces pseudo-tables sont toujours en lecture seule, et sont lus par l'entremise de SQL*Loader. Il s'agit d'une nouveauté de la 9i. On les crée par CREATE TABLE ORGANIZATION EXTERNAL.
• Objet. Chaque ligne de la table représente un objet au sens POO.

8.1.1. Gestion des LOB

CREATE TABLE print_media_new
    ( product_id        NUMBER(6),
      ad_sourcetext     CLOB    
      ad_sourcetext     CLOB    )
    LOB (ad_sourcetext, ad_finaltext) STORE AS
      (TABLESPACE example
       DISABLE STORAGE IN ROW
       STORAGE (INITIAL 6144 NEXT 6144)
       CHUNK 4000
       PCTVERSION 20
       NOCACHE LOGGING);

8.1.2. Les tables partitionnées

CREATE TABLE range_sales
    ( prod_id        NUMBER(6),
      time_id        DATE    ) 
STORAGE (INITIAL 100M NEXT 100M)
PARTITION BY RANGE (time_id)
  (PARTITION SALES_Q1_2000 VALUES LESS THAN ('01-APR-2000'),
   PARTITION SALES_Q2_2000 VALUES LESS THAN ('01-JUL-2000'),
   PARTITION SALES_Q3_2000 VALUES LESS THAN ('01-OCT-2000')
	STORAGE (INITIAL 200M NEXT 200M),
   PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE));

CREATE TABLE hash_products 
    ( product_id          NUMBER(6) ,
      product_name        VARCHAR2(50) ) 
 PARTITION BY HASH (product_id) 
 PARTITIONS 5 
 STORE IN (tbs_1, tbs_2, tbs_3, tbs_4);

CREATE TABLE list_customers 
   ( customer_id             NUMBER(6),
     nls_territory           VARCHAR2(30))
   PARTITION BY LIST (nls_territory) 
(  PARTITION asia VALUES ('CHINA', 'THAILAND'),
   PARTITION europe VALUES ('GERMANY', 'ITALY', 'SWITZERLAND'),
   PARTITION rest VALUES (DEFAULT));

CREATE TABLE customers_part (
   customer_id        NUMBER(6),
   cust_first_name    VARCHAR2(20),
   cust_last_name     VARCHAR2(20),
   nls_territory      VARCHAR2(30),
   credit_limit       NUMBER(9,2)) 
   PARTITION BY RANGE (credit_limit)
   SUBPARTITION BY LIST (nls_territory)
      SUBPARTITION TEMPLATE 
         (SUBPARTITION east  VALUES 
            ('CHINA', 'JAPAN', 'INDIA', 'THAILAND'),
          SUBPARTITION west VALUES 
             ('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'),
          SUBPARTITION other VALUES (DEFAULT))
      (PARTITION p1 VALUES LESS THAN (1000),
       PARTITION p2 VALUES LESS THAN (2500),
       PARTITION p3 VALUES LESS THAN (MAXVALUE));

8.1.3. Autres attributs de création de table

8.2. Modification des tables

ALTER TABLE nom_table ALLOCATE EXTENT
(SIZE 1M DATAFILE 'h:\oracle\oradata\ora9\users.dbf');

ALTER TABLE nom_table DEALLOCATE UNUSED KEEP n;

ALTER TABLE nom_table MOVE TABLESPACE nom_tbs_cible;

ALTER TABLE nom_table DROP COLUMN nom_col;

ALTER TABLE nom_table DROP (col1, col2, …);

ALTER TABLE nom_table SET UNUSED COLUMN nom_col;
ALTER TABLE nom_table SET UNUSED (col1, col2, …);

ALTER TABLE nom_table DROP UNUSED COLUMNS;

ANALYZE TABLE nom_table VALIDATE STRUCTURE [CASCADE][INTO nom_table_cible];

ANALYZE TABLE nom_table LIST CHAINED ROWS [INTO table_cible];

ANALYZE TABLE COMPUTE STATISTICS;
ANALYZE TABLE ESTIMATE STATISTICS SAMPLE n ROWS;
ANALYZE TABLE ESTIMATE STATISTICS SAMPLE n PERCENT;

8.3. Gestion des index

• unique, c'est à dire que chaque valeur de la clé n'apparaît qu'une fois
• non unique (option par défaut)
• à clé inversée. Cette option est utile quand les valeurs de l'index sont insérées en ordre croissant, ce qui conduirait à un arbre dégénéré, par manque de dispersion des valeurs. Le fait d'inverser les données octet par octet (54321, 64321, 74321 au lieu de 12345, 12346, 12347) améliore la dispersion des données, et donc les performances.
• basé sur une fonction, comme SUBSTR ou UPPER.

• Local prefixed
• Local non prefixed
• Global prefixed
• Global non prefixed

CREATE INDEX i ON t (a,b,c) REVERSE;

• des statistiques doivent impérativement avoir été calculées
• le paramètre d'initialisation QUERY_REWRITE_ENABLED doit être à TRUE
• le paramètre d'initialisation QUERY_REWRITE_INTEGRITY doit être à TRUSTED

ALTER INDEX nom_index UNUSABLE;

ALTER INDEX nom_index REBUILD;

ALTER INDEX RENAME ancien_nom TO nouveau_nom;

ALTER INDEX nom_index COALESCE;

ALTER INDEX nom_index MONITORING USAGE;

8.4. Les contraintes

ALTER TABLE nom_table MODIFY nom_colonne NULL/NOT NULL;

ALTER TABLE nom_table DISABLE CONSTRAINT NN_contrainte;

CREATE TABLE test (a INT CONSTRAINT PK_COL1 PRIMARY KEY USING INDEX TABLESPACE indx STORAGE(INITIAL 10K NEXT 20K));

ALTER TABLE nom_table ADD CONSTRAINT nom_contrainte UNIQUE(col) EXCEPTIONS INTO EXCEPTIONS;

ALTER TABLE nom_table ADD CONSTRAINT nom_contrainte CHECK (BONUS >0) DISABLE;

ALTER TABLE nom_table MODIFY CONSTRAINT nom_contrainte ENABLE/DISABLE;
ALTER TABLE nom_table ENABLE/DISABLE CONSTRAINT nom_contrainte;

ALTER TABLE nom_table DISABLE PRIMARY KEY CASCADE;

CREATE TABLE test (col1 int CONSTRAINT unic UNIQUE DEFERRABLE INITIALLY DEFERRED);