Oracle 11g 虚拟列 Virtual Column介绍

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

Oracle 11G 虚拟列 Virtual Column
 
Oracle 11G 在表中引入了虚拟列，虚拟列是一个表达式，在运行时计算，不存储在数据库中，不能更新虚拟列的值。
 
定义一个虚拟列的语法：
 
    column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]
 
1.虚拟列可以用在select，update，delete语句的where条件中，但是不能用于DML语句
2.可以基于虚拟列来做分区
3. 可以在虚拟列上建索引，oracle的函数索引就类似。
4. 可以在虚拟列上建约束
 
创建一个带虚拟列的表：
 
    CREATE TABLE EMPLOYEE
    (
        empl_id        NUMBER,
        empl_nm        VARCHAR2(50),
        monthly_sal    NUMBER(10,2),
        bonus          NUMBER(10,2),
        total_sal      NUMBER(10,2) GENERATED ALWAYS AS (monthly_sal*12 + bonus)
    );
     
 total_sal就是一个虚拟列
  
查看下表定义内容：
 
    SELECT column_name, data_type, data_length, data_default, virtual_column
      FROM user_tab_cols
     WHERE table_name = 'EMPLOYEE';
      
    COLUMN_NAME | DATA_TYPE | DATA_LENGTH | DATA_DEFAULT             | VIRTUAL_COLUMN
    EMPL_ID     | NUMBER    | 22          | null                     | NO           
    EMPL_NM     | VARCHAR2  | 50          | null                     | NO           
    MONTHLY_SAL | NUMBER    | 22          | null                     | NO           
    BONUS       | NUMBER    | 22          | null                     | NO           
    TOTAL_SAL   | NUMBER    | 22          | "MONTHLY_SAL"*12+"BONUS" | YES        
     
virtual_column是yes，表示是虚拟列，我们也可以在建表语句加上VIRTUAL，显示声明为虚拟列
 
    DROP TABLE EMPLOYEE PURGE;
      
    CREATE OR REPLACE FUNCTION get_empl_total_sal ( p_monthly_sal   NUMBER,
                                                    p_bonus         NUMBER)
       RETURN NUMBER
    DETERMINISTIC
    IS
    BEGIN
       RETURN p_monthly_sal * 12 + p_bonus;
    END;
      
    CREATE TABLE EMPLOYEE
    (empl_id     NUMBER,
     empl_nm     VARCHAR2(50),
     monthly_sal NUMBER(10,2),
     bonus       NUMBER(10,2),
     total_sal   NUMBER(10,2) AS (get_empl_total_sal(monthly_sal, bonus)) VIRTUAL
    );
     
我们可以在虚拟列上创建索引
 
    CREATE INDEX idx_total_sal ON employee(total_sal);
      
    SELECT index_name, index_type 
      FROM user_indexes
     WHERE table_name = 'EMPLOYEE';
      
    INDEX_NAME     INDEX_TYPE                 
    IDX_TOTAL_SAL  FUNCTION-BASED NORMAL
     
这个函数也是表定义内容
 
    DROP FUNCTION get_empl_total_sal;
      
    SELECT * FROM employee;
    *
    Error at line 0
    ORA-00904: "schema"."GET_EMPL_TOTAL_SAL": invalid identifier
     
可以添加一个虚拟列
 
    DROP TABLE EMPLOYEE PURGE;
      
    CREATE TABLE EMPLOYEE
    (empl_id     NUMBER,
     empl_nm     VARCHAR2(50),
     monthly_sal NUMBER(10,2),
     bonus       NUMBER(10,2)
    );
      
    ALTER TABLE EMPLOYEE ADD (total_sal AS (monthly_sal * 12 + bonus));
 
这个新列的类型时不确定的，是根据表达式来推断的。
 
    INSERT INTO employee (empl_id, empl_nm, monthly_sal, bonus)
       WITH DATA AS
            (SELECT 100 empl_id, 'AAA' empl_nm, 20000 monthly_sal, 3000 bonus
               FROM DUAL
             UNION
             SELECT 200, 'BBB', 12000, 2000
               FROM DUAL
             UNION
             SELECT 300, 'CCC', 32100, 1000
               FROM DUAL
             UNION
             SELECT 400, 'DDD', 24300, 5000
               FROM DUAL
             UNION
             SELECT 500, 'EEE', 12300, 8000
               FROM DUAL)
       SELECT *
         FROM DATA;
      
    SELECT * FROM employee;
      
    EMPL_ID | EMPL_NM | MONTHLY_SAL | BONUS | TOTAL_SAL
    100     | AAA     | 20000       | 3000  | 243000
    200     | BBB     | 12000       | 2000  | 146000
    300     | CCC     | 32100       | 1000  | 386200
    400     | DDD     | 24300       | 5000  | 296600
    500     | EEE     | 12300       | 8000  | 155600
     
