オプティマイザをだましちゃお！ （マジック・ザ・ギャザリング風w かも) Tweet

ということで、準備から。なにやら索引がいるのか怪しい列にまでBツリー索引を作成しちゃってます ;)

23:34:03 SYS> conn scott/tiger
接続されました。
23:36:01 SCOTT> 
23:36:01 SCOTT> 
23:36:01 SCOTT> create table deluding_tab (id number not null, status number(2) not null, data varchar2(500)) nologging;

表が作成されました。

23:50:47 SCOTT> begin for i in 1..100000 loop insert into deluding_tab values(i,0,lpad(i,500,'*')); end loop; end;
23:55:22   2  /

PL/SQLプロシージャが正常に完了しました。

23:55:27 SCOTT> commit;

コミットが完了しました。

23:55:31 SCOTT> alter table deluding_tab add constraint pk_deluding_tab primary key (id) using index nologging;

表が変更されました。

23:56:07 SCOTT> create index ix_deluding_tab on deluding_tab(status) nologging;

索引が作成されました。

23:58:26 SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'DELUDING_TAB', -
23:58:34 > no_invalidate=>false,cascade=>true,method_opt=>'FOR ALL COLUMNS SIZE AUTO');

PL/SQLプロシージャが正常に完了しました。

ヒストグラムの状態も見ておきましょ。

TABLE_NAME                     COLUMN_NAME                    NUM_BUCKETS HISTOGRAM
------------------------------ ------------------------------ ----------- ---------------
DELUDING_TAB                   ID                             1           NONE
DELUDING_TAB                   STATUS                         1           NONE
DELUDING_TAB                   DATA                           1           NONE

登録したデータ件数も見ておきましょうか。

00:03:27 SCOTT> 
00:08:41 SCOTT> select count(1) from deluding_tab;

  COUNT(1)
----------
    100000

では、オプティマイザとデュエル！

アンタップ、アップキープ、ドロー！　

00:03:54 SCOTT> select * from deluding_tab where status = 1;

レコードが選択されませんでした。

経過: 00:00:00.01

実行計画
----------------------------------------------------------
Plan hash value: 1226994206

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |   508 |     2    (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DELUDING_TAB    |     1 |   508 |     2    (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_DELUDING_TAB |     1 |       |     1    (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("STATUS"=1)


統計
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      2  consistent gets
      0  physical reads
      0  redo size
    487  bytes sent via SQL*Net to client
    509  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed

データはヒットしていないですね。索引レンジスキャンは妥当な実行計画:)

お〜〜っと、ここで、インスタント!　全データをヒント無しで、索引レンジスキャン！

ちなみに、遊戯王ばかりで、マジック・ザ・ギャザリングをしらない、最近の子の為に解説しておくと、フラッシュタイミングで使うカードと似たような使い方をするカードのことだぉ

00:07:27 SCOTT> select * from deluding_tab where status = 2;

100000行が選択されました。

経過: 00:00:02.32

実行計画
----------------------------------------------------------
Plan hash value: 1226994206

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |   508 |     2    (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DELUDING_TAB    |     1 |   508 |     2    (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_DELUDING_TAB |     1 |       |     1    (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("STATUS"=2)


統計
----------------------------------------------------------
       1  recursive calls
       0  db block gets
   20166  consistent gets
       0  physical reads
       0  redo size
53229491  bytes sent via SQL*Net to client
   73846  bytes received via SQL*Net from client
    6668  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
  100000  rows processed

ターンエンド。

リテラル値指定、かつ、全件ヒットするからTABLE FULL SCANになるはずなのに、何故、INDEX RANGE SCANしてすげ〜時間かかっているんだ〜〜〜〜というマジックの秘密は次回。

バインド変数使ってなくてもオプティマイザをだませるんだぉ

つづく。