一区二区三区日韩精品-日韩经典一区二区三区-五月激情综合丁香婷婷-欧美精品中文字幕专区

搜索
分享

【原】Oracle分區(qū)表之創(chuàng)建維護(hù)分區(qū)表索引的詳細(xì)步驟

 數(shù)據(jù)和云 2020-07-01
分區(qū)索引分為本地(local index)索引和全局索引(global index)。局部索引比全局索引容易管理, 而全局索引比較快。

與索引有關(guān)的表：
dba_part_indexes 分區(qū)索引的概要統(tǒng)計(jì)信息，可以得知每個(gè)表上有哪些分區(qū)索引，分區(qū)索引的類型(local/global)
dba_ind_partitions 每個(gè)分區(qū)索引的分區(qū)級(jí)統(tǒng)計(jì)信息
dba_indexes/dba_part_indexes 可以得到每個(gè)表上有哪些非分區(qū)索引
Local索引肯定是分區(qū)索引，Global索引可以選擇是否分區(qū)，如果分區(qū)，只能是有前綴的分區(qū)索引。

分區(qū)索引分2類：有前綴(prefix)的分區(qū)索引和無(wú)前綴(nonprefix)的分區(qū)索引:

（1）有前綴的分區(qū)索引指包含了分區(qū)鍵，并且將其作為引導(dǎo)列的索引。
如：
create index i_id_global on PDBA(id) global --引導(dǎo)列2 partition by range(id) --分區(qū)鍵3 (partition p1 values less than (200),4 partition p2 values less than (maxvalue)5 );

這里的ID 就是分區(qū)鍵，并且分區(qū)鍵id 也是索引的引導(dǎo)列。

（2）無(wú)前綴的分區(qū)索引的列不是以分區(qū)鍵開頭，或者不包含分區(qū)鍵列。
如：
create index ix_custaddr_local_id_p on custaddr(id)local (partition t_list556 tablespace icd_service,partition p_other tablespace icd_service)

這個(gè)分區(qū)是按照areacode來(lái)的。但是索引的引導(dǎo)列是ID。所以它就是非前綴分區(qū)索引。

全局分區(qū)索引不支持非前綴的分區(qū)索引，如果創(chuàng)建，報(bào)錯(cuò)如下：
SQL> create index i_time_global on PDBA(id) global --索引引導(dǎo)列2 partition by range(time) --分區(qū)建3 (partition p1 values less than (TO_DATE(‘2010-12-1’, ‘YYYY-MM-DD’)),4 partition p2 values less than (maxvalue)5 );partition by range(time)*

第 2 行出現(xiàn)錯(cuò)誤:
ORA-14038: GLOBAL 分區(qū)索引必須加上前綴

Local 本地索引

對(duì)于local索引，當(dāng)表的分區(qū)發(fā)生變化時(shí)，索引的維護(hù)由Oracle自動(dòng)進(jìn)行。

分區(qū)表索引注意事項(xiàng)：
（1） 局部索引一定是分區(qū)索引，分區(qū)鍵等同于表的分區(qū)鍵。
（2） 前綴和非前綴索引都可以支持索引分區(qū)消除，前提是查詢的條件中包含索引分區(qū)鍵。
（3） 局部索引只支持分區(qū)內(nèi)的唯一性，無(wú)法支持表上的唯一性，因此如果要用局部索引去給表做唯一性約束，則約束中必須要包括分區(qū)鍵列。
（4） 局部分區(qū)索引是對(duì)單個(gè)分區(qū)的，每個(gè)分區(qū)索引只指向一個(gè)表分區(qū)；全局索引則不然，一個(gè)分區(qū)索引能指向n個(gè)表分區(qū)，同時(shí)，一個(gè)表分區(qū)，也可能指向n個(gè)索引分區(qū)，對(duì)分區(qū)表中的某個(gè)分區(qū)做truncate或者move，shrink等，可能會(huì)影響到n個(gè)全局索引分區(qū)，正因?yàn)檫@點(diǎn)，局部分區(qū)索引具有更高的可用性。
（5） 位圖索引必須是局部分區(qū)索引。
（6） 局部索引多應(yīng)用于數(shù)據(jù)倉(cāng)庫(kù)環(huán)境中。
（7） B樹索引和位圖索引都可以分區(qū)，但是HASH索引不可以被分區(qū)。
示例：
sql> create index ix_custaddr_local_id on custaddr(id) local;
索引已創(chuàng)建。

