Virtual Columns in Oracle Database 11g Release 1

When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below.

column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.

The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form.

CREATE TABLE employees (
  id          NUMBER,
  first_name  VARCHAR2(10),
  last_name   VARCHAR2(10),
  salary      NUMBER(9,2),
  comm1       NUMBER(3),
  comm2       NUMBER(3),
  salary1     AS (ROUND(salary*(1+comm1/100),2)),
  salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
  CONSTRAINT employees_pk PRIMARY KEY (id)
);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (1, 'JOHN', 'DOE', 100, 5, 10);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (2, 'JAYNE', 'DOE', 200, 10, 20);
COMMIT;

Querying the table shows the inserted data plus the derived commission-based salaries.

SELECT * FROM employees;

        ID FIRST_NAME LAST_NAME      SALARY      COMM1      COMM2    SALARY1    SALARY2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1 JOHN       DOE               100          5         10        105        110
         2 JAYNE      DOE               200         10         20        220        240

2 rows selected.

SQL>

The expression used to generate the virtual column is listed in the DATA_DEFAULT column of the [DBA|ALL|USER]_TAB_COLUMNS views.

COLUMN data_default FORMAT A50
SELECT column_name, data_default
FROM   user_tab_columns
WHERE  table_name = 'EMPLOYEES';

COLUMN_NAME                    DATA_DEFAULT
------------------------------ --------------------------------------------------
ID
FIRST_NAME
LAST_NAME
SALARY
COMM1
COMM2
SALARY1                        ROUND("SALARY"*(1+"COMM1"/100),2)
SALARY2                        ROUND("SALARY"*(1+"COMM2"/100),2)

8 rows selected.

SQL>

Notes and restrictions on virtual columns include:

• Indexes defined against virtual columns are equivalent to function-based indexes.
• Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML.
• Tables containing virtual columns can still be eligible for result caching.
• Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled:
  
  • Constraint on the virtual column must be disabled and re-enabled.
  • Indexes on the virtual column must be rebuilt.
  • Materialized views that access the virtual column must be fully refreshed.
  • The result cache must be flushed if cached queries have accessed the virtual column.
  • Table statistics must be regathered.
• Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
• The expression used in the virtual column definition has the following restrictions:
  
  • It cannot refer to another virtual column by name.
  • It can only refer to columns defined in the same table.
  • If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.
  • The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.

Dağıtık Sorgularda Performans İyileştirmesi ( DRIVING_SITE )

Bu yazıda sizlerle dağıtık sorguların performans iyileştirmesi ile ilgili driving_site hintinden ve kullanımından bahs edicem. Umarım farkındalık anlamında faydalı bir yazı olur.

IT dünyasında genellikle farklı veritabanlarında genellikle büyük boyutlu datalar üzerinde oldukça sık işlem yapıyoruz ve bu işlemlerimizi  OLTP ve OLAP sistemlerde dblink’ler aracılığı ile yürütüyoruz. Hepimizin deneyimlediği gibi farklı veritabanlarıdaki datalar üzerinden işlem yapılmaya başlandığı zaman araya network’ude koyduğumuzdan ötürü sorgu performanslarında bir miktar yavaşlama olduğunu görüyoruz. Bu yavaşlama network’e çıkardığımız data miktarı arttığında, lineer olarak performans üzerinde negatif etki yaratarak sorgunun dahada yavaşlamasına neden olmakta. Tek dblink üzerinden farklı makinadaki bir datayı paralel’de çekemiyeceğimizden ötürü performans anlamında yapacak çokta birşey kalmıyor. İşte tam bu noktada Oracle’ın driving_site hinti devreye girip dağıtık sorgu performansımızı geliştirmeye yönelik pozitif etki yapıyor. Bu etkiyi daha iyi ifade edebilme adına bu noktadan sonra örnekler ile ilerleyeceğim. Örnekleri Oracle 10g R2 sürümünde gerçekledim.

Öncelikle 2 Farklı Veritabanım var, A ve B

Şimdi A ve B veritablarında tablo yaratıp örnek sorgu çalıştırıp performansı inceleyelim.

A--> Veritabanındaki İşlemler

CREATE TABLE departments(department_id INTEGER, department_name VARCHAR2(100));

INSERT INTO departments
VALUES (1, ‘finans’);

INSERT INTO departments
VALUES (2, ‘Satis’);

INSERT INTO departments
VALUES (3, ‘Uretim’);

INSERT INTO departments
VALUES (4, ‘Bilgi Islem’);

INSERT INTO departments
VALUES (5, ‘HR’);

COMMIT;

B --> Veritabanındaki İşlemler

CREATE TABLE musteri (musteri_no INTEGER, ad VARCHAR2(1000), departments INTEGER);

– Index kullanabilecegim bir sorgu yazmak istedigimden
–departments kolonuna index atiyorum.

CREATE INDEX ind_dep ON musteri(departments);

