为什么有些oracle数据库会逆向出主键索引?

Viewed 7

系统在逆向解析时自动过滤了主键索引,但是有些建表语句中的建索引差异会导致过滤失败,替换掉索引查询模板即可(如下图):

image.png

模板内容:

SELECT i.owner AS schema_name,
       i.table_name AS TBL_DEF_KEY,
       ic.column_name AS COL_DEF_KEY,
       DATA_DEFAULT AS VIRTUAL_COL_DEF_KEY,
       i.index_name AS DEF_KEY,
       CASE
           WHEN i.uniqueness = 'UNIQUE' THEN 'UNIQUE'
           ELSE 'NORMAL'
           END AS TYPE,
       CASE
           WHEN ic.descend = 'DESC' THEN 'DESC'
           ELSE 'ASC'
           END AS SORT_TYPE,
       ic.column_position AS ORDER_VALUE,
       NULL AS INTRO
FROM all_indexes i
         LEFT JOIN all_ind_columns ic ON i.index_name = ic.index_name AND i.table_name = ic.table_name
         LEFT JOIN ALL_TAB_COLS co ON (co.table_name = i.table_name AND co.column_name = ic.column_name)
         LEFT JOIN all_constraints pk ON pk.owner = i.owner AND pk.table_name = i.table_name AND pk.index_name = i.index_name AND pk.constraint_type = 'P'
WHERE i.owner in (:schemaName)
  AND CONCAT(CONCAT(i.owner, '.') , i.table_name) IN  (:sysTableNames)
  AND co.owner in (:schemaName)
  AND pk.constraint_name IS NULL
ORDER BY i.owner, i.table_name, i.index_name, ic.column_position
0 Answers
Related Questions