和下面SQL 效果相同，因?yàn)閘ocal索引就是分區(qū)索引：

create index ix_custaddr_local_id_p on custaddr(id)local (partition t_list556 tablespace icd_service,partition p_other tablespace icd_service)SQL> create index ix_custaddr_local_areacode on custaddr(areacode) local;
索引已創(chuàng)建。

驗(yàn)證2個(gè)索引的類型：

SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name=‘CUSTADDR’;index_name table_name partition locali alignment
ix_custaddr_local_areacode custaddr list local prefixedix_custaddr_local_id custaddr list local non_prefixed

因?yàn)槲覀兊腸ustaddr表是按areacode進(jìn)行分區(qū)的，所以索引ix_custaddr_local_areacode是有前綴的索引（prefixed）。而ix_custaddr_local_id是非前綴索引。

Global索引

對(duì)于global索引，可以選擇是否分區(qū)，而且索引的分區(qū)可以不與表分區(qū)相對(duì)應(yīng)。全局分區(qū)索引只能是B樹索引，到目前為止(10gR2)，oracle只支持有前綴的全局索引。
另外oracle不會(huì)自動(dòng)的維護(hù)全局分區(qū)索引，當(dāng)我們?cè)趯?duì)表的分區(qū)做修改之后，如果對(duì)分區(qū)進(jìn)行維護(hù)操作時(shí)不加上update global indexes的話，通常會(huì)導(dǎo)致全局索引的INVALDED，必須在執(zhí)行完操作后 REBUILD。

注意事項(xiàng)：
（1）全局索引可以分區(qū)，也可以是不分區(qū)索引，全局索引必須是前綴索引，即全局索引的索引列必須是以索引分區(qū)鍵作為其前幾列。
（2）全局索引可以依附于分區(qū)表；也可以依附于非分區(qū)表。
（3）全局分區(qū)索引的索引條目可能指向若干個(gè)分區(qū)，因此，對(duì)于全局分區(qū)索引，即使只截?cái)嘁粋€(gè)分區(qū)中的數(shù)據(jù)，都需要rebulid若干個(gè)分區(qū)甚至是整個(gè)索引。
（4）全局索引多應(yīng)用于oltp系統(tǒng)中。
（5）全局分區(qū)索引只按范圍或者散列分區(qū)，hash分區(qū)是10g以后才支持。
（6） oracle9i以后對(duì)分區(qū)表做move或者truncate的時(shí)可以用update global indexes語(yǔ)句來(lái)同步更新全局分區(qū)索引，用消耗一定資源來(lái)?yè)Q取高度的可用性。
（7） 表用a列作分區(qū)，索引用b做局部分區(qū)索引，若where條件中用b來(lái)查詢，那么oracle會(huì)掃描所有的表和索引的分區(qū)，成本會(huì)比分區(qū)更高，此時(shí)可以考慮用b做全局分區(qū)索引。

注意：Oracle只支持2中類型的全局分區(qū)索引：
range partitioned 和 Hash Partitioned.

官網(wǎng)的說(shuō)明如下：
Global Partitioned Indexes
Oracle offers two types of global partitioned index: range partitioned and hash partitioned.
（1）Global Range Partitioned Indexes
Global range partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table’s partitioning method. They are commonly used for OLTP environments and offer efficient access to any individual record.
The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. This ensures that all rows in the underlying table can be represented in the index. Global prefixed indexes can be unique or nonunique.
You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE. If you wish to add a new highest partition, use the ALTER INDEX SPLIT PARTITION statement. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.
（2）Global Hash Partitioned Indexes
Global hash partitioned indexes improve performance by spreading out contention when the index is monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.
（3）Maintenance of Global Partitioned Indexes
By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:
ADD (HASH)
COALESCE (HASH)
DROP
EXCHANGE
MERGE
MOVE
SPLIT
TRUNCATE

