Au Pied Levé - À Main Levée
I-3.1.7. Ex&Co SQL CREATE_IDX
par , 01/04/2020 à 10h00 (237 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 de toutes les tables de la BDD "concours".
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{------------------------------------------------------------------------------} { CREATE_IDX.sql } {------------------------------------------------------------------------------} { sql de création des index de toutes les tables de la BDD "concours" Les noms d'index sont constitués du nom de leur table suivi d'un suffixe qui dépend de leur fonction : +---------------------------------------------+------------------+-------------+ |Fonctions |Règle |Exemple | +---------------------------------------------+------------------+-------------+ |Accés aux informations dans un certain ordre |nom-table_tri |cea_tri | |Garantie de l'unicité des items |nom-table_cle |cea_cle | |Accélération de l'accès aux items |nom-table_attribut|cea_n_cec | +---------------------------------------------+------------------+-------------+ } {------------------------------------------------------------------------------} { sql de précaution en cas de run accidentel } {------------------------------------------------------------------------------} select rien from rien; {------------------------------------------------------------------------------} { ac (académies) -----------------------------------------------------------} { cea (candidatures épreuves d'amission) ------------------------------------} { cea_n (candidatures épreuves d'amission) ------------------------------------} { cec (candidatures examens-concours) ---------------------------------------} { cec_n (candidatures examens-concours) ---------------------------------------} { cm (communes) ------------------------------------------------------------} { cnd (candidatures OCEAN) --------------------------------------------------} { cp (codes postaux) -------------------------------------------------------} { dg (district géographique) -----------------------------------------------} { dec (diection des examens-concours : Ministère) ---------------------------} { dip (diplômes) ------------------------------------------------------------} { doc (documentation CONCOURS : views per, ace, shl, sql) -------------------} { dp (départements) --------------------------------------------------------} { ea (épreuves d'admissibilités/admission) ---------------------------------} { ea_n (épreuves d'admissibilités/admission) ---------------------------------} { eaq (examen pour acquis OCEAN = diplômes) ---------------------------------} { ec (examens-concours) ----------------------------------------------------} { ec_n (examens-concours) ----------------------------------------------------} { epr (épreuves OCEAN) ------------------------------------------------------} { exa (examens OCEAN) -------------------------------------------------------} { et (établissements) ------------------------------------------------------} { gr (grades) --------------------------------------------------------------} { itrf (itrf) ----------------------------------------------------------------} { ln (logname) -------------------------------------------------------------} { lv (langues vivantes) ----------------------------------------------------} { mat (matières) ------------------------------------------------------------} { mj (membres du jury) -----------------------------------------------------} { mra (motifs recul d'âge) --------------------------------------------------} { nat (nationalités) --------------------------------------------------------} { ne (natures établissements) ----------------------------------------------} { net (candidatures internet issues d'INSCRINET OCEAN) ----------------------} { oc (origine des candidats) -----------------------------------------------} { opt (options) -------------------------------------------------------------} { oral (ordre oral) ----------------------------------------------------------} { os (origines statutaires) ------------------------------------------------} { pec (personnel examens-concours) ------------------------------------------} { pec_n (personnel examens-concours) ------------------------------------------} { pic (pré-inscriptions CONCOURS) -------------------------------------------} { pic_n (pré-inscriptions CONCOURS) -------------------------------------------} { pim (pré-inscriptions MINITEL) --------------------------------------------} { pj (planning jury) -------------------------------------------------------} { pm (pièces manquantes) ---------------------------------------------------} { prf (professions OCEAN) ---------------------------------------------------} { qcm (questionnaire à choix multiple : importation) ------------------------} { qjo (questionnaire jury d'oral : importation) ------------------------} { rc (rejet de candidature ou refus d'autorisation à concourir) ------------} { sa (situations administratives) ------------------------------------------} { sg (services gestionnaires) ----------------------------------------------} { sh (suivi historique) ----------------------------------------------------} { sh_n (suivi historique) ----------------------------------------------------} { sp (salles/places) -------------------------------------------------------} { spe (spécialités OCEAN) ---------------------------------------------------} { sys_c (colonnes) ------------------------------------------------------------} { sys_t (tables) --------------------------------------------------------------} { t_cm (typologie communes) --------------------------------------------------} { tec (typologie examens-concours) ------------------------------------------} { tj (travaux jury) --------------------------------------------------------} { tj_n (travaux jury) --------------------------------------------------------} { tmp (temporaire) ----------------------------------------------------------} { tmp_hf(temporaire hommes-femmes) --------------------------------------------} { tmp_nd(temporaire niveaux de diplôme) ---------------------------------------} { tmp_nh(temporaire Niveaux hiérarchiques) ------------------------------------} { tmp_ta(temporaire tranches d'âge) -------------------------------------------} { tmp_os(temporaire origines des candidats) -----------------------------------} { ut (unités de traitement) ------------------------------------------------} {------------------------------------------------------------------------------} {------------------------------------------------------------------------------} { ac (académies) -----------------------------------------------------------} lock table ac in exclusive mode; { drop index ac_c_ac; } create unique cluster index ac_c_ac on ac (c_ac); alter index ac_c_ac to cluster; unlock table ac; { cea (candidatures épreuves d'amission) ------------------------------------} lock table cea in exclusive mode; { drop index cea_tri; drop index cea_cle; drop index cea_cts; drop index cea_geo; drop index cea_n_cec; } create cluster index cea_tri on cea (cts_geo, partiel, jury, passage, ordre_oral, n_cec); alter index cea_tri to cluster; create unique index cea_cle on cea (cts_geo, partiel, ordre_oral, n_cec); create index cea_cts on cea (cts); create index cea_geo on cea (geo); create index cea_n_cec on cea (n_cec); unlock table cea; { cea_n (candidatures épreuves d'amission) ------------------------------------} lock table cea_n in exclusive mode; { drop index cea_n_tri; drop index cea_n_cle; drop index cea_n_cts; drop index cea_n_geo; drop index cea_n_n_cec; } create unique cluster index cea_n_tri on cea_n (cts_geo, partiel, ordre_oral, n_cec); alter index cea_n_tri to cluster; create unique index cea_n_cle on cea_n (cts, groupe, epreuve, partiel, n_cec); create index cea_n_cts on cea_n (cts); create index cea_n_geo on cea_n (geo); create index cea_n_n_cec on cea_n (n_cec); unlock table cea_n; { cec (candidatures examens-concours) ---------------------------------------} lock table cec in exclusive mode; { drop index cec_tri; drop index cec_cts; drop index cec_n_cec; drop index cec_n_pec; drop index cec_n_pim; } create unique cluster index cec_tri on cec (exercice, cts, c_dp, ordre_oral, anonymat, place, nom_cec, n_pec); alter index cec_tri to cluster; create index cec_cts on cec (cts); create unique index cec_n_cec on cec (n_cec); create index cec_n_pec on cec (n_pec); create index cec_n_pim on cec (n_pim); unlock table cec; { cec_n (candidatures examens-concours) ---------------------------------------} lock table cec_n in exclusive mode; { drop index cec_n_tri; drop index cec_n_cle; drop index cec_n_n_cec; drop index cec_n_n_pec; drop index cec_n_n_pim; } create unique cluster index cec_n_tri on cec_n (exercice, cts, c_dp, ordre_oral, anonymat, place, nom_cec, n_pec); alter index cec_n_tri to cluster; create unique index cec_n_cle on cec_n (cts, n_cec); create index cec_n_n_cec on cec_n (n_cec); create index cec_n_n_pec on cec_n (n_pec); create index cec_n_n_pim on cec_n (n_pim); unlock table cec_n; { 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; } 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); unlock table cm; { cnd (candidatures OCEAN) --------------------------------------------------} lock table cnd in exclusive mode; { drop index cnd_tri; drop index cnd_num_can; drop index cnd_cod_exa; drop index cnd_cod_spe; drop index cnd_cts; } create unique cluster index cnd_tri on cnd (cod_exa, cod_spe, num_can); alter index cnd_tri to cluster; create unique index cnd_num_can on cnd (num_can); create index cnd_cod_exa on cnd (cod_exa); create index cnd_cod_spe on cnd (cod_spe); create index cnd_cts on cnd (cts); unlock table cnd; { 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; { dg (district géographique) -----------------------------------------------} lock table dg in exclusive mode; { drop index dg_tri; drop index dg_dg; drop index dg_dp; } create unique cluster index dg_tri on dg (c_dp, c_dg); alter index dg_tri to cluster; create index dg_c_dg on dg (c_dg); create index dg_c_dp on dg (c_dp); unlock table dg; { dec (diection des examens-concours : Ministère) ---------------------------} lock table dec in exclusive mode; { drop index dec_tri_cle; drop index dec_cod_exa; drop index dec_cod_spe; drop index dec_num_can; } create unique cluster index dec_tri_cle on dec (cod_exa, cod_spe, num_can); alter index dec_tri_cle to cluster; create index dec_cod_exa on dec (cod_exa); create index dec_cod_spe on dec (cod_spe); create index dec_num_can on dec (num_can); unlock table dec; { dip (diplômes) ------------------------------------------------------------} lock table dip in exclusive mode; { drop index dip_c_dip; } create unique cluster index dip_c_dip on dip (c_dip); alter index dip_c_dip to cluster; unlock table dip; { doc (documentation CONCOURS : 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; } 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); unlock table dp; { ea (épreuves d'admissibilités/admission) ---------------------------------} lock table ea in exclusive mode; { drop index ea_cle; drop index ea_cts_geo; drop index ea_cts; drop index ea_geo; drop index ea_c_ec; drop index ea_t_ec; } create unique cluster index ea_cle on ea (c_ec, t_ec, s_ec, groupe, epreuve, option, partiel); create unique index ea_cts_geo on ea (cts_geo, partiel); create index ea_cts on ea (cts); create index ea_geo on ea (geo); create index ea_c_ec on ea (c_ec); create index ea_t_ec on ea (t_ec); unlock table ea; { ea_n (épreuves d'admissibilités/admission) ---------------------------------} lock table ea_n in exclusive mode; { drop index ea_n_cle; drop index ea_n_cts_geo; drop index ea_n_cts; drop index ea_n_geo; drop index ea_n_c_ec; drop index ea_n_t_ec; } create unique cluster index ea_n_cle on ea_n (c_ec, t_ec, s_ec, groupe, epreuve, option, partiel); create unique index ea_n_cts_geo on ea_n (cts_geo, partiel); create index ea_n_cts on ea_n (cts); create index ea_n_geo on ea_n (geo); create index ea_n_c_ec on ea_n (c_ec); create index ea_n_t_ec on ea_n (t_ec); unlock table ea_n; { eaq (examen pour acquis OCEAN = diplômes) ---------------------------------} lock table eaq in exclusive mode; { drop index eaq_cod_eaq; } create unique cluster index eaq_cod_eaq on eaq (cod_eaq); unlock table eaq; { ec (examens-concours) ----------------------------------------------------} lock table ec in exclusive mode; { drop index ec_cle; drop index ec_cts; drop index ec_c_ec; drop index ec_t_ec; drop index ec_s_ec; } create unique cluster index ec_cle on ec (c_ec, t_ec); alter index ec_cle to cluster; create index ec_cts on ec (cts); create index ec_c_ec on ec (c_ec); create index ec_t_ec on ec (t_ec); create index ec_s_ec on ec (s_ec); unlock table ec; { ec_n (examens-concours) ----------------------------------------------------} lock table ec_n in exclusive mode; { drop index ec_n_cle; drop index ec_n_cts; drop index ec_n_c_ec; drop index ec_n_t_ec; drop index ec_n_s_ec; } create unique cluster index ec_n_cle on ec_n (c_ec, t_ec, s_ec); create index ec_n_cts on ec_n (cts); create index ec_n_c_ec on ec_n (c_ec); create index ec_n_t_ec on ec_n (t_ec); create index ec_n_s_ec on ec_n (s_ec); unlock table ec_n; { epr (épreuves OCEAN) ------------------------------------------------------} lock table epr in exclusive mode; { drop index epr_tri; drop index epr_cts; drop index epr_n_pec; drop index epr_num_can; drop index epr_cod_epr; } create unique cluster index epr_tri on epr (cod_exa, cod_spe, num_can, cod_epr); create index epr_cts on epr (cts); create index epr_n_pec on epr (n_pec); create index epr_num_can on epr (num_can); create index epr_cod_epr on epr (cod_epr); unlock table epr; { exa (examens OCEAN) -------------------------------------------------------} lock table exa in exclusive mode; { drop index exa_cod_exa; } create unique cluster index exa_cod_exa on exa (cod_exa); alter index exa_cod_exa to cluster; unlock table exa; { 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_ac, 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; { gr (grades) --------------------------------------------------------------} lock table gr in exclusive mode; { drop index gr_cle; drop index gr_c_gr; drop index gr_c_gf_a; drop index gr_c_gf_e; } create unique cluster index gr_cle on gr (m_gr, c_gr); alter index gr_cle to cluster; create unique index gr_c_gr on gr (c_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; { itrf (itrf) ----------------------------------------------------------------} lock table itrf in exclusive mode; { drop index itrf_tri; drop index itrf_c_ec; drop index itrf_t_ec; drop index itrf_n_itrf; } create unique cluster index itrf_tri on itrf (c_ec, t_ec, n_itrf); alter index itrf_tri to cluster; create index itrf_c_ec on itrf (c_ec); create index itrf_t_ec on itrf (t_ec); create index itrf_n_itrf on itrf (n_itrf); unlock table itrf; { 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; { lv (langues vivantes) ----------------------------------------------------} lock table lv in exclusive mode; { drop index lv_logname; } create unique cluster index lv_c_lv on lv (c_lv); alter index lv_c_lv to cluster; unlock table lv; { mat (matières) ------------------------------------------------------------} lock table mat in exclusive mode; { drop index mat_c_mat; } create unique cluster index mat_c_mat on mat (c_mat); alter index mat_c_mat to cluster; unlock table mat; { mj (membres du jury) -----------------------------------------------------} lock table mj in exclusive mode; { drop index mj_n_mj; drop index mj_nom; drop index mj_c_et; drop index mj_cle; } create unique index mj_n_mj on mj (n_mj); create cluster index mj_nom on mj (nom); create index mj_c_et on mj (c_et); create unique index mj_cle on mj (civilite, nom, prenom, c_et); unlock table mj; { mra (motifs recul d'âge) --------------------------------------------------} lock table mra in exclusive mode; { drop index mra_c_recul_age; } create unique cluster index mra_c_recul_age on mra (c_recul_age); alter index mra_c_recul_age to cluster; unlock table mra; { nat (nationalités) --------------------------------------------------------} lock table nat in exclusive mode; { drop index nat_c_nationalite; } create unique cluster index nat_c_nationalite on nat (c_nationalite); alter index nat_c_nationalite to cluster; unlock table nat; { ne (natures établissements) ----------------------------------------------} 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; { net (candidatures internet issues d'INSCRINET OCEAN) ----------------------} lock table net in exclusive mode; { drop index net_c_net; } create unique cluster index net_n_net on net (n_net); alter index net_n_net to cluster; unlock table net; { oc (origine des candidats) -----------------------------------------------} lock table oc in exclusive mode; { drop index oc_c_oc; } create unique cluster index oc_c_oc on oc (c_oc); alter index oc_c_oc to cluster; unlock table oc; { opt (options) -------------------------------------------------------------} lock table opt in exclusive mode; { drop index opt_c_option; } create unique cluster index opt_c_option on opt (c_option); alter index opt_c_option to cluster; unlock table opt; { oral (ordre oral) ----------------------------------------------------------} lock table oral in exclusive mode; { drop index oral.cle; } create unique cluster index oral_cle on oral (jury, ordre_oral); alter index oral_cle to cluster; unlock table oral; { os (origines statutaires) ------------------------------------------------} lock table os in exclusive mode; { drop index os_c_os; } create unique cluster index os_c_os on os (c_os); alter index os_c_os to cluster; unlock table os; { pec (personnel examens-concours) ------------------------------------------} lock table pec in exclusive mode; { drop index pec_tri; drop index pec_cle; drop index pec_n_pec; drop index pec_n_pim; drop index pec_num_can; } create unique cluster index pec_tri on pec (nom_naiss, prenom, d_naissance, n_pec); alter index pec_tri to cluster; create index pec_cle on pec (n_pec, exercice); create index pec_n_pec on pec (n_pec); create index pec_n_pim on pec (n_pim); create index pec_num_can on pec (num_can); unlock table pec; { pec_n (personnel examens-concours) ------------------------------------------} lock table pec_n in exclusive mode; { drop index pec_n_tri; drop index pec_n_cle; drop index pec_n_n_pec; drop index pec_n_n_pim; drop index pec_num_can; } create unique cluster index pec_n_tri on pec_n (nom_cec, n_pec, n_pim, exercice); alter index pec_n_tri to cluster; create unique index pec_n_cle on pec_n (n_pec, exercice); create index pec_n_n_pec on pec_n (n_pec); create index pec_n_n_pim on pec_n (n_pim); create index pec_n_num_can on pec_n (num_can); unlock table pec_n; { pic (pré-inscriptions CONCOURS) -------------------------------------------} lock table pic in exclusive mode; { drop index pic_cle; drop index pic_cts; drop index pic_n_pim; } create unique cluster index pic_cle on pic (cts, n_pim); alter index pic_cle to cluster; create index pic_cts on pic (cts); create index pic_n_pim on pic (n_pim); unlock table pic; { pic_n (pré-inscriptions CONCOURS) -------------------------------------------} lock table pic_n in exclusive mode; { drop index pic_n_cle; drop index pic_n_cts; drop index pic_n_n_pim; } create unique cluster index pic_n_cle on pic_n (cts, n_pim); alter index pic_n_cle to cluster; create index pic_n_cts on pic_n (cts); create index pic_n_n_pim on pic_n (n_pim); unlock table pic_n; { pim (pré-inscriptions MINITEL) --------------------------------------------} lock table pim in exclusive mode; { drop index pim_cle; drop index pim_n_pim; drop index pim_c_ec; drop index pim_t_ec; } create unique cluster index pim_cle on pim (c_ec, t_ec, n_pim); alter index pim_cle to cluster; create index pim_n_pim on pim (n_pim); create index pim_c_ec on pim (c_ec); create index pim_t_ec on pim (t_ec); unlock table pim; { pim_n (pré-inscriptions MINITEL) --------------------------------------------} lock table pim_n in exclusive mode; { drop index pim_n_cle; drop index pim_n_c_ec; drop index pim_n_t_ec; drop index pim_n_n_pim; } create unique cluster index pim_n_cle on pim_n (c_ec, t_ec, n_pim); alter index pim_n_cle to cluster; create index pim_n_c_ec on pim_n (c_ec); create index pim_n_t_ec on pim_n (t_ec); create index pim_n_n_pim on pim_n (n_pim); unlock table pim_n; { pj (planning jury) -------------------------------------------------------} lock table pj in exclusive mode; { drop index pj_cle; } create unique cluster index pj_cle on pj (cts_geo, partiel, c_dp, sexe, jury, d_epreuve, passage); alter index pj_cle to cluster; unlock table pj; { pm (pièces manquantes) ---------------------------------------------------} lock table pm in exclusive mode; { drop index pm_c_pm; } create unique cluster index pm_c_pm on pm (c_pm); alter index pm_c_pm to cluster; unlock table pm; { prf (professions OCEAN) ---------------------------------------------------} lock table prf in exclusive mode; { drop index prf_c_prf; } create unique cluster index prf_c_prf on prf (c_prf); alter index prf_c_prf to cluster; unlock table prf; { qcm (questionnaire à choix multiple : importation) ------------------------} lock table qcm in exclusive mode; { drop index qcm_place; } create unique cluster index qcm_place on qcm (place); alter index qcm_place to cluster; unlock table qcm; { qjo (questionnaire jury d'oral : importation) ------------------------} lock table qjo in exclusive mode; { drop index qjo_ordre_oral; } create unique cluster index qjo_ordre_oral on qjo (ordre_oral); alter index qjo_ordre_oral to cluster; unlock table qjo; { rc (rejet de candidature ou refus d'autorisation à concourir) ------------} lock table rc in exclusive mode; { drop index rc_c_rc; } create unique cluster index rc_c_rc on rc (c_rc); alter index rc_c_rc to cluster; unlock table rc; { 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; { sg (services gestionnaires) ----------------------------------------------} lock table sg in exclusive mode; { drop index sg_service; } create unique cluster index sg_service on sg (service); alter index sg_service to cluster; unlock table sg; { sh (suivi historique) ----------------------------------------------------} lock table sh in exclusive mode; { drop index sh_tri; } create unique cluster index sh_tri on sh (cts, d_valide); alter index sh_tri to cluster; unlock table sh; { sh_n (suivi historique) ----------------------------------------------------} lock table sh_n in exclusive mode; { drop index sh_n_tri; } create unique cluster index sh_n_tri on sh_n (cts, d_valide); alter index sh_n_tri to cluster; unlock table sh_n; { sp (salles/places) -------------------------------------------------------} lock table sp in exclusive mode; { drop index sp_tri; drop index sp_cle; drop index sp_cts; drop index sp_c_et; } create unique cluster index sp_tri on sp (c_ec, t_ec, premier); alter index sp_tri to cluster; create unique index sp_cle on sp (c_ec, t_ec, salle); create index sp_cts on sp (c_ec, t_ec, s_ec); create index sp_c_et on sp (c_et); unlock table sp; { spe (spécialités OCEAN) ---------------------------------------------------} lock table spe in exclusive mode; { drop index spe_c_ocean; drop index spe_c_examen; drop index spe_c_specialite; } create unique cluster index spe_c_ocean on spe (c_ocean); alter index spe_c_ocean to cluster; create index spe_c_examen on spe (c_examen); create index spe_c_specialite on spe (c_specialite); unlock table spe; { 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_cm (typologie communes) --------------------------------------------------} 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; { tec (typologie examens-concours) ------------------------------------------} lock table tec in exclusive mode; { drop index tec_cle; } create unique cluster index tec_t_ec on tec (t_ec); alter index tec_t_ec to cluster; unlock table tec; { tj (travaux jury) --------------------------------------------------------} lock table tj in exclusive mode; { drop index tj_cle; drop index tj_cts; drop index tj_geo; drop index tj_n_mj; } create unique cluster index tj_cle on tj (cts_geo, d_debut, h_debut, c_tj, n_mj); alter index tj_cle to cluster; create index tj_cts on tj (cts); create index tj_geo on tj (geo); create index tj_n_mj on tj (n_mj); unlock table tj; { tj_n (travaux jury) --------------------------------------------------------} lock table tj_n in exclusive mode; { drop index tj_n_cle; drop index tj_n_cts; drop index tj_n_geo; drop index tj_n_n_mj; } create unique cluster index tj_n_cle on tj_n (cts_geo, d_debut, h_debut, c_tj, n_mj); alter index tj_n_cle to cluster; create index tj_n_cts on tj_n (cts); create index tj_n_geo on tj_n (geo); create index tj_n_n_mj on tj_n (n_mj); unlock table tj_n; { tmp (temporaire) ----------------------------------------------------------} lock table tmp in exclusive mode; { drop index tmp_cle; drop index tmp_c_ec; drop index tmp_t_ec; drop index tmp_n_cec; drop index tmp_place; drop index tmp_post_it; } create unique cluster index tmp_cle on tmp (c_ec, t_ec, n_cec); alter index tmp_cle to cluster; create index tmp_c_ec on tmp (c_ec); create index tmp_t_ec on tmp (t_ec); create unique index tmp_n_cec on tmp (n_cec); create index tmp_place on tmp (place); create index tmp_post_it on tmp (post_it); unlock table tmp; { tmp_hf(temporaire hommes-femmes) --------------------------------------------} lock table tmp_hf in exclusive mode; { drop index tmp_hf_cts; } create unique cluster index tmp_hf_cts on tmp_hf (cts); alter index tmp_hf_cts to cluster; unlock table tmp_hf; { tmp_nd(temporaire niveaux de diplôme) ---------------------------------------} lock table tmp_nd in exclusive mode; { drop index tmp_nd_cts; } create unique cluster index tmp_nd_cts on tmp_nd (cts); alter index tmp_nd_cts to cluster; unlock table tmp_nd; { tmp_nh(temporaire Niveaux hiérarchiques) ------------------------------------} lock table tmp_nh in exclusive mode; { drop index tmp_nh_cts; } create unique cluster index tmp_nh_cts on tmp_nh (cts); alter index tmp_nh_cts to cluster; unlock table tmp_nh; { tmp_ta(temporaire tranches d'âge) -------------------------------------------} lock table tmp_ta in exclusive mode; { drop index tmp_ta_cts; } create unique cluster index tmp_ta_cts on tmp_ta (cts); alter index tmp_ta_cts to cluster; unlock table tmp_ta; { tmp_os(temporaire origines des candidats) -----------------------------------} lock table tmp_os in exclusive mode; { drop index tmp_os_cts; } create unique cluster index tmp_os_cts on tmp_os (cts); alter index tmp_os_cts to cluster; unlock table tmp_os; { uad (unités administratives) ----------------------------------------------} lock table uad in exclusive mode; { drop index uad_c_uad; } create unique cluster index uad_c_uad on uad (c_uad); alter index uad_c_uad to cluster; unlock table uad; { 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; {------------------------------------- FIN ------------------------------------}
I-3. Annexes
▲ I-3.1.6. Ex&Co Sql CREATE_BDD
► I-3.1.7. Ex&Co Sql CREATE_IDX
▼ I-3.2.1. OSMOSE Étude préalable
Mis à jour 25/02/2024 à 19h53 par APL-AML
Tags:
informix,
méthodologie,
sgbd,
sql
- Catégories
- ■ APL-AML , I- L’ART , I-3. Annexes , I-3.1. BDD Examens-Concours
Consulter le profil
Marquer ce blog comme lu