– Tablo içine 50 bin, departments kolonu 5 olan kayit ekledim.
BEGIN
FOR i IN 1 .. 50000
LOOP
EXECUTE IMMEDIATE ‘insert into musteri values(:1,:2,5)’
USING i, ‘ad_’ || i;
END LOOP;

COMMIT;
END;

– Tablo içine 1M, departments kolonu 1,2,3,4 olan kayıt –ekledim.
BEGIN
FOR i IN 1 .. 1000000
LOOP
EXECUTE IMMEDIATE ‘insert into musteri values(:1,:2,:3)’
USING i, ‘ad_’ || i, MOD (i, 4) + 1;
END LOOP;

COMMIT;
END;

Şimdi  A ve B veritabanında 2 tablo oluşturduk. A veritabanındaki departments tablosu bir lookup ve içeride toplam 5 kayıt var. B veritabanındaki müşteri tablosu oda bir lookup ve içinde 1050000 kayıt var. Şimdi A veritabanında aşağıdaki sorguları çalıştırıp sonuçları inceleyelim.

İlk etapta hiç hint kullanmadığımız yöntemde execution plan ile sorgu çalışma zamanına bakalım.

EXPLAIN  PLAN FOR SELECT a.ad, b.department_name
FROM musteri@myLink a, departments b
WHERE a.departments = b.department_id
AND a.departments = 5;

SET linesize 2000
SELECT *
FROM TABLE (DBMS_XPLAN.display);

Evet execution plan’dan da görüldüğü üzere yazdığımız sorgu karakteristiği gereği index kullanması gerekirken dblink ile erişim yaptığımızdan dolayı karşı taraftaki tablo datasının tamamını network’e çıkardı, datanın tamamını sorguyu çalıştırdığımız tarafa getirdi ve işlemi kendi local’imizde yaptı. Ayrıca dblink ile karşı tarafta bir tabloya bağlandığımız için local makinemiz karşı tarafın metadata’sından haberdar değil bu yüzden var olan index’ide kullanamadı. Şimdi sorgunun ne kadar sürede bittiğini görelim.

SET timing on
SELECT a.ad, b.department_name
FROM musteri@myLink a, departments b
WHERE a.departments = b.department_id   AND a.departments = 5;

/*
 50000 rows selected.
Elapsed: 00:00:29.63
*/

Şimdi driving_site hintimiz’i yazarak execution plan inceleyip sorgunun ne kadar sürede bittiğini görelim.

EXPLAIN  PLAN FOR SELECT /*+ DRIVING_SITE(a) */ a.ad, b.department_name
FROM musteri@myLink a, departments b
WHERE a.departments = b.department_id
AND a.departments = 5;

SET linesize 2000
SELECT *
FROM TABLE (DBMS_XPLAN.display);

Evet execution plan’dan da anlasildigi uzere sorgumuz index kullanabildi. Bunun nedeni yazdığımız hint kaynaklı local’imizde bulunan küçük tabloyu karşı tarafa transfer edip işlemi karşı tarafta yapıp sonuç dataset’ini network’e çıkaraıp işlemi tamamladı. Böylelikle hem network üzerine ihtiyacımız olmayan dataları çıkarmadı hemde işlemi remote’da yaptığından dolayı index kullanabildi. Şimdi sorgumuzun performansına bakalım.

SET timing on
SELECT /*+ DRIVING_SITE(a) */
a.ad, b.department_name
FROM musteri@myLink a, departments b
WHERE a.departments = b.department_id AND a.departments = 5;

/*
50000 rows selected.
Elapsed: 00:00:20.80
*/

Evet sonuçlardan da anlaşıldığı üzere bu tarz case’lerde işlemi remote’da yaptırarak sorgu performansımızı oldukça iyi seviyelere çekebiliriz. Ancak hint yazmak riskli bir işlem olduğundan kullanmadan önce sorgumuzun karakteristiğini iyi incelememiz gerekmektedir. Aksi takdirde pozitif etki beklerken negatif sonuçlar elde edebiliriz.

Kaynaklar

http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements006.htm

http://www.dba-oracle.com/t_driving_site_hint.htm

http://rohanrajkalra.blogspot.com/2008/11/drivingsite-hint-and-distributed.html

Tablodaki Herhangi Bir Alanı Random Bir Değerle Doldurmak

Kullanmakta oldugumuz  tablolarda  number olan bir alanı birbirinden farklı değerlerle doldurmak gibi bazı gereksinimlerimiz olusabiliyor cogu zaman.. Bende burdan yola çıkarak kapsamı biraz daha genişleterek number, string ve date alanları için birbirinden farklı olarak değerlerle bu alanları nasıl doldurabiliriz diye ufak bir çalışma yaptım.
Şimdi çalışmamız için örnek bir tablo oluşturalım.

create table deneme
(
id number,
name varchar2(10),
sdate date
) ;
Table created.
Şimdi bikaç tane örnek data oluşturalım.
insert into deneme values (1,erdem,trunc(sysdate)) ;
insert into deneme values (2,erdem,trunc(sysdate)) ;
insert into deneme values (3,erdem,trunc(sysdate)) ;
commit ;
Aşağıdaki çalışmayı biz 180.000 kayıt üzerinde yaptık ama burda sadece örnek olması açısından 3 kayıt üzerinde yapıyorum.

