Partitionning d'index d'une PK [Résolu]

[farenheiit]

Bonjour,

Dans ma base décisionnelle Oracle 9208, j'ai des tables partitionnées (By LIST).

mes tables sont crées de la manière suivante:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
CREATE TABLE TDIM_CTR (
   CTR_ID               NUMBER                          NOT NULL,
   RPT_ID               NUMBER                          NOT NULL,
   CTR_CODE             CHAR(3),
   CTR_LIBELLE_COURT    VARCHAR2(30),
   CTR_LIBELLE_LONG     VARCHAR2(110),
   CTR_LIBELLE_ANG_COURT VARCHAR2(30),
   CTR_LIBELLE_ANG_LONG VARCHAR2(110),
   NOM_SCHEMA           VARCHAR2(20),
   constraint PK_TDIM_CTR PRIMARY KEY (CTR_ID, RPT_ID)
   USING INDEX TABLESPACE TB_PRISM_REP_V402_IDX)
   PARTITION BY list (NOM_SCHEMA)
	(PARTITION prt_DBO_DATA_DEC06  VALUES('DBO_DATA_DEC06'))
/

Comment faire pour que l'index généré par la définition de la clé primaire soit partitionné localement?

[orafrance]

Il faut le créer après la création de la table avec l'option LOCAL.

[farenheiit]

créer quoi? la PK ou l'index?

[orafrance]

les 2, la création de la PK crée automatiquement l'index

[farenheiit]

Dans ce cas c'est quoi la commande pour créer la PK de manière à ce que l'index crée soit partitionnée? Dois-je rajouter le mot LOCAL à la fin comme ceci?

Code :

1
2
3
 
ALTER TABLE TDIM_CTR
ADD constraint PK_TDIM_CTR PRIMARY KEY(CTR_ID, RPT_ID) LOCAL

[orafrance]

Lis la note 74224.1 de Metalink

[farenheiit]

j'ai pas accès à metalink

[orafrance]

tu crées la table, tu crées un index unique et tu crées la contrainte PK

[farenheiit]

ça marche pas:

je crée la table :

Code :

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE AAA_TDIM_CTR (
   CTR_ID               NUMBER                          NOT NULL,
   RPT_ID               NUMBER                          NOT NULL,
   CTR_CODE             CHAR(3),
   CTR_LIBELLE_COURT    VARCHAR2(30),
   CTR_LIBELLE_LONG     VARCHAR2(110),
   CTR_LIBELLE_ANG_COURT VARCHAR2(30),
   CTR_LIBELLE_ANG_LONG VARCHAR2(110),
   NOM_SCHEMA           VARCHAR2(20))
   PARTITION BY list (NOM_SCHEMA) (PARTITION prt_DBO_DATA_DEC06  VALUES('DBO_DATA_DEC06'))

=> OK

je crée l'index unique:

Code :

1
2
 
CREATE INDEX IDX_PK_AAA_TDIM_CTR ON AAA_TDIM_CTR(CTR_ID, RPT_ID) LOCAL

=> OK

je crée la PK:

Code :

1
2
3
 
ALTER TABLE AAA_TDIM_CTR
 ADD constraint PK_AAA_TDIM_CTR PRIMARY KEY (CTR_ID, RPT_ID)

=> NOK
j'obtiens l'erreur suivante:

Code :

ORA-01408: such COLUMN list already indexed

[orafrance]

essaye

Code :

1
2
ALTER TABLE AAA_TDIM_CTR
 ADD constraint PK_AAA_TDIM_CTR PRIMARY KEY (RPT_ID,CTR_ID)

[farenheiit]

ça marche mais le pb c'est qu' oracle m'empeche de supprimer l'index lié à la primary key que je viens de créer.

J'aurais donc 2 indexes à gérer sur chaque table partitionnée. ça va faire lourd.

Moi ce que je voulais c'était savoir s'il était possible de préciser au moment de la création de la PK le fait que l'index associé soit partitionné.

[orafrance]

La note dit ceci

How To Create Primary Key Partitioned Indexes:
==============================================

Example:

SQL> -- Create partitioned table TEST_A
SQL>
SQL> CREATE TABLE test_a (col1 number, col2 number, col3 varchar2(20))
2 PARTITION BY RANGE (col1, col2)
3 (partition part_test_a_1 values less than (10, 100),
4 partition part_test_a_2 values less than (20, 200),
5 partition part_test_a_3 values less than (30, 300),
6 partition part_test_a_4 values less than (40, 400));

Table created.


