针对表分区-索引分区的误解

关于分区有许多误解，比如：
*分区索引只能在分区表上创建
*分区表内部，子分区个数必须相同
*表与索引使用相同的分区方式（列与分区方式）
以下针对这些误解举反例。
当然，这里只是说明Oracle支持这些”非对称性“，只适合在非常特殊的情况下使用。

1，对非分区表建分区索引：
--创建非分区表
create table non_part_tab(d date,s varchar2(100),i int);
--创建分区索引
CREATE INDEX idx_non_part_tab ON non_part_tab (i)
  GLOBAL PARTITION BY HASH (i)
  PARTITIONS 4;

--结果：
select index_name,partitioning_type from user_part_indexes where index_name='IDX_NON_PART_TAB';
IDX_NON_PART_TAB HASH

select index_name,partition_name from user_ind_partitions where index_name='IDX_NON_PART_TAB';
IDX_NON_PART_TAB SYS_P821
IDX_NON_PART_TAB SYS_P822
IDX_NON_PART_TAB SYS_P823
IDX_NON_PART_TAB SYS_P824

2，对分区表内不同分区，可以设置不同数量的hash子分区（包括表和索引）：
--创建范围-HASH分区表,其中子分区个数不同
CREATE TABLE composite_sales
    ( prod_id        NUMBER(6)
    , cust_id        NUMBER
    , time_id        DATE
    , channel_id     CHAR(1)
    , promo_id       NUMBER(6)
    , quantity_sold  NUMBER(3)
    , amount_sold         NUMBER(10,2)
    )
PARTITION BY RANGE (time_id)
SUBPARTITION BY HASH (channel_id)
  (PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-04-2000','DD-MM-YYYY')),
   PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-07-2000','DD-MM-YYYY'))
      SUBPARTITIONS 8,
   PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-10-2000','DD-MM-YYYY'))
     (SUBPARTITION ch_c,
      SUBPARTITION ch_i,
      SUBPARTITION ch_p,
      SUBPARTITION ch_s,
      SUBPARTITION ch_t),
   PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE)
      SUBPARTITIONS 4)
;
--创建范围-HASH分区表索引,其中子分区个数不同
CREATE INDEX sales_ix ON composite_sales(time_id, prod_id)
   STORAGE (INITIAL 1M MAXEXTENTS UNLIMITED)
   LOCAL
   (PARTITION q1_2000,
    PARTITION q2_2000
      (SUBPARTITION pq2001, SUBPARTITION pq2002,
       SUBPARTITION pq2003, SUBPARTITION pq2004,
       SUBPARTITION pq2005, SUBPARTITION pq2006,
       SUBPARTITION pq2007, SUBPARTITION pq2008),
    PARTITION q3_2000
      (SUBPARTITION c1,
       SUBPARTITION c2,
       SUBPARTITION c3,
       SUBPARTITION c4,
       SUBPARTITION c5),
    PARTITION q4_2000
      (SUBPARTITION pq4001,
       SUBPARTITION pq4002,
       SUBPARTITION pq4003,
       SUBPARTITION pq4004)
);

--结果：
select table_name,partitioning_type,subpartitioning_type,partition_count,def_subpartition_count from user_part_tables where table_name='COMPOSITE_SALES';
COMPOSITE_SALES RANGE HASH 4 1

select table_name,composite,partition_name,subpartition_count from user_tab_partitions where table_name='COMPOSITE_SALES';
COMPOSITE_SALES YES SALES_Q1_2000 1
COMPOSITE_SALES YES SALES_Q2_2000 8
COMPOSITE_SALES YES SALES_Q3_2000 5
COMPOSITE_SALES YES SALES_Q4_2000 4

select table_name,partition_name,subpartition_name,subpartition_position from user_tab_subpartitions where table_name='COMPOSITE_SALES';
COMPOSITE_SALES SALES_Q1_2000 SYS_SUBP825 1
COMPOSITE_SALES SALES_Q2_2000 SYS_SUBP826 1
COMPOSITE_SALES SALES_Q2_2000 SYS_SUBP827 2
COMPOSITE_SALES SALES_Q2_2000 SYS_SUBP828 3
COMPOSITE_SALES SALES_Q2_2000 SYS_SUBP829 4
COMPOSITE_SALES SALES_Q2_2000 SYS_SUBP830 5
COMPOSITE_SALES SALES_Q2_2000 SYS_SUBP831 6
COMPOSITE_SALES SALES_Q2_2000 SYS_SUBP832 7
COMPOSITE_SALES SALES_Q2_2000 SYS_SUBP833 8
COMPOSITE_SALES SALES_Q3_2000 CH_C 1
COMPOSITE_SALES SALES_Q3_2000 CH_I 2
COMPOSITE_SALES SALES_Q3_2000 CH_P 3
COMPOSITE_SALES SALES_Q3_2000 CH_S 4
COMPOSITE_SALES SALES_Q3_2000 CH_T 5
COMPOSITE_SALES SALES_Q4_2000 SYS_SUBP834 1
COMPOSITE_SALES SALES_Q4_2000 SYS_SUBP835 2
COMPOSITE_SALES SALES_Q4_2000 SYS_SUBP836 3
COMPOSITE_SALES SALES_Q4_2000 SYS_SUBP837 4

