Oracle View Yaratmak ve Değiştirmek / Performans

@ 25 Şubat 2011 tarihinde yazdı. Yazıya yorum yazın.

Merhabalar,

Öncelikle bir view (bundan böyle görüntü olarak adlandıralım) tanımını yapmakla başlayalım. Bir görüntü, tablo veya başka bir görüntüdeki verinin yeniden gösterilmesi ve düzenlenmesi anlamına gelen objedir. Bir görüntünün referans olduğu objenin adı ise baz tablodur. Görüntülerle ilgili bilinmesi gereken belki de en önemli nokta; görüntüler veri tutmamaktadır. Yalnız içeriğine ait olan sorgunun data dictionary’de tutulmasıdır. Görüntüler, ana objelere refere olan bir işaretleyicidir. Herhangi bir boyutu bulunmamaktadır çünkü içerisinde veri tutmuyordur. Hazır yeri gelmişken, içerisinde veri barındırabilen ve sürekli olarak güncellenebilen görüntü türüne “materialized view” denmektedir. Görüntüler ayrıca bir veri görüntülenebilirliği koruması olarakta kullanabilirsiniz. Örnek; tabloda 10 tane sütun olduğunu düşünün. Bunlardan 2 tanesini tanımlayacağınız gruptaki insanların görmesini istemiyorsunuz. Yapmanız gereken bir görüntü hazırlamak ve kalan 8 sütunu bu görüntünün içeriği olarak işaretlemek.

Oracle komutları arasından “CREATE VIEW” komutu istenilen görüntünün oluşturulmasını sağlar. Yaratılacak olan bu görüntü birden fazla objeye refere ediyor olabilir. Bir örnekle devam edelim;

SQL> create table yonetim
2 (
3 isim varchar2(30) not null,
4 soyisim varchar2(60) not null,
5 kullanici_kodu number(5) not null
6 );

Table created.

SQL> CREATE VIEW ogan_deneme AS
2 SELECT isim, soyisim, kullanici_kodu
3 FROM yonetim
4 WHERE kullanici_kodu IN (100,200,300);

View created.

SQL> DESCRIBE ogan_deneme;
Name Null? Type
----------------------------------------- -------- ----------------------------
ISIM NOT NULL VARCHAR2(30)
SOYISIM NOT NULL VARCHAR2(60)
KULLANICI_KODU NOT NULL NUMBER(5)

Yukarıdaki komut basit bir görüntü yaratma komutudur ve yonetim isimli objeden, kullanici_kodu 100,200 ve 300 olanların; isim, soyisim ve kullanici_kodu bilgilerini almaktadır. Başka bir örnek;

SQL> DESCRIBE yonetim;
Name Null? Type
----------------------------------------- -------- ----------------------------
ISIM NOT NULL VARCHAR2(30)
SOYISIM NOT NULL VARCHAR2(60)
KULLANICI_KODU NOT NULL NUMBER(5)

SQL> CREATE OR REPLACE VIEW ogan_deneme AS
2 SELECT isim||' '||soyisim "AD-SOYAD"
3 FROM yonetim
4 WHERE kullanici_kodu IN (100,200,300);

View created.

SQL> DESCRIBE ogan_deneme;
Name Null? Type
----------------------------------------- -------- ----------------------------
AD-SOYAD VARCHAR2(91)

Yukarıdaki örnekte isim ve soyisim bilgilerini bir adet boşluk ile birleştirmek koşuluyla “AD-SOYAD” olarak tanımladık ve yine kullanici_kodu 100,200 ve 300 içerisinde olan kişileri istediğimizi belirttik. Burada dikkat edilmesi gereken husus Oracle’ın “AD-SOYAD” sütununu 91 byte olarak tanımlamasıdır. ISIM 30, SOYISIM 60 ve bir ara karaterle birlikte 91 olarak tanımlama gerçekleştirilmiştir. Bunu bir de NUMBER için nasıl yapıtığına bakalım;

SQL> ALTER TABLE yonetim ADD kimlik number(7,1);

Table altered.

SQL> ALTER TABLE yonetim ADD departman_id number(3);

Table altered.

SQL> DESCRIBE yonetim;
Name Null? Type
----------------------------------------- -------- ----------------------------
ISIM NOT NULL VARCHAR2(30)
SOYISIM NOT NULL VARCHAR2(60)
KULLANICI_KODU NOT NULL NUMBER(5)
KIMLIK NUMBER(7,1)
DEPARTMAN_ID NUMBER(3)

