Oracle中的SYS_NC00$ SYS_C000$ SYS_STU SYS_STS 和虚拟字段

在Oracle数据库中，你可能会在数据字典或AWR报告中看到一些奇怪的列名，如SYS_NC00001$、SYS_C00042$、SYS_STU...等。这些是Oracle内部自动生成的系统隐藏列和虚拟列。本文将深入解析这些神秘列的来源、用途以及如何查询和管理它们。

一、系统生成列概述

1.1 什么是系统生成列？

Oracle在特定功能场景下会自动在用户表中创建隐藏的辅助列，这些列具有以下特点：

• 自动生成：由Oracle内部机制创建，用户无需手动干预
• 隐藏属性：在DESCRIBE或常规SELECT *中不可见
• 系统命名：遵循特定的命名规则，如SYS_NC、SYS_C、SYS_STU等
• 存储在COL$：可通过数据字典SYS.COL$或DBA_TAB_COLS查询

1.2 系统列分类速查表

二、SYS_NC00$：隐藏虚拟列

2.1 命名规则

SYS_NC代表"System Non-visible Column"，完整命名格式为：

SYS_NC<5位内部列号>$
例如：SYS_NC00001$、SYS_NC00042$

2.2 产生场景

场景1：函数索引（Function-Based Index）

当创建函数索引时，Oracle会自动创建一个虚拟列来存储函数表达式的计算结果：

-- 创建测试表
CREATE TABLE employees_test (
    emp_id    NUMBER,
    emp_name  VARCHAR2(100),
    email     VARCHAR2(100)
);

-- 创建函数索引
CREATE INDEX idx_emp_upper_name ON employees_test(UPPER(emp_name));

-- 查看系统生成的虚拟列
SELECT column_name, data_type, hidden_column, virtual_column, data_default
FROM user_tab_cols
WHERE table_name = 'EMPLOYEES_TEST'
ORDER BY column_id;

/*
COLUMN_NAME      DATA_TYPE   HIDDEN  VIRTUAL  DATA_DEFAULT
---------------- ----------- ------- -------- ------------------
EMP_ID           NUMBER      NO      NO       
EMP_NAME         VARCHAR2    NO      NO       
EMAIL            VARCHAR2    NO      NO       
SYS_NC00004$     VARCHAR2    YES     YES      UPPER("EMP_NAME")
*/

场景2：带默认值添加列（12c+优化）

Oracle 12c引入了元数据优化，添加带默认值的列时不再更新每行数据：

-- 12c之前：添加NOT NULL默认值列会锁表并更新所有行
-- 12c之后：使用元数据存储默认值，不更新现有行

ALTER TABLE employees_test ADD (status VARCHAR2(10) DEFAULT 'ACTIVE' NOT NULL);

-- 查看内部实现
SELECT column_name, hidden_column, virtual_column, data_default
FROM user_tab_cols
WHERE table_name = 'EMPLOYEES_TEST'
  AND column_name LIKE 'SYS%';

场景3：行归档（Row Archival）

启用In-Database Archiving时，Oracle创建隐藏列ORA_ARCHIVE_STATE：

-- 启用行归档
ALTER TABLE employees_test ROW ARCHIVAL;

-- 查看归档状态列
SELECT column_name, hidden_column, data_type
FROM user_tab_cols
WHERE table_name = 'EMPLOYEES_TEST'
  AND column_name = 'ORA_ARCHIVE_STATE';

/*
COLUMN_NAME         HIDDEN  DATA_TYPE
------------------- ------- ----------
ORA_ARCHIVE_STATE   YES     VARCHAR2
*/

场景4：LOB列的内部管理

-- 创建包含LOB的表
CREATE TABLE doc_test (
    doc_id   NUMBER,
    content  CLOB
);

-- LOB列会产生额外的系统列
SELECT column_name, hidden_column, segment_column_id
FROM user_tab_cols
WHERE table_name = 'DOC_TEST';

2.3 COL$.PROPERTY属性解析

在SYS.COL$表中，PROPERTY列使用位标志表示列的特性：

-- 查询列属性详情
SELECT c.name AS column_name,
       c.property,
       CASE WHEN BITAND(c.property, 8) = 8 THEN 'YES' ELSE 'NO' END AS virtual_old,
       CASE WHEN BITAND(c.property, 32) = 32 THEN 'YES' ELSE 'NO' END AS hidden_old,
       CASE WHEN BITAND(c.property, 65536) = 65536 THEN 'YES' ELSE 'NO' END AS expression_new