在虚拟列上执行update语句是不允许的
 
    UPDATE employee
       SET total_sal = 2000;
      
    ORA-54017: UPDATE operation disallowed on virtual columns
     
能够从虚拟列上收集表的统计信息
 
    EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'EMPLOYEE');
      
    SELECT column_name, num_distinct, 
           display_raw (low_value, data_type)  low_value, 
           display_raw (high_value, data_type) high_value
      FROM dba_tab_cols
     WHERE table_name = 'EMPLOYEE';
      
    COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE
    TOTAL_SAL   | 5            | 146000    | 386200
    BONUS       | 5            | 1000      | 8000
    MONTHLY_SAL | 5            | 12000     | 32100
    EMPL_NM     | 5            | AAA       | EEE
    EMPL_ID     | 5            | 100       | 500
     
基于虚拟列的分区
 
    DROP TABLE EMPLOYEE PURGE;
      
    CREATE TABLE employee
    (empl_id     NUMBER,
     empl_nm     VARCHAR2(50),
     monthly_sal NUMBER(10,2),
     bonus       NUMBER(10,2),
     total_sal   NUMBER(10,2) AS (monthly_sal*12 + bonus)
    )
    PARTITION BY RANGE (total_sal)
        (PARTITION sal_200000 VALUES LESS THAN (200000),
         PARTITION sal_400000 VALUES LESS THAN (400000),
         PARTITION sal_600000 VALUES LESS THAN (600000),
         PARTITION sal_800000 VALUES LESS THAN (800000),
         PARTITION sal_default VALUES LESS THAN (MAXVALUE));
      
    INSERT INTO employee (empl_id, empl_nm, monthly_sal, bonus)
       WITH DATA AS
            (SELECT 100 empl_id, 'AAA' empl_nm, 20000 monthly_sal, 3000 bonus
               FROM DUAL
             UNION
             SELECT 200, 'BBB', 12000, 2000
               FROM DUAL
             UNION
             SELECT 300, 'CCC', 32100, 1000
               FROM DUAL
             UNION
             SELECT 400, 'DDD', 24300, 5000
               FROM DUAL
             UNION
             SELECT 500, 'EEE', 12300, 8000
               FROM DUAL)
       SELECT *
         FROM DATA;
      
    EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'EMPLOYEE',granularity => 'PARTITION');
      
    SELECT   table_name, partition_name, num_rows
        FROM user_tab_partitions
       WHERE table_name = 'EMPLOYEE'
    ORDER BY partition_name;
      
    TABLE_NAME | PARTITION_NAME | NUM_ROWS
    EMPLOYEE   | SAL_200000     | 2
    EMPLOYEE   | SAL_400000     | 3
    EMPLOYEE   | SAL_600000     | 0
    EMPLOYEE   | SAL_800000     | 0
    EMPLOYEE   | SAL_DEFAULT    | 0
     
在分区情况下，不能更新虚拟列引用的列
 
    UPDATE employee
       SET monthly_sal = 30000
     WHERE empl_id = 500;
      
    ORA-14402: updating partition key column would cause a partition change
     
如果在分区情况能够更新，则需要设置ENABLE ROW MOVEMENT
 
    ALTER TABLE employee ENABLE ROW MOVEMENT;
      
    UPDATE employee
       SET monthly_sal = 80000
     WHERE empl_id = 500;
      
    1 row updated.
     
不能基于函数表达式的虚拟列上的分区
 
    CREATE TABLE employee_new
    (empl_id     NUMBER,
     empl_nm     VARCHAR2(50),
     monthly_sal NUMBER(10,2),
     bonus       NUMBER(10,2),
     total_sal   NUMBER(10,2) AS (get_empl_total_sal(monthly_sal, bonus))
    )
    PARTITION BY RANGE (total_sal)
        (PARTITION sal_200000 VALUES LESS THAN (200000),
         PARTITION sal_400000 VALUES LESS THAN (400000),
         PARTITION sal_600000 VALUES LESS THAN (600000),
         PARTITION sal_800000 VALUES LESS THAN (800000),
         PARTITION sal_default VALUES LESS THAN (MAXVALUE));
      
    ORA-54021: Cannot use PL/SQL expressions in partitioning or subpartitioning columns