oracle 非global 分区,【分区管理】如何确定分区索引是Global还是Local,PREFIXED 还是NON-PREFIXED...

【分区管理】如何确定分区索引是Global还是Local,PREFIXED 还是NON-PREFIXED

可以通过 DBA_PART_INDEXES视图中的LOCALITY和ALIGNMENT确定这一点：

LOCALITY VARCHAR2(6) Whether this partitioned index is LOCAL or GLOBAL

ALIGNMENT VARCHAR2(12)   Whether this partitioned index is PREFIXED or NON-PREFIXED

CREATE TABLE employees

(employee_id NUMBER(4) NOT NULL,

last_name VARCHAR2(10),

department_id NUMBER(2))

PARTITION BY RANGE (department_id)

(PARTITION employees_part1 VALUES LESS THAN (11) TABLESPACE users,

PARTITION employees_part2 VALUES LESS THAN (21) TABLESPACE users,

PARTITION employees_part3 VALUES LESS THAN (31) TABLESPACE users);

CREATE INDEX local_one ON employees (employee_id) LOCAL;

SQL> CREATE INDEX local_one ON employees (employee_id) LOCAL;

索引已创建。

SQL> select locality,ALIGNMENT from dba_part_indexes where index_name='LOCAL_ONE';

LOCALITY ALIGNMENT

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

LOCAL NON_PREFIXED

drop index LOCAL_ONE;

CREATE INDEX global_one ON employees(employee_id)

GLOBAL PARTITION BY RANGE(employee_id)

(PARTITION p1 VALUES LESS THAN(5000),

PARTITION p2 VALUES LESS THAN(MAXVALUE));

SQL> select locality,ALIGNMENT from dba_part_indexes where index_name='GLOBAL_ONE';

LOCALITY ALIGNMENT

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

GLOBAL PREFIXED

脚本如下：

select locality,ALIGNMENT from dba_part_indexes where index_name='&INDEX_NAME';