FROM sys.col$ c, sys.obj$ o
WHERE c.obj# = o.obj#
  AND o.name = 'EMPLOYEES_TEST'
  AND o.owner# = (SELECT user_id FROM all_users WHERE username = USER);

三、SYS_C00$：系统约束名

3.1 产生场景

当创建约束时未指定名称，Oracle自动生成SYS_C格式的约束名：

-- 未指定约束名的各种约束
CREATE TABLE constraint_demo (
    id          NUMBER PRIMARY KEY,              -- 生成 SYS_C00xxxx
    email       VARCHAR2(100) UNIQUE,            -- 生成 SYS_C00xxxx
    status      VARCHAR2(10) CHECK (status IN ('A','I')),  -- 生成 SYS_C00xxxx
    dept_id     NUMBER REFERENCES departments(dept_id),    -- 生成 SYS_C00xxxx
    salary      NUMBER NOT NULL                  -- 生成 SYS_C00xxxx
);

-- 查看生成的约束名
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'CONSTRAINT_DEMO';

/*
CONSTRAINT_NAME   TYPE  SEARCH_CONDITION
----------------- ----- --------------------------
SYS_C00542871     C     "SALARY" IS NOT NULL
SYS_C00542872     C     status IN ('A','I')
SYS_C00542873     P     
SYS_C00542874     U     
SYS_C00542875     R     
*/

3.2 最佳实践：显式命名约束

为便于维护和问题排查，强烈建议显式命名所有约束：

-- 推荐的命名规范
CREATE TABLE employees (
    emp_id      NUMBER 
                CONSTRAINT pk_employees PRIMARY KEY,
    email       VARCHAR2(100) 
                CONSTRAINT uk_employees_email UNIQUE,
    status      VARCHAR2(10) 
                CONSTRAINT ck_employees_status CHECK (status IN ('ACTIVE','INACTIVE')),
    dept_id     NUMBER 
                CONSTRAINT fk_employees_dept REFERENCES departments(dept_id),
    salary      NUMBER 
                CONSTRAINT nn_employees_salary NOT NULL
);

-- 命名规范建议：
-- PK_表名           主键
-- UK_表名_列名      唯一约束
-- FK_表名_关联表    外键
-- CK_表名_列名      检查约束
-- NN_表名_列名      NOT NULL约束

3.3 重命名已有的SYS_C约束

-- 查找需要重命名的约束
SELECT 'ALTER TABLE ' || table_name || 
       ' RENAME CONSTRAINT ' || constraint_name || 
       ' TO ' || 
       CASE constraint_type
           WHEN 'P' THEN 'PK_' || table_name
           WHEN 'U' THEN 'UK_' || table_name || '_' || 
                         (SELECT LISTAGG(column_name, '_') WITHIN GROUP (ORDER BY position)
                          FROM user_cons_columns cc 
                          WHERE cc.constraint_name = c.constraint_name)
           WHEN 'R' THEN 'FK_' || table_name || '_' || 
                         SUBSTR(r_constraint_name, 4)
           ELSE 'CK_' || table_name
       END || ';' AS rename_stmt
FROM user_constraints c
WHERE constraint_name LIKE 'SYS_C%';

四、SYS_STU/SYS_STS：扩展统计信息

4.1 什么是扩展统计信息？

Oracle 11g引入的扩展统计信息（Extended Statistics）允许收集多列组合或表达式的统计信息，帮助优化器做出更准确的基数估算。

4.2 命名规则

• SYS_STU... - User-created：用户手动创建的扩展统计
• SYS_STS... - System-generated：系统自动创建的扩展统计（如通过SQL Tuning Advisor）

4.3 创建列组统计信息

-- 方法1：手动创建列组
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(
    ownname   => USER,
    tabname   => 'CUSTOMERS',
    extension => '(CUST_CITY, CUST_STATE_PROVINCE)'
) FROM DUAL;

-- 返回：SYS_STU#S#WF25Z#QAHIHE#MOFFMM_

-- 方法2：基于工作负载自动创建
-- 先开启列使用监控
EXEC DBMS_STATS.SEED_COL_USAGE(NULL, NULL, 300);

