Alle Constraints, Keys, Indices löschen und wieder anlegen

[Truus]

Hallo liebe Community,

folgendes Problem:

Ich habe diverse Spalten,
für die ich den Datentyp ändern muss und zusätzlich müssen einige Spalten von NULLABLE auf NOT NULL gesetzt werden.
Bei einem relativ unüberschaubaren Schema gar nicht so leicht.
Es gibt unzählige Foreign Key-Beziehungen, Primary Keys und auch Indices. Hin und wieder auch unmögliche Default Constraints auf NULLABLE Spalten.
Zum verrückt werden.

Folgende Idee kam mir:

- Ich lösche alle Constraints, alle Keys und alle Indices
- Ich führe alle meine DDL Änderungen aus
- Ich lege wieder alles an

Jetzt bräuchte ich Hilfe,
ich hab mir diverse Views geschrieben
um möglichst alle Objekte zu finden und zu droppen und gleichzeitig per C# Programm in ein DataSet wegzuschreiben für eine spätere Wiederanlage.
Leider erwische ich nicht alles und es kommt immer wieder zu Fehlern beim erneuten Anlegen der Keys und Constraints.

Gibt es eine rein SQL-seitige Lösung für mein Vorhaben,
könnt ihr mir helfen ein Skript zu bauen
um alle Constraints, alle Keys und alle Indices zu löschen und temporär zu speichern
und wieder anzulegen auf Grundlage der temporär gespeicherten Infos.


*Sorry for the long post, hoping 4 help*

Gruß

Alex

 

[Distrilec]

Gehst du wirklich davon aus, dass wenn du Datentypen änderst die Indizes immer noch den gleichen Effekt haben werden wie vorher?
mMn. Solltest du sämtliche Änderungen manuell durchgehen und Indizes für die bestehenden Anfragen anpassen. (Die du ja auch alle anpassen musst)
Andernfalls kann es gut und gerne sein, dass deine Indizes keinen Effekt mehr haben werden... Oder zumindest nichtmehr den gewünschten...

 

[Truus]

Gehst du wirklich davon aus, dass wenn du Datentypen änderst die Indizes immer noch den gleichen Effekt haben werden wie vorher?
mMn. Solltest du sämtliche Änderungen manuell durchgehen und Indizes für die bestehenden Anfragen anpassen. (Die du ja auch alle anpassen musst)
Andernfalls kann es gut und gerne sein, dass deine Indizes keinen Effekt mehr haben werden... Oder zumindest nichtmehr den gewünschten...

Eine manuelle Abhandlung kommt nicht in Frage, da es sich um mehrere 100 Tabellen und Spalten handelt,
da lediglich eine Änderung der Datentypen von FLOAT auf BIGINT durchgeführt wird
bzw. von FLOAT auf NUMBER(38,0) wird die Funktionalität der Indices eher noch verbessert,
da ein Index auf Float Spalten eher unsinnig bzw. unperformant ist

 

[ukulele]

Also eine Spalte von NULL auf NOT NULL zu ändern sollte mit Constraints und Indizes aber eben ohne NULL Werte möglich sein.

Den Rest könnte man eventuell per dynamischem SQL abbilden aber ganz ehrlich, bis ich das geschrieben und getestet habe und es alle Fälle abdeckt habe ich vermutlich in 100 Fällen grade meine Befehle mit Copy Paste zusammengebaut UND ich habe dabei sehr viel mehr Überblick.

 

[Truus]

So könnte es gehen:

select 'alter table '
  || table_name
  || ' drop constraint '
  || constraint_name
  ||';' constraint_disable
from all_constraints
where constraint_type = 'R'
and r_constraint_name in
(
  select constraint_name
  from all_constraints
  where constraint_type in ('P', 'U')
  and table_name in ('LIEFERUNG','LIEFPOS')
);

select  'alter table ' || t1_table_name
  || ' add constraint ' || t1_constraint_name
  || ' foreign key (' || t1_column_names || ')'
  || ' references ' || t2_table_name
  || '(' || t2_column_names || ');' FK_script
  from
  (select a.table_name t1_table_name
  , a.constraint_name t1_constraint_name
  , b.r_constraint_name t2_constraint_name
  -- Concatenate columns to handle composite
  -- foreign keys [handles up to 5 columns]
  , max(decode(a.position, 1,
  a.column_name,NULL)) ||
  max(decode(a.position, 2,', '||
  a.column_name,NULL)) ||
  max(decode(a.position, 3,', '||
  a.column_name,NULL)) ||
  max(decode(a.position, 4,', '||
  a.column_name,NULL)) ||
  max(decode(a.position, 5,', '||
  a.column_name,NULL))
  t1_column_names
  from user_cons_columns a
  , user_constraints b
  where a.constraint_name = b.constraint_name
  and b.constraint_type = 'R'
  group by a.table_name
  , a.constraint_name
  , b.r_constraint_name
  ) t1,
  (select a.constraint_name t2_constraint_name
  , a.table_name t2_table_name
  -- Concatenate columns for PK/UK referenced
  -- from a composite foreign key
  , max(decode(a.position, 1,
  a.column_name,NULL)) ||
  max(decode(a.position, 2,', '||
  a.column_name,NULL)) ||
  max(decode(a.position, 3,', '||
  a.column_name,NULL)) ||
  max(decode(a.position, 4,', '||
  a.column_name,NULL)) ||
  max(decode(a.position, 5,', '||
  a.column_name,NULL))
  t2_column_names
  from user_cons_columns a, user_constraints b
  where a.constraint_name = b.constraint_name
  and b.constraint_type in ( 'P', 'U' )
  group by a.table_name
  , a.constraint_name ) t2
where t1.t2_constraint_name = t2.t2_constraint_name
  and t2.t2_table_name in ('LIEFERUNG','LIEFPOS');

Da kommen 2 Skripte raus...
aber vielleicht mache ich es wirklich mit der Hand am Arm
manuell

 

[Distrilec]

Wie @ukulele auch schon sagte... Alle Möglichkeiten in Betracht zu ziehen dauert wahrscheinlich genauso lange (wenn nicht sogar länger), als es einfach per Hand zu machen...
Da du anscheinend Oracle laufen hast...

select * from all_indexes; --Alle Indizes
select * from all_ind_columns; --Alle indizierten Spalten
select * from all_ind_expressions; --Column_Expression liefert dir besondere Ausdrücke... Bspw. die Funktion eines function-based Index

Dann bekommst du mal ein Gefühl dafür, was es alles für Möglichkeiten gibt...
(Du solltest das ganze auf ein Schema begrenzen... Indizes von System-Usern (SYS, SYSTEM, etc.) bitte nicht anpacken!)