select * from deneme ;
ID            NAME SDATE
———- ———- ———
1              erdem   05-KAS-10
2              erdem   05-KAS-10
3              erdem   05-KAS-10
3 rows selected.

Öncelikle number olan ıd alanındaki tüm değerleri random olarak farklı değerler olacak şekilde update edelim.
UPDATE deneme m
SET id =
(
SELECT s.val
FROM (
select val, id from
(select rowid rd from deneme) a, deneme b, (SELECT trunc(dbms_random.value(1,10000)) val FROM dual) c
where a.rd = b.rowid
and a.rd = m.rowid
) s
WHERE m.id = s.id
);
3 rows updated.
Select * from deneme;
ID            NAME  SDATE
———- ———- ———
2283        erdem 05-KAS-10
5817        erdem 05-KAS-10
8495        erdem 05-KAS-10
3 rows selected.

Scripteki “dbms_random.value(1,10000) “ ifadesinde (1,10000) ifadesi 1 ile 10000 değerleri arasında random bir kayıt döndür demektir. Siz tediğiniz aralığı belirtebilirsiniz.
Ikinci örneğimize geldik varchar olan string bir alanı random olarak dolduralım şimdide ;
UPDATE deneme m
SET (name) =
(
select val from
(select rowid rd from deneme) a, (SELECT dbms_random.string(‘X’, 9) val FROM dual) c
where
a.rd = m.rowid
);
3 rows updated.
Select * from deneme ;
ID           NAME           SDATE
———- ———-         ———
2283    TZZV8RE2N   05-KAS-10
5817    BEQ5BSIDH   05-KAS-10
8495    2ECDB3U3Z   05-KAS-10
3 rows selected.

Burada da varchar2 type’ ındaki name alanını random değerlerle update etmiş olduk . Scripteki “dbms_random.string(‘X’, 9)” ifadesindeki 9 döndürülecek olan random kayıdın kaç karakterde olacağını ifade ediyor. X kolonu ise aşağıdaki parametrik değerlerden herhangi birini yazabilirsiniz. Örneğin P yazarsanız klavye üzerinde tanımlı olan tüm yazılabilir karakteri içeren random kayıtlar döndürür.

‘a’, ’A' alpha characters only (mixed case)
‘l’,  ’L' lower case alpha characters only
‘p’, ’P' any printable characters
‘u’, ’U' upper case alpha characters only
‘x’, ’X' any alpha-numeric characters (upper)

Son olarak date olan bir alanı nasıl random data ile dolduraibleceğimize bakalım. Bunun için öncelikle Julian date formatından faydanılıyoruz.
Aşağıdaki script ile hangi tarih aralığındaki kayıtları kullanmak istiyorsak onları belirliyoruz. Benim örnekde 01/01/2010 başlayıp bugüne kadarki olan tüm tarihleri kullanabilir diyorum. Başlangıç tarihim olan 01/01/2010 tarihini Julian formatında neye denk geldiğini hesaplıyorum öncelikle ;

SELECT  TO_CHAR(TO_DATE(’2010-01-01′, ’YYYY-MM-DD’), ’J') FROM DUAL;
TO_CHAR(TO_DATE(’2010-01-01′,’YYYY-MM-DD’),’J')
———————————————–
2455198
1 row selected.

UPDATE deneme m
SET (sdate) =
(
select val from
(select rowid rd from deneme) a, 
(SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE((SELECT TO_CHAR(TO_DATE(’2006-12-30′, ’YYYY-MM-DD’), ’J') FROM DUAL), (SELECT TO_CHAR(sysdate, ’J') FROM DUAL) )), ’J') VAL FROM DUAL) c
where a.rd = m.rowid
);
3 rows updated.
Scriptte ilk verdiğim tarih başlangıç, son vardiğim tarih bilgisi olan sysdate ise bitiş aralığı gösteriyor.

ID            NAME              SDATE
———- ———-              ———
2283    TZZV8RE2N       20-NIS-10
5817    BEQ5BSIDH      07-TEM-10
8495    2ECDB3U3Z      08-EKI-10
3 rows selected.

Scripte tarih formatını yukarıdaki verebileceğimiz gibi Julian formatında da verebiliriz. O zaman scriptimiz aşağıdaki formatta olacaktır.
UPDATE deneme m
SET (sdate) =
(
select val from
(select rowid rd from deneme) a, (SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2455198, 2455198+365)), ’J') val FROM DUAL) c
where
a.rd = m.rowid
);
3 rows updated.
Datalarımız ise ;
ID           NAME               SDATE
———- ———-             ———
2283    TZZV8RE2N      22-NIS-10
5817    BEQ5BSIDH      26-TEM-10
8495    2ECDB3U3Z      26-ŞUB-10
3 rows selected.

Istediğimiz tarih aralığında random olarak update etmiş olduk.