5. Virtual Columns
Virtual Columns are either created at the time of creating or modifying a table. Virtual Columns are defined by expressions or functions, which will be included as part of table structure and hence its metadata. Virtual Columns are like Views, they don't use additional disk space for its data.
U1> CREATE TABLE student_mark(
2 stud_id NUMBER(5),
3 stud_name VARCHAR2(15),
4 subj1_mark NUMBER(3),
5 subj2_mark NUMBER(3),
6 subj3_mark NUMBER(3),
7 tot_mark AS (subj1_mark + subj2_mark + subj3_mark));
Table created.
U1> DESCRIBE student_mark
Name Null? Type
----------------------- -------- ----------------
STUD_ID NUMBER(5)
STUD_NAME VARCHAR2(15)
SUBJ1_MARK NUMBER(3)
SUBJ2_MARK NUMBER(3)
SUBJ3_MARK NUMBER(3)
TOT_MARK NUMBER
U1> DROP TABLE student_mark;
Table dropped.
U1> CREATE TABLE student_mark(
2 stud_id NUMBER(5),
3 stud_name VARCHAR2(15),
4 subj1_mark NUMBER(3),
5 subj2_mark NUMBER(3),
6 subj3_mark NUMBER(3),
7 tot_mark NUMBER(4) AS (subj1_mark + subj2_mark + subj3_mark));
Table created.
U1> DESCRIBE student_mark
Name Null? Type
----------------------- -------- ----------------
STUD_ID NUMBER(5)
STUD_NAME VARCHAR2(15)
SUBJ1_MARK NUMBER(3)
SUBJ2_MARK NUMBER(3)
SUBJ3_MARK NUMBER(3)
TOT_MARK NUMBER(4)
U1> INSERT
2 INTO student_mark(stud_id, stud_name, subj1_mark, subj2_mark, subj3_mark)
3 VALUES(10, 'Rose', 75, 45, 66);
1 row created.
U1> INSERT
2 INTO student_mark(stud_id, stud_name, subj1_mark, subj2_mark, subj3_mark)
3 VALUES(20, 'Bose', 45, 79, 88);
1 row created.
U1> SELECT *
2 FROM student_mark;
STUD_ID STUD_NAME SUBJ1_MARK SUBJ2_MARK SUBJ3_MARK TOT_MARK
---------- --------------- ---------- ---------- ---------- ----------
10 Rose 75 45 66 186
20 Bose 45 79 88 212
U1> ALTER TABLE student_mark
2 ADD avg_mark NUMBER(3) AS (((subj1_mark + subj2_mark + subj3_mark) / 3));
Table altered.
U1> DESCRIBE student_mark
Name Null? Type
----------------------- -------- ----------------
STUD_ID NUMBER(5)
STUD_NAME VARCHAR2(15)
SUBJ1_MARK NUMBER(3)
SUBJ2_MARK NUMBER(3)
SUBJ3_MARK NUMBER(3)
TOT_MARK NUMBER(4)
AVG_MARK NUMBER(3)
U1> INSERT
2 INTO student_mark(stud_id, stud_name, subj1_mark, subj2_mark, subj3_mark)
3 VALUES(30, 'Jose', 63, 68, 72);
1 row created.
U1> SELECT *
2 FROM student_mark;
STUD_ID STUD_NAME SUBJ1_MARK SUBJ2_MARK SUBJ3_MARK TOT_MARK AVG_MARK
---------- --------------- ---------- ---------- ---------- ---------- ----------
10 Rose 75 45 66 186 62
20 Bose 45 79 88 212 71
30 Jose 63 68 72 203 68
U1> ALTER TABLE student_mark DROP COLUMN avg_mark;
Table altered.
Attaching a user defined function with a virtual column:-
U1> CREATE OR REPLACE FUNCTION get_avg(stud_no IN NUMBER)
2 RETURN NUMBER deterministic IS subj_avg NUMBER(3);
3 BEGIN
4 SELECT((subj1_mark + subj2_mark + subj3_mark) / 3)
5 INTO subj_avg
6 FROM student_mark
7 WHERE stud_id = stud_no;
8 RETURN(subj_avg);
9 END;
10 /
Function created.
U1> ALTER TABLE student_mark
2 ADD avrg_mark AS (get_avg(stud_id));
Table altered.
U1> DESCRIBE student_mark
Name Null? Type
----------------------- -------- ----------------
STUD_ID NUMBER(5)
STUD_NAME VARCHAR2(15)
SUBJ1_MARK NUMBER(3)
SUBJ2_MARK NUMBER(3)
SUBJ3_MARK NUMBER(3)
TOT_MARK NUMBER(4)
AVRG_MARK NUMBER
U1> SELECT * FROM student_mark;
STUD_ID STUD_NAME SUBJ1_MARK SUBJ2_MARK SUBJ3_MARK TOT_MARK AVRG_MARK
---------- --------------- ---------- ---------- ---------- ---------- ----------
10 Rose 75 45 66 186 62
20 Bose 45 79 88 212 71
30 Jose 63 68 72 203 68
Lets see what happens when we drop the function:-
U1> DROP FUNCTION get_avg;
Function dropped.
U1> SELECT *
2 FROM student_mark;
SELECT *
*
ERROR at line 1:
ORA-00904: "U1"."GET_AVG": invalid identifier
U1> DESCRIBE student_mark
Name Null? Type
----------------------- -------- ----------------
STUD_ID NUMBER(5)
STUD_NAME VARCHAR2(15)
SUBJ1_MARK NUMBER(3)
SUBJ2_MARK NUMBER(3)
SUBJ3_MARK NUMBER(3)
TOT_MARK NUMBER(4)
AVRG_MARK NUMBER
U1> CREATE OR REPLACE FUNCTION get_avg(stud_no IN NUMBER)
2 RETURN NUMBER deterministic IS subj_avg NUMBER(3);
3 BEGIN
4 SELECT((subj1_mark + subj2_mark + subj3_mark) / 3)
5 INTO subj_avg
6 FROM student_mark
7 WHERE stud_id = stud_no;
8 RETURN(subj_avg);
9 END;
10 /
Function created.
U1> SELECT * FROM student_mark;
STUD_ID STUD_NAME SUBJ1_MARK SUBJ2_MARK SUBJ3_MARK TOT_MARK AVRG_MARK
---------- --------------- ---------- ---------- ---------- ---------- ----------
10 Rose 75 45 66 186 62
20 Bose 45 79 88 212 71
30 Jose 63 68 72 203 68
Partition on Virtual Columns
This enables to partition the table based on the expression or function that were used to create a virtual column. Lets take the below example:-
U1> CREATE TABLE phone_cust (
2 cust_id NUMBER(5),
3 name VARCHAR2(15),
4 street VARCHAR2(15),
5 city VARCHAR2(10),
6 zip NUMBER(5),
7 phone NUMBER(10),
8 area_code AS (SUBSTR(phone,1,3)||'-'||SUBSTR(phone,4,3)));
Table created.
U1> DESCRIBE phone_cust
Name Null? Type
----------------------- -------- ----------------
CUST_ID NUMBER(5)
NAME VARCHAR2(15)
STREET VARCHAR2(15)
CITY VARCHAR2(10)
ZIP NUMBER(5)
PHONE NUMBER(10)
AREA_CODE VARCHAR2(7)
U1> ALTER TABLE phone_cust modify area_code AS (SUBSTR(phone, 1, 3));
Table altered.
U1> DESCRIBE phone_cust
Name Null? Type
----------------------- -------- ----------------
CUST_ID NUMBER(5)
NAME VARCHAR2(15)
STREET VARCHAR2(15)
CITY VARCHAR2(10)
ZIP NUMBER(5)
PHONE NUMBER(10)
AREA_CODE VARCHAR2(3)
U1> SELECT * FROM phone_cust;
CUST_ID NAME STREET CITY ZIP PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
10 John 660 Boas Street Harrisburg 17102 7171234567 717
20 Raju 123 Mkt Street Lemoyne 17011 7174567891 717
U1> DROP TABLE phone_cust;
Table dropped.
U1> CREATE TABLE phone_cust(
2 cust_id NUMBER(5),
3 name VARCHAR2(15),
4 street VARCHAR2(15),
5 city VARCHAR2(10),
6 zip NUMBER(5),
7 phone NUMBER(10),
8 area_code AS (SUBSTR(phone, 1, 3)))
9 PARTITION BY range(area_code)(
10 PARTITION p1 VALUES less than(500) TABLESPACE encr_tbs_1,
11 PARTITION p2 VALUES less than(999) TABLESPACE encr_tbs_2)
12 enable ROW movement;
Table created.
U1> INSERT
2 INTO phone_cust(cust_id, name, street, city, zip, phone)
3 VALUES(10, 'Chel', '456 Walnut St', 'Philly', '19139', 2154567891);
1 row created.
U1> INSERT
2 INTO phone_cust(cust_id, name, street, city, zip, phone)
3 VALUES(20, 'John', '660 Boas Street', 'Harrisburg', '17102', 7171234567);
1 row created.
U1> INSERT
2 INTO phone_cust(cust_id, name, street, city, zip, phone)
3 VALUES(30, 'Raju', '123 Mkt Street', 'Lemoyne', '17011', 7174567891);
1 row created.
U1> INSERT
2 INTO phone_cust(cust_id, name, street, city, zip, phone)
3 VALUES(40, 'Appu', '3 Bridge Trc', 'Woodbridge', '07085', 7324567891);
1 row created.
U1> INSERT
2 INTO phone_cust(cust_id, name, street, city, zip, phone)
3 VALUES(50, 'Babu', '5 1st Ave', 'Manhattan', '10037', 2124567891);
1 row created.
U1> INSERT
2 INTO phone_cust(cust_id, name, street, city, zip, phone)
3 VALUES(60, 'Rangasamy', '12 Circle Dr', 'Redlands', '92415', 9094567891);
1 row created.
U1> commit;
Commit complete.
U1> DESCRIBE phone_cust
Name Null? Type
----------------------- -------- ----------------
CUST_ID NUMBER(5)
NAME VARCHAR2(15)
STREET VARCHAR2(15)
CITY VARCHAR2(10)
ZIP NUMBER(5)
PHONE NUMBER(10)
AREA_CODE VARCHAR2(3)
U1> SELECT * FROM phone_cust;
CUST_ID NAME STREET CITY ZIP PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
10 Chel 456 Walnut St Philly 19139 2154567891 215
50 Babu 5 1st Ave Manhattan 10037 2124567891 212
20 John 660 Boas Street Harrisburg 17102 7171234567 717
30 Raju 123 Mkt Street Lemoyne 17011 7174567891 717
40 Appu 3 Bridge Trc Woodbridge 7085 7324567891 732
60 Rangasamy 12 Circle Dr Redlands 92415 9094567891 909
U1> SELECT * FROM phone_cust PARTITION(p1);
CUST_ID NAME STREET CITY ZIP PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
10 Chel 456 Walnut St Philly 19139 2154567891 215
50 Babu 5 1st Ave Manhattan 10037 2124567891 212
U1> SELECT * FROM phone_cust PARTITION(p2);
CUST_ID NAME STREET CITY ZIP PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
20 John 660 Boas Street Harrisburg 17102 7171234567 717
30 Raju 123 Mkt Street Lemoyne 17011 7174567891 717
40 Appu 3 Bridge Trc Woodbridge 7085 7324567891 732
60 Rangasamy 12 Circle Dr Redlands 92415 9094567891 909
U1> UPDATE phone_cust SET phone = 2121234567 WHERE cust_id = 20;
1 row updated.
U1> commit;
Commit complete.
U1> SELECT * FROM phone_cust;
CUST_ID NAME STREET CITY ZIP PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
10 Chel 456 Walnut St Philly 19139 2154567891 215
50 Babu 5 1st Ave Manhattan 10037 2124567891 212
20 John 660 Boas Street Harrisburg 17102 2121234567 212
30 Raju 123 Mkt Street Lemoyne 17011 7174567891 717
40 Appu 3 Bridge Trc Woodbridge 7085 7324567891 732
60 Rangasamy 12 Circle Dr Redlands 92415 9094567891 909
6 rows selected.
U1> SELECT * FROM phone_cust PARTITION(p1);
CUST_ID NAME STREET CITY ZIP PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
10 Chel 456 Walnut St Philly 19139 2154567891 215
50 Babu 5 1st Ave Manhattan 10037 2124567891 212
20 John 660 Boas Street Harrisburg 17102 2121234567 212
U1> SELECT * FROM phone_cust PARTITION(p2);
CUST_ID NAME STREET CITY ZIP PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
30 Raju 123 Mkt Street Lemoyne 17011 7174567891 717
40 Appu 3 Bridge Trc Woodbridge 7085 7324567891 732
60 Rangasamy 12 Circle Dr Redlands 92415 9094567891 909
