Welche Indizes hat Oracle?

Zu den Indextypen von Oracle gehören: nicht eindeutiger Index, eindeutiger Index, Bitmap-Index, lokaler Präfix-Partitionsindex, lokaler Nicht-Präfix-Partitionsindex, globaler Präfix-Partitionsindex, Hash-Partitionsindex und funktionsbasierter Index. Der Index muss nach dem Einfügen von Daten in die Tabelle erstellt werden. Mit der Anweisung „create unique index“ kann ein eindeutiger Index erstellt werden.

Die Betriebsumgebung dieses Tutorials: Windows 7-System, Oracle 11g-Version, Dell G3-Computer.

Was ist ein Index?

• Ein Index ist ein Hilfsobjekt, das auf einer oder mehreren Spalten einer Tabelle aufgebaut ist, mit dem Zweck, den Zugriff auf Daten in der Tabelle zu beschleunigen;
• Die Datenstruktur des Oracle-Speicherindex ist ein B*-Baum (Balanced Tree). ), Bitmap-Index Das Gleiche gilt, aber die Blattknoten haben unterschiedliche B*-Nummernindizes.

• Der Index besteht aus dem Wurzelknoten, dem Zweigknoten und dem Blattknoten. Der Indexblock der oberen Ebene enthält die Indexdaten der unteren -Level-Indexblock, und der Blattknoten enthält die Indexdaten und bestimmt die tatsächliche Position der Zeilen-ID.

Indexbeschreibung

1) Der Index ist eines der Datenbankobjekte, das zur Beschleunigung des Abrufs von Daten verwendet wird, ähnlich dem Index eines Buches. Die Indizierung in einer Datenbank kann die Datenmenge reduzieren, die ein Datenbankprogramm beim Abfragen von Ergebnissen lesen muss, ähnlich wie wir in Büchern Indizes verwenden können, um die gewünschten Informationen zu finden, ohne das gesamte Buch lesen zu müssen.

2) Der Index ist ein optionales Objekt, das auf der Tabelle erstellt wird. Der Schlüssel zum Index besteht darin, die Standardmethode zum Abrufen des vollständigen Tabellenscans durch einen Satz sortierter Indexschlüssel zu ersetzen und so die Abrufeffizienz zu verbessern.

3) Der Index ist logisch und physisch. Es hat nichts mit den zugehörigen Tabellen und Daten zu tun. Wenn ein Index erstellt oder gelöscht wird, hat dies keine Auswirkungen auf die Basistabelle (oder Löschen) Während verwandter Vorgänge verwaltet Oracle den Index automatisch und das Löschen des Index hat keine Auswirkungen auf die Tabelle.

5) Der Index ist für den Benutzer transparent, unabhängig davon, ob ein Index in der Tabelle vorhanden ist

6) Oracle-Erstellung Wenn der Primärschlüssel verwendet wird, wird automatisch ein Index für die Spalte erstellt

Der Zweck der Verwendung des Index:

Beschleunigen Abfragegeschwindigkeit

• E/A-Vorgänge reduzieren
• Festplattensortierung eliminieren (Indizes können die Sortierung beschleunigen)

Wann man Indizes verwendet:

Die Anzahl der von der Abfrage zurückgegebenen Datensätze beträgt

Die Tabelle ist stärker fragmentiert (häufige Hinzufügungen und Löschungen)

Indextypen

Nicht eindeutiger Index (am häufigsten verwendet)

• Eindeutiger Index
• Bitmap-Index
• Lokaler Partitionsindex mit Präfix
• Lokaler Partitionsindex ohne Präfix
• Globaler Partitionsindex mit Präfix
• Hash-partitionierter Index
• Funktionsbasierter Index

Richtlinien für die Verwaltung von Indizes

Erstellen einen Index nach dem Einfügen von Daten in die Tabelle

• Daten mit dem SQL*Loader oder dem import-Tool einfügen oder laden. Schließlich ist die Indizierung effektiver
• import工具插入或装载数据后，建立索引比较有效；

索引正确的表和列

• 经常检索排序大表中40%或非排序表7%的行，建议建索引；
• 为了改善多表关联，索引列用于联结；
• 列中的值相对比较唯一；
• 取值范围（大：B*树索引，小：位图索引）；
• Date型列一般适合基于函数的索引；
• 列中有许多空值，不适合建立索引

为性能而安排索引列

• 经常一起使用多个字段检索记录，组合索引比单索引更有效；
• 把最常用的列放在最前面，例：dx_groupid_serv_id(groupid,serv_id)，在where条件中使用groupid或groupid,serv_id，查询将使用索引，若仅用到serv_id

