a) who cares, it is much faster than if you tried to do it yourself and it is the only correct way to do it - they MUST be enforced or they do not exist.
This is not excessive.
This is good.
b) and a look up table would be what? Oh, yet another check (foreign key).
For something like this:
... KIND is null or (KIND in
('BAND','ORCHESTRA','COMPOSER','SONGWRITER','CONDUCTOR' ...
if it is a small list, we need not bother with a lookup table, unless the lookup table would be useful somewhere else (like in a pick list).
c) ummm, what would be the point here - why would you want to do that in this case???
d) don't remember having one, but it is pretty straight forward:
ops$tkyte%ORA11GR1> /*
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> drop table t;
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create table t ( a int, b int, c int, d int );
ops$tkyte%ORA11GR1> create index t_idx1 on t(a,b,c,d);
ops$tkyte%ORA11GR1> create index t_idx2 on t(a,b);
ops$tkyte%ORA11GR1> */
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> with index_data
2 as
3 (
4 select index_name, table_name, column_name, column_position, descend, count(*) over (partition by index_name) col_cnt
5 from user_ind_columns
6 where table_name not like 'BIN$%'
7 )
8 select table_name, index_name, might_be_covered_by
9 from (
10 select a.table_name,
11 a.index_name,
12 b.index_name might_be_covered_by,
13 a.column_position,
14 a.col_cnt,
15 count(*) over (partition by a.index_name, b.index_name) col_cnt2,
16 a.column_name acol, b.column_name bcol
17 from index_data a, index_data b
18 where a.table_name = b.table_name
19 and a.index_name <> b.index_name
20 and a.column_name = b.column_name
21 and a.column_position = b.column_position
22 and a.descend = b.descend
23 and a.col_cnt <= b.col_cnt
24 )
25 where col_cnt = col_cnt2
26 and column_position = 1
27 /
TABLE_NAME INDEX_NAME MIGHT_BE_COVERED_BY
------------------------------ ------------------------------ ------------------------------
T T_IDX2 T_IDX1
ops$tkyte%ORA11GR1>
e) if your fk is (a,b,c) and you have any index that starts with (a,b,c,.....) the foreign key is indexed enough already