写一个 SQL ,实现如下功能:
1、有一个字段标识出该表是否是分区表;
2、找出该表的所有索引,同时有个字段用来标识 某个索引是否是 unique索引,是什么类型索引(normal、bitmap),是否是local 索引;
3、拼接创建该表所有索引的语句,使用 parallel 关键字;
4、拼接创建索引后关闭并行属性的语句;
注意:
1、如果一个表是分区表,可以创建 LOCAL 索引和 全局索引;
2、如果一个表不是分区表,不能创建 LOCAL 索引;
查询结果里包含如下列:
TABLE_OWNER,
TABLE_NAME,
PART_BZ,(分区表标志)
INDEX_OWNER,
INDEX_NAME,
UNIQUE_INDEX_BZ,
INDEX_TYPE,
LOCAL_INDEX_BZ,(本地索引标志)
CREATE_INDEX_SQL,(创建索引语句)
CLOSE_INDEX_PARALLEL_SQL(关闭并行语句)
代码:
SELECT I.OWNER AS TABLE_OWNER,
I.TABLE_NAME,
I.PARTITIONED AS PART_BZ,
T.INDEX_OWNER,
I.INDEX_NAME,
I.UNIQUENESS AS UNIQUE_INDEX_BZ,
I.INDEX_TYPE,
CASE
WHEN LO.LOCALITY = 'LOCAL' THEN
LO.LOCALITY
ELSE
'GLOBAL'
END AS LOCAL_INDEX_BZ,
'CREATE ' || (CASE
WHEN I.UNIQUENESS = 'UNIQUE' THEN
I.UNIQUENESS || ' '
ELSE
CASE
WHEN I.INDEX_TYPE = 'NORMAL' THEN
''
ELSE
I.INDEX_TYPE || ' '
END
END) || 'INDEX ' || I.INDEX_NAME || ' ON ' || I.TABLE_NAME || '(' ||
WM_CONCAT(COLUMN_NAME) || ');' AS CREATE_INDEX_SQL,
'ALTER INDEX ' || I.INDEX_NAME || ' NOPARALLEL;' AS CLOSE_INDEX_PARALLEL_SQL
FROM DBA_INDEXES I, DBA_IND_COLUMNS T, DBA_PART_INDEXES LO
WHERE T.INDEX_NAME = I.INDEX_NAME
AND T.INDEX_OWNER = I.OWNER
AND T.INDEX_NAME = LO.INDEX_NAME(+)
GROUP BY I.OWNER,
I.TABLE_NAME,
I.PARTITIONED,
T.INDEX_OWNER,
I.INDEX_NAME,
I.UNIQUENESS,
I.INDEX_TYPE,
LO.LOCALITY