özel index tipleri

-Funtion-based index:
Fonksiyonları index olarak kullanabilmemizi sağlar.
Cost base optimizerda etkilidir. Bu sayede fonksiyonlar where sartında yer aldıgında plan onları
pass geçmez.

– Bitmap Join index:
Tek indexin birden fazla tablodan oluşmasına izin verir.
Performans artısına önemli katkısı olabilir.

– İndex only table:
Tabloyu indexe özel organizasyonda yaratmaktır.
Ciddi performans artısı sağlayabilir.
Primary key şartı vardır.

Funtion Base index:

İndexler experrsionda yeralabilir.
SUBSTR, UPPER gibi örnekler için idealdir.
Oracle parametrelerinde
query_rewrite_enabled = true
query_rewrite_integrity = trusted
örnek olarak Create index func_fname_idx on emp (UPPER(first_name));
Enterprise edition versiyonunda bulunur.
SQL Functionlarında tavsiye edilir.
range scans
Functions in order by clauses
İkinci bir index olarakda index Organized Tables (IOT) da kullanılabilir.
Örnek karşılaştırma

SQL> select count(*) from patients
2 where upper(last_name) like ‘CA%’;
Elapsed: 00:00:02.34 *
Execution Plan
————————————————
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF ‘PATIENTS‘
*function-based index kullanmamış

SQL> create index patients_fn1 on 2 patients(upper(last_name));
SQL> select count(*) from patients 2 where upper(last_name) like ‘CA%’;
Elapsed: 00:00:00.91*
Execution Plan————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF ‘PATIENTS_FN1’
* Elapsed time 1 saniyenin altında.

– Bitmap join index

CREATE TABLE EMP1 AS SELECT * FROM SCOTT.EMP;

CREATE TABLE DEPT1 AS SELECT * FROM SCOTT.DEPT;

ALTER TABLE DEPT1 ADD CONSTRAINT DEPT_CONSTR1 UNIQUE (DEPTNO);

Ufak bir tabloda bitmap join index örneği

CREATE BITMAP INDEX EMPDEPT_IDX
ON EMP1(DEPT1.DEPTNO)
FROM EMP1, DEPT1
WHERE EMP1.DEPTNO = DEPT1.DEPTNO
/

SELECT /*+ INDEX(EMP1 EMPDEPT_IDX) */ COUNT(*)
FROM EMP1, DEPT1
WHERE EMP1.DEPTNO = DEPT1.DEPTNO;
COUNT(*)
—————
14
Elapsed: 00:00:00.67

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT)
3 2 BITMAP INDEX (FULL SCAN) OF ‘EMPDEPT_IDX’

– İndex organized Table

Oracle 8den beri vardır.
İndex datasını tablo datasının dışında ayrı bir storage alanda tutmak yerine tablo üzerinde tutar.
Primary key yaratıldığı anda oluşur.
Full index scan primary key datasının dönmesi demektir.
Normel indexlere göre eşitlik ve aralık aramalarda daha hızlıdır.Yeterki I/O işleri fazla olmasın.
OLAP/DWH/VLDB ortamları için idealdir.
Fakat ROWIDs olmadığından IOTları distributed transactions veya advanced replication
kullanamayız.

CREATE TABLE test8
( doc_code CHAR(5),
doc_type INTEGER,
doc_desc VARCHAR(512),
CONSTRAINT pk_docindex PRIMARY KEY (doc_code,doc_type) )
ORGANIZATION INDEX TABLESPACE data_tbs1
PCTTHRESHOLD 20 INCLUDING doc_type
OVERFLOW TABLESPACE data_tbs2
/

11g ile beraber

•11g virtual columns indexleme
•11g invisible indexes
•Bitmap join for IOT

yöntemleride mevcuttur.

Advertisements

About oracledocuments

Zekeriya Beşiroğlu, It joined the Bilginc IT Academy in July 2000. In the meantime, the Oracle Education, Oracle Data Base Management System and Oracle Internet Technologies, Oracle development technologies such as the training of Oracle products is responsible for training as consultants. During this task, since 1 April 2008 with 293 Oracle training in total, in 2150 Oracle customers successfully submitted. Oracle 10g New York in November 2002 have received training and education in Turkey was the first time the consultants. 20 April 2008 on education in Turkey 11g is the first who was a consultant. Oracle Real Application Cluster Expert advisor is certified is the first . Http://zekeriyabesiroglu.blogspot.com and Http://www.oracleforum.info owner and manager of the site. Its own has more than one hundred articles. In addition, creation and improvement of training materials are also related to work. Oracle products are used, database management and reporting Academy eruditely It also is working on. From the date of 1 January 2009 will continue to work as the Technical Director
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s