Indizieren Sie die richtigen Tabellen und Spalten

Oft werden 40 % der Zeilen in einer großen sortierten Tabelle oder 7 % einer nicht sortierten Tabelle abgerufen. Es wird empfohlen, einen Index zu erstellen. Um die Zuordnung mehrerer Tabellen zu verbessern, wird die Indexspalte für die Verknüpfung verwendet

Der Wert in der Spalte ist relativ eindeutig;

• Wertebereich (groß: B*-Baumindex, klein: Bitmap-Index);
  Datumstypspalte ist im Allgemeinen für funktionsbasierte Indizes geeignet; ​in der Spalte, die nicht für die Indizierung geeignet ist
Indexspalten nach Leistung anordnen

Mehrere Felder werden oft zusammen zum Abrufen von Datensätzen verwendet, und kombinierte Indizes sind effizienter als einzelne Indizes

• Die am häufigsten verwendeten Spalten werden vorne platziert, zum Beispiel: dx_groupid_serv_id(groupid,serv_id), verwenden Sie groupid oder groupid im <code>where Bedingung, serv_id, die Abfrage verwendet den Index. Wenn nur das Feld serv_id verwendet wird, ist der Index ungültig.

• Unnötige Indizes zusammenführen/aufteilen.

• Begrenzen Sie die Anzahl der Indizes pro Tabelle

Eine Tabelle kann Hunderte von Indizes haben (würden Sie das tun?), aber bei häufigen Einfügungen und Aktualisierungen der Tabelle gilt: Je mehr Indizes die System-CPU, desto mehr Indizes benötigt ich/ O Je schwerer die Belastung;

🎜 Es wird empfohlen, dass jede Tabelle nicht mehr als 5 Indizes hat. 🎜🎜🎜🎜🎜Nicht mehr benötigte Indizes löschen🎜🎜🎜🎜🎜Ungültige Indizes, hauptsächlich aufgrund der Verwendung von funktionsbasierten Indizes oder Bitmap-Indizes anstelle von B*-Baum-Indizes; 🎜🎜🎜🎜Abfragen in Anwendungen sind ungültig Index verwenden; 🎜🎜🎜🎜Sie müssen den Index löschen, bevor Sie den Index neu erstellen. Wenn Sie alter index...rebuild verwenden, um den Index neu zu erstellen, müssen Sie den Index nicht löschen. 🎜🎜🎜🎜🎜Speicherplatznutzung für Indexdatenblöcke🎜🎜

• Geben Sie den Tabellenbereich an, wenn Sie einen Primärschlüssel erstellen.
• Stellen Sie pctfress angemessen ein.
• Schätzen Sie die Größe des Index und legen Sie die Speicherparameter angemessen fest. Der Standardwert ist die Tabellenbereichsgröße, oder initial und next werden auf die gleiche Größe eingestellt.

Erwägen Sie die parallele Erstellung von Indizes

• Die parallele Indexerstellung kann für große Tabellen verwendet werden. Bei der parallelen Erstellung von Indizes werden Speicherparameter von jedem Abfrageserverprozess separat verwendet, zum Beispiel: initial ist 1M, die Parallelität ist 8, dann werden mindestens 8M Speicherplatz während der Indexerstellung verbraucht. initial为1M，并行度为8，则创建索引期间至少要消耗8M空间；

考虑用nologging创建索引

• 对大表创建索引可以使用nologging来减少重做日志；
• 节省重做日志文件的空间；
• 缩短创建索引的时间；
• 改善了并行创建大索引时的性能。

怎样建立最佳索引?

明确地创建索引

create index index_name on table_name(field_name)
　　tablespace tablespace_name
　　pctfree 5
　　initrans 2
　　maxtrans 255
　　storage
　　(
　　minextents 1
　　maxextents 16382
　　pctincrease 0
　　);

创建基于函数的索引:

常用与UPPER、LOWER、TO_CHAR(date)等函数分类上，例：

create index idx_func on emp (UPPER(ename)) tablespace tablespace_name;

创建位图索引:

对基数较小，且基数相对稳定的列建立索引时，首先应该考虑位图索引，例：

create bitmap index idx_bitm on class (classno) tablespace tablespace_name;

明确地创建唯一索引

可以用create unique index语句来创建唯一索引，例：

create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;

创建与约束相关的索引

可以用using index字句，为与unique和primary key约束相关的字段创建索引，例如：