SQL> -- Create partitioned table TEST_B
SQL>
SQL> CREATE TABLE test_b (col1 number, col2 number, col3 varchar2(20))
2 PARTITION BY RANGE (col1, col2)
3 (partition part_test_b_1 values less than (10, 100),
4 partition part_test_b_2 values less than (20, 200),
5 partition part_test_b_3 values less than (30, 300),
6 partition part_test_b_4 values less than (40, 400));

Table created.


SQL> -- Create a non-unique local partitioned index, IX_TEST_A,
SQL> -- on TEST_A
SQL>
SQL> CREATE INDEX ix_test_a ON test_a(col1, col2)
2 LOCAL
3 (partition ix_test_a_1,
4 partition ix_test_a_2,
5 partition ix_test_a_3,
6 partition ix_test_a_4);

Index created.


SQL> -- Create a unique global partitioned index, IX_TEST_B,
SQL> -- on TEST_B
SQL>
SQL> CREATE UNIQUE INDEX ix_test_b1 ON test_b(col1, col2)
2 GLOBAL PARTITION BY RANGE (col1, col2)
3 (partition ix_test_b1_1 values less than (20, 200),
4 partition ix_test_b1_2 values less than (maxvalue, maxvalue));

Index created.


SQL> -- Add a primary key constraint, PK_TEST_A, to TEST_A
SQL>
SQL> ALTER TABLE test_a ADD CONSTRAINT pk_test_a
2 PRIMARY KEY (col2, col1);

Table altered.


SQL> -- Attempt to drop index IX_TEST_A; note the following error...
SQL>
SQL> DROP INDEX ix_test_a;
drop index ix_test_a
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key


SQL> -- Attempt to create a second index, IX_TEST_B2 on TEST_B
SQL> -- using the same columns used to partition IX_TEST_B1.
SQL> -- Note the following error...
SQL>
SQL> CREATE INDEX ix_test_b2 ON test_b(col1, col2)
2 LOCAL;
create index ix_test_b2 on test_b(col1, col2)
*
ERROR at line 1:
ORA-01408: such column list already indexed


SQL> -- Add a primary key constraint, PK_TEST_B, to TEST_B
SQL>
SQL> ALTER TABLE test_b ADD CONSTRAINT pk_test_b
2 PRIMARY KEY (col1, col2);

Table altered.


SQL> -- Attempt to drop index IX_TEST_B1; note the following error...
SQL>
SQL> DROP INDEX ix_test_b1;
drop index ix_test_b1
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key


SQL> -- A listing of the indexes and their associated partitions.
SQL>
SQL> SELECT index_name, partition_name, status
2 FROM user_ind_partitions
3 ORDER BY index_name, partition_name;

INDEX_NAME PARTITION_NAME STATUS
----------- --------------- --------
IX_TEST_A IX_TEST_A_1 USABLE
IX_TEST_A IX_TEST_A_2 USABLE
IX_TEST_A IX_TEST_A_3 USABLE
IX_TEST_A IX_TEST_A_4 USABLE

IX_TEST_B1 IX_TEST_B1_1 USABLE
IX_TEST_B1 IX_TEST_B1_2 USABLE

6 rows selected.


SQL> -- Drop the primary key constraint from TEST_A
SQL>
SQL> ALTER TABLE test_a DROP CONSTRAINT pk_test_a;

Table altered.


SQL> -- Drop the primary key constraint from TEST_B
SQL>
SQL> ALTER TABLE test_b DROP CONSTRAINT pk_test_b;

Table altered.


SQL> -- A listing of the indexes and their associated partitions.
SQL> -- Note that while IX_TEST_A, the non-unique local partitioned
SQL> -- index, remains and has a status of USABLE.
SQL> -- IX_TEST_B, the unique global partitioned index, has been
SQL> -- dropped.
SQL>
SQL> SELECT index_name, partition_name, status
2 FROM user_ind_partitions
3 ORDER BY index_name, partition_name;

INDEX_NAME PARTITION_NAME STATUS
--------------- -------------- --------

IX_TEST_A IX_TEST_A_1 USABLE
IX_TEST_A IX_TEST_A_2 USABLE
IX_TEST_A IX_TEST_A_3 USABLE
IX_TEST_A IX_TEST_A_4 USABLE


The primary key uses the underlying index if the index is built using
the same columns as defined in the primary key. This is consistent
without regard to whether the index was created as a unique or non-unique
index, or if it is a global or local partioned index. It is important
to note that while in the example a primary key was established on a
non-unique index, this will only occur if the values within the index
are in fact unique. Attempting to enable a primary key constraint when
duplicate values are present within the index will result in the
following error:

