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

http://www.askmaclean.com/archives/determine-partition-index-local-global-prefixed.html

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

相关文章 | Related posts:

1. Function Based Indexes and Global Temporary Tables
2. Script:Lists Text index and it’s dependent objects
3. Script:列出数据库中子表上没有对应索引的外键
4. Script:Generate A DDL Script For A Table
5. Script to Detect Tablespace Fragmentation
6. Restoring a user's original password
7. Oracle SQL Developer 的一个Bug
8. DBMS_REPAIR example

FILED UNDER: ORACLE, ORACLE分区技术PARTITION