分区索引知识点拾遗

索引是一般索引还是分区索引，可以看dba_indexes的partitioned字段。

如果partitioned字段是YES，说明是分区索引，那么，这个索引是global还是local，可以看dba_part_indexes的LOCALITY字段。

另外，我们还可以看ALIGNMENT字段，看这个索引是基于前导列（prefixed）还是非前导列。(注：global肯定是基于前导列，因为不能建基于非前导列的global索引。而local索引可以基于前导列和非前导列)

SQL> drop table invoices;

Table dropped.

--创建分区表，是range分区：
SQL> CREATE TABLE invoices
  2  (invoice_no    NUMBER NOT NULL,
  3   invoice_date  DATE   NOT NULL,
  4   invoice_area varchar2(200),
  5   invoice_serial number,
  6   comments      VARCHAR2(500),
  7   invoice_name varchar2(20)
  8   )
  9  PARTITION BY RANGE (invoice_date)
 10  (PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,
 11   PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users,
 12   PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users,
 13   PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')) TABLESPACE users);

Table created.

SQL>
SQL> --创建global分区索引，分区类型可以和表一样，也可以不一样。这个索引是和表分区类型一样，但是value less值不一样。
SQL> CREATE INDEX idx_glob_inv_ser ON invoices (invoice_serial,comments) GLOBAL
  2  PARTITION BY range (invoice_serial)
  3  (PARTITION invoices_q1 VALUES LESS THAN (10) TABLESPACE users,
  4   PARTITION invoices_q2 VALUES LESS THAN (20) TABLESPACE users,
  5   PARTITION invoices_q3 VALUES LESS THAN (30) TABLESPACE users,
  6   PARTITION invoices_q4 VALUES LESS THAN (40) TABLESPACE users,
  7   PARTITION invoices_qmax VALUES LESS THAN (MAXVALUE) TABLESPACE users);

Index created.

SQL>
SQL> --注意global分区索引，必须使用前导列，及如果索引列是 (comments,invoice_serial)，
SQL> --但partition by xx(invoice_serial)用了非前导列，是会报错，不能创建成功的。
SQL> CREATE INDEX idx_glob_inv_date ON invoices (comments,invoice_serial) GLOBAL
  2  PARTITION BY hash (invoice_serial) partitions 16;
PARTITION BY hash (invoice_serial) partitions 16
                                 *
ERROR at line 2:
ORA-14038: GLOBAL partitioned index must be prefixed


SQL>
SQL>
SQL> --创建另一个global分区索引，这个索引的分区类型是和表分区类型不一样的。用了hash分区。但是prefix前导列的原理也是一样的，需要使用前导列。
SQL> CREATE INDEX idx_glob_inv_date ON invoices (comments,invoice_serial) GLOBAL
  2  PARTITION BY hash (comments) partitions 16;

Index created.

SQL>
SQL>
SQL> --创建local索引，注意不能指定partition by的类型的，local索引的分区类型和分区数量必须和table一致，但是可以指定不同的表空间。这个local使用了前导列。
SQL> CREATE INDEX idx_glo_inv_dt ON invoices (invoice_date,invoice_serial) LOCAL
  2   (PARTITION invoices_q1 TABLESPACE users,
  3    PARTITION invoices_q2 TABLESPACE users,
  4    PARTITION invoices_q3 TABLESPACE users,
  5    PARTITION invoices_q4 TABLESPACE users);

Index created.
SQL>
SQL> --如果分区数量必须和table不一致，会报错：
SQL> CREATE INDEX idx_glo_inv_dt ON invoices (invoice_date,invoice_serial) LOCAL
  2   (PARTITION invoices_q1 TABLESPACE users,
  3    PARTITION invoices_q2 TABLESPACE users,
  4    PARTITION invoices_q3 TABLESPACE users,
  5    PARTITION invoices_q4 TABLESPACE users,
  6    PARTITION invoices_q5 TABLESPACE users);
CREATE INDEX idx_glo_inv_dt ON invoices (invoice_date,invoice_serial) LOCAL
                               *
ERROR at line 1:
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table

SQL> --创建local索引，注，local索引可以使用非前导列。而global索引只能使用前导列，不能使用非前导列。
SQL> CREATE INDEX idx_glo_inv_serl ON invoices (invoice_serial,invoice_date) LOCAL
  2   (PARTITION invoices_q1 TABLESPACE users,
  3    PARTITION invoices_q2 TABLESPACE users,
  4    PARTITION invoices_q3 TABLESPACE users,
  5    PARTITION invoices_q4 TABLESPACE users);

Index created.
SQL>
SQL> --如果分区数量必须和table不一致，会报错：
SQL> CREATE INDEX idx_glo_inv_serl ON invoices (invoice_serial,invoice_date) LOCAL
  2   (PARTITION invoices_q1 TABLESPACE users,
  3    PARTITION invoices_q2 TABLESPACE users,
  4    PARTITION invoices_q3 TABLESPACE users,
  5    PARTITION invoices_q4 TABLESPACE users
  6    PARTITION invoices_q5 TABLESPACE users,
  7    PARTITION invoices_q6 TABLESPACE users);
  PARTITION invoices_q5 TABLESPACE users,
  *
ERROR at line 6:
ORA-14010: this physical attribute may not be specified for an index partition


SQL> 
SQL> --创建一般索引（即非分区索引）：
SQL> create index idx_glo_inv_comm on invoices (comments);

Index created.

SQL>
SQL>
SQL>
SQL>
SQL> select index_name,PARTITIONED from dba_indexes where table_name='INVOICES';

INDEX_NAME           PARTIT
-------------------- ------
IDX_GLOB_INV_SER     YES
IDX_GLOB_INV_DATE    YES
IDX_GLO_INV_DT       YES
IDX_GLO_INV_SERL     YES
IDX_GLO_INV_COMM     NO

SQL>
SQL> select INDEX_NAME,PARTITIONING_TYPE,LOCALITY,ALIGNMENT from dba_part_indexes where table_name='INVOICES';

INDEX_NAME           PARTITIONING_TYPE  LOCALITY     ALIGNMENT
-------------------- ------------------ ------------ ------------------------
IDX_GLOB_INV_SER     RANGE              GLOBAL       PREFIXED
IDX_GLOB_INV_DATE    HASH               GLOBAL       PREFIXED
IDX_GLO_INV_DT       RANGE              LOCAL        PREFIXED
IDX_GLO_INV_SERL     RANGE              LOCAL        NON_PREFIXED

SQL>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

SQL> drop table invoices;

Table dropped.

--创建分区表，是range分区：

SQL> CREATE TABLE invoices

2 (invoice_no NUMBER NOT NULL,

3 invoice_date DATE NOT NULL,

4 invoice_area varchar2(200),

5 invoice_serial number,

6 comments VARCHAR2(500),

7 invoice_name varchar2(20)

8 )

9 PARTITION BY RANGE (invoice_date)

10 (PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,

11 PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users,

12 PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users,

13 PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')) TABLESPACE users);

Table created.

SQL>

SQL> --创建global分区索引，分区类型可以和表一样，也可以不一样。这个索引是和表分区类型一样，但是value less值不一样。

SQL> CREATE INDEX idx_glob_inv_ser ON invoices (invoice_serial,comments) GLOBAL

2 PARTITION BY range (invoice_serial)

3 (PARTITION invoices_q1 VALUES LESS THAN (10) TABLESPACE users,

4 PARTITION invoices_q2 VALUES LESS THAN (20) TABLESPACE users,

5 PARTITION invoices_q3 VALUES LESS THAN (30) TABLESPACE users,

6 PARTITION invoices_q4 VALUES LESS THAN (40) TABLESPACE users,

7 PARTITION invoices_qmax VALUES LESS THAN (MAXVALUE) TABLESPACE users);

Index created.

SQL>

SQL> --注意global分区索引，必须使用前导列，及如果索引列是 (comments,invoice_serial)，

SQL> --但partition by xx(invoice_serial)用了非前导列，是会报错，不能创建成功的。

SQL> CREATE INDEX idx_glob_inv_date ON invoices (comments,invoice_serial) GLOBAL

2 PARTITION BY hash (invoice_serial) partitions 16;

PARTITION BY hash (invoice_serial) partitions 16

ERROR at line 2:

ORA-14038: GLOBAL partitioned index must be prefixed

SQL>

SQL> --创建另一个global分区索引，这个索引的分区类型是和表分区类型不一样的。用了hash分区。但是prefix前导列的原理也是一样的，需要使用前导列。

SQL> CREATE INDEX idx_glob_inv_date ON invoices (comments,invoice_serial) GLOBAL

2 PARTITION BY hash (comments) partitions 16;

Index created.

SQL>

SQL> --创建local索引，注意不能指定partition by的类型的，local索引的分区类型和分区数量必须和table一致，但是可以指定不同的表空间。这个local使用了前导列。

SQL> CREATE INDEX idx_glo_inv_dt ON invoices (invoice_date,invoice_serial) LOCAL

2 (PARTITION invoices_q1 TABLESPACE users,

3 PARTITION invoices_q2 TABLESPACE users,

4 PARTITION invoices_q3 TABLESPACE users,

5 PARTITION invoices_q4 TABLESPACE users);

Index created.

SQL>

SQL> --如果分区数量必须和table不一致，会报错：

SQL> CREATE INDEX idx_glo_inv_dt ON invoices (invoice_date,invoice_serial) LOCAL

2 (PARTITION invoices_q1 TABLESPACE users,

3 PARTITION invoices_q2 TABLESPACE users,

4 PARTITION invoices_q3 TABLESPACE users,

5 PARTITION invoices_q4 TABLESPACE users,

6 PARTITION invoices_q5 TABLESPACE users);

CREATE INDEX idx_glo_inv_dt ON invoices (invoice_date,invoice_serial) LOCAL

ERROR at line 1:

ORA-14024: number of partitions of LOCAL index must equal that of the underlying table

SQL> --创建local索引，注，local索引可以使用非前导列。而global索引只能使用前导列，不能使用非前导列。

SQL> CREATE INDEX idx_glo_inv_serl ON invoices (invoice_serial,invoice_date) LOCAL

2 (PARTITION invoices_q1 TABLESPACE users,

3 PARTITION invoices_q2 TABLESPACE users,

4 PARTITION invoices_q3 TABLESPACE users,

5 PARTITION invoices_q4 TABLESPACE users);

Index created.

SQL>

SQL> --如果分区数量必须和table不一致，会报错：

SQL> CREATE INDEX idx_glo_inv_serl ON invoices (invoice_serial,invoice_date) LOCAL

2 (PARTITION invoices_q1 TABLESPACE users,

3 PARTITION invoices_q2 TABLESPACE users,

4 PARTITION invoices_q3 TABLESPACE users,

5 PARTITION invoices_q4 TABLESPACE users

6 PARTITION invoices_q5 TABLESPACE users,

7 PARTITION invoices_q6 TABLESPACE users);

PARTITION invoices_q5 TABLESPACE users,

ERROR at line 6:

ORA-14010: this physical attribute may not be specified for an index partition

SQL>

SQL> --创建一般索引（即非分区索引）：

SQL> create index idx_glo_inv_comm on invoices (comments);

Index created.

SQL>

SQL> select index_name,PARTITIONED from dba_indexes where table_name='INVOICES';

INDEX_NAME PARTIT

-------------------- ------

IDX_GLOB_INV_SER YES

IDX_GLOB_INV_DATE YES

IDX_GLO_INV_DT YES

IDX_GLO_INV_SERL YES

IDX_GLO_INV_COMM NO

SQL>

SQL> select INDEX_NAME,PARTITIONING_TYPE,LOCALITY,ALIGNMENT from dba_part_indexes where table_name='INVOICES';

INDEX_NAME PARTITIONING_TYPE LOCALITY ALIGNMENT

-------------------- ------------------ ------------ ------------------------

IDX_GLOB_INV_SER RANGE GLOBAL PREFIXED

IDX_GLOB_INV_DATE HASH GLOBAL PREFIXED

IDX_GLO_INV_DT RANGE LOCAL PREFIXED

IDX_GLO_INV_SERL RANGE LOCAL NON_PREFIXED

SQL>

分区索引知识点拾遗

相关文章

12c的网络设置

控制文件丢失的恢复

一步一步配置autolog CDC

发表回复 取消回复

发表回复取消回复