SQL> CREATE OR REPLACE VIEW ogan_deneme AS
2 SELECT isim, soyisim, kullanici_kodu * departman_id "Calisan Kodu"
3 FROM yonetim;

View created.

SQL> DESCRIBE ogan_deneme;
Name Null? Type
----------------------------------------- -------- ----------------------------
ISIM NOT NULL VARCHAR2(30)
SOYISIM NOT NULL VARCHAR2(60)
Calisan Kodu NUMBER

NUMBER bir alanı birleştirdiğimi zaman “precision” ve “scale” alanları birleştirildi ve yalnız NUMBER olarak gösterilmeye başlandı.

Bir görüntüyü “SELECT *” komutu ile yarattıktan sonra eğer tablo üzerinde yeni bir sütun eklediyseniz görüntüyüde yeniden yaratmanız veya içeriğini güncellemeniz gerekmektedir. Örnek;

SQL> CREATE OR REPLACE VIEW ogan_deneme AS
2 SELECT * FROM yonetim;

View created.

SQL> ALTER TABLE yonetim ADD maas number(10);

Table altered.

SQL> DESCRIBE ogan_deneme;
Name Null? Type
----------------------------------------- -------- ----------------------------
ISIM NOT NULL VARCHAR2(30)
SOYISIM NOT NULL VARCHAR2(60)
KULLANICI_KODU NOT NULL NUMBER(5)
KIMLIK NUMBER(7,1)
DEPARTMAN_ID NUMBER(3)

SQL> SELECT dbms_metadata.get_ddl('VIEW','OGAN_DENEME','SYSTEM') OUTPUT
2 FROM DUAL;

OUTPUT
-------------------------------
CREATE OR REPLACE FORCE VIEW "SYSTEM"."OGAN_DENEME" ("ISIM", "SOYISIM", "KULLANICI_KODU", "KIMLIK", "DEPARTMAN_ID") AS
SELECT "ISIM","SOYISIM","KULLANICI_KODU","KIMLIK","DEPARTMAN_ID" FROM yonetim

yonetim tablosuna maas sütununu eklememe rağmen daha önceden tanımladığım görüntü değişmedi. Bir üstteki sorgundan da anlaşıldığı üzere “*” isteğimiz karşılanmış ancak data dictionary’de, o anki bütün sütunlar çözümlenerek yaratılmış. Buradaki sizi çelişkiye düşürebilecek nokta “select *” olarak tanımladım, neden sonradan yarattığım sütunları da görüntüde göremiyorum” olacaktır. İşte sebebi de budur.

Bir görüntüyü henüz veritabanında olmayan bir obje için yaratabilirsiniz. Bunu yapabilmek için kullanacağınız komut FORCE komutudur. Bir görüntü eğer hata alıyorsa bu o görüntünün yaratılamadığı anlamına gelmektedir. FORCE komutunun kullanımı için;

SQL> CREATE VIEW ogan_yeni AS
2 SELECT *
3 FROM olmayan_bir_tablo;
FROM olmayan_bir_tablo
*
ERROR at line 3:
ORA-00942: table or view does not exist

SQL> CREATE FORCE VIEW ogan_yeni AS
2 SELECT *
3 FROM olmayan_bir_tablo;

Warning: View created with compilation errors.

SQL> SHOW ERRORS VIEW ogan_yeni;
Errors for VIEW OGAN_YENI:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 ORA-00942: table or view does not exist

SQL> SELECT *
2 FROM ogan_yeni;
FROM ogan_yeni
*
ERROR at line 2:
ORA-04063: view "SYSTEM.OGAN_YENI" has errors

Şimdi de ilgili tabloyu yaratalım;

SQL> CREATE TABLE olmayan_bir_tablo AS
2 SELECT *
3 FROM yonetim;

Table created.

SQL> SELECT *
2 FROM ogan_yeni;

no rows selected

Az önce hata veren görüntü, şimdi sadece satır olmadığını gösteriyor. FORCE ile yarattığımız görüntüyü artık kullanabiliriz. Bir altsorgu (subquery) içerisinde FOR UPDATE kelimesini eğer view yaratırken kullanmaya kalkarsanız hata alırsınız.

