IOT et gestion de l'overflow

Version imprimable

10/02/2013, 21h17
carreau

IOT et gestion de l'overflow

bonjour à tous,

J'ai créé des IOT pour améliorer les perfs d'accès à des tables très peu souvent modifiées

Mais à l'occasion de cette création, sont apparues des tables SYS_IOT_OVER... que je n'arrive pas à manipuler facilement (difficile de changer les droits par exemple) et que je dois gérer de manière particulière (dans certaines parties de mon code applicatif).

Et il conseillé ou possible de créer des IOT sans condition d'OVERFLOW ?
Comment calculer le meilleur PCTHRESHOLD ? (taille en % au delà duquel les colonnes sont enregistrées dans cette table système de gestion de l'info séparée de l'index PK)

Merci pour votre expérience
A bientôt
11/02/2013, 09h45
pachot

Bonjour,

Citation:

et que je dois gérer de manière particulière (dans certaines parties de mon code applicatif).

Il s'agit de l'implémentation physique. Transparent pour l'appli !

Les colonnes en overflow peuvent être déterminées explicitement avec la clause INCLUDING. On peut y mettre les colonnes larges qui ne sont pas frequemment accédées, afin de réduire la taille de l'index.

Cordialement,
Franck.
12/02/2013, 07h16
carreau

comment se passer des tables SYS_IOT_OVER_... ?

Merci Franck pour ta réponse

Dans mon code applicatif, j'ai une procédure stockée qui liste toutes mes tables et tous les objets pour en contrôler les droits et pour les recréer de manière systématique (je sais ce n'est pas forcement très malin mais pour l'instant j'ai cette contrainte et je me sers de cette PS comme d'un utilitaire)

Je chercherais donc à créer des IOT sans SYS_IOT_OVER_... d'autant que mes tables organisées en index sont plutôt très légères
Est ce que PCthreshold=0 suffirait il ?

Et comment faire si j'ai besoin de changer de tablespace ces tables-système ou si je veux les copier dans un autre schéma et changer le propriétaire ?

J'imagine que c'est la meme chose pour les tables SYS_IOT_MAPPING_...

Bonne journée
Jerome
12/02/2013, 09h43
pachot

Bonjour,
La valeur par défaut de PCTTHRESHOLD, 50, est le maximum je pense. C'est une limitation de la structure d'un index: il faut pouvoir stocker 2 entrées au moins.
Les options de storage(comme le tablespace) peuvent être précisés dans la clause OVERFLOW.
Cordialement,
Franck.

je ne trouve pas d'info sur la manière de créer des IOT

Les autres tables d'un meme schéma qui sont déjà des IOT n'ont pas de table SYS_IOT_OVER_xxxx (ou je ne les vois pas meme en system ...)
et voici leur code DDL
Je comprends qu'on empeche la gestion de l'OVERFLOW

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
CREATE TABLE forms (
  cd_form       NUMBER        NOT NULL,
  nm_form       VARCHAR2(100) NULL,
  txt_form      VARCHAR2(255) NULL,
  filter_passes INTEGER       NULL,
  CONSTRAINT pk_forms PRIMARY KEY (
    cd_form
  )
)
  ORGANIZATION INDEX
  PCTTHRESHOLD   0
  OVERFLOW
    TABLESPACE ""
    PCTFREE   -1
    PCTUSED   -1
    INITRANS  -1
    MAXTRANS  -1
    STORAGE (
      INITIAL       0 K
      NEXT          0 K
      MINEXTENTS   -1
      MAXEXTENTS   -1
      PCTINCREASE  -1
      FREELISTS    -1
      FREELIST GROUPS -1
      BUFFER_POOL
    )
    NOLOGGING
/

Merci pour votre regard d'expert

12/02/2013, 22h30
pachot

Bonjour,
Exact, ici pas besoin d'overflow parce que ici la taille d'une entrée de l'index, vu la définition des colonnes, ne dépassera jamais les 50% de la taille d'un block.
Essayez par contre avec des varchar(4000):

Code:

ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces

Cordialement,
Franck.
19/02/2013, 04h42
carreau

[10g] IOT et gestion de l'OVERFLOW
Donc, pour calculer si ma table aura besoin d'un bloc overflow géré par oracle dans un bloc sys_iot_over_...,
il faut que je calcule la taille d'un tuple avec tous ses éléments et que j'y ajoute la taille potentielle d'un index (qu'on estime pouvoir atteindre 50% de la taille du bloc)

Ainsi, pour une instance dont la taille d'un bloc est de 8K, il suffit d'un varchar2(4000) et d'un blob (même si on en connait pas la taille par avance) et d'un index de taille potentielle aussi de 4K pour être quasiment sûr qu'oracle essayera d'ajouter une table système SYS_IOT_OVER_...
On pourra donc se poser la question d'un tablespace avec une taille particulière de bloc qui serait par exemple de 16K

Sur ce même excellent site developpez.com, j'ai trouvé un bout de code pl/sql pour connaître la taille d'un lob,

Citation:

http://sheikyerbouti.developpez.com/...=Chap9#L9.2.17

mais je ne comprends pas très bien à quelle table 'lob_table' il fait référence pour sélectionner un élément selon la valeur d'un champ 'key_value'
Code:

1 2 3 ... SELECT b_lob INTO lobd FROM lob_table WHERE key_value = 42; ...
et je ne comprends pas très bien le sens de ce champ (dans certains exemples cette valeur passe à 12 ou 21 comme pour le recopie du contenu d'un lob d'une table dans une autre table)

(Merci aux experts comme vous pour toutes leurs contributions et toute leur aide)
19/02/2013, 13h32
pachot