select index_name,partitioned from user_indexes where index_name='SALES_IX';
SALES_IX YES

select index_name,table_name,partitioning_type,subpartitioning_type,partition_count,def_subpartition_count
from user_part_indexes where index_name='SALES_IX';
SALES_IX COMPOSITE_SALES RANGE HASH 4 1

select index_name,partition_name,subpartition_count
from user_ind_partitions where index_name='SALES_IX';
SALES_IX Q1_2000 1
SALES_IX Q2_2000 8
SALES_IX Q3_2000 5
SALES_IX Q4_2000 4

select index_name,partition_name,subpartition_name,subpartition_position
from user_ind_subpartitions where index_name='SALES_IX';
SALES_IX Q1_2000 SYS_SUBP825 1
SALES_IX Q2_2000 PQ2001 1
SALES_IX Q2_2000 PQ2002 2
SALES_IX Q2_2000 PQ2003 3
SALES_IX Q2_2000 PQ2004 4
SALES_IX Q2_2000 PQ2005 5
SALES_IX Q2_2000 PQ2006 6
SALES_IX Q2_2000 PQ2007 7
SALES_IX Q2_2000 PQ2008 8
SALES_IX Q3_2000 C1 1
SALES_IX Q3_2000 C2 2
SALES_IX Q3_2000 C3 3
SALES_IX Q3_2000 C4 4
SALES_IX Q3_2000 C5 5
SALES_IX Q4_2000 PQ4001 1
SALES_IX Q4_2000 PQ4002 2
SALES_IX Q4_2000 PQ4003 3
SALES_IX Q4_2000 PQ4004 4

3，表与索引使用不同方式分区：
--创建表，以d列进行范围分区
create table part_tab_by_date(d date,s varchar2(100),i int)
partition by range(d)
(partition p1 values less than (to_date('20000101','YYYYMMDD')),
 partition p2 values less than (to_date('20010101','YYYYMMDD')),
 partition pmax values less than (maxvalue)
);

--创建索引，索引以i列进行hash分区
CREATE INDEX idx_part_tab_by_date1 ON part_tab_by_date (i)
  GLOBAL PARTITION BY HASH (i)
  PARTITIONS 4;

--创建索引，全局非分区索引
CREATE INDEX idx_part_tab_by_date2 ON part_tab_by_date (i,'1');

--创建索引，本地前缀索引
CREATE INDEX idx_part_tab_by_date3 ON part_tab_by_date (d,s) local;

--创建索引，本地非前缀索引
CREATE INDEX idx_part_tab_by_date4 ON part_tab_by_date (s) local;

--分区表上的索引类型共３类：
--所有：
SELECT index_name, partitioned
  FROM user_indexes
 WHERE table_name = 'PART_TAB_BY_DATE'
 order by 1;
IDX_PART_TAB_BY_DATE1 YES
IDX_PART_TAB_BY_DATE2 NO
IDX_PART_TAB_BY_DATE3 YES
IDX_PART_TAB_BY_DATE4 YES

--1,本地索引(alignment说明前缀／非前缀)
SELECT index_name,locality,alignment
  FROM user_part_indexes
 WHERE table_name = 'PART_TAB_BY_DATE'
   AND locality = 'LOCAL'
   order by 1;
IDX_PART_TAB_BY_DATE3 LOCAL PREFIXED
IDX_PART_TAB_BY_DATE4 LOCAL NON_PREFIXED

--2,全局非分区
SELECT index_name, partitioned
  FROM user_indexes
 WHERE table_name = 'PART_TAB_BY_DATE'
   AND partitioned = 'NO'
   order by 1;
IDX_PART_TAB_BY_DATE2 NO

--3,全局分区索引
SELECT index_name,locality,alignment
  FROM user_part_indexes
 WHERE table_name = 'PART_TAB_BY_DATE'
   AND locality = 'GLOBAL'
order by 1;
IDX_PART_TAB_BY_DATE1 GLOBAL PREFIXED

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-709972/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-709972/