Bir görüntüyü sadece okuma özelliği ile (read-only) de yaratabilirsiniz. Şu şekilde;

SQL> CREATE VIEW ogan_yeni
2 SELECT *
3 FROM yonetim
4 WITH READ ONLY;

View created.

Bu yaratılan görüntü üzerinden hiçbir DML operasyonu kabul edilmeyecektir.

BİR 11G ÖZELLİĞİ

Oracle 11g versiyonu görüntüler üzerinde constraint yaratabilmenizi sağlamaktadır. Görüntüler üzerindeki kısıtlar (constraint) uygulanmamaktadır veya zorlanmamaktadır. Bir kısıtı uygulamaya almak istiyorsanız baz tabloya gitmeniz gerekmektedir. Bu tipte kısıtlar yalnızca deklere edici tiptetir ve DISABLE NOVALIDATE olarak yaratılmak zorundadırlar. Bir görüntü üzerinde birincil, benzersiz ve referans anahtarları yaratabilirsiniz. Örnek;

SQL> CREATE VIEW ogan_kisit AS
2 (isim CONSTRAINT fk_isim REFERENCES kisiler DISABLE NOVALIDATE,
3 soyisim,
4 kullanici_kodu CONSTRAINT kullanici_kodu_benzer UNIQUE DISABLE NOVALIDATE,
5 AS
6 SELECT isim, soyisim, kullanici_kodu
7 FROM yonetim
8 WHERE kullanici_kod = 400;

View created.

OR REPLACE cümleciğini zaten varolan bir görüntünün içeriğini değiştirmekte veya olmayan bir görüntü yaratmakta kullanabiliyoruz. Bu komutu kullanarak görüntüyü düşürüp yeniden yaratmadan, yalnızca içeriğini değiştirerek ve görüntü üzerindeki bütün hakları da(grant) koruyarak göntüyü güncelleyebiliriz.

Bir tablo üzerinde yapısal bir değişiklik olduğu durumda (örneğin ALTER TABLE komutu ile bir sütunun tipini değiştirdiğimizi düşünün) görüntü geçersiz olacaktır. Eğer görüntüye erişim sağlanırsa görüntü yeniden düzenlecek (recompile) ve çalışır hale gecektir. Bu düzenleme işini biz de yapabiliyoruz;

SQL> ALTER VIEW ogan_deneme COMPILE;

Bir görütüyü kalıcı olarak veritabanından silmek isterseniz;

SQL> DROP VIEW ogan_deneme;

Bir görüntüyü sildiğimiz zaman data dictionary’den bütün bilgileri silinecek, üzerindeki bütün haklar kalkacak ve bu objeye bağlı olan diğer görüntüler de geçersiz kılınacaktır ve yeniden düzenlenmesi gerekecektir.

Bir görüntü kullanmanın bir diğer olumlu yanı ise görüntülerin arkasına kompleks birleşim koşullarını saklayabilmemiz. Bu şekilde görüntüyü kullanan kişinin ne derece kompleks bir erişim sağladığı size kalacaktır. Bununla birlikte size göre anlamlı fakat son kullanıcıya anlamsız gelen sütun isimlendirmelerini yeniden düzenleyebilir ve daha anlamlı isimlerle bir görüntü altnıda sunabilirsiniz.

Bir görüntü üzerinden DML uygulama şansına sahipsiniz ancak aşağıdaki koşulların görüntü için geçerli olmaması gerekmekte yani görüntünün içeriğinin içerisinde bulunmaması gerekmekte.

1) DISTINCT veya UNIQUE
2) GROUP BY
3) START WITH
4) CONNECT BY
5) ROWNUM
6) Bütün SET operatörleri (UNION, UNION ALL, INTERSECT, MINUS)
7) SELECT içerisinde bir altsorgunun varlığı

Bir görüntü üzerinden insert işlemi yaparken görüntü içerisindeki WHERE koşulları uygulanmadan insert işlemi yapılmaktadır. Örnek;

SQL> CREATE VIEW ogan_yeni AS
2 SELECT *
3 FROM yonetim
4 WHERE kullanici_kod > 99;

View created.

SQL> INSERT INTO ogan_yeni
2 VALUES('Ogan','Ozdogan',10);

1 row created.

