החל מגרסת 11g אנו יכולים לבנות בטבלה עמודות אשר ערכיהן נגזרים מביטוי כלשהו. עמודות אלו למעשה ריקות והערכים המיוצגים בהן אלו לא נשמרים בטבלה ולא תופסים מקום.
מלבד היתרון הברור – יכולת הצגת ערכים מחושבים בצורה פשוטה ונוחה, כפי שנראה בדוגמאות הבאות, לעמודות אלה יתרונות נוספים – ניתן לייצר עליהן אינדקסים, לאסוף עליהן סטטיסטיקות, להשתמש בהן כתחליף לטריגר אשר מחשב ומכניס ערכים לעמודה, כתחליף ל View אשר מציג ערכים מחושבים, ועוד.
Creating Virtual Column
כאמור, יתרון בסיסי בעבודה מול עמודות אלו הוא הצגת ביטויים בצורה פשוטה ונוחה. נתחיל בהדגמה קצרה, הקוד הבא מייצר טבלת לקוחות עם שתי עמודות ווירטואליות :
· after_discount המציגה את המחיר אותו הלקוח משלם לאחר ההנחה שניתנה לו (כנגזרת של discount ו – payment)
· email המציגה את מייל הלקוח כנגזרת של שמו הפרטי ושם משפחתו.
> CONN ram/kedem Connected. RAM > RAM > RAM > COL LAST_NAME FOR A10 RAM > COL FIRST_NAME FOR A10 RAM > SET LINES 200 RAM > RAM > DROP TABLE test_customers ; Table dropped. RAM > RAM > CREATE TABLE test_customers 2 (id number(5), 3 first_name varchar2(10), 4 last_name varchar2(10), 5 payment number(8,2), 6 discount number(3), 7 after_discount 8 generated always as (payment - discount) virtual, 9 email 10 generated always as (substr(first_name , 1 ,1) || substr(last_name , 1 , 4) || '@gmail.com')virtual); Table created. RAM > RAM > INSERT INTO test_customers (id, first_name , last_name , payment, discount) 2 VALUES (1, 'Moshe' , 'Cohen' , 80, 15); 1 row created. RAM > RAM > COL LAST_NAME FOR A10 RAM > COL FIRST_NAME FOR A10 RAM > SET LINES 200 RAM > SELECT * FROM test_customers ; ID FIRST_NAME LAST_NAME PAYMENT DISCOUNT AFTER_DISCOUNT EMAIL ---------- ---------- ---------- ---------- ---------- -------------- --------------- 1 Moshe Cohen 80 15 65 MCohe@gmail.com RAM > RAM > INSERT INTO test_customers 2 VALUES (2, 'Yossi' , 'Levi' , 90 , 20 , 70 , 'Ylevi@gmail.com' ) ; INSERT INTO test_customers * ERROR at line 1: ORA-54013: INSERT operation disallowed on virtual columns RAM > RAM > UPDATE test_customers 2 SET email = 'ram@gmail.com' 3 WHERE id = 1 ; UPDATE test_customers * ERROR at line 1: ORA-54017: UPDATE operation disallowed on virtual columns RAM > RAM > COMMIT ; Commit complete. RAM > RAM > DROP TABLE test_customers ; Table dropped. RAM >
ניתן לראות את תוצאות הערכים באותן עמודות לאחר אכלוס הטבלה, שימו לב כי פעולות Update או Insert ישירות על ערכים אלו יובילו לשגיאה.
Virtual Column Metadata
את המידע אודות העמודות המחושבות ניתן לקבל באמצעות טבלאות המערכת הבאות :
RAM > SELECT column_name , 2 data_default 3 FROM user_tab_columns 4 WHERE table_name = 'TEST_CUSTOMERS'; COLUMN_NAME DATA_DEFAULT ------------------------------ ------------------------------------------------------------------ ID FIRST_NAME LAST_NAME PAYMENT DISCOUNT AFTER_DISCOUNT "PAYMENT"-"DISCOUNT" EMAIL SUBSTR("FIRST_NAME",1,1)||SUBSTR("LAST_NAME",1,4)||'@gmail.com' 7 rows selected. RAM > RAM > -- Using user_tab_cols RAM > RAM > SELECT table_name , column_name , virtual_column 2 FROM user_tab_cols 3 WHERE table_name = 'TEST_CUSTOMERS' ; TABLE_NAME COLUMN_NAME VIR ------------------------------ ------------------------------ --- TEST_CUSTOMERS ID NO TEST_CUSTOMERS FIRST_NAME NO TEST_CUSTOMERS LAST_NAME NO TEST_CUSTOMERS PAYMENT NO TEST_CUSTOMERS DISCOUNT NO TEST_CUSTOMERS AFTER_DISCOUNT YES TEST_CUSTOMERS EMAIL YES 7 rows selected. RAM >
Virtual Column Storage
כפי שנאמר בפתח הפוסט, עמודות אלו לא מכילות נתונים ולא תופסות מקום. נייצר שתי טבלאות כאשר מלבד העמודות המחושבות של הטבלה הראשונה – שתיהן זהות, נאכלס אותן בנתונים ונבדוק את גודלן.
RAM > CREATE TABLE test_customers 2 (id number(5), 3 first_name varchar2(10), 4 last_name varchar2(10), 5 payment number(8,2), 6 discount number(3), 7 after_discount 8 generated always as (payment - discount) virtual, 9 email 10 generated always as (substr(first_name , 1 ,1) || substr(last_name , 1 , 4) || '@gmail.com')virtual); Table created. RAM > RAM > RAM > CREATE TABLE test_customers_2 2 AS 3 SELECT id, first_name , last_name , payment, discount 4 FROM test_customers ; Table created. RAM > RAM > INSERT INTO test_customers_2 (id, first_name , last_name , payment, discount) 2 SELECT 3 object_id , 4 SUBSTR(object_name , 1 , 5) , 5 SUBSTR(object_name , 5 , 5) , 6 object_id , 7 30 8 FROM all_objects ; 71588 rows created. RAM > RAM > SELECT COUNT(*) FROM test_customers_2 ; COUNT(*) ---------- 71588 RAM > RAM > INSERT INTO test_customers (id, first_name , last_name , payment, discount) 2 SELECT 3 object_id , 4 SUBSTR(object_name , 1 , 5) , 5 SUBSTR(object_name , 5 , 5) , 6 object_id , 7 30 8 FROM all_objects ; 71588 rows created. RAM > RAM > SELECT COUNT(*) FROM test_customers ; COUNT(*) ---------- 71588 RAM > RAM > SELECT segment_name , bytes, blocks, extents 2 FROM user_segments 3 WHERE segment_name LIKE '%TEST_CUSTOMERS%'; SEGMENT_NAME BYTES BLOCKS EXTENTS --------------------------------------------------------------------------------- ---------- ---------- ---------- TEST_CUSTOMERS 3145728 384 18 TEST_CUSTOMERS_2 3145728 384 18 RAM >
ניתן לראות כי גודל הטבלאות זהה
Index Virtual Column
עמודות אלה ניתנות לאינדוקס, זהו יתרון נהדר כאשר יש צורך לבצע סינון עפ"י ביטוי מסויים, שימו לב להבדלי ה Cost של תוכניות הפעולה :
RAM > CREATE TABLE test_customers 2 (id number(5), 3 first_name varchar2(10), 4 last_name varchar2(10), 5 payment number(8,2), 6 discount number(3), 7 after_discount 8 generated always as (payment - discount) virtual, 9 email 10 generated always as (substr(first_name , 1 ,1) || substr(last_name , 1 , 4) || '@gmail.com')virtual); Table created. RAM > RAM > INSERT INTO test_customers (id, first_name , last_name , payment, discount) 2 SELECT 3 object_id , 4 SUBSTR(object_name , 1 , 5) , 5 SUBSTR(object_name , 5 , 5) , 6 object_id , 7 30 8 FROM all_objects ; 71588 rows created. RAM > RAM > COMMIT ; Commit complete. RAM > RAM > SELECT * FROM test_customers WHERE rownum <= 10 ; ID FIRST_NAME LAST_NAME PAYMENT DISCOUNT AFTER_DISCOUNT EMAIL ---------- ---------- ---------- ---------- ---------- -------------- --------------- 304 I_TSM M_SRC 304 30 274 IM_SR@gmail.com 305 I_TSM M_SRC 305 30 275 IM_SR@gmail.com 306 TSM_D DST$ 306 30 276 TDST$@gmail.com 307 I_TSM M_DST 307 30 277 IM_DS@gmail.com 308 I_TSM M_DST 308 30 278 IM_DS@gmail.com 309 TSM_M MIG_S 309 30 279 TMIG_@gmail.com 310 SERVI ICE$ 310 30 280 SICE$@gmail.com 311 CLUST TER_D 311 30 281 CTER_@gmail.com 312 CLUST TER_N 312 30 282 CTER_@gmail.com 313 CLUST TER_I 313 30 283 CTER_@gmail.com 10 rows selected. RAM > SELECT * FROM test_customers WHERE email = 'IAP_M@gmail.com' ; Execution Plan ---------------------------------------------------------- Plan hash value: 1250064393 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 3 | 138 | 108 (6)| 00:00:02 | |* 1 | TABLE ACCESS FULL| TEST_CUSTOMERS | 3 | 138 | 108 (6)| 00:00:02 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("EMAIL"='IAP_M@gmail.com') RAM > RAM > -- Index email virtual column RAM > CREATE INDEX cust_mail_ix ON test_customers(email) ; Index created. RAM > RAM > SELECT * FROM test_customers WHERE email = 'IAP_M@gmail.com' ; Execution Plan ---------------------------------------------------------- Plan hash value: 4162253353 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 138 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_CUSTOMERS | 3 | 138 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | CUST_MAIL_IX | 3 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMAIL"='IAP_M@gmail.com') RAM > RAM > SET AUTOTRACE OFF RAM >
אך כאשר יש צורך לבצע סינון עפ"י ביטוי מסויים, מדוע לא לבנות Function Based Index מבלי לייצר עמודה ווירטואלית ? התשובה מאוד מעניינת – עמודה וירטואלית נוצרת בכל מקרה כאשר אנו מייצרים אינדקס מבוסס פונקציה 🙂
נדגים זאת באמצעות הקוד הבא, ראשית ננסה לייצר אינדקס על הביטוי של העמודה הוירטואלית – discount, שימו לב כי לא ניתן לייצר על ביטוי זה אינדקס מכיוון שהמערכת מזהה כי קיימת עמודה ווירטואלית בעלת אותו ביטוי.
RAM > CREATE INDEX after_discount_ix ON test_customers ( payment - discount) ; CREATE INDEX after_discount_ix ON test_customers ( payment - discount) * ERROR at line 1: ORA-54018: A virtual column exists for this expression RAM >
נמחק את העמודה הוירטואלית וננסה שנית
RAM > ALTER TABLE test_customers 2 DROP COLUMN after_discount ; Table altered. RAM > RAM > CREATE INDEX after_discount_ix ON test_customers ( payment - discount) ; Index created.
כעת נצפה בתוכנית הפעולה הממשית כאשר אנו מבצעים סינון עפ”י אותו ביטוי
RAM > SELECT * FROM test_customers WHERE payment - discount = 10 ; ID FIRST_NAME LAST_NAME PAYMENT DISCOUNT EMAIL ---------- ---------- ---------- ---------- ---------- --------------- 40 I_OBJ J5 40 30 IJ5@gmail.com RAM > RAM > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 61ytuc92fsqz6, child number 2 ------------------------------------- SELECT * FROM test_customers WHERE payment - discount = 10 Plan hash value: 3412534715 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_CUSTOMERS | 1 | 46 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | AFTER_DISCOUNT_IX | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TEST_CUSTOMERS"."SYS_NC00007$"=10)
שימו לב כי תוכנית הפעולה מראה כי החיפוש מתבצע באמצעות גישה לעמודה וירטואלית חדשה בשם – SYS_NC00007 , זוהי עמודה אשר נוצרה באופן אוטומטי. ניתן לראות אותה בטבלאות המערכת וכן לתשאל אותה :
RAM > RAM > SELECT table_name , column_name , virtual_column 2 FROM user_tab_cols 3 WHERE table_name = 'TEST_CUSTOMERS' ; TABLE_NAME COLUMN_NAME VIR ------------------------------ ------------------------------ --- TEST_CUSTOMERS ID NO TEST_CUSTOMERS FIRST_NAME NO TEST_CUSTOMERS LAST_NAME NO TEST_CUSTOMERS PAYMENT NO TEST_CUSTOMERS DISCOUNT NO TEST_CUSTOMERS EMAIL YES TEST_CUSTOMERS SYS_NC00007$ YES 7 rows selected. RAM > RAM > SELECT ID, 2 FIRST_NAME, 3 LAST_NAME, 4 PAYMENT, 5 DISCOUNT, 6 SYS_NC00007$, 7 EMAIL 8 FROM TEST_CUSTOMERS 9 WHERE ROWNUM <= 10; ID FIRST_NAME LAST_NAME PAYMENT DISCOUNT SYS_NC00007$ EMAIL ---------- ---------- ---------- ---------- ---------- ------------ --------------- 304 I_TSM M_SRC 304 30 274 IM_SR@gmail.com 305 I_TSM M_SRC 305 30 275 IM_SR@gmail.com 306 TSM_D DST$ 306 30 276 TDST$@gmail.com 307 I_TSM M_DST 307 30 277 IM_DS@gmail.com 308 I_TSM M_DST 308 30 278 IM_DS@gmail.com 309 TSM_M MIG_S 309 30 279 TMIG_@gmail.com 310 SERVI ICE$ 310 30 280 SICE$@gmail.com 311 CLUST TER_D 311 30 281 CTER_@gmail.com 312 CLUST TER_N 312 30 282 CTER_@gmail.com 313 CLUST TER_I 313 30 283 CTER_@gmail.com 10 rows selected. RAM >
Using Virtual Column Instead Of Triggers
במקרים מסויימים עמודות וירטואליות מהוות חלופה נהדרת לטריגרים, ובמקרים אחרים בהם אנו מנסים לעשות דברים אשר מעבר למגבלותיהן עדיין ישנו הצורך להשתמש בטריגרים.
דוגמא א – עמודות וירטואליות יכולות להכיל פונקציות דטרמיניסטיות בלבד, מכיוון שבדוגמא הבאה קיים נסיון לעבוד עם פונקצית sysdate יצירת הטבלה נכשלת.
לכן – נייצר את העמודה המחושבת כעמודה רגילה, נייצר על הטבלה view כך שלא תהיה גישה אל אותה עמודה, ונייצר טריגר אשר מבצע את הפעולה החישובית.
RAM > -- Error RAM > CREATE TABLE orders 2 (order_id number, 3 order_total number, 4 order_discount 5 generated always as 6 (case when to_char(sysdate , 'd')=7 then 20 7 else 30 8 end) virtual) 9 / (case when to_char(sysdate , 'd')=7 then 20 * ERROR at line 6: ORA-54002: only pure functions can be specified in a virtual column expression RAM > -- Using a Trigger RAM > RAM > CREATE TABLE orders 2 (order_id number, 3 order_total number, 4 order_discount number); Table created. RAM > RAM > CREATE OR REPLACE VIEW orders_view 2 AS 3 SELECT order_id, order_total 4 FROM orders ; View created. RAM > RAM > RAM > CREATE OR REPLACE TRIGGER order_discount_trig 2 BEFORE INSERT 3 ON orders 4 FOR EACH ROW 5 DECLARE 6 v_day varchar2(1); 7 v_discount number ; 8 BEGIN 9 v_day := to_char(sysdate , 'd') ; 10 IF v_day = '7' THEN 11 :new.order_discount := 20 ; 12 ELSE 13 :new.order_discount := 30; 14 END IF ; 15 END ; 16 / Trigger created. RAM > RAM > INSERT INTO orders_view VALUES (1, 560) ; 1 row created. RAM > RAM > SELECT * FROM orders_view ; ORDER_ID ORDER_TOTAL ---------- ----------- 1 560 RAM > RAM > SELECT * FROM orders ; ORDER_ID ORDER_TOTAL ORDER_DISCOUNT ---------- ----------- -------------- 1 560 30 RAM >
דוגמא ב – עמודות וירטואליות לא יכולות לבצע חישוב על עמודה וירטואלית נוספת. מכיוון שבדוגמא הבאה קיים נסיון לייצר עמודה וירטואלית כנגזרת של עמודה וירטואלית אחרת, יצירת הטבלה נכשלת.
לכן, כמו בדוגמא הקודמת – נייצר את העמודה המחושבת כעמודה רגילה, נייצר על הטבלה view כך שלא תהיה גישה אל אותה עמודה, ונייצר טריגר אשר מבצע את הפעולה החישובית.
RAM > DROP TABLE orders ; Table dropped. RAM > RAM > -- Error RAM > CREATE TABLE orders 2 (order_id number, 3 order_total number, 4 order_discount generated always as (order_total * 30 / 100) virtual, 5 discount_grade generated always as (case when order_discount < 100 then 'Low' else 'High' end) virtual); order_discount generated always as (order_total * 30 / 100) virtual, * ERROR at line 4: ORA-54012: virtual column is referenced in a column expression RAM > RAM > -- Using a Trigger RAM > RAM > DROP TABLE orders ; DROP TABLE orders * ERROR at line 1: ORA-00942: table or view does not exist RAM > RAM > CREATE TABLE orders 2 (order_id number, 3 order_total number, 4 order_discount generated always as (order_total * 30 / 100) virtual, 5 discount_grade varchar2(4)); Table created. RAM > RAM > RAM > CREATE OR REPLACE VIEW orders_view 2 AS 3 SELECT order_id, order_total 4 FROM orders ; View created. RAM > RAM > CREATE OR REPLACE TRIGGER order_discount_trig 2 BEFORE INSERT 3 ON orders 4 FOR EACH ROW 5 BEGIN 6 IF :new.order_discount < 100 THEN 7 :new.discount_grade := 'Low' ; 8 ELSE 9 :new.discount_grade := 'High' ; 10 END IF ; 11 END ; 12 / Trigger created. RAM > RAM > INSERT INTO orders_view VALUES (1, 560) ; 1 row created. RAM > RAM > INSERT INTO orders_view VALUES (2, 20) ; 1 row created. RAM > RAM > SELECT * FROM orders_view ; ORDER_ID ORDER_TOTAL ---------- ----------- 1 560 2 20 RAM > RAM > SELECT * FROM orders ; ORDER_ID ORDER_TOTAL ORDER_DISCOUNT DISC ---------- ----------- -------------- ---- 1 560 168 High 2 20 6 Low RAM >
Using Virtual Column Instead Of View
היתרון המשמעותי של Virtual Columns לעומת View בא לידי ביטוי כאשר יש על העמודה הוירטואלית אינדקסים או סטטיסטיקות מתקדמות כגון היסטוגרמות.
בכל מקרה אחר, נכון יהיה לאמר שמבחינת ביצועים – אין לצורה אחת יתרון על פני השניה.