fbpx
צור קשר

החל מגרסת 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 בא לידי ביטוי כאשר יש על העמודה הוירטואלית אינדקסים או סטטיסטיקות מתקדמות כגון היסטוגרמות.
בכל מקרה אחר, נכון יהיה לאמר שמבחינת ביצועים – אין לצורה אחת יתרון על פני השניה.

קורסים קרובים

עלינו

UpScale Analytics מתמחה במתן פתרונות הכשרה לארגונים וללקוחות פרטיים בתחומי הדאטה, ומהווה פלטפורמה ללימוד ותרגול אונליין לתלמידים מכל רחבי העולם.

קראו עוד >>

Facebook Linkedin Youtube

קורסים פופולריים

קישורים מהירים

Upscale Analytics 2022 © All Rights Reserved