Oracle 11g virtual Column

The database of the Oracle 11g virtual column Acme contains a table named SALES, as shown in the preceding figure. The table structure is as follows: SALES_IDNUMBERCUST_IDNUMBERSALES_AMTNUMBER some users want to add a column named SALE_CATEGORY to identify the sales type based on the sales volume and the current customer: LOW, MEDIUM, HIGH, and ULTRA. This column will help them identify the corresponding action records and route the records to relevant personnel for processing. The logic of the column value is as follows: if sale_amt is greater than and sale_amt is less than or equal to: sale_category is 01000low10001100000medium1_11000000high000001 unlimited ULTRA although this column is an important business requirement, however, the development team does not want to change the code to create the necessary logic. Of course, you can add a new column named sale_category to the table and write a trigger to fill the column with the above logic-a fairly simple operation. However, context switching with the trigger code may cause performance problems. In Oracle Database 11g, you do not need to write any trigger code. You only need to add a virtual column. Virtual columns provide you with the flexibility to add columns that convey business awareness without increasing complexity or performance impact. Create table sales 2 (3 sales_id number, 4 cust_id number, 5 sales_amt number, 6 sale_category varchar2 (6) 7 generated always as 8 (9 case 10 when sales_amt <= 10000 then 'low' 11 when sales_amt> 10000 and sales_amt <= 100000 then 'medium' 12 when sales_amt> 100000 and sales_amt <= 1000000 then 'high' 13 else' ULTRA '14 end 15) virtual 16); note that 6-7 rows; this column is specified as "generated always ", Which means that the column values are generated at runtime, rather than stored as part of the table. The clause is followed by a detailed CASE statement to calculate the value. Finally, in row 15th, "virtual" is specified to enhance the fact that this is a virtual column. Now, if you insert some records: SQL> insert into sales (sales_id, cust_id, sales_amt) values (100,); 1 row created. SQL> insert into sales (sales_id, cust_id, sales_amt) values (2,102,150 0); 1 row created. SQL> insert into sales (sales_id, cust_id, sales_amt) values (3,102,100 000); 1 row created. SQL> commit; Commit complete. SQL> select * from sales; SALES_ID CUST_ID SALES_AMT SALE_C ---------------------------- -- ------ 1 1 100 LOW2 102 1500 LOW3 102 100000 MEDIUM 3 rows selected. The virtual column values will be filled as usual. Even if the column is not stored, You can regard it as any other column in the table, or even create an index on it. SQL> create index in_sales_cat on sales (sale_category); Index created. The result is a function-based index. SQL> select index_type 2 from user_indexes 3 where index_name = 'in _ SALES_CAT '; INDEX_TYPE---------------------------FUNCTION-BASED normal SQL> select column_expression 2 from user_ind_expressions 3 where index_name = 'in _ SALES_CAT '; COLUMN_EXPRESSION--------------------------------------------------------------------------------CASE WHEN "SALES_AMT" <= 10000 THEN 'low' WHEN ("SALES_AMT"> between and "SA LES_AMT "<= 100000) then case when" CUST_ID "<101 THEN 'low' WHEN (" CUST_ID "> = 101 AND" CUST_ID "<= 200) THEN 'medium 'else' MEDIUM 'end WHEN ("SALES_AMT"> 100000 AND "SALES_AMT" <= 1000000) then case when "CUST_ID" <101 THEN 'medium 'WHEN ("CUST_ID"> = 101 AND "CUST_ID" <= 200) THEN 'high' else' ULTRA 'end else' ULTRA 'END you can even partition on this column, as described in this series partition article. However, you cannot enter a value for this column. If you try to enter a value, you will soon receive the ERROR message: insert into sales values (5,100,300, 'high'); * ERROR at line 1: ORA-54013: INSERToperation disallowed on virtual columns ---------------------------- Present By Dylan.