alter table table_name
　　add constraint PK_primary_keyname primary key (field_name)
　　using index tablespace tablespace_name；

如何创建局部分区索引

• 基础表必须是分区表；
• 分区数量与基础表相同；
• 每个索引分区的子分区数量与相应的基础表分区相同；
• 基础表的子分区中的行的索引项，被存储在该索引的相应的子分区中,例如:

　　Create Index TG_CDR04_SERV_ID_IDX On TG_CDR04(SERV_ID)
　　Pctfree 5　　Tablespace TBS_AK01_IDX
　　Storage (
　　MaxExtents 32768　　PctIncrease 0　　FreeLists 1　　FreeList Groups 1　　)
　　local　　/

如何创建范围分区的全局索引

基础表可以是全局表和分区表。

create index idx_start_date on tg_cdr01(start_date)
　　global partition by range(start_date)
　　(partition p01_idx vlaues less than (‘0106’)
　　partition p01_idx vlaues less than (‘0111’)
　　…
　　partition p01_idx vlaues less than (‘0401’ ))
　　/

　　重建现存的索引
　　重建现存的索引的当前时刻不会影响查询；

　　重建索引可以删除额外的数据块；
　　提高索引查询效率；

alter index idx_name rebuild nologging;

　　对于分区索引：

alter index idx_name rebuild partition partiton_name nologging;

要删除索引的原因

• 不再需要的索引；
• 索引没有针对其相关的表所发布的查询提供所期望的性能改善；
• 应用没有用该索引来查询数据；
• 该索引无效，必须在重建之前删除该索引；
• 该索引已经变的太碎了，必须在重建之前删除该索引；
• 语句：<br/> drop index idx_name; <br/> drop index idx_name drop partition partition_name; <br/>

建立索引的代价

基础表维护时，系统要同时维护索引，不合理的索引将严重影响系统资源，主要表现在CPU和I/O上；

插入、更新、删除数据产生大量db file sequential read锁等待；

一个表中有几百万条数据，对某个字段加了索引，但是查询时性能并没有什么提高，这主要可能是oracle的索引限制造成的。

oracle的索引有一些索引限制，在这些索引限制发生的情况下，即使已经加了索引，oracle还是会执行一次全表扫描，查询的性能不会比不加索引有所提高，反而可能由于数据库维护索引的系统开销造成性能更差。

扩展知识：常见的索引限制问题

1、使用不等于操作符(<>, !=)

下面这种情况，即使在列dept_id有一个索引，查询语句仍然执行一次全表扫描

select * from dept where staff_num <> 1000;

但是开发中的确需要这样的查询，难道没有解决问题的办法了吗？

有！

通过把用 or 语法替代不等号进行查询，就可以使用索引，以避免全表扫描：上面的语句改成下面这样的，就可以使用索引了。

select * from dept shere staff_num < 1000 or dept_id > 1000;

2、使用 is null 或 is not null

使用 is null 或is nuo null也会限制索引的使用，因为数据库并没有定义null值。如果被索引的列中有很多null，就不会使用这个索引（除非索引是一个位图索引，关于位图索引，会在以后的blog文章里做详细解释）。在sql语句中使用null会造成很多麻烦。

解决这个问题的办法就是：建表时把需要索引的列定义为非空(not null)

3、使用函数

如果没有使用基于函数的索引，那么where

🎜🎜Erwägen Sie die Verwendung von nologging</code > Index erstellen 🎜🎜🎜🎜 Sie können Nologging verwenden, um Redo-Logs beim Erstellen von Indizes zu reduzieren. 🎜🎜 Sparen Sie Platz in Redo-Log-Dateien. 🎜🎜 Verbessern Sie die Leistung beim parallelen Erstellen großer Indizes. 🎜🎜🎜<span style="font-size: 18px;"><strong>Wie erstellt man den besten Index?</strong></span>🎜🎜Indizes explizit erstellen🎜<div class="code" style="position:relative; padding:0px; margin:0px;"><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="prettyprint">select * from staff where trunc(birthdate) = &amp;#39;01-MAY-82&amp;#39;;</pre><div class="contentsignin">Nach dem Login kopieren</div></div><div class="contentsignin">Nach dem Login kopieren</div></div>🎜Funktionsbasierte Indizes erstellen:🎜🎜 Wird häufig mit <code>UPPER, LOWER, TO_CHAR(date) und anderen Funktionen verwendet, zum Beispiel: 🎜