Gördüğünüz gibi 10 numaralı bir kullanıcı kodunun girişine izin verildi. Bunun kontrol edilmesini istiyorsanız eğer WITH_CHECK_OPTION eklemeniz gerekiyor. Bu arada WITH_CHECK_OPTION ALL_CONSTRAINT görüntüsü içerisinde “V” tipinde gösterilmektedir. CHECK kısıtlarının da “C” olarak gösterildiğini hatırlayalım.

SQL> CREATE OR REPLACEVIEW ogan_yeni AS
2 SELECT *
3 FROM yonetim
4 WHERE kullanici_kod > 99;
5 WITH CHECK OPTION;

View created.

SQL> INSERT INTO ogan_yeni
2 VALUES('Ogan','Ozdogan',10);
INSERT INTO ogan_yeni
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

SQL> SELECT constraint_name, table_name
2 FROM user_constraints
3 WHERE constraint_type = 'V';

CONSTRAINT_NAME TABLE_NAME
------------------------------------------------------------
SYS_B000234 ogan_yeni

Son olarak dokümante edilmeyen bir konuya değinmek istiyorum. Görüntülerin performansı arttığı gibi bir efsane var. Bakalım gerçekten öyle mi?

SQL> CREATE TABLE ogan_tablo AS
2 SELECT *
3 FROM all_users;

SQL> select count(*)
2 from ogan_tablo;

COUNT(*)
----------
593

SQL> CREATE OR REPLACE VIEW ogan_perf AS
2 SELECT *
3 FROM ogan_tablo;

View created.

SQL> SELECT *
2 FROM OGAN_TABLO;

Execution Plan
----------------------------------------------------------
Plan hash value: 3842809937

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 593 | 23127 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| OGAN_TABLO | 593 | 23127 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
23128 bytes sent via SQL*Net to client
917 bytes received via SQL*Net from client
41 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
593 rows processed

SQL> SELECT *
2 FROM OGAN_PERF;

Execution Plan
----------------------------------------------------------
Plan hash value: 3842809937

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 593 | 23127 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| OGAN_TABLO | 593 | 23127 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
51 consistent gets
1 physical reads
0 redo size
23128 bytes sent via SQL*Net to client
917 bytes received via SQL*Net from client
41 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
593 rows processed

“Recursive calls” sayısı Oracle’ın içsel olarak yarattığı peşpeşe okumaları sembolize etmektedir ve bu okumalar hem kullanıcı hem de sistem seviyesinde olmaktadır.
“Consistent gets” sayısı bir blok için kaç defa tutarlı okuma yapıldığını göstermektedir.
“Physical reads” disk üzerindeki bloklardan yapılan fiziksel okuma sayısı.

Yukarıdaki örnekte görüntünün fiziksel okuma yaptığı ve birkaç rakam kadar daha fazla tutarlı okuma yaptığını gördük. Şimdi bu iki sorguyu arka arkaya çalıştıralım ve parse aşamasını geçmesini sağlayarak cache içerisine yazılmasını sağlayalım;

SQL> SELECT *
2 FROM OGAN_PERF;

Execution Plan
----------------------------------------------------------
Plan hash value: 3842809937

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 593 | 23127 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| OGAN_TABLO | 593 | 23127 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
23128 bytes sent via SQL*Net to client
917 bytes received via SQL*Net from client
41 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
593 rows processed

SQL> SELECT *
2 FROM OGAN_TABLO;

Execution Plan
----------------------------------------------------------

Plan hash value: 3842809937

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 593 | 23127 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| OGAN_TABLO | 593 | 23127 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
23128 bytes sent via SQL*Net to client
917 bytes received via SQL*Net from client
41 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
593 rows processed

Gördüğünüz gibi görüntünün bana sağladığı bir getiri bulunmamakta ve ilk defa parse edildikleri zaman farklılıklar görülmekte ancak bu farklılığın görüntünün daha hızlı olduğunu açıklayan bir yanı bulunmamakta. Kısacası görüntülerin performansı arttırdığı tek nokta eğer başında nasıl sorgu yazmasını bilen bir geliştirici varsa, yani emin ellerdeyse evet, görüntüler performansı arttırabilir ama bunu arttıran görüntünün yani objenin kendisi değil, içerisinde bulundurduğu sorgudur.

İyi çalışmalar dilerim.

Ogan