-- 执行代表性SQL工作负载...

-- 自动创建列组
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'CUSTOMERS') FROM DUAL;

/*
示例输出：
###########################################################################
EXTENSIONS FOR HR.CUSTOMERS
................................
1. (CUST_CITY, CUST_STATE_PROVINCE, COUNTRY_ID):SYS_STUMZ$C3AIHLPBROI#SKA58H_N created
2. (CUST_STATE_PROVINCE, COUNTRY_ID):SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ created
###########################################################################
*/

4.4 创建表达式统计信息

-- 为常用表达式创建统计
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(
    ownname   => USER,
    tabname   => 'EMPLOYEES',
    extension => '(UPPER(LAST_NAME))'
) FROM DUAL;

-- 收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPLOYEES');

4.5 查看扩展统计信息

-- 查看所有扩展统计
SELECT extension_name, extension, creator, droppable
FROM user_stat_extensions
WHERE table_name = 'CUSTOMERS';

/*
EXTENSION_NAME                    EXTENSION                           CREATOR  DROPPABLE
--------------------------------- ----------------------------------- -------- ---------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_   ("CUST_CITY","CUST_STATE_PROVINCE") USER     YES
SYS_NC00015$                     (UPPER("LAST_NAME"))                SYSTEM   NO
*/

-- 查看扩展统计的直方图
SELECT column_name, num_distinct, histogram
FROM user_tab_col_statistics
WHERE table_name = 'CUSTOMERS'
  AND column_name LIKE 'SYS_ST%';

4.6 删除扩展统计信息

-- 删除指定扩展统计
EXEC DBMS_STATS.DROP_EXTENDED_STATS(USER, 'CUSTOMERS', '(CUST_CITY, CUST_STATE_PROVINCE)');

-- 或使用扩展名删除
EXEC DBMS_STATS.DROP_EXTENDED_STATS(USER, 'CUSTOMERS', 'SYS_STU#S#WF25Z#QAHIHE#MOFFMM_');

五、用户自定义虚拟列

5.1 创建虚拟列

Oracle 11g开始支持用户显式创建虚拟列：

-- 创建带虚拟列的表
CREATE TABLE orders (
    order_id       NUMBER,
    quantity       NUMBER,
    unit_price     NUMBER,
    -- 虚拟列：自动计算总金额
    total_amount   AS (quantity * unit_price),
    -- 带数据类型的虚拟列
    order_year     NUMBER GENERATED ALWAYS AS (EXTRACT(YEAR FROM order_date)) VIRTUAL
);

-- 为现有表添加虚拟列
ALTER TABLE orders ADD (
    discount_amount AS (total_amount * 0.1)
);

5.2 虚拟列的特性

• 不占用存储空间：值在查询时动态计算
• 可创建索引：索引实际存储计算后的值
• 可收集统计信息：优化器可利用虚拟列统计
• 可用于分区：虚拟列可作为分区键

-- 在虚拟列上创建索引
CREATE INDEX idx_orders_total ON orders(total_amount);

-- 基于虚拟列分区
CREATE TABLE sales (
    sale_id     NUMBER,
    sale_date   DATE,
    amount      NUMBER,
    sale_year   AS (EXTRACT(YEAR FROM sale_date))
)
PARTITION BY RANGE (sale_year) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax  VALUES LESS THAN (MAXVALUE)
);

5.3 查询虚拟列信息

-- 查看表中所有虚拟列
SELECT column_name, 
       data_type, 
       virtual_column,
       hidden_column,
       data_default AS expression
FROM user_tab_cols
WHERE table_name = 'ORDERS'
  AND virtual_column = 'YES';

-- 区分系统虚拟列和用户虚拟列
SELECT column_name,
       CASE 
           WHEN column_name LIKE 'SYS_%' THEN 'SYSTEM'
           ELSE 'USER'
       END AS created_by,
       data_default AS expression
FROM user_tab_cols
WHERE table_name = 'ORDERS'
  AND virtual_column = 'YES';

六、查询和管理系统列

6.1 查看表的所有列（包括隐藏列）

-- 使用 USER_TAB_COLS（包含隐藏列）
-- 注意：USER_TAB_COLUMNS 不包含隐藏列
SELECT column_name, 
       column_id,
       segment_column_id,
       internal_column_id,
       hidden_column,
       virtual_column,
       data_type,
       data_default