示例1：全局索引，全局索引對(duì)所有分區(qū)類型都支持：
sql> create index ix_custaddr_ global_id on custaddr(id) global;

索引已創(chuàng)建。

示例2：全局分區(qū)索引，只支持Range 分區(qū)和Hash 分區(qū)：

（1）創(chuàng)建2個(gè)測(cè)試分區(qū)表：
sql> create table pdba (id number, time date) partition by range (time)2 (3 partition p1 values less than (to_date(‘2010-10-1’, ‘yyyy-mm-dd’)),4 partition p2 values less than (to_date(‘2010-11-1’, ‘yyyy-mm-dd’)),5 partition p3 values less than (to_date(‘2010-12-1’, ‘yyyy-mm-dd’)),6 partition p4 values less than (maxvalue)7 );表已創(chuàng)建。SQL> create table Thash2 (3 id number primary key,4 item_id number(8) not null5 )6 partition by hash(id)7 (8 partition part_01,9 partition part_02,10 partition part_0311 );表已創(chuàng)建。

（2）創(chuàng)建分區(qū)索引
示例2：全局分區(qū)索引
SQL> create index i_id_global on PDBA(id) global2 partition by range(id)3 (partition p1 values less than (200),4 partition p2 values less than (maxvalue)5 );

索引已創(chuàng)建。

–這個(gè)是有前綴的分區(qū)索引。
SQL> create index i_time_global on PDBA(id) global2 partition y range(time)3 (partition p1 values less than (TO_DATE(‘2010-12-1’, ‘YYYY-MM-DD’)),4 partition p2 values less than (maxvalue)5 );partition by range(time)*

第 2 行出現(xiàn)錯(cuò)誤:
ORA-14038: GLOBAL 分區(qū)索引必須加上前綴SQL> create index i_time_global on PDBA(time) global2 partition by range(time)3 (partition p1 values less than (TO_DATE(‘2010-12-1’, ‘YYYY-MM-DD’)),4 partition p2 values less than (maxvalue)5 );

索引已創(chuàng)建。

–有前綴的分區(qū)索引
SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name=‘PDBA’;index_name table_name partition locali alignmenti_id_global pdba range global prefixedi_time_global pdba range global prefixedSQL> CREATE INDEX ix_hash ON PDBA (id,time) GLOBAL2 PARTITION BY HASH (id)3 (PARTITION p1,4 PARTITION p2,5 PARTITION p3,6 PARTITION p4);

索引已創(chuàng)建。

只要索引的引導(dǎo)列包含分區(qū)鍵，就是有前綴的分區(qū)索引。
索引重建問題

（1）分區(qū)索引

對(duì)于分區(qū)索引，不能整體進(jìn)行重建，只能對(duì)單個(gè)分區(qū)進(jìn)行重建。語(yǔ)法如下：
Alter index idx_name rebuild partition index_partition_name [online nologging]

說(shuō)明：
online:表示重建的時(shí)候不會(huì)鎖表。
nologging：表示建立索引的時(shí)候不生成日志，加快速度。
如果要重建分區(qū)索引，只能drop表原索引，在重新創(chuàng)建：
SQL>create index loc_xxxx_col on xxxx(col) local tablespace SYSTEM;
這個(gè)操作要求較大的臨時(shí)表空間和排序區(qū)。

示例：
SQL> select index_name,partition_name from user_ind_partitions where index_name=‘I_TIME_GLOBAL’;INDEX_NAME PARTITION_NAMEI_TIME_GLOBAL P1I_TIME_GLOBAL P2SQL> alter index I_TIME_GLOBAL rebuild partition p1 online nologging;

索引已更改。

SQL> alter index I_TIME_GLOBAL rebuild partition p2 online nologging;

索引已更改。

（2）全局索引
Oracle 會(huì)自動(dòng)維護(hù)分區(qū)索引，對(duì)于全局索引，如果在對(duì)分區(qū)表操作時(shí)，沒有指定update index，則會(huì)導(dǎo)致全局索引失效，需要重建。

SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME=‘IX_PDBA_GLOBAL’;owner index_name table_name statussys ix_pdba_global pdba valid

刪除一個(gè)分區(qū)：
SQL> alter table pdba drop partition p2;

表已更改。
SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME=‘IX_PDBA_GLOBAL’;owner index_name table_name statussys ix_pdba_global pdba validsplit 分區(qū)：SQL> alter table pdba split partition P4 at(TO_DATE(‘2010-12-21 00:00:00’,‘YYYY-MM-DD HH24:MI:SS’)) into (partition P4, partition P5);

表已更改。
SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME=‘IX_PDBA_GLOBAL’;owner index_name table_name statussys ix_pdba_global pdba validdrop 分區(qū)時(shí)使用update indexesSQL> alter table pdba drop partition P4 UPDATE INDEXES;

表已更改。
SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME=‘IX_PDBA_GLOBAL’;owner index_name table_name statussys ix_pdba_global pdba valid

做了幾個(gè)drop分區(qū)操作，全局索引沒有失效，有點(diǎn)奇怪。不過如果在生產(chǎn)環(huán)境中，還是小心點(diǎn)。

重建全局索引命令如下：
Alter index idx_name rebuild [online nologging]

示例：
SQL> Alter index ix_pdba_global rebuild online nologging;

索引已更改。

補(bǔ)充一點(diǎn)，分區(qū)表存儲(chǔ)空間的問題：
SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name=‘DBA’;TABLE_NAME PARTITION_NAME TABLESPACE_NAMEDBA P1 SYSTEMDBA P2 SYSTEMDBA P3 SYSTEMDBA P4 SYSTEM

通過user_tab_partitions 表可以查看到每個(gè)分區(qū)對(duì)應(yīng)的tablesapce_name. 但是，如果通過all_tables 表，卻查不到分區(qū)表對(duì)應(yīng)表空間的信息。

分區(qū)表：
SQL> select owner,table_name,tablespace_name,cluster_name from all_tables where table_name=‘DBA’;OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAMESYS DBA

普通表：
SQL> select owner,table_name,tablespace_name,cluster_name from all_tables where table_name=‘DAVE’;OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME

墨天輪原文鏈接：https://www./db/21901

    轉(zhuǎn)藏 分享 獻(xiàn)花（0）

    0條評(píng)論

    發(fā)表

    請(qǐng)遵守用戶 評(píng)論公約

    類似文章 更多

    午夜福利大片亚洲一区| 中文字幕亚洲人妻在线视频| 在线观看视频成人午夜| 中文字幕一二区在线观看| 久久亚洲午夜精品毛片| 久久99午夜福利视频| 午夜福利网午夜福利网| 99秋霞在线观看视频| 日韩精品你懂的在线观看| av国产熟妇露脸在线观看| 美女露小粉嫩91精品久久久| 在线一区二区免费的视频| 亚洲一区在线观看蜜桃| 午夜国产福利在线播放| 国产精品自拍杆香蕉视频| 久久精品久久久精品久久| 国产高清三级视频在线观看| 99精品国产一区二区青青 | 人人爽夜夜爽夜夜爽精品视频| 精品一区二区三区不卡少妇av | 欧美日韩黄片免费试看 | 最新国产欧美精品91| 国产精品白丝一区二区| 欧美精品一区二区三区白虎| 国产亚洲视频香蕉一区| 中文字幕熟女人妻视频| 亚洲欧洲日韩综合二区| 亚洲av日韩av高潮无打码| 五月婷婷亚洲综合一区| 午夜视频在线观看日韩| 日本 一区二区 在线| 午夜福利视频偷拍91| 夜色福利久久精品福利| 国产精品内射视频免费| 日韩一级欧美一级久久| 日本午夜免费福利视频 | 免费观看日韩一级黄色大片| 超薄肉色丝袜脚一区二区| 太香蕉久久国产精品视频| 国产一区欧美一区日本道| 亚洲中文字幕三区四区|