"ORA-02437: cannot enable (STEELY.PK_TEST_B) - primary key violated."

Two indexes cannot be created using the same ordered columns. This was
demonstrated above when attempting to create a second index on table
TEST_B. This resulted in the following error:

"ORA-01408: such column list already indexed."

However, changing the order of the columns will permit the creation of
additional indexes using the same columns.

Contrary to the previous note, the column order for index IX_TEST_A and
the definition for the primary key PK_TEST_A were reversed. Yet the
primary key still used IX_TEST_A as the underlying index.

When dropping a primary key constraint from a table, the corresponding
index is also dropped if the index was created as a UNIQUE index. This
behavior is consistent for both LOCAL as well as GLOBAL partitioned
indexes.

To receive the full benefits of partitioning, users/DBA must use the
STORAGE clause when creating partitioned tables/indices.

[orafrance]

et si tu désactives la contrainte pour dropper l'index qu'elle a créée ?

Edit : c'est une anerie j'essayerai cet après-midi

[farenheiit]

Hé Hé bien vu

ça me met l'erreur suivante:

Code :

ORA-01418: specified INDEX does NOT exist

mais l'index est tout de même supprimé

merci

[orafrance]

Apparemment ce n'est pas possible si la partition n'est pas un sous-ensemble de la PK.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
  1  CREATE TABLE TDIM_CTR (
  2     CTR_ID               NUMBER                          NOT NULL,
  3     RPT_ID               NUMBER                          NOT NULL,
  4     CTR_CODE             CHAR(3),
  5     CTR_LIBELLE_COURT    VARCHAR2(30),
  6     CTR_LIBELLE_LONG     VARCHAR2(110),
  7     CTR_LIBELLE_ANG_COURT VARCHAR2(30),
  8     CTR_LIBELLE_ANG_LONG VARCHAR2(110),
  9     NOM_SCHEMA           VARCHAR2(20),
 10     constraint PK_TDIM_CTR PRIMARY KEY (CTR_ID, RPT_ID)
 11     USING INDEX LOCAL)
 12     PARTITION BY list (NOM_SCHEMA)
 13*    (PARTITION prt_DBO_DATA_DEC06  VALUES('DBO_DATA_DEC06'))
 14  /
CREATE TABLE TDIM_CTR (
*
ERROR at line 1:
ORA-14039: partitioning COLUMNS must form a subset of KEY COLUMNS of a UNIQUE INDEX

Note le USING INDEX LOCAL tout simplement

et c'est confirmé ici avec le pourquoi de la chose : http://asktom.oracle.com/pls/asktom/...18741227819236

the primary key index can ONLY be local IF and ONLY IF, the primary key is in fact the
(or part of the) partition key.

[farenheiit]

Pourquoi je veux créer un index local?

parceque mes tables chaque mois se voient rajouter une nouvelle partition. La clé de partition de la table correspond à la colonne NOM_SCHEMA qui change chaque mois à chaque fois que j'effectue un chargement de ma base décisionnelle.
J'ai beaucoup de requêtes effectuées sur ces tables avec en clause where la clé primaire. Donc je me dis que si l'optimiseur passe par l'index il risque de parcourir toutes les valeurs même celles corespondants à des lignes d'autres partitions? NON?

De plus quand je drop mes partitions, les indexes de clés primaires deviennent unusable=> je suis obligé de les reconstruire à chaque fois

[orafrance]

j'ai édité mon message, j'avais pas vu que c'était pas la PK la clé de partitionnement

Envoyé par farenheiit

De plus quand je drop mes partitions, les indexes de clés primaires deviennent unusable=> je suis obligé de les reconstruire à chaque fois

UPDATE GLOBAL INDEXES évite ce désagrément au prix d'une reconstruction automatique de l'index

[farenheiit]

the primary key index can ONLY be local IF and ONLY IF, the primary key is in fact the
(or part of the) partition key.

donc si je rajoute le champ NOM_SCHEMA (qui est ma clé de partitionnement) aux colonnes formant la PK je pourrai utiliser le USING INDEX LOCAL lors de la création de la PK???

[orafrance]

non, faut ajouter la PK dans la clé de partitionnement

La clé primaire peut être locale SI et SEULEMENT SI, la clé primaire est (ou une partie de) la clé de partitionnement.

[farenheiit]

Ah oui d'accord c'est l'inverse

bon ba tan pi

merci en tout cas