FROM user_tab_cols
WHERE table_name = 'YOUR_TABLE'
ORDER BY internal_column_id;

6.2 识别函数索引对应的虚拟列

-- 查找函数索引及其对应的虚拟列
SELECT i.index_name,
       ic.column_name,
       ic.column_position,
       tc.data_default AS index_expression
FROM user_indexes i
JOIN user_ind_columns ic ON i.index_name = ic.index_name
JOIN user_tab_cols tc ON ic.table_name = tc.table_name 
                      AND ic.column_name = tc.column_name
WHERE i.table_name = 'YOUR_TABLE'
  AND tc.virtual_column = 'YES';

6.3 综合诊断脚本

-- 表结构完整分析脚本
SET LINESIZE 200
COLUMN column_name FORMAT A25
COLUMN data_type FORMAT A15
COLUMN expression FORMAT A40
COLUMN column_type FORMAT A20

SELECT column_name,
       data_type,
       CASE 
           WHEN hidden_column = 'YES' AND column_name LIKE 'SYS_NC%' THEN 'SYSTEM VIRTUAL'
           WHEN hidden_column = 'YES' AND column_name LIKE 'SYS_STU%' THEN 'USER STAT EXT'
           WHEN hidden_column = 'YES' AND column_name LIKE 'SYS_STS%' THEN 'SYSTEM STAT EXT'
           WHEN hidden_column = 'YES' THEN 'HIDDEN'
           WHEN virtual_column = 'YES' THEN 'USER VIRTUAL'
           ELSE 'NORMAL'
       END AS column_type,
       CASE 
           WHEN virtual_column = 'YES' THEN SUBSTR(data_default, 1, 40)
           ELSE NULL
       END AS expression
FROM user_tab_cols
WHERE table_name = UPPER('&table_name')
ORDER BY internal_column_id;

七、对性能和存储的影响

7.1 虚拟列的性能考量

7.2 存储影响

-- 虚拟列不占用行存储空间
-- 但函数索引会占用索引段空间

-- 查看索引空间使用
SELECT index_name, 
       segment_type,
       bytes/1024/1024 AS size_mb
FROM user_segments
WHERE segment_name IN (
    SELECT index_name 
    FROM user_indexes 
    WHERE table_name = 'YOUR_TABLE'
);

八、常见问题FAQ

Q1: 可以删除SYS_NC开头的列吗？

不能直接删除。这些列是功能依赖的，删除相关的索引或功能后，列会自动移除。例如删除函数索引后，对应的虚拟列会自动删除。

Q2: 为什么SELECT * 看不到这些列？

系统隐藏列的HIDDEN_COLUMN属性为'YES'，常规查询会自动过滤。使用USER_TAB_COLS视图可以看到所有列。

Q3: 如何区分SYS_STU和SYS_STS？

SYS_STU是用户通过DBMS_STATS.CREATE_EXTENDED_STATS创建的，SYS_STS是系统自动创建的（如通过SQL调优建议）。可通过USER_STAT_EXTENSIONS.CREATOR列确认。

Q4: 大量SYS_NC列会影响性能吗？

虚拟列本身不存储数据，对DML性能影响极小。但过多的函数索引会增加INSERT/UPDATE的维护开销。

Q5: 如何查看某个函数索引使用哪个虚拟列？

SELECT i.index_name, c.column_name, tc.data_default
FROM user_ind_columns c
JOIN user_indexes i ON c.index_name = i.index_name
JOIN user_tab_cols tc ON c.column_name = tc.column_name 
                      AND c.table_name = tc.table_name
WHERE tc.virtual_column = 'YES'
  AND i.table_name = 'YOUR_TABLE';

总结

Oracle的系统生成列是数据库内部实现各种高级功能的基础设施。理解这些列的来源和用途，有助于更好地进行数据库设计、问题诊断和性能优化。关键要点：

• SYS_NC$：函数索引、虚拟列、LOB、默认值等功能的内部支撑
• SYS_C$：未命名约束的自动命名，建议显式命名所有约束
• SYS_STU/STS：扩展统计信息，帮助优化器做出更准确的执行计划
• 用户虚拟列：可作为计算列、分区键、索引列使用