V ERİTABANI O BJELERİ

1. VERİTABANI OBJELERİ TalipHakan ÖZTÜRK BANKASYA – ORACLE DBA OCE, 10g OCA, 10g/11g OCP http://taliphakanozturk.wordpress.com

2. Gündem • Veritabanı Objeleri – Demo • B*Tree Indexes • Bitmap Indexes • Function Based Indexes • External Tables • Index Organized Tables • Constraints • Views • DML Triggers • DDL Triggers • Synonyms http://taliphakanozturk.wordpress.com

3. S D E U AUSBELCAN SCOSPA USA DEN ENG B*Tree Indexes Set autotrace on SELECT * FROM CALISANLAR WHERE EMPLOYEE_ID=200; CREATE INDEX CALISANLAR_ID_X ON CALISANLAR(EMPLOYEE_ID); --ALTER SYSTEM FLUSH SHARED_POOL; --ALTER SYSTEM FLUSH BUFFER_CACHE; analyze index CALISANLAR_ID_X compute statistics; http://taliphakanozturk.wordpress.com

4. Bitmap Indexes create table bitmap_index_demo1 as select * from bitmap_index_demo; insert into bitmap_index_demo1 select decode(mod(rownum,2),0,'M','F') from all_objects; create index bitmap_index_demo1_idx on bitmap_index_demo1(value); -- birinci session insert into bitmap_index_demo1 values ('M'); -- ikinci session insert into bitmap_index_demo1 values ('F'); -- birinci session insert into bitmap_index_demo1 values ('F'); create table bitmap_index_demo ( value varchar2(20) ); insert into bitmap_index_demo select decode(mod(rownum,2),0,'M','F') from all_objects; create bitmap index bitmap_index_demo_idx on bitmap_index_demo(value); -- birinci session insert into bitmap_index_demo values ('M'); -- ikinci session insert into bitmap_index_demo values ('F'); -- birinci session insert into bitmap_index_demo values ('F'); http://taliphakanozturk.wordpress.com

5. Function Based Indexes CREATE INDEX CALISANLAR_ID_2X ON CALISANLAR(SALARY); SELECT * FROM calisanlar WHERE SALARY*10/100>=1000; CREATE INDEX CALISANLAR_ID_3X ON CALISANLAR(SALARY*10/100); http://taliphakanozturk.wordpress.com

6. External Tables create or replace directory talipdir as '/excel'; as sysdba grant read,write on directory talipdir to talip; as sysdba Connect as Talip User create table talip.dblist ( CI_name varchar2(20), ID varchar2(20), logical_name varchar2(20), dbname varchar2(20), thread number, type varchar2(20), subtype varchar2(20), environment varchar2(20) ) organization external ( default directory talipdir access parameters ( records delimited by newline fields terminated by ';' ) location ('dblist.csv') )REJECT LIMIT unlimited; selectci_name from talip.dblist order by ci_name desc; http://taliphakanozturk.wordpress.com

7. Index Organized Tables Veriler B*Tree index yapısında saklanır. create table iot_table ( id number primary key, name varchar2(5) ) organization index; create table normal_table ( id number primary key, name varchar2(5) ); insert into iot_table values(1,'a'); commit; http://taliphakanozturk.wordpress.com

8. Constraints ALTER TABLE calisanlar ADD ( CONSTRAINT salary_check CHECK ( NVL(salary,0) >= 1000 )); insert into calisanlar (first_name,last_name,email,salary) values ('talip','ozturk','talipozturk@gmail.com',1001); insert into calisanlar (first_name,last_name,email,salary) values ('talip','ozturk','talipozturk@gmail.com',999); http://taliphakanozturk.wordpress.com

9. Views • Tablo joinleri içerebilirler. • DML? Simple CREATE OR REPLACE FORCE VIEW calisanlar_v AS SELECT c.employee_id, c.first_name, c.last_name, c.hire_date FROM calisanlar c; Complex CREATE OR REPLACE FORCE VIEW calisanlar_v (min_salary, department_name) AS SELECT min(c.salary),d.department_name FROM calisanlar c, departmanlar d WHERE c.department_id=d.department_id GROUP BY d.department_name; http://taliphakanozturk.wordpress.com

10. DML Triggers create table calisanlar_log ( user_id varchar2(10), log_date date, action varchar2(1000) ); CREATE OR REPLACE TRIGGER calisanlar_kontrol_delete BEFORE DELETE ON calisanlar REFERENCING NEW AS new OLD AS old FOR EACH ROW DECLARE BEGIN raise_application_error(-20001,'Silme islemi yapamazsiniz! DBA ile gorusunuz!'); end; delete from calisanlar where employee_id=105; CREATE OR REPLACE TRIGGER calisanlar_kontrol_insert AFTER INSERT ON calisanlar REFERENCING NEW AS new OLD AS old FOR EACH ROW DECLARE BEGIN INSERT INTO calisanlar_log (user_id,log_date,action) VALUES (USER, SYSDATE, 'calisanlar tablosuna kayıt insert edildi'); end; insert into calisanlar (first_name,last_name,email,salary) values ('talip','ozturk','talipozturk@gmail.com',1001); http://taliphakanozturk.wordpress.com

11. DDL Triggers CREATE OR REPLACE TRIGGER logon_trig AFTER LOGON ON SCHEMA BEGIN INSERT INTO log_trig_table(user_id,log_date,action) VALUES (USER, SYSDATE, 'Logging on'); END; / create table log_trig_table ( user_id varchar2(10), log_date date, action varchar2(10) ); select * from log_trig_table; http://taliphakanozturk.wordpress.com

12. VELI SELIM AYSE Synonyms • Private Synonyms • Public Synonyms HESAP_NO Public Synonym To ALI.HESAP_NO ALI . HESAP_NO Select * from hesap_no; create synonym ygm_test.calisanlar for talip.calisanlar; create public synonym calisanlar for talip.calisanlar; http://taliphakanozturk.wordpress.com

13. TEŞEKKÜRLER Talip Hakan ÖZTÜRK Bilgisayar Mühendisi OCE, 10g OCA, 10g/11g OCP talip_hakan_ozturk@hotmail.com http://taliphakanozturk.wordpress.com http://taliphakanozturken.wordpress.com