select * from staff where birthdate < (to_date(&#39;01-MAY-82&#39;) + 0.9999);

🎜Erstellen eines Bitmap-Index:🎜🎜Beim Erstellen eines Index für eine Spalte mit kleiner Basis und relativ stabiler Struktur Als Basis sollten zunächst Bitmap-Indizes berücksichtigt werden, zum Beispiel: 🎜

select * from dept where dept_id = 900198;

🎜 Explizit einen eindeutigen Index erstellen 🎜🎜 Sie können die Anweisung create unique index verwenden, um einen eindeutigen Index zu erstellen, zum Beispiel: 🎜

select * from dept where dept_id = '900198';

🎜 Erstellen ein Index, der sich auf eine Einschränkung bezieht 🎜🎜 Sie können die Klausel using index verwenden, um einen Index für die Felder zu erstellen, die sich auf unique und primary key beziehen Einschränkungen, zum Beispiel: 🎜

Column1 like ‘aaa%’ 是可以的
Column1 like ‘%aaa%’用不到

🎜So erstellen Sie einen lokalen Partitionsindex🎜🎜Die Basistabelle muss eine partitionierte Tabelle sein; 🎜🎜Die Anzahl der Partitionen ist die gleiche wie die Basistabelle; 🎜🎜Die Anzahl der Unterpartitionen jedes Index Die Partition ist mit der entsprechenden Basistabellenpartition identisch. 🎜🎜Die Indexeinträge der Zeilen in den Unterpartitionen der Basistabelle werden in der entsprechenden Unterpartition des Index gespeichert, zum Beispiel: 🎜🎜

select count(*) from person_info where xb in (select xb_id from dic_sex);

Select count(*) from n_acntbasic a where shbxdjm =:a and exists(select 1 from person_info where pid=a.pid and …);

Select * from person_info where zjhm=3101….;--将会对person_info全表扫描

Select * from person_info where zjhm =‘3101…’;--才能用到索引

🎜So erstellen Sie eine Bereichspartitionierter globaler Index🎜🎜Die Basistabelle kann eine globale Tabelle und eine partitionierte Tabelle sein. 🎜

Select * from test where to_char(dt,’yyyymmdd’) =‘20041010’;

🎜　Die Neuerstellung des vorhandenen Indexes hat derzeit keine Auswirkungen auf die Abfrage. 🎜🎜　Die Neuerstellung des Indexes kann die Effizienz der Indexabfrage verbessern Indizes: 🎜

select * from test where dt >=to_date(‘20041010’,’yyyymmdd’) and dt < to_date(‘20041010’,’yyyymmdd’) + 1

🎜Grund für das Löschen eines Indexes🎜🎜🎜Der Index wird nicht mehr benötigt. 🎜🎜Der Index bietet nicht die erwartete Leistungsverbesserung für Abfragen, die von den zugehörigen Tabellen ausgegeben werden. 🎜🎜Die Anwendung verwendet den Index nicht zum Abfragen data;🎜🎜 Der Index ist ungültig und muss vor der Neuerstellung gelöscht werden. 🎜🎜Der Index ist zu fragmentiert und der Index muss vor der Neuerstellung gelöscht werden. 🎜🎜Anweisung: <br/> drop index idx_name; > drop index idx_name drop partition partition_name; <br/>🎜🎜🎜Die Kosten für die Indizierung🎜🎜Basic Tabelle Während der Wartung muss das System gleichzeitig den Index verwalten. Unangemessene Indizes wirken sich ernsthaft auf die Systemressourcen aus, hauptsächlich auf CPU und E/A sequentielles Lesen Warten auf Sperre; 🎜🎜Eine Tabelle enthält Millionen von Daten und einem bestimmten Feld wurde ein Index hinzugefügt, aber die Abfrageleistung hat sich hauptsächlich durch die Indexbeschränkung von verbessert Orakel. 🎜🎜Der Index von oracle weist einige Indexeinschränkungen auf, selbst wenn der Index hinzugefügt wurde, führt oracle weiterhin einen vollständigen Tabellenscan und eine Abfrage durch. Die Leistung wird im Vergleich zum Verzicht auf das Hinzufügen eines Index nicht verbessert. Im Gegenteil, die Leistung kann aufgrund des Systemaufwands für die Verwaltung des Index in der Datenbank schlechter sein. 🎜🎜Erweitertes Wissen: Häufige Probleme mit der Indexbeschränkung🎜🎜1. !=)🎜🎜In der folgenden Situation führt die Abfrageanweisung dennoch einen vollständigen Tabellenscan durch, selbst wenn ein Index für die Spalte dept_id vorhanden ist. 🎜

Order byGroup byDistinctIn

🎜Aber eine solche Abfrage ist tatsächlich so Wird in der Entwicklung benötigt, gibt es keine Lösung für das Problem? 🎜🎜Ja! 🎜🎜Durch Ersetzen des Ungleichheitszeichens durch die oder-Syntax für die Abfrage können Sie einen Index verwenden, um einen vollständigen Tabellenscan zu vermeiden: Ändern Sie die obige Anweisung wie folgt, und Sie können den Index verwenden. 🎜rrreee🎜2. Die Verwendung von ist null oder ist nicht null🎜🎜Die Verwendung von ist null oder ist nuo null schränkt auch die Verwendung von Indizes ein. weil Die Datenbank den null-Wert nicht definiert. Wenn die indizierte Spalte viele Nullen enthält, wird der Index nicht verwendet (es sei denn, der Index ist ein Bitmap-Index, was in einem zukünftigen Blog-Artikel ausführlich erläutert wird). Die Verwendung von null in SQL-Anweisungen führt zu großen Problemen. 🎜🎜Die Lösung dieses Problems ist: Definieren Sie beim Erstellen der Tabelle die Spalten, die indiziert werden müssen, als nicht null (nicht null)🎜🎜3. Verwenden Sie Funktionen🎜🎜Wenn nicht verwendet. Bei funktionsbasierten Indizes ignoriert der Optimierer diese Indizes, wenn Funktionen in der where-Klausel für indizierte Spalten verwendet werden. Die folgende Abfrage verwendet den Index nicht: 🎜

select * from staff where trunc(birthdate) = '01-MAY-82';

但是把函数应用在条件上，索引是可以生效的，把上面的语句改成下面的语句，就可以通过索引进行查找。

select * from staff where birthdate < (to_date(&#39;01-MAY-82&#39;) + 0.9999);

4、比较不匹配的数据类型

比较不匹配的数据类型也是难于发现的性能问题之一。下面的例子中，dept_id是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。

select * from dept where dept_id = 900198;

这是因为oracle会自动把where子句转换成to_number(dept_id)=900198，就是3所说的情况，这样就限制了索引的使用。把SQL语句改为如下形式就可以使用索引

select * from dept where dept_id = '900198';

5、使用like子句

使用like子句查询时，数据需要把所有的记录都遍历来进行判断，索引不能发挥作用，这种情况也要尽量避免。

Like 的字符串中第一个字符如果是‘%’则用不到索引

Column1 like ‘aaa%’ 是可以的
Column1 like ‘%aaa%’用不到

6、使用IN

尽管In写法要比exists简单一些，exists一般来说性能要比In要高的多

用In还是用Exists的时机

当in的集合比较小的时候,或者用Exists无法用到选择性高的索引的时候，用In要好，否则就要用Exists
例：

select count(*) from person_info where xb in (select xb_id from dic_sex);

Select count(*) from n_acntbasic a where shbxdjm =:a and exists(select 1 from person_info where pid=a.pid and …);

Select * from person_info where zjhm=3101….;--将会对person_info全表扫描

Select * from person_info where zjhm =‘3101…’;--才能用到索引

假定TEST表的dt字段是date类型的并且对dt建了索引。
如果要查‘20041010’一天的数据.下面的方法用不到索引

Select * from test where to_char(dt,’yyyymmdd’) =‘20041010’;

而以下将会用到索引。

select * from test where dt >=to_date(‘20041010’,’yyyymmdd’) and dt < to_date(‘20041010’,’yyyymmdd’) + 1

7、如果能不用到排序，则尽量避免排序。

用到排序的情况有
集合操作。Union ,minus ,intersect等,注：union all 是不排序的。

Order byGroup byDistinctIn

有时候也会用到排序
确实要排序的时候也尽量要排序小数据量,尽量让排序在内存中执行，有文章说，内存排序的速度是硬盘排序的1万倍。

在排序的字段上创建索引，让排序在内存中执行，加快排序速度。

8、在基于CBO的优化器（花费）下，表的统计数据过期。也可能导致不使用索引。

解决：执行表分析。获取表的最新信息。

9、获取的数据量过大，全部扫描效率更高

10、索引字段的值分散率太低，值太集中，如类型字段都是1,2， 状态类型Y-有效/N-无效。这类型的字段最好别建索引。

尽管在这些字段上建立了索引，但对全表数据区分度不大。最后还是会全表扫描。

推荐教程：《Oracle教程》

Das obige ist der detaillierte Inhalt vonWelche Indizes hat Oracle?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!