索引の有る無しによるパフォーマンス、実行計画の違いを確認してみます。

SQL> create table TBL1 (col1 number, col2 varchar2(100)) segment creation immediate tablespace TBS_BIG;
表が作成されました。

SQL> alter table TBL1 add constraint PK_TBL1_COL1 primary key(COL1);

表が変更されました。

TBL1は主キー索引を持ちます。

SQL> create table TBL2 as select * from TBL1;

表が作成されました。

TBL2はTBL1と同じデータを持ちます。
ただし、索引は持ちません。

[oracle@db01 ~]$ sqlplus / as sysdba

SQL> show parameter filesystemio

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none
SQL> alter system set FILESYSTEMIO_OPTIONS='SETALL' scope=spfile;

システムが変更されました。

SQL> shutdown immediate;
SQL> startup

SQL> show parameter filesystemio

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      SETALL

パフォーマンスを比較する前に、OSのファイルシステムキャッシュを利用しない設定を行ないます。
これでダイレクトにディスクへの書き込みを行ないます。

[oracle@db01 ~]$ sqlplus / as sysdba

SQL> alter system FLUSH BUFFER_CACHE;

システムが変更されました。

また、各テストを実施する前に、DBのバッファキャッシュを空にします。
各テストの実施条件を一緒にするためです。

SQL> set timing on
SQL> select * from TBL1 where col1 = 10000;

      COL1
----------
COL2
--------------------------------------------------------------------------------
     10000
10000AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAA


経過: 00:00:00.15

まずは索引の有る表でSELECTを実行します。
実行時間0.15秒

[oracle@db01 ~]$ sqlplus / as sysdba

SQL> alter system FLUSH BUFFER_CACHE;

システムが変更されました。

もう一個の表を確認する前にバッファキャッシュをクリアします。

SQL> set timing on
SQL> select * from TBL2 where col1 = 10000;

      COL1
----------
COL2
--------------------------------------------------------------------------------
     10000
10000AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAA


経過: 00:00:02.91

索引の無い表でSELECTを実行します。
時間は2.91秒
明らかにパフォーマンスが違います。

次はそれぞれの実行計画を確認してみます。