Au Pied Levé - À Main Levée
I-3.2.7. OSMOSE SQL CREATE_IDX
par , 01/04/2020 à 09h25 (216 Affichages)
■ CREATE_IDXAPL-AML est une monographie fragmentée en plusieurs billets pour des raisons de volume.
Un billet SYNOPSIS et un billet SOMMAIRE agrègent tous les billets du blog via des liens hypertextes.
■ ■ ■ SOMMAIRE DU BILLET ■ ■ ■
- CREATE_IDX
SQL de création des index des 93 tables essentielles de la BDD "osmose".
L’application gérant un historique de 4 années et l’année n+1, certaines tables existent en 6 exemplaires. Le sql complet compte 205 tables, soit environ 168.000 caractères. Mais un billet étant limité à 65.536 caractères, le présent sql ne conserve que les 93 tables essentielles.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351{------------------------------------------------------------------------------} { CREATE_IDX } {------------------------------------------------------------------------------} { sql de précaution en cas de run accidentel } {------------------------------------------------------------------------------} select rien from rien; {==============================================================================} { ac (académies) -----------------------------------------------------------} { act (actions de formation KHEOPS) -----------------------------------------} { af (actions de formation) ------------------------------------------------} { am (agglomérations multi-communales) -------------------------------------} { bb (bilans budgétaires) --------------------------------------------------} { ca (certificats administratifs) ------------------------------------------} { caf (cellule académique formation) ----------------------------------------} { (extraction FILE MAKER pour MàJ des codes GAIA de la table "af") ------} { (objectif -> Bilans de formation DESCO) -------------------------------} { cde (commande) ------------------------------------------------------------} { cde_l (commande ligne) ------------------------------------------------------} { cdf (congés de formation) -------------------------------------------------} { cf (candidatures formation) ----------------------------------------------} { ci (candidatures individualisées) ----------------------------------------} { cl (communes limitrophes) ------------------------------------------------} { cm (communes) ------------------------------------------------------------} { cp (codes postaux) -------------------------------------------------------} { dc (décisions candidatures) ----------------------------------------------} { df (disciplines de formation) --------------------------------------------} { dg (districts géographiques) ---------------------------------------------} { di_m (disciplines poste/recrutement) ---------------------------------------} { (extraction disciplines EPP pour mise à jour de la table "drdp") } { div (divisions) -----------------------------------------------------------} { dl (dates et lieux) ------------------------------------------------------} { dm (disciplines-matières) ------------------------------------------------} { doc (documentation OSMOSE : views, per, ace, shl, sql) --------------------} { dp (départements) --------------------------------------------------------} { drdp (disciplines de recrutement/disciplines de poste) ---------------------} { ds (discipline-spécialité - idem table nnsp) -----------------------------} { et (établissements) ------------------------------------------------------} { et_di (établissements DI) ---------------------------------------------------} { et_nt (établissements néo-titulaires) ---------------------------------------} { fd (fonctions-disciplines EPP) -------------------------------------------} { ff (fiches formateurs) ---------------------------------------------------} { fo (finalités-objectifs) -------------------------------------------------} { gaia (candidatures GAIA) ---------------------------------------------------} { gf (grades/fonctions) ----------------------------------------------------} { gr (grades) --------------------------------------------------------------} { gr_m (grades) --------------------------------------------------------------} { (extraction grades EPP pour mise à jour de la table "gr") } { gs (grades statutaires) --------------------------------------------------} { hbt (habilitations basse tension) -----------------------------------------} { hsa (heures supplémentaires effectives) -----------------------------------} { hse (heures supplémentaires effectives) -----------------------------------} { if (interventions de formation) ------------------------------------------} { ik (interventions khéops) ------------------------------------------------} { (interface pour création d'interventions KHEOPS depuis la table "if_0")} { (voir ifTOik_0.ace, el_ik_0.ace) --------------------------------------} { km (distancier kilométrique) ---------------------------------------------} { ln (logname) -------------------------------------------------------------} { lp (line printer) --------------------------------------------------------} { mf (macro-formations) ----------------------------------------------------} { mp (média/post) ----------------------------------------------------------} { na (nomenclature analytique) ---------------------------------------------} { nb (nomenclature budgétaire) ---------------------------------------------} { ne (natures établissements) ----------------------------------------------} { neo (néo-titulaires) ------------------------------------------------------} { nnco (nomenclature nationale : contenus de formation) ----------------------} { nndc (nomenclature nationale : décisions candidatures) ---------------------} { nndo (nomenclature nationale : domaines de formation) ----------------------} { nnfo (nomenclature nationale : forme de l'organisation) --------------------} { nnmo (nomenclature nationale : modalité de l'organisation) -----------------} { nnni (nomenclature nationale : niveau d'initiative) ------------------------} { nnob (nomenclature nationale : objectifs de formation) ---------------------} { nnpc (nomenclature nationale : public cible) -------------------------------} { nnsp (nomenclature nationale : spécialités - idem table ds) ----------------} { nntc (nomenclature nationale : type de candidature) ------------------------} { nntf (nomenclature nationale : type de formation) --------------------------} { nntmp (nomenclature nationale : temporaire) ---------------------------------} { omp (OM permanents) -------------------------------------------------------} { pa (priorités académiques) -----------------------------------------------} { pdf (parcours de formation) -----------------------------------------------} { pep4 (pep_4) ---------------------------------------------------------------} { pf (personnel formation) -------------------------------------------------} { piaf (procédure d'inscription aux actions de formation) --------------------} { pn (priorités nationales) ------------------------------------------------} { rp (répartitions pédagogiques des certificats administratifs) ------------} { sa (situations administratives -------------------------------------------} { ser (services) ------------------------------------------------------------} { sf (stages de formation) -------------------------------------------------} { sg (services gestionnaires) ----------------------------------------------} { sh (suivi historique) ----------------------------------------------------} { sys_c (colonnes) ------------------------------------------------------------} { sys_t (tables) --------------------------------------------------------------} { sysmenus (system catalog) -----------------------------------------------} { sysmenuitems (system catalog) -----------------------------------------------} { t_cf (temporaire candidatures formation) -----------------------------------} { t_cm (typologie communes) --------------------------------------------------} { t_ne (typologie nature établissement) --------------------------------------} { tbb (temporaire bilans budgétaires) ---------------------------------------} { tmp (temporaire) ----------------------------------------------------------} { ts (transports-séjours) --------------------------------------------------} { ts_t (transports-séjours temporaire) ---------------------------------------} { tsa (transport-séjour animateur) ------------------------------------------} { tsdp (transport-séjour dépense prévisionnelle) -----------------------------} { tss_t (transport-séjour stagiaire temporaire) -------------------------------} { uaa (table nationale des établissements) ----------------------------------} { uf (unités de formation) -------------------------------------------------} { ut (unités de traitement) ------------------------------------------------} { zep (zone d'éducation prioritaire) ----------------------------------------} {==============================================================================} { ac (académies) -----------------------------------------------------------} lock table ac in exclusive mode; { drop index ac_c_academie; } create unique cluster index ac_c_ac on ac (c_ac); alter index ac_c_ac to cluster; unlock table ac; { act (actions de formations KHEOPS) ----------------------------------------} lock table act in exclusive mode; { drop index act_tri; drop index act_staco; } create unique cluster index act_tri on act (chap, artx, staco); alter index act_tri to cluster; create unique index act_staco on act (staco); unlock table act; { af (actions de formation) ------------------------------------------------} lock table af in exclusive mode; { drop index af_c_af; drop index af_c_mf; drop index af_c_df; drop index af_logname; drop index af_c_nndo; drop index af_c_kheops; } create unique cluster index af_c_af on af (c_af); alter index af_c_af to cluster; create index af_c_mf on af (c_mf); create index af_c_df on af (c_df); create index af_logname on af (logname); create index af_c_nndo on af (c_nndo); create index af_c_kheops on af (c_kheops); unlock table af; { am (agglomérations multi-communales) -------------------------------------} lock table am in exclusive mode; { drop index am_c_am; } create unique cluster index am_c_am on am (c_am); unlock table am; { ca (certificats administratifs) ------------------------------------------} lock table ca in exclusive mode; { drop index ca_n_ca; drop index ca_c_df; drop index ca_c_af; drop index ca_c_sf; } create unique cluster index ca_n_ca on ca (n_ca); alter index ca_n_ca to cluster; create index ca_c_df on ca (c_df); create index ca_c_af on ca (c_af); create index ca_c_sf on ca (c_sf); unlock table ca; { caf (cellule académique formation) ----------------------------------------} { (extraction FILE MAKER pour MàJ des codes GAIA de la table "af") ------} { (objectif -> Bilans de formation DESCO) -------------------------------} lock table caf in exclusive mode; { drop index caf_c_sf; drop index caf_c_af; } create unique cluster index caf_c_sf on caf (c_sf); alter index caf_c_sf to cluster; create index caf_c_af on caf (c_af); unlock table caf; { cde (commande) ------------------------------------------------------------} lock table cde in exclusive mode; { drop index cde_n_cde; } create unique cluster index cde_n_cde on cde (n_cde); alter index cde_n_cde to cluster; unlock table cde; { cde_l (commande ligne) ------------------------------------------------------} lock table cde_l in exclusive mode; { drop index cde_l_n_cde; } create unique cluster index cde_l_n_cde on cde_l (n_cde); alter index cde_l_n_cde to cluster; unlock table cde_l; { cdf (conges de formation) -------------------------------------------------} lock table cdf in exclusive mode; { drop index cdf_n_pf; } create unique cluster index cdf_n_pf on cdf (n_pf); alter index cdf_n_pf to cluster; unlock table cdf; { cf (candidatures formation) ----------------------------------------------} lock table cf in exclusive mode; { drop index cf_tri; drop index cf_cle; drop index cf_cle_sf; drop index cf_n_pf; drop index cf_c_sf; drop index cf_c_uf; } create unique cluster index cf_tri on cf (nom_cf, n_pf, initiative, t_cf, rang); alter index cf_tri to cluster; create unique index cf_cle on cf (n_pf, initiative, t_cf, rang); create unique index cf_cle_sf on cf (n_pf, c_sf); create index cf_n_pf on cf (n_pf); create index cf_c_sf on cf (c_sf); create index cf_c_uf on cf (c_uf); unlock table cf; { ci (candidatures individualisées) ----------------------------------------} lock table ci in exclusive mode; { drop index ci_tri; drop index ci_n_pf; drop index ci_c_sf; } create unique cluster index ci_tri on ci (n_pf, c_sf, groupe, d_debut); alter index ci_tri to cluster; create index ci_n_pf on ci (n_pf); create index ci_c_sf on ci (c_sf); unlock table ci; { cl (communes limitrophes) ------------------------------------------------} lock table cl in exclusive mode; { drop index cl_tri; drop index cl_c_cm; drop index cl_c_cl; } create cluster index cl_tri on cl (c_cm, c_cl); alter index cl_tri to cluster; create index cl_c_cm on cl (c_cm); create index cl_c_cl on cl (c_cl); unlock table cl; { cm (communes) ------------------------------------------------------------} lock table cm in exclusive mode; { drop index cm_tri; drop index cm_cle; drop index cm_c_cm; drop index cm_c_dp; drop index cm_c_dg; drop index cm_c_am; } create cluster index cm_tri on cm (c_dp, l_cm); alter index cm_tri to cluster; create index cm_cle on cm (c_dp, c_dg); create unique index cm_c_cm on cm (c_cm); create index cm_c_dp on cm (c_dp); create index cm_c_dg on cm (c_dg); create index cm_c_am on cm (c_am); unlock table cm; { cp (codes postaux) -------------------------------------------------------} lock table cp in exclusive mode; { drop index cp_c_cp; drop index cp_c_cm; } create unique cluster index cp_c_cp on cp (c_cp); alter index cp_c_cp to cluster; create index cp_c_cm on cp (c_cm); unlock table cp; { dc (décisions candidatures) ----------------------------------------------} lock table dc in exclusive mode; { drop index dc_c_decision; } create unique cluster index dc_c_decision on dc (c_decision); alter index dc_c_decision to cluster; unlock table dc; { df (disciplines de formation) --------------------------------------------} lock table df in exclusive mode; { drop index df_c_df; } create unique cluster index df_c_df on df (c_df); alter index df_c_df to cluster; unlock table df; { dg (districts géographiques) ---------------------------------------------} lock table dg in exclusive mode; { drop index dg_tri; drop index dg_c_dg; drop index dg_c_dp; } create unique cluster index dg_tri on dg (c_dp, c_dg); alter index dg_tri to cluster; create unique index dg_c_dg on dg (c_dg); create index dg_c_dp on dg (c_dp); unlock table dg; { di_m (disciplines poste/recrutement) ---------------------------------------} { (extraction disciplines EPP pour mise à jour de la table "drdp") } lock table di_m in exclusive mode; { drop index di_m_c_di; } create unique cluster index di_m_c_di on di_m_c_di (c_di); alter index di_m_c_di to cluster; unlock table di_m; { div (divisions) -----------------------------------------------------------} lock table div in exclusive mode; { drop index div_c_div; drop index div_c_et; } create unique cluster index div_c_div on div (c_div); alter index div_c_div to cluster; create index div_c_et on div (c_et); unlock table div; { dl (dates et lieux) ------------------------------------------------------} lock table dl in exclusive mode; { drop index dl_tri; drop index dl_c_sf; drop index dl_c_et; } create unique cluster index dl_tri on dl (c_sf, d_debut, c_et); alter index dl_tri to cluster; create index dl_c_sf on dl (c_sf); create index dl_c_et on dl (c_et); unlock table dl; { dm (disciplines/matières) ------------------------------------------------} lock table dm in exclusive mode; { drop index dm_c_dm; } create unique cluster index dm_c_dm on dm (c_dm); alter index dm_c_dm to cluster; unlock table dm; { doc (documentation OSMOSE : views per, ace, shl, sql) ---------------------} lock table doc in exclusive mode; { drop index doc_tri; drop index doc_c_ut; } create unique cluster index doc_tri on doc (cd, ls); alter index doc_tri to cluster; create index doc_c_ut on doc (c_ut); unlock table doc; { dp (départements) --------------------------------------------------------} lock table dp in exclusive mode; { drop index dp_cle; drop index dp_c_dp; drop index dp_c_academie; } create unique cluster index dp_cle on dp (c_dp, c_dt); alter index dp_cle to cluster; create index dp_c_dp on dp (c_dp); create index dp_c_academie on dp (c_academie); unlock table dp; { drdp (disciplines de recrutement/disciplines de poste) ---------------------} lock table drdp in exclusive mode; { drop index drdp_c_drdp; } create unique cluster index drdp_c_drdp on drdp (c_drdp); alter index drdp_c_drdp to cluster; unlock table drdp; { ds (discipline-spécialité - idem table nnsp) -----------------------------} lock table ds in exclusive mode; { drop index ds_c_ds; } create unique cluster index ds_c_ds on ds (c_ds); alter index ds_c_ds to cluster; unlock table ds; { et (établissements) ------------------------------------------------------} lock table et in exclusive mode; { drop index et_tri; drop index et_c_et; drop index et_c_ne; drop index et_c_dp; drop index et_c_cm; } create unique cluster index et_tri on et (c_academie, c_et); alter index et_tri to cluster; create unique index et_c_et on et (c_et); create index et_c_ne on et (c_ne); create index et_c_dp on et (c_dp); create index et_c_cm on et (c_cm); unlock table et; { et_di (établissements néo-titulaires) ---------------------------------------} lock table et_di in exclusive mode; { drop index et_c_et; } create unique cluster index et_di_c_et on et_di (c_et); alter index et_di_c_et to cluster; unlock table et_di; { et_nt (établissements néo-titulaires) ---------------------------------------} lock table et_nt in exclusive mode; { drop index et_c_et; } create unique cluster index et_nt_c_et on et_nt (c_et); alter index et_nt_c_et to cluster; unlock table et_nt; { fd (fonctions EPP) -------------------------------------------------------} lock table fd in exclusive mode; { drop index fd_c_fd; } create unique cluster index fd_c_fd on fd (c_fd); alter index fd_c_fd to cluster; unlock table fd; { ff (fiches formateurs) ---------------------------------------------------} lock table ff in exclusive mode; { drop index ff_n_pf; } create unique cluster index ff_n_pf on ff (n_pf); unlock table ff; { fo (finalités/objectifs) -------------------------------------------------} lock table fo in exclusive mode; { drop index fo_tri; drop index fo_c_fo; } create unique cluster index fo_tri on fo (t_fo, c_fo); alter index fo_tri to cluster; create unique index fo_c_fo on fo (c_fo); unlock table fo; { gaia (candidatures GAIA) ---------------------------------------------------} lock table gaia in exclusive mode; { drop index gaia_numen; } create unique index gaia_numen on gaia (numen); unlock table gaia; { gf (grades/fonctions) ----------------------------------------------------} lock table gf in exclusive mode; { drop index gf_c_gf; } create unique cluster index gf_c_gf on gf (c_gf); alter index gf_c_gf to cluster; unlock table gf; { gr (grades) --------------------------------------------------------------} lock table gr in exclusive mode; drop index gr_cle; drop index gr_c_gr; drop index gr_c_gf_e; drop index gr_c_gf_a; create unique cluster index gr_c_gr on gr (c_gr); alter index gr_c_gr to cluster; create unique index gr_cle on gr (c_gr, m_gr); create index gr_c_gf_a on gr (c_gf_a); create index gr_c_gf_e on gr (c_gf_e); unlock table gr; { gr_m (grades) --------------------------------------------------------------} { (extraction grades EPP pour mise à jour de la table "gr") } lock table gr_m in exclusive mode; { drop index gr_m_n_gr; drop index gr_m_c_gr; } create unique index gr_m_n_gr on gr_m (n_gr); create unique cluster index gr_m_c_gr on gr_m (c_gr); alter index gr_m_c_gr to cluster; unlock table gr_m; { gs (grades statutaires) --------------------------------------------------} lock table gs in exclusive mode; { drop index gs_c_gs; } create unique cluster index gs_c_gs on gs (c_gs); alter index gs_c_gs to cluster; unlock table gs; { hbt (habilitations basse tension) -----------------------------------------} lock table hbt in exclusive mode; { drop index hbt_n_pf; } create unique cluster index hbt_n_pf on hbt (n_pf); alter index hbt_n_pf to cluster; unlock table hbt; { hsa (heures supplémentaires années) ---------------------------------------} lock table hsa in exclusive mode; { drop index hsa_tri; drop index hsa_c_af; drop index hsa_n_pf; } create unique cluster index hsa_tri on hsa (n_pf, c_af, n_bordereau); alter index hsa_tri to cluster; create index hsa_c_af on hsa (c_af); create index hsa_n_pf on hsa (n_pf); unlock table hsa; { hse (heures supplémentaires effectives) -----------------------------------} lock table hse in exclusive mode; { drop index hse_tri; drop index hse_c_sf; drop index hse_n_pf; } create unique cluster index hse_tri on hse (n_pf, c_sf, c_ac, mm_hse, aa_hse, n_bordereau); alter index hse_tri to cluster; create index hse_c_sf on hse (c_sf); create index hse_n_pf on hse (n_pf); unlock table hse; { if (interventions de formation) ------------------------------------------} lock table if in exclusive mode; { drop index if_cle_om; drop index if_n_pf; drop index if_c_sf; drop index if_c_et; } create unique cluster index if_cle_om on if (n_pf, c_sf, d_debut, pm_fin, am_debut, d_fin, c_et, animateurs); alter index if_cle_om to cluster; create index if_n_pf on if (n_pf); create index if_c_sf on if (c_sf); create index if_c_et on if (c_et); unlock table if; { ik (interventions khéops) ------------------------------------------------} { (interface pour création d'interventions KHEOPS depuis la table "if_0")} { (voir ifTOik_0.ace, el_ik_0.ace) --------------------------------------} lock table ik in exclusive mode; { drop index ik_cle_om; drop index ik_cle_el; drop index ik_n_pf; drop index ik_c_sf; drop index ik_c_et; } create unique cluster index ik_cle_om on ik (n_pf, c_sf, d_debut, pm_fin, am_debut, d_fin, c_et); alter index ik_cle_om to cluster; create unique index ik_cle_el on ik (n_pf, c_sf, d_debut, c_et, animateurs); create index ik_n_pf on ik (n_pf); create index ik_c_sf on ik (c_sf); create index ik_c_et on ik (c_et); unlock table ik; { km (distancier kilométrique) ---------------------------------------------} lock table km in exclusive mode; { drop index km_l_cm; drop index km_c_cm_1; drop index km_c_cm_2; } create unique cluster index km_l_cm on km (l_cm_1, l_cm_2); alter index km_l_cm to cluster; create index km_c_cm_1 on km (c_cm_1); create index km_c_cm_2 on km (c_cm_2); unlock table km; { ln (logname) -------------------------------------------------------------} lock table ln in exclusive mode; { drop index ln_tri; drop index ln_cle; drop index ln_n_pf; } create unique cluster index ln_tri on ln (service, logname); alter index ln_tri to cluster; create unique index ln_cle on ln (service, initiales); create index ln_n_pf on ln (n_pf); unlock table ln; { lp (line printer) --------------------------------------------------------} lock table lp in exclusive mode; { drop index lp_tri; drop index lp_file_attente; drop index lp_logname; } create unique cluster index lp_tri on lp (bureau, logname); alter index lp_tri to cluster; create index lp_file_attente on lp (file_attente); create unique index lp_logname on lp (logname); unlock table lp; { mf (macro-formations) ----------------------------------------------------} lock table mf in exclusive mode; { drop index mf_c_mf; drop index mf_c_df; } create unique cluster index mf_c_mf on mf (c_mf); alter index mf_c_mf to cluster; create index mf_c_df on mf (c_df); unlock table mf; { mp (média/post) ----------------------------------------------------------} lock table mp in exclusive mode; { drop index mp_c_cp; } create cluster index mp_c_cp on mp (c_cp); alter index mp_c_cp to cluster; unlock table mp; { na (nomenclature analytique) ---------------------------------------------} lock table na in exclusive mode; { drop index na_c_na; } create unique cluster index na_c_na on na (c_na); alter index na_c_na to cluster; unlock table na; { nb (nomenclature budgétaire) ---------------------------------------------} lock table nb in exclusive mode; { drop index nb_cle; } create unique cluster index nb_cle on nb (exercice, chapitre, article, paragraphe); alter index nb_cle to cluster; unlock table nb; { ne (nature établissement) ------------------------------------------------} lock table ne in exclusive mode; { drop index ne_c_ne; } create unique cluster index ne_c_ne on ne (c_ne); alter index ne_c_ne to cluster; unlock table ne; { neo (néo-titulaires) ------------------------------------------------------} lock table neo in exclusive mode; { drop index neo_n_serial; drop index neo_n_pf; drop index neo_numen; } create cluster index neo_n_serial on neo (n_serial); alter index neo_n_serial to cluster; create index neo_n_pf on neo (n_pf); create unique index neo_numen on neo (numen); unlock table neo; { nnco (nomenclature nationale : contenus de formation) ----------------------} lock table nnco in exclusive mode; { drop index nnco_c_nnco; } create unique cluster index nnco_c_nnco on nnco (c_nnco); alter index nnco_c_nnco to cluster; unlock table nnco; { nndc (nomenclature nationale : décisions candidatures) ---------------------} lock table nndc in exclusive mode; { drop index nndc_c_decision; } create unique cluster index nndc_c_dc on nndc (c_nndc); alter index nndc_c_dc to cluster; unlock table nndc; { nndo (nomenclature nationale : domaines de formation) ----------------------} lock table nndo in exclusive mode; { drop index nndo_c_nndo; } create unique cluster index nndo_c_nndo on nndo (c_nndo); alter index nndo_c_nndo to cluster; unlock table nndo; { nnfo (nomenclature nationale : forme de l'organisation) --------------------} lock table nnfo in exclusive mode; { drop index nnfo_c_nnfo; } create unique cluster index nnfo_c_nnfo on nnfo (c_nnfo); alter index nnfo_c_nnfo to cluster; unlock table nnfo; { nnmo (nomenclature nationale : modalité de l'organisation) -----------------} lock table nnmo in exclusive mode; { drop index nnmo_c_nnmo; } create unique cluster index nnmo_c_nnmo on nnmo (c_nnmo); alter index nnmo_c_nnmo to cluster; unlock table nnmo; { nnni (nomenclature nationale : niveau d'initiative) ------------------------} lock table nnni in exclusive mode; { drop index nnni_c_nnni; } create unique cluster index nnni_c_nnni on nnni (c_nnni); alter index nnni_c_nnni to cluster; unlock table nnni; { nnob (nomenclature nationale : objectifs de formation) ---------------------} lock table nnob in exclusive mode; { drop index nnob_c_nnob; } create unique cluster index nnob_c_nnob on nnob (c_nnob); alter index nnob_c_nnob to cluster; unlock table nnob; { nnpc (nomenclature nationale : public cible) -------------------------------} lock table nnpc in exclusive mode; { drop index nnpc_c_nnpc; } create unique cluster index nnpc_c_nnpc on nnpc (c_nnpc); alter index nnpc_c_nnpc to cluster; unlock table nnpc; { nnsp (nomenclature nationale : spécialités) --------------------------------} lock table nnsp in exclusive mode; { drop index nnsp_c_nnsp; } create unique cluster index nnsp_c_nnsp on nnsp (c_nnsp); alter index nnsp_c_nnsp to cluster; unlock table nnsp; { nntc (nomenclature nationale : type de candidature) ------------------------} lock table nntc in exclusive mode; { drop index nntc_c_nntc; } create unique cluster index nntc_c_nntc on nntc (c_nntc); alter index nntc_c_nntc to cluster; unlock table nntc; { nntf (nomenclature nationale : type de formation) --------------------------} lock table nntf in exclusive mode; { drop index nntf_c_nntf; } create unique cluster index nntf_c_nntf on nntf (c_nntf); alter index nntf_c_nntf to cluster; unlock table nntf; { nntmp (nomenclature nationale : temporaire) ---------------------------------} lock table nntmp in exclusive mode; { drop index nntmp_c_mf; drop index nntmp_c_af; drop index nntmp_c_sf; } create index nntmp_c_mf on nntmp (c_mf); create index nntmp_c_af on nntmp (c_af); create unique cluster index nntmp_c_sf on nntmp (c_sf); alter index nntmp_c_sf to cluster; unlock table nntmp; { omp (ordres de mission permanents) ----------------------------------------} lock table omp in exclusive mode; { drop index omp_n_pf; } create unique cluster index omp_n_pf on omp (n_pf); alter index omp_n_pf to cluster; unlock table omp; { pa (priorités académiques) -----------------------------------------------} lock table pa in exclusive mode; { drop index pa_c_pa; } create unique cluster index pa_c_pa on pa (c_pa); alter index pa_c_pa to cluster; unlock table pa; { pdf (parcours de formation) -----------------------------------------------} lock table pdf in exclusive mode; { drop index pdf_c_pdf; } create unique cluster index pdf_c_pdf on pdf (c_pdf); alter index pdf_c_pdf to cluster; unlock table pdf; { pep4 (pep_4) ---------------------------------------------------------------} lock table pep4 in exclusive mode; { drop index pep4_numen; drop index pep4_n_pep4; } create unique cluster index pep4_numen on pep4 (numen); alter index pep4_numen to cluster; create index pep4_n_pep4 on pep4 (n_pep4); unlock table pep4; { pf (personnel formation)--------------------------------------------------} lock table pf in exclusive mode; { drop index pf_tri; drop index pf_cle; drop index pf_n_pf; drop index pf_numen; drop index pf_nom; drop index pf_c_et; } create unique cluster index pf_tri on pf (nom_cf, n_pf); alter index pf_tri to cluster; create unique index pf_cle on pf (civilite, nom, prenom, c_et); create unique index pf_n_pf on pf (n_pf); create index pf_numen on pf (numen); create index pf_nom on pf (nom); create index pf_c_et on pf (c_et); unlock table pf; { piaf (procédure d'inscription aux actions de formation) --------------------} lock table piaf in exclusive mode; { drop index piaf_n_pf; drop index piaf_numen; } create index piaf_n_pf on piaf (n_pf); create index piaf_numen on piaf (numen); unlock table piaf; { pn (priorités nationales) ------------------------------------------------} lock table pn in exclusive mode; { drop index pn_tri; drop index pn_c_pn; } create unique cluster index pn_tri on pn (direction, c_pn); alter index pn_tri to cluster; create unique index pn_c_pn on pn (c_pn); unlock table pn; { rp (répartitions pédagogiques des ca) ------------------------------------} lock table rp in exclusive mode; { drop index rp_cle; drop index rp_c_sf; drop index rp_n_ca; } create unique cluster index rp_cle on rp (n_ca, c_sf); alter index rp_cle to cluster; create index rp_c_sf on rp (c_sf); create index rp_n_ca on rp (n_ca); unlock table rp; { sa (situations administratives) ------------------------------------------} lock table sa in exclusive mode; { drop index sa_c_sa; } create unique cluster index sa_c_sa on sa (c_sa); alter index sa_c_sa to cluster; unlock table sa; { ser (services) ------------------------------------------------------------} lock table ser in exclusive mode; { drop index ser_c_div; drop index ser_c_ser; } create index ser_c_div on ser (c_div); create unique cluster index ser_c_ser on ser (c_ser); alter index ser_c_ser to cluster; unlock table ser; { sf (stages de formation) -------------------------------------------------} lock table sf in exclusive mode; { drop index sf_cle; drop index sf_c_sf; drop index sf_c_af; } create unique cluster index sf_cle on sf (c_af, c_ma, c_sm); alter index sf_cle to cluster; create unique index sf_c_sf on sf (c_sf); create index sf_c_af on sf (c_af); unlock table sf; { sg (services gestionnaires) ----------------------------------------------} lock table sg in exclusive mode; { drop index sg_c_sg; } create unique cluster index sg_c_bdd on sg (c_bdd); alter index sg_c_bdd to cluster; unlock table sg; { sh (suivi historique) ----------------------------------------------------} lock table sh in exclusive mode; { drop index sh_cle; drop index sh_c_sf; } create unique cluster index sh_cle on sh (c_sf, d_mailing desc, t_mailing desc); alter index sh_cle to cluster; create index sh_c_sf on sh (c_sf); unlock table sh; { sys_c (colonnes) ------------------------------------------------------------} lock table sys_c in exclusive mode; { drop index sys_c_cle; drop index sys_c_tabid; drop index sys_c_colname; } create unique cluster index sys_c_cle on sys_c (tabid, colno); alter index sys_c_cle to cluster; create index sys_c_tabid on sys_c (tabid); create index sys_c_colname on sys_c (colname); unlock table sys_c; { sys_t (tables) --------------------------------------------------------------} lock table sys_t in exclusive mode; { drop index sys_t_tabname; drop index sys_t_tabid; } create unique cluster index sys_t_tabname on sys_t (tabname); alter index sys_t_tabname to cluster; create unique index sys_t_tabid on sys_t (tabid); unlock table sys_t; { t_cf (temporaire candidatures formation) -----------------------------------} lock table t_cf in exclusive mode; { drop index t_cf_cle; drop index t_cf_n_pf; drop index t_cf_c_sf; } create unique cluster index t_cf_cle on t_cf (n_pf, c_sf); alter index t_cf_cle to cluster; create index t_cf_n_pf on t_cf (n_p); create index t_cf_c_sf on t_cf (c_s); unlock table t_cf; { t_cm (typologie commune) ---------------------------------------------------} lock table t_cm in exclusive mode; { drop index t_cm_t_cm; } create unique cluster index t_cm_t_cm on t_cm (t_cm); alter index t_cm_t_cm to cluster; unlock table t_cm; { t_ne (typologie nature établissement) --------------------------------------} lock table t_ne in exclusive mode; { drop index t_ne_t_ne; } create unique cluster index t_ne_t_ne on t_ne (t_ne); alter index t_ne_t_ne to cluster; unlock table t_ne; { tbb (temporaire bilans budgétaires) ---------------------------------------} lock table tbb in exclusive mode; { drop index tbb_cle; drop index tbb_c_df; drop index tbb_c_af; drop index tbb_c_sf; } create unique cluster index tbb_cle on tbb (c_df, c_af, c_sf); alter index tbb_cle to cluster; create index tbb_c_df on tbb (c_df); create index tbb_c_af on tbb (c_af); create index tbb_c_sf on tbb (c_sf); unlock table tbb; { tmp (temporaire) ----------------------------------------------------------} lock table tmp in exclusive mode; { drop index tmp_cle; drop index tmp_c_df; drop index tmp_c_af; drop index tmp_c_sf; } create unique cluster index tmp_cle on tmp (c_df, c_af, c_sf); alter index tmp_cle to cluster; create index tmp_c_df on tmp (c_df); create index tmp_c_af on tmp (c_af); create index tmp_c_sf on tmp (c_sf); { ts (transport-séjour) ----------------------------------------------------} lock table ts in exclusive mode; { drop index ts_n_pf; drop index ts_numen; drop index ts_c_af; drop index ts_c_sf; drop index ts_c_kheops; } create cluster index ts_n_pf on ts (n_pf); alter index ts_n_pf to cluster; create index ts_c_af on ts (c_af); create index ts_c_sf on ts (c_sf); create index ts_numen on ts (numen); create index ts_c_kheops on ts (c_kheops); unlock table ts; { ts_t (transports-séjours temporaire) ---------------------------------------} lock table ts_t in exclusive mode; { drop index ts_t_numen; drop index ts_t_n_pf; drop index ts_t_c_sf; drop index ts_t_cle; } create cluster index ts_t_cle on ts_t (n_pf, c_sf); alter index ts_t_cle to cluster; create index ts_t_numen on ts_t (numen); create index ts_t_n_pf on ts_t (n_pf); create index ts_t_c_sf on ts_t (c_sf); unlock table ts_t; { tsa (transport-séjour animateur) ------------------------------------------} lock table tsa in exclusive mode; { drop index tsa_cle; drop index tsa_n_pf; drop index tsa_c_sf; } create cluster index tsa_cle on tsa (n_pf, c_sf, exercice); alter index tsa_cle to cluster; create index tsa_n_pf on tsa (n_pf); create index tsa_c_sf on tsa (c_sf); unlock table tsa; { tsdp (transport-séjour dépense prévisionnelle) -----------------------------} lock table tsdp in exclusive mode; { drop index tsdp_c_sf; } create cluster index tsdp_c_sf on tsdp (c_sf); alter index tsdp_c_sf to cluster; unlock table tsdp; { tss_t (transport-séjour stagiaires temporaire) ------------------------------} lock table tss_t in exclusive mode; { drop index tss_t_cle; drop index tss_t_n_pf; drop index tss_t_c_sf; } create unique cluster index tss_t_cle on tss_t (n_pf, c_sf); alter index tss_t_cle to cluster; create index tss_t_n_pf on tss_t (n_pf); create index tss_t_c_sf on tss_t (c_sf); unlock table tss_t; { uaa (uaa DEIG) ------------------------------------------------------------} lock table uaa in exclusive mode; { drop index uaa_c_et } create unique cluster index uaa_c_et on uaa (c_et); alter index uaa_c_et to cluster; unlock table uaa; { uf (unités de formation) -------------------------------------------------} lock table uf in exclusive mode; { drop index uf_cle; drop index uf_c_af; drop index uf_c_uf; drop index uf_c_sf; } create unique cluster index uf_cle on uf (c_uf, c_uf_ptr, c_sf); alter index uf_cle to cluster; create index uf_c_af on uf (c_af); create index uf_c_uf on uf (c_uf); create index uf_c_sf on uf (c_sf); unlock table uf; { ut (unités de traitement) ------------------------------------------------} lock table ut in exclusive mode; { drop index ut_c_ut; } create unique cluster index ut_c_ut on ut (c_ut); alter index ut_c_ut to cluster; unlock table ut; { zep (zone d'éducation prioritaire) ----------------------------------------} lock table zep in exclusive mode; { drop index zep_tri; } create unique cluster index zep_tri on zep (c_dp, c_df, c_dm); alter index zep_tri to cluster; unlock table zep; {------------------------------------- FIN ------------------------------------}
I-3. Annexes
▲ I-3.2.6. OSMOSE Sql CREATE_BDD
► I-3.2.7. OSMOSE Sql CREATE_IDX
▼ II-1.1. PRINCIPES MÉTHODOLOGIQUES : Manifeste Agile
Mis à jour 25/02/2024 à 20h13 par APL-AML
Tags:
informix,
méthodologie,
sgbd,
sql
- Catégories
- ■ APL-AML , I- L’ART , I-3. Annexes , I-3.2. BDD Formation continue
Consulter le profil
Marquer ce blog comme lu