Avec un LOB, c'est un peu plus compliqué à calculer, vu qu'un LOB est stocké dans une segment à part s'il dépasse une certaine taille, un peu à la manière de l'overflow segment.
Mais je ne comprends plus le besoin de départ. Lorsqu'on définit une table comme IOT, il y a pas mal de choses à prendre en compte avant de se demander s'il est nécessaire d'avoir un overflow segment ou non. Quel ordre des colonnes, quels sont les index secondaires, etc.
22/02/2013, 08h09
carreau

IOT et gestion de l'overflow

Et bien le lien entre mon sujet IOT et LOB est simple,
j'aurais eu besoin de transformer/créer des IOT avec des BLOB
et je croyais que les LOB d'un IOT seraient stockés dans des blocs SYS_IOT_OVER_...

En suivant vos conseils d'analyse de la taille des tuples,
je me pose la question de limiter la taille de mes champs à (4K) la moitié de la taille d'un bloc (8K)
par exemple une table composée de :
- la PK numérique 1K
- une FK numérique 1K
- un champ texte VARCHAR2(1000) soit 1K
- un champ blob BLOB(1000) soit 1K
Cela est il correcte pour qu'il y ait suffisamment de place dans le reste d'un bloc (4K occupés sur les 8K de la taille par défaut soient 50%) pour l'indexation sur la clef primaire et l'indexation sur la clef étrangère (ou éventuellement la paire PK + FK) ?

Ou est ce que de toutes les manières, un LOB est forcement stocké dans un bloc à part et lié par un pointeur précisé dans le champ BLOB de ma table ?

Merci pour votre aide et vos explications,
je ne trouve toujours pas d'info / de doc quand à l'organisation et l'optimisation des IOTs
ni sur la manière de gérer les transferts de taille comme par exemple la copie d'un BLOB(4000) dans un BLOB(1000) (erreur de syntaxe quand aux différences de types)
22/02/2013, 11h32
Waldar

Une PK et une FK sur 1000 caractères ?
C'est vraiment trop long.

Pouvez-vous détailler un peu plus le besoin réel : script de la table de départ (vous pouvez renommer les objets mais conservez bien la logique), utilisation de cette table (les requêtes DML les plus courantes) et surtout qu'est-ce qui motive votre désir de la transformer en IOT.
22/02/2013, 16h21
pachot

Idem : Pouvez-vous détailler un peu plus le besoin réel

Les colonnes et leur tailles sont déterminées par le besoin métier. Ensuite, lorsqu'on a le modèle on peut voir ses détails d'implémentation physique.

Et pourquoi éviter un segment secondaire pour l'overflow ou pour les blob ?
4000 bytes, c'est déjà pas mal pour stocker un enregistrement ordinaire. Si c'est plus gros, il y a des colonnes larges qui ne sont pas souvent utilisées, du moins dans la clause where, et qui sont probablement mieux stockés à part.

structure des tables et idée/besoin

bonjour

Voici la structure des tables de départ

La première table régulière
et dont la colonne

Citation:

vbin BLOB

ne contient que trop peu d'info pour ne pas être supprimée
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 CREATE TABLE tab1 ( id_attribut NUMBER NOT NULL, vnum NUMBER NULL, vtxt VARCHAR2(4000) NULL, vdat DATE NULL, vbin BLOB NULL ) / PROMPT CREATE INDEX i_fk_tab1 CREATE INDEX i_fk_tab1 ON tab1 ( id_attribut ) TABLESPACE tab1_idx NOLOGGING / PROMPT ALTER TABLE tab1 ADD CONSTRAINT pk_tab1 PRIMARY KEY ALTER TABLE tab1 ADD CONSTRAINT pk_tab1 PRIMARY KEY ( id_attribut ) / PROMPT ALTER TABLE tab1 ADD CONSTRAINT fk_tab1 FOREIGN KEY ALTER TABLE tab1 ADD CONSTRAINT fk_tab1 FOREIGN KEY ( id_attribut ) REFERENCES attribut ( id_attribut ) /
Et voilà une seconde table déjà organisée en index
qui contient déjà une colonne

Citation:

vbin BLOB

et qui est conçue pour ranger et gérer des BLOB
Cette table ne gère pas d'overflow
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 PROMPT CREATE TABLE tab2 CREATE TABLE tab2 ( id_annexe NUMBER NOT NULL, nm_annexe VARCHAR2(50) NULL, vbin BLOB NULL, CONSTRAINT pk_tab2 PRIMARY KEY ( id_annexe ) ) ORGANIZATION INDEX PCTTHRESHOLD 0 OVERFLOW TABLESPACE "" PCTFREE -1 PCTUSED -1 INITRANS -1 MAXTRANS -1 STORAGE ( INITIAL 0 K NEXT 0 K MINEXTENTS -1 MAXEXTENTS -1 PCTINCREASE -1 FREELISTS -1 FREELIST GROUPS -1 BUFFER_POOL ) NOLOGGING /
Ces tables ont peu de lignes qui sont très peu souvent modifiées mais leurs infos sont très souvent consultées

Je souhaite optimiser leur accès
L'idée est de :
- déplacer les lignes avec un élément dans la colonne blob de la première table dans la seconde,
- supprimer la colonne blob de la première table lorsqu'il n'y aura plus d'élément dans celle là
- organiser la première table en index

Mais je suis confronté à un problème de type entre

Citation:

tab1.vbin BLOB(4000)

et

Citation:

tab2.vbin BLOB(125)

Je ne sais pas comment maitriser la recopie des éléments de la colonne plus large vers la colonne moins large en m'assurant de ne pas tronquer des infos

Et je ne sais pas comment faire gérer l'overflow ou mieux, m'assurer qu'il n'y en aura pas besoin pour limiter encore plus les temps d'accès