オラクルパーティションとアルゴリズムの検証

最近オラクルのパーティションの置換に気になって、検証しました。
・パーティションはWHERE条件に合っていれば、自動的に使われる
・exchange partition without validationはデータの保障を一切しない
・場合によって実行の文は正しい値が戻せない

SQL> drop table hist_part_list;

表が削除されました。

SQL> create table hist_part_list (cust_no varchar2(12), trade_code number(3), call_datetime date, memo varchar2(20),
  2  constraint hist_part_list_pk primary key (cust_no, trade_code, call_datetime) using index local
  3  )
  4  partition by list(trade_code) (
  5  partition hist_part_list_01 values (1, 11),
  6  partition hist_part_list_02 values (2, 12)
  7  );

表が作成されました。

SQL> insert into hist_part_list values('000000000001', 1, to_date('2011/01/01','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000002', 2, to_date('2010/01/01','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000001', 1, to_date('2011/01/02','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000002', 2, to_date('2010/01/02','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000001', 1, to_date('2011/01/03','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000002', 2, to_date('2010/01/03','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> commit;

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

SQL>
SQL> insert into hist_part_list values('000000000001', 1, to_date('2012/01/01','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000002', 2, to_date('2012/01/01','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000001', 1, to_date('2012/01/02','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000002', 2, to_date('2012/01/02','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000001', 1, to_date('2012/01/03','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000002', 2, to_date('2012/01/03','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> commit;

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

SQL> drop table hist_part_rng_list;

表が削除されました。

SQL> create table hist_part_rng_list (cust_no varchar2(12), trade_code number(3), call_datetime date, memo varchar2(20),

  2  constraint hist_part_rng_list_pk primary key (cust_no, trade_code, call_datetime) using index local
  3  )
  4  partition by range(call_datetime) subpartition by list(trade_code) (
  5     partition hist_part_rng_list_10 values less than (to_date('2012/01/01','YYYY/MM/DD')) (
  6             subpartition hist_part_rng_list_11_01 values(1, 11),
  7             subpartition hist_part_rng_list_11_02 values(2, 12)
  8     ),
  9     partition hist_part_rng_list_11 values less than (to_date('2013/01/01','YYYY/MM/DD')) (
 10             subpartition hist_part_rng_list_12_01 values(1, 11),
 11             subpartition hist_part_rng_list_12_02 values(2, 12)
 12     )
 13  );

表が作成されました。

SQL> drop table hist_temp;

表が削除されました。

SQL> create table hist_temp (cust_no varchar2(12), trade_code number(3), call_datetime date, memo varchar2(20),
  2  constraint hist_temp_pk primary key (cust_no, trade_code, call_datetime)
  3  );

表が作成されました。

SQL> alter table hist_part_list exchange partition hist_part_list_01
  2  with table hist_temp
  3  including indexes without validation;

表が変更されました。

SQL> alter table hist_part_rng_list exchange subpartition hist_part_rng_list_11_01
  2  with table hist_temp
  3  including indexes without validation;

表が変更されました。

SQL> select * from hist_part_rng_list;

CUST_NO      TRADE_CODE CALL_DAT MEMO
------------ ---------- -------- --------------------
000000000001          1 11-01-01 test
000000000001          1 11-01-02 test
000000000001          1 11-01-03 test
000000000001          1 12-01-01 test
000000000001          1 12-01-02 test
000000000001          1 12-01-03 test

6行が選択されました。

SQL> alter table hist_part_list exchange partition hist_part_list_02
  2  with table hist_temp
  3  including indexes without validation;

表が変更されました。

SQL> alter table hist_part_rng_list exchange subpartition hist_part_rng_list_11_02
  2  with table hist_temp
  3  including indexes without validation;

表が変更されました。

SQL> select * from hist_part_rng_list;

CUST_NO      TRADE_CODE CALL_DAT MEMO
------------ ---------- -------- --------------------
000000000001          1 11-01-01 test
000000000001          1 11-01-02 test
000000000001          1 11-01-03 test
000000000001          1 12-01-01 test
000000000001          1 12-01-02 test
000000000001          1 12-01-03 test
000000000002          2 10-01-01 test
000000000002          2 10-01-02 test
000000000002          2 10-01-03 test
000000000002          2 12-01-01 test
000000000002          2 12-01-02 test
000000000002          2 12-01-03 test

12行が選択されました。

SQL> set autot on explain;
SQL> select * from hist_part_rng_list a where trade_code = 1 and call_datetime > to_date('2012/01/01','yyyy/mm/dd');

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

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

-------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                    |     1 |    42 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|                    |     1 |    42 |     2   (0)| 00:00:01 |     2 |     2 |
|   2 |   PARTITION LIST SINGLE|                    |     1 |    42 |     2   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    TABLE ACCESS FULL   | HIST_PART_RNG_LIST |     1 |    42 |     2   (0)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------------

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

   3 - filter("TRADE_CODE"=1 AND "CALL_DATETIME">TO_DATE(' 2012-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Note
-----
   - dynamic sampling used for this statement

SQL>