oracle自动分区如何创建本地索引吗,Oracle非分区索引，全局分区索引和本地分区索引。...

--创建非分区表

create tabletest_partition_01(

number_1number,

number_2number,

string_1varchar2(10),

string_2varchar2(20)

);

-- 写入数据insert intotest_partition_01(number_1,

number_2,

string_1,

string_2)select dbms_random.random() asnumber_1,round(dbms_random.value(0, 100000)) asnumber_2,

dbms_random.string(opt=> 'A', len => 1) asString_1,

dbms_random.string(opt=> 'p', len => 10) asString_2fromdual

connectby rownum < 100000;commit;

--试图创建本地分区索引 报错CREATE INDEX ix_test_partition_01_1 ONtest_partition_01(number_1)

local (PARTITION p1,

PARTITION p2,

PARTITION p3);---ORA-14016

---创建普通索引

CREATE INDEX ix_test_partition_01_1 ONtest_partition_01(number_1);

--创建 全局分区索引CREATE INDEX ix_test_partition_01_2 ONtest_partition_01(number_2)

GLOBAL PARTITIONBYRANGE (number_2)

(PARTITION p1VALUES LESS THAN (10000),

PARTITION p2VALUES LESS THAN (55000),

PARTITION p3VALUESLESS THAN (MAXVALUE));

结论：非分区表可以创建普通索引和全局分区索引不能创建本地分区索引。

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

create tabletest_partiton_02(

number_1number,

number_2number,

string_1varchar2(10),

string_2varchar2(20)

) partitionbyrange(number_2)

(

partition p1values less than (10000),

partition p2values less than (20000),

partition p3values less than (50000),

partition p4values less than (70000),

partition p5valuesless than (maxvalue)

);

--试图创建本地分区索引CREATE INDEX ix_test_partiton_02_1 ONtest_partiton_02(number_1)

local (PARTITION p1,

PARTITION p2,

PARTITION p3);--ora-14024 索引的分区数必须等于基础表的分区数

--创建本地分区索引

CREATE INDEX ix_test_partiton_02_1 ONtest_partiton_02(number_1)

local (PARTITION p1,

PARTITION p2,

PARTITION p3,

PARTITION p4,

PARTITION p5

);--drop index ix_test_partiton_02_1;

CREATE INDEX ix_test_partiton_02_1 ON test_partiton_02(number_1) local; --和上面的创建方式等效

--drop index ix_test_partiton_02_1;

CREATE INDEX ix_test_partiton_02_1 ON test_partiton_02(number_1) ; --默认创建的是 非分区索引，分区索引才分 全局索引还是本地索引 ;

---创建全局分区索引CREATE INDEX ix_test_partiton_02_2 ONtest_partiton_02(number_2)

GLOBAL PARTITIONBYRANGE (number_2)

(PARTITION p1VALUES LESS THAN (10000),

PARTITION p2VALUES LESS THAN (55000),

PARTITION p3VALUESLESS THAN (MAXVALUE));

---写入测试数据insert intotest_partiton_02(number_1,

number_2,

string_1,

string_2)select dbms_random.random() asnumber_1,round(dbms_random.value(0, 100000)) asnumber_2,

dbms_random.string(opt=> 'A', len => 1) asString_1,

dbms_random.string(opt=> 'p', len => 10) asString_2fromdual

connectby rownum < 100001;commit;

--分析表

analyzetable test_partiton_02 compute statistics;--查看 普通索引是否可用

select * from user_indexes t where t.INDEX_NAME in('IX_TEST_PARTITON_02_1','IX_TEST_PARTITON_02_2','IX_TEST_PARTITON_02_3');---查看分区索引是否 可用

select * from user_ind_partitions t where t.INDEX_NAME in('IX_TEST_PARTITON_02_1','IX_TEST_PARTITON_02_2','IX_TEST_PARTITON_02_3');---改变分区，查看普通索引和分区索引是否可用

--必须调整最后一个分区的大小，所以如果最后一个分区指定了最大值 必须先删除，再添加

alter table test_partiton_02 add partition p6 values less than (90000);alter table test_partiton_02 droppartition p5;--- 结论 改变分区 普通索引和全局分区索引都会失效 只有本地分区索引好使

PS：最好用以下语句查看索引的定义语句select dbms_metadata.get_ddl(object_type => 'INDEX',

name=> 'IX_TEST_PARTITON_02_1')FROMDUAL;select dbms_metadata.get_ddl(object_type => 'INDEX',

name=> 'IX_TEST_PARTITON_02_2')FROM DUAL;

--- 发现了 plsql developer Version 11.0.5.1790 (64 bit) 的一个 bug 在用View 查看 DDL时 没有反应真实的情况