【分区管理】如何确定分区索引是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';