V er tabani o bjeler
This presentation is the property of its rightful owner.
Sponsored Links
1 / 13

V ERİTABANI O BJELERİ PowerPoint PPT Presentation


  • 59 Views
  • Uploaded on
  • Presentation posted in: General

V ERİTABANI O BJELERİ. Talip Hakan ÖZTÜRK B ANK ASYA – ORACLE DBA OCE, 10g OCA, 10g /11g OCP http://taliphakanozturk.wor dpress.com. Gündem. Veritabanı Objeleri – Demo B*Tree Indexes Bitmap Indexes Function Based Indexes External Tables Index Organized Tables Constraints Views

Download Presentation

V ERİTABANI O BJELERİ

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


V er tabani o bjeler

VERİTABANI OBJELERİ

TalipHakan ÖZTÜRK

BANKASYA – ORACLE DBA

OCE, 10g OCA, 10g/11g OCP

http://taliphakanozturk.wordpress.com


V er tabani o bjeler

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


V er tabani o bjeler

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


V er tabani o bjeler

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


V er tabani o bjeler

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


V er tabani o bjeler

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


V er tabani o bjeler

  • 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


V er tabani o bjeler

  • 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',[email protected]',1001);

insert into calisanlar (first_name,last_name,email,salary)

values ('talip','ozturk',[email protected]',999);

http://taliphakanozturk.wordpress.com


V er tabani o bjeler

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


V er tabani o bjeler

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',[email protected]',1001);

http://taliphakanozturk.wordpress.com


V er tabani o bjeler

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


V er tabani o bjeler

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


V er tabani o bjeler

TEŞEKKÜRLER

Talip Hakan ÖZTÜRK

Bilgisayar Mühendisi

OCE, 10g OCA, 10g/11g OCP

[email protected]

http://taliphakanozturk.wordpress.com

http://taliphakanozturken.wordpress.com


  • Login