In welchen Fällen wird Oracle automatisch Indizes erstellen?

Question

Soviel ich weiß ( diese Seite ) Oracle erstellt automatisch einen Index für jede UNIQUE- oder PRIMARY KEY-Deklaration. Ist dies eine vollständige Liste der Fälle, in denen Indizes in Oracle automatisch erstellt werden?

Answer 1

Ich werde versuchen, die gegebenen Antworten zu konsolidieren und ein Gemeinschafts-Wiki daraus zu machen.
Daher werden von Oracle für solche Fälle automatisch Indizes erstellt:

1. APC : Für Primärschlüssel und eindeutige Schlüssel, sofern solche Indizes nicht bereits vorhanden sind.
2. APC : Für LOB-Speicher und XMLType.
3. Gary : Für eine Tabelle mit einer verschachtelten Tabelle.
4. Jim Hudson : Für materialisierte Ansicht.

Answer 2

Erstens erstellt Oracle nicht immer einen Index, wenn wir einen Primär- oder eindeutigen Schlüssel erstellen. Wenn es bereits einen Index für diese Spalte gibt, wird er stattdessen verwendet...

SQL> create table t23 (id number not null)
  2  /

Table created.

SQL> create index my_manual_idx on t23 ( id )
  2  /

Index created.

SQL> select index_name from user_indexes
  2  where table_name = 'T23'
  3  /

INDEX_NAME
------------------------------
MY_MANUAL_IDX

SQL> 

... beachten Sie, dass MY_MANUAL_IDX kein eindeutiger Index ist, spielt das keine Rolle ...

SQL> alter table t23
  2      add constraint t23_pk primary key (id) using index
  3  /

Table altered.

SQL> select index_name from user_indexes
  2  where table_name = 'T23'
  3  /

INDEX_NAME
------------------------------
MY_MANUAL_IDX

SQL> drop index my_manual_idx
  2  /
drop index my_manual_idx
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> 

Es gibt einen weiteren Fall, in dem Oracle automatisch einen Index erstellt: LOB storage....

SQL> alter table t23
  2      add txt clob
  3      lob (txt) store as basicfile t23_txt (tablespace users)
  4  /

Table altered.

SQL> select index_name from user_indexes
  2  where table_name = 'T23'
  3  /

INDEX_NAME
------------------------------
MY_MANUAL_IDX
SYS_IL0000556081C00002$$

SQL>

bearbeiten

Die Datenbank behandelt den XMLType genauso wie andere LOBs...

SQL> alter table t23
  2      add xmldoc xmltype
  3  /

Table altered.

SQL> select index_name from user_indexes
  2  where table_name = 'T23'
  3  /

INDEX_NAME
------------------------------
MY_MANUAL_IDX
SYS_IL0000556081C00002$$
SYS_IL0000556081C00004$$

SQL>

Answer 3

Nein, wir kommen der Sache schon näher, aber die Liste ist noch nicht ganz vollständig.

Es wird auch automatisch ein Index erstellt, wenn Sie eine materialisierte Ansicht erstellen, da Oracle in der Lage sein muss, die Zeilen schnell zu identifizieren, wenn eine schnelle Aktualisierung durchgeführt wird. Für rowid-basierte materialisierte Ansichten wird I_SNAP$_tablename verwendet. Für materialisierte Primärschlüsselansichten wird der ursprüngliche PK-Name verwendet, der nach Bedarf geändert wird, um ihn eindeutig zu machen.

create materialized view testmv 
refresh force with rowid
as select * from dual;

select index_name from user_indexes where table_name = 'TESTMV';

Index Name
--------------
I_SNAP$_TESTMV

Answer 4

Und noch etwas: Wenn Sie eine Tabelle mit einer verschachtelten Tabelle erstellen, wird automatisch ein Index erstellt. Bei der objektbasierten Speicherung ist dies im Allgemeinen möglich, da versteckte Tabellen erstellt werden können.

Ich denke, dass schemabasierte XMLTypes dies ebenfalls tun werden.

Answer 5

Ja, das ist die vollständige Liste. Oracle erstellt automatisch einen Index für jede UNIQUE- oder PRIMARY KEY-Deklaration.