oracleパーティションインデックスの使用に関する問題

テーブルはA列でパーティション化され、インデックスはBでローカルパーティションインデックス(local partitioned indexes)され、where条件でBでクエリされるとoracleはすべてのテーブルをスキャンします.
インデックスとのパーティション化は、コストが高くなります.この場合、Bをグローバルパーティションインデックス(global partitioned indexes)として使用することも考えられます.
 
≪グローバル・パーティション・インデックス｜Global Partitioning Index｜oem_src≫:
 

create index IDX_MER_ACC_DETAIL on TBL_MERCHANT_ACCOUNT_DETAIL(STEP_NAME)  
global partition by range(STEP_NAME)
(
partition pa1 values less than ('dd'),
partition pa2 values less than ('hi'),
partition pa3 values less than ('st'),
partition pa4 values less than ('wt'),
partition pa5 values less than (maxvalue)
)

この記事に感謝します.
Oracleデータベースには、2つのタイプのパーティションインデックス、グローバルインデックス、ローカルインデックスがあります.ローカルインデックスは、ローカル接頭辞インデックスとローカル非接頭辞インデックスに分けることができます.各タイプのインデックスのそれぞれの特徴を見てみましょう.
グローバルインデックスは、テーブル全体のデータをオブジェクトとしてインデックスを作成します.インデックスパーティション内のインデックスエントリは、同じキー値に基づいている可能性がありますが、異なるパーティションから来ている可能性もあります.また、複数の異なるキー値の組合せである可能性もあります.
グローバル・インデックスでは、インデックス・パーティションのキー値とテーブル・パーティションのキー値が同じであるか、異なることができます.グローバルインデックスとテーブルの間には直接的なつながりはありません.これはローカルインデックスとは異なります.
SQL> create table orders (
order_no number,
part_no varchar2(40),
ord_date date
)
partition by range (ord_date)
(partition Q1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
partition Q2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
partition Q3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
partition Q4 values less than (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
)
;
Table created.
SQL> create index orders_global_1_idx
on orders(ord_date)
global partition by range (ord_date)
(partition GLOBAL1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
partition GLOBAL2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
partition GLOBAL3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
partition GLOBAL4 values less than (MAXVALUE)
)
;
Index created.
SQL> create index orders_global_2_idx
on orders(part_no)
global partition by range (part_no)
(partition IND1 values less than (555555),
partition IND2 values less than (MAXVALUE)
)
;
Index created.
上の文から分かるように、グローバルインデックスとテーブルは直接関連付けられておらず、maxvalue値を明示的に指定する必要があります.テーブルにパーティションが追加された場合、グローバルインデックスに新しいパーティションが自動的に追加されないため、手動でパーティションを追加する必要があります.
SQL> alter table orders add partition Q5 values less than (TO_DATE('01-APR-2000','DD-MON-YYYY'));
Table altered.
SQL> select TABLE_NAME, PARTITION_NAME from dba_tab_partitions where table_name='ORDERS';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
ORDERS Q1
ORDERS Q2
ORDERS Q3
ORDERS Q4
ORDERS Q5
SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_global_1_idx');
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
ORDERS_GLOBAL_1_IDX GLOBAL1
ORDERS_GLOBAL_1_IDX GLOBAL2
ORDERS_GLOBAL_1_IDX GLOBAL3
ORDERS_GLOBAL_1_IDX GLOBAL4
グローバルインデックスを使用するには、インデックスキー値がパーティションキー値と同じである必要があります.これが、いわゆるプレフィックスインデックスです.Oracleでは、接頭辞なしのグローバル・パーティション・インデックスはサポートされていません.接頭辞なしのパーティション・インデックスを作成する必要がある場合は、ローカル・インデックスを作成する必要があります.
SQL> create index orders_global_2_idx
2 on orders(part_no)
3 global partition by range (order_no)
4 (partition IND1 values less than (555555),
5 partition IND2 values less than (MAXVALUE)
6 )
7 ;
global partition by range (order_no)
*
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed
次にローカルパーティションを見てみましょう.
ローカルインデックスのパーティションは、対応するテーブルパーティションの数と等しいため、各テーブルパーティションは対応するインデックスパーティションに対応します.ローカル・インデックスを使用すると、パーティションの範囲を指定する必要はありません.インデックスはテーブルに対してローカルであるため、ローカル・インデックスが作成されると、Oracleはテーブル内の各パーティションに独立したインデックス・パーティションを自動的に作成します.
ローカルインデックスを作成するには、テーブルに新しいテーブルパーティションを追加すると、対応するインデックスパーティションが自動的に追加されるため、maxvalue値を明示的に指定する必要はありません.
create index orders_local_1_idx
on orders(ord_date)
local
(partition LOCAL1,
partition LOCAL2,
partition LOCAL3,
partition LOCAL4
)
;
Index created.
SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_local_1_idx');
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
ORDERS_LOCAL_1_IDX LOCAL1
ORDERS_LOCAL_1_IDX LOCAL2
ORDERS_LOCAL_1_IDX LOCAL3
ORDERS_LOCAL_1_IDX LOCAL4
SQL> alter table orders add partition Q5 values less than (TO_DATE('01-APR-2000','DD-MON-YYYY'));
Table altered.
SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_local_1_idx');
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
ORDERS_LOCAL_1_IDX LOCAL1
ORDERS_LOCAL_1_IDX LOCAL2
ORDERS_LOCAL_1_IDX LOCAL3
ORDERS_LOCAL_1_IDX LOCAL4
ORDERS_LOCAL_1_IDX Q5
ここで、システムはテーブルパーティションと同じ名前でインデックスパーティションを自動的に作成しました.同様に、テーブルパーティションを削除するときに対応するインデックスパーティションも自動的に削除されます.
ローカルインデックスとグローバルインデックスには、前述したように、ローカルインデックスはコスト的に非接頭辞型を作成できますが、グローバルインデックスは接頭辞型のみです.
SQL> create index orders_local_2_idx
on orders(part_no)
local
(partition LOCAL1,
partition LOCAL2,
partition LOCAL3,
partition LOCAL4)
;
Index created.
SQL> select INDEX_NAME, PARTITION_NAME, HIGH_VALUE from dba_ind_partitions
where index_name=upper('orders_local_2_idx');
INDEX_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ---------------------------------------------------------
ORDERS_LOCAL_2_IDX LOCAL1 TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA'
ORDERS_LOCAL_2_IDX LOCAL2 TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA'
ORDERS_LOCAL_2_IDX LOCAL3 TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA'
ORDERS_LOCAL_2_IDX LOCAL4 TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA'
上の出力から分かるように、インデックスのキー値はpart_ですがNoですが、インデックスパーティションのキー値はテーブルのパーティションキー値と同じです.ord_date、すなわち、いわゆる非接頭辞型インデックスである.
最後に、接頭辞インデックスおよび非接頭辞インデックスの適用例をさらに参照して説明する.
DATEカラムパーティションを使用する大きなテーブルがあるとします.クエリーにはVARCHAR 2カラム(VCOL)をよく使用しますが、このカラムはテーブルのパーティションキー値ではありません.
VCOL列のデータにアクセスするには、VCOL列に基づくローカル非接頭辞インデックスを確立する2つの可能な方法があります.
| |
------- -------
| | (10 more | |
Values: A.. Z.. partitions here) A.. Z..
もう1つはVCOL列に基づくグローバルインデックスを確立することであり、
| |
------- -------
| | (10 more | |
Values: A.. D.. partitions here) T.. Z..
VCOLカラム値の一意性を保証できれば,グローバルインデックスが最良の選択であることが分かる.VCOLカラムの値が一意でない場合は、ローカルの非接頭辞インデックスのパラレルクエリとグローバルインデックス順序クエリ、および高いメンテナンスコストの間で選択する必要があります.