drop index partition ORA-14076

SQL> select partition_name from dba_ind_partitions where index_name='LOCAL_ONE';

PARTITION_NAME
---------------------------------------------------------------------------------------------

EMPLOYEES_PART1
EMPLOYEES_PART2
EMPLOYEES_PART3

SQL> alter index LOCAL_ONE drop partition EMPLOYEES_PART1;
alter index LOCAL_ONE drop partition EMPLOYEES_PART1
            *
第 1 行出现错误:
ORA-14076: 提交的变更索引分区/子分区操作对本地分区的索引无效

You cannot explicitly drop a partition from a local index.  Instead, local 
index partitions are dropped only when you drop a partition from the underlying 
table. You have to disable the local partition index by making it unusable.

Example:

-- alter table make all local partition indexes unusable
ALTER TABLE &v_table_name
MODIFY PARTITION &v_part_name
UNUSABLE LOCAL INDEXES;

This will set the UNUSABLE status in the DBA_IND_PARTITIONS on all
the local partitioned indexes equipartitioned with &v_part_name.

ALTER SESSION SET skip_unusable_indexes = true;
 
-- load data in the corresponding partitions
INSERT ... VALUES ...
 
-- alter table make partition local index usable
ALTER TABLE &v_table_name
MODIFY PARTITION &v_part_name
REBUILD UNUSABLE LOCAL INDEXES;

The status of the &v_part_name partitions indexes will be now USABLE again in
DBA_IND_PARTITIONS.

 

Oracle 数据分区创建和使用的咨询

ORACLE的分区(Partitioning Option)是一种处理超大型表的技术。分区是一种“分而治之”的技术,通过将大表和索引分成可以管理的小块,从而避免了对每个表作为一个大的、单独的对象进行管理,为大量数据提供了可伸缩的性能。分区通过将操作分配给更小的存储单元,减少了需要进行管理操作的时间,并通过增强的并行处理提高了性能,通过屏蔽故障数据的分区,还增加了可用性。

在Oracle 11g中,Oracle支持三种普通分区以及两种组合分区。
ORACLE的分区表的划分方法包括:按字段值进行划分的范围分区(RANGE);按字段的HASH 函数值进行的划分HASH 分区;按字段值列表进行划分的列表 (LIST) 分区方法。
Oracle支持两种组合分区,分别是先按范围划分,再按HASH 划分的RANGE-HASH组合分区;另一种是先按范围进行分区,再按LIST进行子分区的RANGE-LIST组合分区。
管理员可以指定每个分区的存储属性,分区在宿主文件系统(Storage subsystem)中的放置情况,这样便增加了对超大型数据库的控制粒度(granularity)。分区可以被单独地删除、卸载或装入、备份、恢复,因此减少了需要进行管理操作的时间。
还可以为表分区创建单独的索引分区,从而减少了需要进行索引维护操作的时间。此外,还提供了种类繁多的局部和全局的索引技术。分区操作也可以被并行执行。
分区技术还提高了数据的可用性。当部分数据由于故障或其它原因不可用时,其它分区内的数据可用不收影响继续使用。
分区对应用是透明的,可以通过标准的SQL语句对分区表进行操作。Oracle 的优化器在访问数据时会分析数据的分区情况,在进行查询时,那些不包含任何查询数据的分区将被忽略,从而大大提高系统的性能。

 

Range分区
范围分区是最常使用的一种分区。

分区介绍

范围分区根据分区字段(Partition Key)的值实现数据和分区的映射。
分区的字段可以是一个或多个字段(an ordered list of columns)组合。分区字段可以是除ROWID,LONG,LONG,LOB TIMESTAMP WITH TIME ZONE之外其他数据库内嵌数据类型。
在创建范围分区时,应指定各分区的值域和各个分区的存储特性。

适用情景
范围分区特别适用于待处理分区数据在分布上具有逻辑范围或范围值域,如数据在按月份进行分区。当数据能够在所有分区上均匀分布时范围分区能获得非常好的性能,若因数据分布不均匀而导致各个分区数据在数据量上变化很大,则应考虑采用其他的分区方法。
范围分区也适用于数据随时间变化而增长的情景。
范围分区亦适用于数据归档和定期维护的需要,若能结合业务的需要定义好范围分区可实现按分区归档和数据维护的需要。
通过设定分区的值域(partition bond),范围分区可以显式地指定数据的分布。

示例

分区字段为单一字段

 

CREATE TABLE sales_range
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
COMPRESS
PARTITION BY RANGE(sales_date) —- partition key
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE(’02/01/2000′,’DD/MM/YYYY’)),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE(’03/01/2000′,’DD/MM/YYYY’)),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE(’04/01/2000′,’DD/MM/YYYY’)),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE(’05/01/2000′,’DD/MM/YYYY’)));

 

分区字段为单一字段

CREATE TABLE sales
( invoice_no NUMBER,
sale_year INT NOT NULL,
sale_month INT NOT NULL,
sale_day INT NOT NULL )
PARTITION BY RANGE (sale_year, sale_month, sale_day) –partition key
( PARTITION sales_q1 VALUES LESS THAN (1999, 04, 01)
TABLESPACE tsa,
PARTITION sales_q2 VALUES LESS THAN (1999, 07, 01)
TABLESPACE tsb,
PARTITION sales_q3 VALUES LESS THAN (1999, 10, 01)
TABLESPACE tsc,
PARTITION sales_q4 VALUES LESS THAN (2000, 01, 01)
TABLESPACE tsd );

 

Hash分区

哈希分区是Oracle支持的、通过哈希算法来实现记录和分区映射的一种分区方法。

分区介绍

哈希分区是实现数据在各个分区上均匀分区的最佳分区方式,哈希分区可以保证各个分区大小近似,记录数相当。
Oracle所采用的哈希算法是一种线性算法,即哈希分区不是根据业务规则或者数据本身的逻辑来分布数据,哈希分区数应该是2的指数(2,4,6,8,…)。
分区字段(partition key) 可以是单一字段或者字段列表(不能超过16个字段)。
分区字段不支持ROWID,UROWID, LONG, LOB等数据类型。
在创建哈希分区时,可以通过individual_hash_partitions 的方式单独指定每个分区的特征;

 

QQ截图20160321153830

在创建哈希分区时,也可以通过hash_partitions_by_quantity的方式指定哈希分区数(partition quantity),如下图

QQ截图20160321154239

当数据与时间的关系不紧密时,哈希分区可用于替代范围分区。
因为数据的分布完全由哈希算法决定,因此采用哈希分区主要是希望通过将数据均匀分布到各个分区以获得性能上的提升。

由于哈希分区的实现是基于哈希函数,因此不支持如删除哈希分区、合并哈希分区以及拆分哈希分区操作等一些分区操作。但支持增加、交换、移动、缩减等分区操作。

 

示例
individual_hash_partitions
hash_partitions_by_quantity
下面的示例是创建一个4个HASH 分区表的例子,并为各个分区指定了表空间。
CREATE TABLE scubagear
( id NUMBER,
name VARCHAR2 (60))
PARTITION BY HASH (id) –哈希分区
PARTITIONS 4 –分区数
STORE IN (gear1, gear2, gear3, gear4);

 

List分区
List分区是Oracle 9i引入的一种分区方式。
分区介绍
LIST分区是指通过为分区字段(partition key)指定一组离散的值来实现的。
可以指定缺省LIST分区,这样不满足任何分区条件的记录可以存储到缺省的分区。
分区字段(Partition Key)不能是LOB类型,也不能是多个字段的组合。
适用情景
当需要显式地控制数据与分区的映射关系时应采用LIST分区。
List分区以可以实现将无关、无序的离散数据集组合到一个分区。
示例
CREATE TABLE sales_list
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_state) –范围分区
(PARTITION sales_west VALUES(‘California’, ‘Hawaii’),
PARTITION sales_east VALUES (‘New York’, ‘Virginia’, ‘Florida’),
PARTITION sales_central VALUES(‘Texas’, ‘Illinois’),
PARTITION sales_other VALUES(DEFAULT)); –缺省分区

 

Composite分区:Range-Hash

在11g 中,Oracle提供了2中组合分区方式,即Range-Hash分区和Range-List分区。
分区介绍
Oracle首先通过指定值域的方式对数据进行了范围分区,然后在分区的基础上按哈希算法进一步划分。Range-Hash分区兼具范围分区和哈希分区的优势。
适用情景
适用于特大型的数据表。
数据在分区的级别上实现了按值域范围划分,同时在分区内又通过哈希函数将数据均匀分布到各个子分区内。
在子分区上也可实现并行数据操纵操作(PDML)。
支持分区排除(Partition-Pruning)和Partition-Wise Join。
示例
CREATE TABLE scubagear
(equipno NUMBER, equipname VARCHAR(32), price NUMBER)
PARTITION BY RANGE (equipno) –第一层Range分区
SUBPARTITION BY HASH(equipname) –第二层Hash分区
SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4) –指定哈希子分区数
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (MAXVALUE));

 

Composite分区:Range-List
Range-List是一种实现2层分区的分区技术。
分区介绍
数据首先通过指定值域的方式进行范围分区,然后在分区的基础上通过指定离散的值来进一步划分分区内无序、无关的数据。
适用情景
特大型数据表可考虑采用。
要求分区(第一级)可以通过划分子分区的方式分布到不同的表空间和存储介质上以充分利用MPP和多通道I/O处理能力,以进一步提升性能。
支持分区排除(Partition-Pruning)和Partition-Wise Join。
示例
CREATE TABLE quarterly_regional_sales
(deptno NUMBER, item_no VARCHAR2(20),
txn_date DATE, txn_amount NUMBER, state VARCHAR2(2))
PARTITION BY RANGE (txn_date) –第一层 范围分区
SUBPARTITION BY LIST (state) –第二层 列表分区
SUBPARTITION TEMPLATE( –指定子分区特征
SUBPARTITION northwest VALUES (‘OR’, ‘WA’) TABLESPACE ts1,
SUBPARTITION southwest VALUES (‘AZ’, ‘UT’) TABLESPACE ts2,
SUBPARTITION northeast VALUES (‘NY’, ‘VM’, ‘NJ’) TABLESPACE ts3,
SUBPARTITION southeast VALUES (‘FL’, ‘GA’) TABLESPACE ts4,
SUBPARTITION northcentral VALUES (‘SD’, ‘WI’) TABLESPACE ts5,
SUBPARTITION southcentral VALUES (‘NM’, ‘TX’) TABLESPACE ts6)
(PARTITION q1_1999 VALUES LESS THAN(TO_DATE(‘1-APR-1999′,’DD-MON-YYYY’)),
PARTITION q2_1999 VALUES LESS THAN(TO_DATE(‘1-JUL-1999′,’DD-MON-YYYY’)),
PARTITION q3_1999 VALUES LESS THAN(TO_DATE(‘1-OCT-1999′,’DD-MON-YYYY’)),
PARTITION q4_1999 VALUES LESS THAN(TO_DATE(‘1-JAN-2000′,’DD-MON-YYYY’)));

 

分区原则
下面主要列出在进行表分区常遵循的一些设计原则。这些原则也适用于确定哪些表或者索引应考虑分区。
表分区原则
表的大小
对于大表进行分区,将有益于大表操作的性能和大表的数据维护。通常当表的大小超过1.5GB-2GB,或对于OLTP系统,表的记录超过1000万,都应考虑对表进行分区。
数据访问特性
若基于表的大部分查询应用,只访问表中少量或者部分数据,对于这样表进行分区,可充分利用分区排除无关数据查询 (partition-pruning) 的特性。
数据维护
某些表的数据维护,经常按时间段删除成批的数据或者按特定规则进行数据归档,例如按月删除历史数据。对于这样的表需要考虑进行分区,以满足维护的需要。因为删除(Delete)大量的数据,对系统开销很大,有时甚至是不可接受的。
只读数据
如果一个表中大部分数据都是只读数据,通过对表进行分区,可将只读数据存储在只读表空间中,对于数据库的备份是非常有益的。
并行数据操作 (Parallel DML)
对于经常执行并行操作 (如 Parallel Insert, Parallel Update 等) 的表应考虑进行分区。
表的可用性
当对表的部分数据可用性要求很高时,应考虑进行表分区。

 

 

索引分区
索引分区原则
索引分区与数据表分区的关系
对表进行分区的规则同样适用对索引进行分区。
表(无论分区与否)上的索引可以是分区的也可以是非分区的。
分区表可以有分区的或者不分区的B-Tree索引。
索引分区设计适当,也可以提高索引的可管理性、应用性能和可用性。
索引分区介绍
Oracle提供的索引分区类型
Oracle 提供了如下三种分区索引:
 Local Index
1. Local Prefixed Index
2. Local Non-Prefixed Index
 Global Prefixed Index
Local Index指的是索引分区内的索引键值指向的记录都对应到基表的一个分区,即索引分区和基表分区是equipartitioned(分区一一对应)。
Global Index是指索引分区与表分区相互独立,不存在索引分区和表分区之间的一一对应关系

 

 

Local Index

QQ截图20160321154541

Local Prefixed Index
若分区索引的partition key 是基表(Base table)的partition key的严格左前缀,则称之为Local Prefixed Index。
如下表中,表EMP按DEPTNO进行范围分区,表上索引IX1(基于字段DEPTNO)同样按DEPTNO进行分区,使得索引分区和表分区一一对应。

 

QQ截图20160321154607

Local Non-prefixed Index

若索引分区的partition key不是基表partition key的严格左前缀(left prefix),则称之为Local Non-prefixed Index。
如下图中,数据表CHECKS基于字段CHKDATE进行了范围分区(Range),但是范围分区索引IX3(基于ACCTNO字段)则采用了ACCTNO进行分区,而ACCTNO不是CHKDATE的左前缀。

 

QQ截图20160321154636

Global Prefixed Index

全局分区索引
若一个全局分区索引按其索引字段的严格左前缀进行分区则称之为Global Prefixed Index。反之,若全局分区索引不采用其索引字段的左前缀进行分区则称为Global Non-prefixed Index。Oracle不支持采用Global Non-prefixed Index。
例:假设索引基于字段(A、B、C),则可以采用(A、B、C)、(A、B)、(A、C)或者A作为全局分区的Partition Key;但不可以采用(B、C)、B或者C作为Partition Key。
Oracle支持两种Global Partition Index,分别是按Range或Hash进行全局索引划分,如下图:

QQ截图20160321154702

Global Partition By Range
如下图,表EMP按字段DEPTNO进行了Range分区,而基于EMPNO字段的全局分区索引IX03则按EMPNO字段进行了Range分区。每个索引分区存储的键值可以指向底层表的任意数据分区。

 

QQ截图20160321155344

Global Partition By Hash
在以HASH进行全局分区的索引中,每个索引分区包含由Oracle HASH函数所确定的键值对。
采用HASH全局分区索引,索引键值被HASH按分区字段(Partition Key)分散到不同的分区上,有效地降低了竞争和提高了性能。包含有“=”或者“IN”等条件子句的查询能够有效地利用全局HASH索引分区的特性,在以下两种情景尤其适用:

 

1. 在并发多用户应用(OLTP)中若索引的部分页节点数据库存在竞争,则采用HASH进行全局索引分区能有效提高性能;
2. 若索引所对应的索引字段在应用上存在单调增的特性,则容易导致索引一直进行右扩,索引树严重不均衡,最右端的索引块容易成为“热点”,导致性能下降;

如何确定索引类型
上述索引分别适应于不同的性能和数据管理需求,一般可通过下图所定义的规则,可确定分区索引类型:

QQ截图20160321155448

 

 

 

 

 

Oracle分区表基础知识培训

分区表基础知识培训,适合对分区表知识了解不多的朋友。

 

顺利在2月28日给客户单位的运维和开发部门做了关于分区表基础知识的培训, 大家提了不少值得思考的问题,Great!

 

 

 

Oracle Partitioning分区技术历年新特性回顾

Partitioning分区是Oracle一种重要的处理大量数据的特性,从Oracle 8开始引入range partition范围分区,到现在11g中出现的Interval Partition、REF Partitions、Partition Advisor等new feature,分区技术在一步步走向成熟。

 

 

下表列出了从Oracle 8到11g的各历史版本中出现的分区特性:

 


Core functionality Performance Manageability
Oracle8 Range partitioning “Static” partition pruning Basic maintenance operations: add, drop, exchange
Global range indexes
Oracle8i Hash and composite range-hash partitioning Partition-wise joins Merge operation
“Dynamic” pruning
Oracle9i List partitioning
Global index maintenance
Oracle9i R2 Composite range-list partitioning Fast partition split
Oracle10g Global hash indexes
Local Index maintenance
Oracle10g R2 1M partitions per table “Multi-dimensional” pruning Fast drop table
Oracle Database 11g More composite choices
Interval Partitioning
REF Partitioning Partition Advisor
Virtual Column Partitioning

 

复合分区(Composite Partitioning)最早在版本8i中被引入(Range-Hash),是一种二维的模式分区技术; 在11g中进一步扩展了Composite Partitioning,11g 支持RANGE-RANGE、 RANGE-LIST、LIST-LIST、LIST-HASH等几种模式。 其中RANGE-RANGE是我们久违了的,很多初学分区概念的同学都会惊讶于11g以前居然没有RANGE-RANGE分区可用。

 


Range List Hash
Range 11g 9i 8i
List 11g 11g 11g

 

解决Oracle中Split Partition缓慢的问题

有这样一个case , 用户的10g产品数据库中有一张按照月份划分的RANGE分区表 , 其范围为Less than (maxvalue)的最后一个Partition分区总是为空Empty的, 用户每隔半年会对这个MaxValue Partition 执行Split Partition 操作, 以便存放后续月份的数据, 同时这个表上还有一个非分区索引Nonpartitioned indexes。

 

满以为Split 这样一个Empry Partition会是一个回车就结束的任务, 但是Performance issue就在这平淡无奇的分区维护工作中出现了, 实际在执行”Alter Table Split partition Pn at (value) into …” 的DDL语句时,发现需要花费超过十多分钟才能完成一次Split。问题就在于,如果是有大量数据的Partition分区 , Split 操作慢一些也是正常的(expected预期内的) , 但是实际这里的Max Partition总是为空的,没有任何一条数据, 为什么Split 一个空的分区也会是这种蜗牛的速度呢?

 

我们来模拟这个现象, 首先创建一张分区表,Maxvalue的Partition是Empty的,且有一个普通索引:

 

 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com & www.askmaclean.com

SQL>   CREATE TABLE Maclean
  2     (    "OWNER" VARCHAR2(30),
  3          "OBJECT_NAME" VARCHAR2(128),
  4          "SUBOBJECT_NAME" VARCHAR2(30),
  5          "OBJECT_ID" NUMBER,
  6          "DATA_OBJECT_ID" NUMBER,
  7          "OBJECT_TYPE" VARCHAR2(19),
  8          "CREATED" DATE,
  9          "LAST_DDL_TIME" DATE,
 10          "TIMESTAMP" VARCHAR2(19),
 11          "STATUS" VARCHAR2(7),
 12          "TEMPORARY" VARCHAR2(1),
 13          "GENERATED" VARCHAR2(1),
 14          "SECONDARY" VARCHAR2(1)
 15     ) nologging
 16     partition by range(object_id)
 17     (partition p1 values less than (99999) tablespace users,
 18     partition p2 values less than (maxvalue) tablespace users);

Table created.

SQL> insert /*+ append */ into maclean select * from maclean1;

38736384 rows created.

SQL> commit; 

Commit complete.

SQL> create index ind_obj on maclean(DATA_OBJECT_ID,OBJECT_ID,LAST_DDL_TIME,TIMESTAMP,object_type,owner,status)
nologging parallel
  2  ;

Index created.

SQL> alter index ind_obj noparallel;

Index altered.

SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN',cascade=>true,degree=>2);

PL/SQL procedure successfully completed.

SQL> select num_rows from dba_tables where table_name='MACLEAN';

  NUM_ROWS
----------
  38818438

SQL>  select * from maclean partition (p2);

no rows selected

/* Maclean表有大量的数据 ,但是都在p1分区中, p2分区没有任何数据 */

 

 

我们执行Split partition 的DDL 语句,并使用10046 level 12 event监控该过程:

 

 oradebug setmypid;
oradebug event 10046 trace name context forever,level 12;
SQL>  alter table maclean split partition p2 at (100001)
2   into (partition p3, partition p4);
Table altered.
[oracle@vrh8 ~]$ tkprof /s01/admin/G10R21/udump/g10r21_ora_4896.trc g10r21_ora_4896.tkf
TKPROF: Release 10.2.0.1.0 - Production on Thu Nov 17 23:42:48 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
从tkf 文件中可以找出以下内容:
alter table maclean split partition p2 at (100001)
into (partition p3, partition p4)
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.13       0.30         20       1139          0           0
Execute      1      0.01       0.18          3          6         33           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.14       0.48         23       1145         33           0
select /*+ FIRST_ROWS PARALLEL("MACLEAN", 1) */ 1
from
 "SYS"."MACLEAN" PARTITION ("P2")  where ( (  (  ( "OBJECT_ID" < 100001 )  )
  ) ) and rownum < 2
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1     24.85      39.15     371276     385828          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     24.85      39.15     371276     385828          0           0
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: SYS   (recursive depth: 1)
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  COUNT STOPKEY (cr=385828 pr=371276 pw=0 time=39153836 us)
      0   TABLE ACCESS BY GLOBAL INDEX ROWID MACLEAN PARTITION: 2 2 (cr=385828 pr=371276 pw=0 time=39153817 us)
38736384    INDEX FULL SCAN IND_OBJ (cr=385828 pr=371276 pw=0 time=309891137 us)(object id 52832)
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                    371276        0.08         19.46
  latch: cache buffers lru chain                  1        0.00          0.00
select /*+ FIRST_ROWS PARALLEL("MACLEAN", 1) */ 1
from
 "SYS"."MACLEAN" PARTITION ("P2")  where ( (  (  ( "OBJECT_ID" >= 100001 OR
  "OBJECT_ID" IS NULL  )  )  ) ) and rownum < 2
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           0
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: SYS   (recursive depth: 1)
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  COUNT STOPKEY (cr=3 pr=0 pw=0 time=83 us)
      0   PARTITION RANGE SINGLE PARTITION: 2 2 (cr=3 pr=0 pw=0 time=63 us)
      0    TABLE ACCESS FULL MACLEAN PARTITION: 2 2 (cr=3 pr=0 pw=0 time=36 us)

 

 

可以看到在执行”Alter table Split partition”的时候该DDL 语句产生了另外2条递归SQL(recursive sql)被调用,即上例中红色标注的SQL语句,这2条递归SQL分别以 “OBJECT_ID” >= 100001 OR “OBJECT_ID” IS NULL  和  “OBJECT_ID” < 100001 作为条件查询P2分区中的数据, Split Partition的DDL需要使用这2条SQL来找出是否存在满足分隔条件过滤的数据(注意语句中有rownum<2 , 所以实际最多也只返回1条数据,Oracle这样来判定分隔条件的左端或右端是否有数据)。

 

但是这里可以看到,其中 以”OBJECT_ID” < 100001 作为条件的语句运行耗时39.15s, 产生了大量的逻辑和物理读, 究其原因是该SQL的执行计划采用了Index FULL SCAN ,而另外一条以 “OBJECT_ID” >= 100001 OR “OBJECT_ID” IS NULL 作为条件的递归SQL语句则采用了TABLE ACCESS FULL MACLEAN PARTITION, 因为实际P2分区中是没有任何数据的,所以后者运行时间是us级别的, 而前者所要扫描的是一整个没有分区的索引, 这产生了大量的”db file sequential read”等待事件, 我们再来看一下其执行计划:

 

 

SQL> explain plan for
2  select /*+ FIRST_ROWS PARALLEL("MACLEAN", 1) */
3   1
4    from "SYS"."MACLEAN" PARTITION("P2")
5   where (((("OBJECT_ID" < 100001))))
6     and rownum < 2; 
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------
Plan hash value: 985826631
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |     1 |    13 |   380K  (1)| 01:16:01 |       |       |
|*  1 |  COUNT STOPKEY                      |         |       |       |            |          |       |       |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| MACLEAN |     1 |    13 |   380K  (1)| 01:16:01 |     2 |     2 |
|*  3 |    INDEX FULL SCAN                  | IND_OBJ |    38M|       |   380K  (1)| 01:16:01 |       |       |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<2)
   3 - access("OBJECT_ID"<100001)
       filter("OBJECT_ID"<100001)

 

注意以上递归SQL中的Hint “FIRST_ROWS PARALLEL(“MACLEAN”, 1)”是该Recursive SQL所固有的,换句话说是写死在Split Partition的Oracle代码层里的。

我们可以分析该Recursive SQL 采用INDEX FULL SCAN的原因可能有2种:
1. Split 所指定的分区的分区统计信息不准确,或者已经被清除。 注意一旦我们Split 某个分区后该分区原有统计信息将失效,且被清除。

如下面的例子:

 

SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN',cascade=>true,degree=>2);
PL/SQL procedure successfully completed.
SQL> col high_value for a20
SQL> select partition_name,high_value,num_rows,blocks from dba_tab_partitions where table_name='MACLEAN';
PARTITION_NAME                 HIGH_VALUE             NUM_ROWS     BLOCKS
------------------------------ -------------------- ---------- ----------
P1                             99999                  38789142     533240
P3                             100001                        0          0
P4                             MAXVALUE                      0          0
SQL>  alter table maclean split partition p4 at (100010)
2   into (partition p5, partition p4);
这里我们再次Split 当前的MAXVALUE分区p4
SQL> select partition_name,high_value,num_rows,blocks from dba_tab_partitions where table_name='MACLEAN';
PARTITION_NAME                 HIGH_VALUE             NUM_ROWS     BLOCKS
------------------------------ -------------------- ---------- ----------
P1                             99999                  38789142     533240
P3                             100001                        0          0
P4                             MAXVALUE
P5                             100010

 

 

可以发现Split Partiton 会导致原Partiton的统计信息失效,即便使用dbms_stats.lock_table_stats锁住统计信息也无法避免这种失效。
且单个partiton的统计信息失效并不会导致动态采用的发生(dynamic sampling):

 

SQL> show parameter dyna 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
SQL> select * from maclean partition (p4);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3900731449
--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |  9699K|   860M| 29715   (2)| 00:05:57 |       |       |
|   1 |  PARTITION RANGE SINGLE|         |  9699K|   860M| 29715   (2)| 00:05:57 |     4 |     4 |
|   2 |   TABLE ACCESS FULL    | MACLEAN |  9699K|   860M| 29715   (2)| 00:05:57 |     4 |     4 |
--------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
124  recursive calls
0  db block gets
23  consistent gets
0  physical reads
0  redo size
1139  bytes sent via SQL*Net to client
458  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
0  rows processed
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set optimizer_dynamic_sampling=10;
Session altered.
SQL> select * from maclean partition (p4);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3900731449
--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |  9699K|   860M| 29715   (2)| 00:05:57 |       |       |
| 1 | PARTITION RANGE SINGLE | | 9699K| 860M| 29715 (2)| 00:05:57 | 4 | 4 |
|   2 |   TABLE ACCESS FULL    | MACLEAN |  9699K|   860M| 29715   (2)| 00:05:57 |     4 |     4 |
--------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
5812  recursive calls
0  db block gets
1141  consistent gets
22  physical reads
0  redo size
1139  bytes sent via SQL*Net to client
458  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
139  sorts (memory)
0  sorts (disk)
0  rows processed
SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN',partname=>'P4');
PL/SQL procedure successfully completed.
SQL> select * from maclean partition (p4);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3900731449
--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |     1 |   177 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|         |     1 |   177 |     2   (0)| 00:00:01 |     4 |     4 |
|   2 |   TABLE ACCESS FULL    | MACLEAN |     1 |   177 |     2   (0)| 00:00:01 |     4 |     4 |
--------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
3  consistent gets
0  physical reads
0  redo size
1139  bytes sent via SQL*Net to client
458  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
0  rows processed

 

 

由于Split Partition 本身会导致分区的统计信息失效, 这叫造成由该Split DDL所生成的递归查询SQL语句在解析时CBO Optimizer优化器无法了解该分区的必要统计信息,所以优化器会根据整张Table的统计信息来估算(根据算法来估,而没有实际的统计操作), 实际统计信息中整张表有38818438 行 , 且共有4个分区, 所以P4分区就顺利成长的拥有38818438 /4 = 9704k Rows了 , 实际上例中红色标注的估算值时9699k rows, 因为CBO 优化器得到的统计信息是该分区中有大量的数据 ,这导致其最终选择了 FULL INDEX SCAN的执行计划 ,而不是去扫描其实是空空如也的分区。

 

这是我们在 10g 中执行Split Partition 操作时需要密切注意的一个问题, 解决方法是没执行一次Split Partition DDL语句之前都收集一遍MaxValue Partiton 的统计信息, 因为该分区是空的 ,所以这种统计是十分迅速的:

 

exec dbms_stats.gather_table_stats('&OWNER','&TABNAME',partname=>'&PARNAME');

 

 

另一种手段则是在每次Split 之前都 手动修改Maxvalue分区的统计信息, 这样做会更快一些:

 

exec dbms_stats.set_table_stats(ownname => '&OWNER',tabname => '&TABNAME',partname => '&PARNAME',
numrows => 0,numblks => 0,force => TRUE);

 

 

2. 另一个原因是 相关的递归SQL语句 被嵌入了”First Rows”的hint , 该提示会让CBO更倾向于使用索引以便快速返回前几行的结果,注意因为这些递归SQL实际只要求返回一行结果,所以First Rows 可以说是恰当且明智的; 另外在分区表+本地分区的情景中, 即便这个分区是非空的且存有大量的数据, 那么使用索引都可以说是正确的选择。

 

但是在这里选择INDEX FULL SCAN 恰恰是不明智的, 很显然Oracle开发部门没有为分区表+ 非分区索引(Non-partitioned Indexes) 或 全局索引(global partitioned indexes)的场景考虑该First Rows提示可能带来的后果,  已知在版本10.2.0.2 和 10.2.0.3 上都存在该不恰当的递归SQL hint造成的Split Partiton性能问题,Bug Note<Bug 6435544: SPLIT PARTITION SLOW BECAUSE OF HINTED RECURSIVE SQL>说明了该问题:

 

Hdr: 6435544 10.2.0.2.0 RDBMS 10.2.0.2.0 QRY OPTIMIZER PRODID-5 PORTID-226
Abstract: SPLIT PARTITION SLOW BECAUSE OF HINTED RECURSIVE SQL
PROBLEM:
--------
Split partition operation took more than 45 minutes to complete. Almost all
the time is taken up by the following SQL -
select /*+ FIRST_ROWS PARALLEL("D_CUSTOMER_ORDER_ITEM_CHANNELS", 8) */ 1
from
"BOOKER"."D_CUSTOMER_ORDER_ITEM_CHANNELS" PARTITION ("COIC101_MAX")
where ( ( ( ( "LEGAL_ENTITY_ID" < 101 )  )  OR  ( "LEGAL_ENTITY_ID" = 101 AND
(
"ORDER_DAY" < TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN') )  )  ) ) and rownum < 2
DIAGNOSTIC ANALYSIS:
--------------------
a. Table is partitioned on - (LEGAL_ENTITY_ID, ORDER_DAY).
Index PK_D_CUST_ORDER_ITEM_CHANNELS is on -
(CUSTOMER_ORDER_ITEM_ID, MARKETPLACE_ID, LEGAL_ENTITY_ID, ORDER_DAY)
b. Table has 555 partitions. Index is a global index. 
c. From the tkprof output -
call   count       cpu    elapsed       disk      query  current  rows
----- ------  -------- ---------- ---------- ---------- --------  ----
Parse      1      0.00       0.00          0          0        0     0
Execute    1      0.00       0.00          0          0        0     0
Fetch      1    405.50    2796.28    1408207    1444973        0     0
----- ------  -------- ---------- ---------- ---------- --------  ----
total      3    405.50    2796.28    1408207    1444973        0     0
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 108     (recursive depth: 1)
Rows     Row Source Operation
-------  ---------------------------------------------------
time=6 us)(object id 1160811)
d. Although we have partition_name in the query, we go for INDEX FULL scan
because of hard coded hint FIRST_ROWS. A FTS of the concerned partition would
be much faster in this case.
WORKAROUND:
-----------
Drop the global index which would force an FTS on a single partition.
RELEASE NOTES:
]] Poor performance was possible for ALTER TABLE SPLIT PARTITION if there
]] was an index on the partition key.
REDISCOVERY INFORMATION:
If you get poor performance for an ALTER TABLE SPLIT PARTITION operation
and there is an index on the partition key, you may be hitting this bug.
WORKAROUND:
None

 

 

Metalink给出的Workaround方案是将分区表上的global index 全局索引drop 掉, 这样可以让CBO只能选择对single partition的FULL TABLE SCAN。

 

实际上这个Solution并很不能让人满意,在产品环境中我们不可能随意drop掉某张关键表上的索引,所以这个solution的可操作性很低。

 

补充:我们来看一下First Rows Hint 在CBO计算成本时如何起作用的:

 

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10053 trace name context forever,level 1;
Statement processed.
SQL> explain plan for select /*+ FIRST_ROWS PARALLEL("MACLEAN", 1) */
2   1
3    from "SYS"."MACLEAN" PARTITION("P4")
4   where (((("OBJECT_ID" < 100010))))
5     and rownum < 2; Explained. SQL> oradebug tracefile_name;
10053 trace content ================================================
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
optimizer_mode_hinted               = true
optimizer_mode                      = first_rows
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: MACLEAN  Alias: MACLEAN  Partition [3]
    #Rows: 0  #Blks:  1  AvgRowLen:  0.00
    #Rows: 0  #Blks:  1  AvgRowLen:  0.00
Index Stats::
  Index: IND_OBJ  Col#: 5 4 8 9 6 1 10
    LVLS: 3  #LB: 380544  #DK: 50447  LB/K: 7.00  DB/K: 777.00  CLUF: 39208542.00
***************************************
SINGLE TABLE ACCESS PATH
  Column (#4): OBJECT_ID(NUMBER)  Part#: 3
    AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+00 Min: 0 Max: 0
  Column (#4): OBJECT_ID(NUMBER)
    AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+00 Min: 0 Max: 0
  Table: MACLEAN  Alias: MACLEAN     
    Card: Original: 0  Rounded: 1  Computed: 0.00  Non Adjusted: 0.00
  Access Path: TableScan
    Cost:  2.00  Resp: 2.00  Degree: 0
      Cost_io: 2.00  Cost_cpu: 7121
      Resp_io: 2.00  Resp_cpu: 7121
kkofmx: index filter:"MACLEAN"."OBJECT_ID"<100010 AND ROWNUM<2
  Access Path: index (skip-scan)
    SS sel: 0.0000e+00  ANDV (#skips): 4073
    SS io: 32584.00 vs. table scan io: 2.00
    Skip Scan rejected
  Access Path: index (FullScan)
    Index: IND_OBJ
    resc_io: 380547.00  resc_cpu: 10551751028
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 382007.38  Resp: 382007.38  Degree: 1
  Best:: AccessPath: IndexRange  Index: IND_OBJ
         Cost: 382007.38  Degree: 1  Resp: 382007.38  Card: 0.00  Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]:  MACLEAN[MACLEAN]#0
***********************
Best so far: Table#: 0  cost: 382007.3822  card: 0.0000  bytes: 13
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
prefetching is on for IND_OBJ
Final - First Rows Plan:  Best join order: 1
  Cost: 382007.3822  Degree: 1  Card: 1.0000  Bytes: 13
  Resc: 382007.3822  Resc_io: 380547.0000  Resc_cpu: 12512178128
  Resp: 382007.3822  Resp_io: 380547.0000  Resc_cpu: 12512178128
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT /*+ FIRST_ROWS NO_PARALLEL ("MACLEAN") */ 1 "1" FROM "SYS"."MACLEAN" PARTITION ("P4") 
"MACLEAN" WHERE ROWNUM<2 AND "MACLEAN"."OBJECT_ID"<100010
kkoqbc-end
          : call(in-use=46464, alloc=49080), compile(in-use=39288, alloc=40552)
apadrv-end: call(in-use=46464, alloc=49080), compile(in-use=40072, alloc=40552)
sql_id=ff1ft3uxsq105.
Current SQL statement for this session:
explain plan for select /*+ FIRST_ROWS PARALLEL("MACLEAN", 1) */
 1
  from "SYS"."MACLEAN" PARTITION("P4")
 where (((("OBJECT_ID" < 100010))))
   and rownum < 2
============
Plan Table
============
-------------------------------------------------------+-----------------------------------+---------------+
| Id  | Operation                            | Name    | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
-------------------------------------------------------+-----------------------------------+---------------+
| 0   | SELECT STATEMENT                     |         |       |       |  373K |           |       |       |
| 1   |  COUNT STOPKEY                       |         |       |       |       |           |       |       |
| 2   |   TABLE ACCESS BY GLOBAL INDEX ROWID | MACLEAN |     1 |    13 |  373K |  01:16:25 | 4     | 4     |
| 3   |    INDEX FULL SCAN                   | IND_OBJ |   37M |       |  373K |  01:16:25 |       |       |
-------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
1 - filter(ROWNUM<2)
3 - access("OBJECT_ID"<100010)
3 - filter("OBJECT_ID"<100010)
Content of other_xml column
===========================
  db_version     : 10.2.0.1
  parse_schema   : SYS
  plan_hash      : 985826631
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      FIRST_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "MACLEAN"@"SEL$1" ("MACLEAN"."DATA_OBJECT_ID" "MACLEAN"."OBJECT_ID"
"MACLEAN"."LAST_DDL_TIME" "MACLEAN"."TIMESTAMP" "MACLEAN"."OBJECT_TYPE" "MACLE
AN"."OWNER" "MACLEAN"."STATUS"))
    END_OUTLINE_DATA
  */

 

 

可以看到虽然INDEX FULL SCAN的成本(cost:382007)大于 Access Path: TableScan (cost : 2.00) 很多 ,但是optimizer 最终仍然选择了Index Full Scan , 因为其是满足First Rows 要求的执行计划(红色标注部分)。

 

于是我开始自己寻找workaround的路径,目标是让优化器忽略”First Rows”的影响。我一开始寄望于能够通过设置一些影响CBO计算cost的优化器参数来让optimizer 迷途知返, 包括设置optimizer_index_cost_adj和”_db_file_optimizer_read_count”的值到一个很大水平,但发现并不起作用:

 

SQL> alter session set "_db_file_optimizer_read_count"=65535;
Session altered.
SQL>  alter session set optimizer_index_cost_adj=10000;
Session altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select /*+ FIRST_ROWS PARALLEL("MACLEAN", 1) */
2   1
3    from "SYS"."MACLEAN" PARTITION("P4")
4   where (((("OBJECT_ID" < 100010))))
5     and rownum < 2;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 985826631
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |     1 |    13 |    38M  (1)|127:20:09 |       |       |
|*  1 |  COUNT STOPKEY                      |         |       |       |            |          |       |       |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| MACLEAN |     1 |    13 |    38M  (1)|127:20:09 |     4 |     4 |
|*  3 |    INDEX FULL SCAN                  | IND_OBJ |    39M|       |    38M  (1)|127:20:09 |       |       |
---------------------------------------------------------------------------------------------------------------

 

得益于好奇心,我以’%optimizer%ignore’的Like语句去查了Oracle的隐式参数表,果然有志者事竟成,最终有所斩获:

 

SQL> col name for a40
SQL> col value for a20
SQL> col describ for a60
SQL> set linesize 200 pagesize 1400
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2   FROM SYS.x$ksppi x, SYS.x$ksppcv y
3   WHERE x.inst_id = USERENV ('Instance')
4   AND y.inst_id = USERENV ('Instance')
5   AND x.indx = y.indx
6  AND x.ksppinm LIKE '%optimizer%ignore%';
NAME                                     VALUE                DESCRIB
---------------------------------------- -------------------- ---------------------
_optimizer_ignore_hints                  TRUE                 enables the embedded hints to be ignored

 

 

在metalink上查了下没有关于该”_optimizer_ignore_hints” 隐式参数的任何有用信息, 就注释来看是可以启用是否忽略SQL中嵌入的HINT提示信息, 我们来具体看以下是否其作用:

 

 

SQL> alter system flush shared_pool;
System altered.
SQL> explain plan for select /*+ FIRST_ROWS PARALLEL("MACLEAN", 1) */
2   1
3    from "SYS"."MACLEAN" PARTITION("P4")
4   where (((("OBJECT_ID" < 100010))))
5     and rownum < 2; 
Explained. 
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------
Plan hash value: 985826631
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |     1 |    13 |    38M  (1)|127:20:09 |       |       |
|*  1 |  COUNT STOPKEY                      |         |       |       |            |          |       |       |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| MACLEAN |     1 |    13 |    38M  (1)|127:20:09 |     4 |     4 |
|*  3 |    INDEX FULL SCAN                  | IND_OBJ |    39M|       |    38M  (1)|127:20:09 |       |       |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<2)
   3 - access("OBJECT_ID"<100010)
       filter("OBJECT_ID"<100010)
SQL> alter session set "_optimizer_ignore_hints"=true;
Session altered.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10053 trace name context forever , level 1;
Statement processed.
SQL> explain plan for select /*+ FIRST_ROWS PARALLEL("MACLEAN", 1) */
2   1
3    from "SYS"."MACLEAN" PARTITION("P4")
4   where (((("OBJECT_ID" < 100010))))
5     and rownum < 2; Explained. SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------
Plan hash value: 4280157877
---------------------------------------------------------------------------------------------------
| Id  | Operation               | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |     1 |    13 |     2   (0)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY          |         |       |       |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|         |     1 |    13 |     2   (0)| 00:00:01 |     4 |     4 |
|*  3 |    TABLE ACCESS FULL    | MACLEAN |     1 |    13 |     2   (0)| 00:00:01 |     4 |     4 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<2)
   3 - filter("OBJECT_ID"<100010)

 

惊喜! 该 “_optimizer_ignore_hints”参数确实起到无视HINT提示的作用, 且可以直接在session级别修改,十分方便, 我们透过10053 event来观察该参数是如何其作用的:

 

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
_optimizer_ignore_hints             = true
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: MACLEAN  Alias: MACLEAN  Partition [3]
    #Rows: 0  #Blks:  1  AvgRowLen:  0.00
    #Rows: 0  #Blks:  1  AvgRowLen:  0.00
Index Stats::
  Index: IND_OBJ  Col#: 5 4 8 9 6 1 10
    LVLS: 3  #LB: 380544  #DK: 50447  LB/K: 7.00  DB/K: 777.00  CLUF: 39208542.00
***************************************
SINGLE TABLE ACCESS PATH
  Column (#4): OBJECT_ID(NUMBER)  Part#: 3
    AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+00 Min: 0 Max: 0
  Column (#4): OBJECT_ID(NUMBER)
    AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+00 Min: 0 Max: 0
  Table: MACLEAN  Alias: MACLEAN
    Card: Original: 0  Rounded: 1  Computed: 0.00  Non Adjusted: 0.00
  Access Path: TableScan
    Cost:  2.00  Resp: 2.00  Degree: 0
      Cost_io: 2.00  Cost_cpu: 7121
      Resp_io: 2.00  Resp_cpu: 7121
kkofmx: index filter:"MACLEAN"."OBJECT_ID"<100010 AND ROWNUM<2
  Access Path: index (skip-scan)
    SS sel: 0.0000e+00  ANDV (#skips): 4073
    SS io: 32584.00 vs. table scan io: 2.00
    Skip Scan rejected
  Access Path: index (FullScan)
    Index: IND_OBJ
    resc_io: 380547.00  resc_cpu: 10551751028
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 382007.38  Resp: 382007.38  Degree: 1
  Best:: AccessPath: TableScan
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]:  MACLEAN[MACLEAN]#0
***********************
Best so far: Table#: 0  cost: 2.0008  card: 0.0000  bytes: 13
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan:  Best join order: 1
  Cost: 2.0008  Degree: 1  Card: 1.0000  Bytes: 13
  Resc: 2.0008  Resc_io: 2.0000  Resc_cpu: 7121
  Resp: 2.0008  Resp_io: 2.0000  Resc_cpu: 7121
Current SQL statement for this session:
explain plan for select /*+ FIRST_ROWS PARALLEL("MACLEAN", 1) */
 1
  from "SYS"."MACLEAN" PARTITION("P4")
 where (((("OBJECT_ID" < 100010))))
   and rownum < 2
============
Plan Table
============
-------------------------------------------+-----------------------------------+---------------+
| Id  | Operation                | Name    | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
-------------------------------------------+-----------------------------------+---------------+
| 0   | SELECT STATEMENT         |         |       |       |     2 |           |       |       |
| 1   |  COUNT STOPKEY           |         |       |       |       |           |       |       |
| 2   |   PARTITION RANGE SINGLE |         |     1 |    13 |     2 |  00:00:01 | 4     | 4     |
| 3   |    TABLE ACCESS FULL     | MACLEAN |     1 |    13 |     2 |  00:00:01 | 4     | 4     |
-------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
1 - filter(ROWNUM<2)
3 - filter("OBJECT_ID"<100010)
Content of other_xml column
===========================
  db_version     : 10.2.0.1
  parse_schema   : SYS
  plan_hash      : 4280157877
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "MACLEAN"@"SEL$1")
    END_OUTLINE_DATA
  */
Dumping Hints
=============
  atom_hint=(@=0x988453c0 err=20 resol=1 used=0 token=453 org=1 lvl=2 txt=FIRST_ROWS )
  atom_hint=(@=0x988451f8 err=0 resol=1 used=1 token=177 org=1 lvl=3 txt=NO_PARALLEL ("MACLEAN") )
********** WARNING: SOME HINTS HAVE ERRORS *********

 

实际 “_optimizer_ignore_hints”参数是起到的作用是使SQL 隐式地回归到默认的optimizer_mode=ALL_ROWS上来,  我们终于战胜了嵌入在SQL语句中的”First Rows” Hint 。

 

总结

在针对10g 多个版本上的Split Partition 可能因 其DDL所附带的递归SQL 使用了 固定的 而又不恰当的”First rows Hint” 提示而造成语句执行缓慢的问题, 我们可以通过 以下3个方案解决:

A.  通过每次执行Split之前都收集指定分区的统计,并设置”_optimizer_ignore_hints” =true 来规避 分区统计信息失效和 不恰当的”First Rows hint” 所可能带来的问题, 方法如下:

exec dbms_stats.gather_table_stats('&OWNER','&TABNAME',partname=>'&PARNAME');
alter session set "_optimizer_ignore_hints"=true;

推荐使用方案A

补充:

有同学反映:

”隐藏参数_optimizer_ignore_hints在分区表的split操作中并未启到作用。

从我的测试结果来看,虽然导致split操作慢的根源是FIRST_ROWS优化器模式下的分区表select语句:

select /*+ FIRST_ROWS PARALLEL(“MACLEAN”, 1) */ 1
from
 MACLEAN PARTITION (“P4”)  where ( (  (  (
  “OBJECT_ID” < ‘1000010’ )  )  ) ) and rownum < 2;

不过以上的sql语句在_optimizer_ignore_hints参数调整后仍使用FIRST_ROWS的优化器模式,即没有生效。

我觉得这可能与oracle在执行自己内部命令时,会忽略该参数的设置。(直接执行select的sql语句确实会忽略hint)”

 

实际通过10053 事件追踪该SPLIT PARTITION DDL语句所生产的递归SQL语句,发现带有”FIRST_ROWS”提示的SELECT语句甚至没有解析的过程, 很有可能是该递归SQL语句直接使用了内部硬编码的存储大纲OUTLINES所导致的。

 

换句话说之前_optimizer_ignore_hints的隐式参数针对我们手动执行的带有FIRST_ROWS HINT的SELECT语句是有效的,而对于DDL所附带的递归SQL无效。

 

注:实际在对嵌入了HINT的非recursive SQL语句做调优时,若我们无法修改该SQL的HINT,则还是可以利用到”_optimizer_ignore_hints”的。

 

想了一下可以通过设置较旧的优化器特性了解决该问题(alter session set optimizer_features_enable=’8.0.0′;),该optimizer_features_enable参数同样可以在session级别设置, 如:

 

TEST A:
SQL> set timing on;
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> oradebug tracefile_name;
/s01/admin/G10R21/udump/g10r21_ora_13646.trc
SQL> alter table maclean split partition p4 at (10000081) into (partition p14, partition p4);
Table altered.
Elapsed: 00:00:42.50
TEST B:
SQL> set timing on;
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/s01/admin/G10R21/udump/g10r21_ora_13656.trc
SQL> alter session set optimizer_features_enable='8.0.0';
Session altered.
Elapsed: 00:00:00.01
SQL> alter table maclean split partition p4 at (10000091) into (partition p15, partition p4);
Table altered.
Elapsed: 00:00:00.05
PARSING IN CURSOR #2 len=152 dep=1 uid=0 oct=3 lid=0 tim=1291531645417871 hv=2124209225 ad='a74b41f0'
select /*+ FIRST_ROWS PARALLEL("MACLEAN", 1) */ 1 from "SYS"."MACLEAN" PARTITION ("P4") 
where ( (  (  ( "OBJECT_ID" < 10000091 )  )  ) ) and rownum < 2
END OF STMT
PARSE #2:c=1000,e=684,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=2,tim=1291531645417864
BINDS #2:
EXEC #2:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=2,tim=1291531645417984
FETCH #2:c=0,e=92,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=2,tim=1291531645418094
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=3 pr=0 pw=0 time=122 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=0 op='PARTITION RANGE SINGLE PARTITION: 13 13 (cr=3 pr=0 pw=0 time=95 us)'
STAT #2 id=3 cnt=0 pid=2 pos=1 obj=52809 op='TABLE ACCESS FULL MACLEAN PARTITION: 13 13 (cr=3 pr=0 pw=0 time=59 us)'
=====================
PARSING IN CURSOR #2 len=177 dep=1 uid=0 oct=3 lid=0 tim=1291531645418799 hv=339345368 ad='a74cedd8'
select /*+ FIRST_ROWS PARALLEL("MACLEAN", 1) */ 1 from "SYS"."MACLEAN" PARTITION ("P4")
 where ( (  (  ( "OBJECT_ID" >= 10000091 OR "OBJECT_ID" IS NULL  )  )  ) ) and
rownum < 2
END OF STMT
PARSE #2:c=1000,e=589,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=2,tim=1291531645418792
BINDS #2:
EXEC #2:c=0,e=67,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=2,tim=1291531645418908
FETCH #2:c=0,e=66,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=2,tim=1291531645418992
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=3 pr=0 pw=0 time=92 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=0 op='PARTITION RANGE SINGLE PARTITION: 13 13 (cr=3 pr=0 pw=0 time=64 us)'
STAT #2 id=3 cnt=0 pid=2 pos=1 obj=52809 op='TABLE ACCESS FULL MACLEAN PARTITION: 13 13 (cr=3 pr=0 pw=0 time=36 us)'
WAIT #1: nam='control file sequential read' ela= 31 file#=0 block#=1 blocks=1 obj#=-1 tim=1291531645419383
WAIT #1: nam='control file sequential read' ela= 12 file#=1 block#=1 blocks=1 obj#=-1 tim=1291531645419432
WAIT #1: nam='control file sequential read' ela= 23 file#=0 block#=15 blocks=1 obj#=-1 tim=1291531645419494
WAIT #1: nam='control file sequential read' ela= 10 file#=0 block#=17 blocks=1 obj#=-1 tim=1291531645419556

 

B. 如果确实遇到了该问题 ,也可以将Index FULL SCAN 所使用的全局索引drop 掉来达到强制使用FULL single table partition SCAN的目的, 实际使用中不推荐

C. 避免使用Global Partitioned index 或 Non-partitioned Index , 而采用Local partitioned index , 这似乎更难做到

D. 也可以通过将原Maxvalue的分区drop掉之后(前提是该分区真的是空的),再添加新分区的做法来绕过该问题

解决ORA-14098分区交换索引不匹配错误

上周在客户一套BRM系统上执行分区交换Exchange Partition操作的时候出现了ORA-14098错误,该错误是由于分区表上的LOCAL分区索引与非分区表上的索引不匹配造成的,我们来看一下这个错误:

[oracle@rh2 ~]$ oerr ora 14098
14098, 00000, "index mismatch for tables in ALTER TABLE EXCHANGE PARTITION"
// *Cause:  The two tables specified in the EXCHANGE have indexes which are
//          not equivalent
// *Action: Ensure that the indexes for the two tables have indexes which
//          follow this rule
//          For every non partitioned index for the non partitioned table,
//          there has to be an identical LOCAL index on the partitioned
//          table and vice versa. By identical, the column position, type
//          and size have to be the same.
SQL> ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with
table SALES_TMP INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES;
ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with
table SALES_TMP INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

如果表上有很多的索引,以至于你无法确定到底是哪个索引引发了ORA-14098错误,那么我们可以通过trace的方式来协助定位到具体的索引:

SQL>  select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com
SQL> alter session set max_dump_file_size = unlimited;
Session altered.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> alter session set events '14098 trace name errorstack forever, level 4';
Session altered.
##SQL> alter system flush buffer_cache;
System altered.
Rerun Exchange Partition DDL 
SQL> ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with table SALES_TMP
INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES;
ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with table SALES_TMP
INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
11g中直接查询v$diag_info就可以得到trace的路径,10g执行gettracename.sql
SELECT    d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') d;
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/s01/admin/G10R2/udump/g10r2_ora_17749.trc
==========================10046/errorstack trace contents========================
PARSING IN CURSOR #1 len=127 dep=0 uid=64 oct=15 lid=64 tim=1277655207436065 hv=1207961095 ad='9098f018'
ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with table SALES_TMP
INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES
END OF STMT
PARSE #1:c=0,e=1145,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1277655207436059
BINDS #1:
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=98001 op='INDEX FULL SCAN SALES_UNID_TMP (cr=1 pr=0 pw=0 time=39 us)'
*** 2011-06-17 21:55:32.417
ksedmp: internal or fatal error
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
Current SQL statement for this session:
ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with table SALES_TMP
INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES
我们可以在trace中看到在出现ORA-14098错误前,正在对索引SALES_UNID_TMP的Fast Full Scan

除了通过10046/errorstack的trace信息诊断外,更多的问题可以直接从DDL语句中发现,在以上示例中非分区表的DDL语句:

-- Create table
create table SALES_TMP
(
UNI_ID        NUMBER NOT NULL,
PROD_ID       NUMBER not null,
CUST_ID       NUMBER not null,
TIME_ID       DATE not null,
CHANNEL_ID    NUMBER not null,
PROMO_ID      NUMBER not null,
QUANTITY_SOLD NUMBER(10,2) not null,
AMOUNT_SOLD   NUMBER(10,2) not null
);
create index SALES_CHANNEL_TMP       ON SALES_TMP (CHANNEL_ID) ;
create index SALES_CUST_TMP          ON SALES_TMP (CUST_ID)    ;
create index SALES_UNID_TMP          ON SALES_TMP  (UNI_ID,TIME_ID);  --注意细节该索引是非UNIQUE的
create index SALES_PROD_TMP          ON SALES_TMP (PROD_ID)    ;
create index SALES_PROMO_TMP         ON SALES_TMP (PROMO_ID)   ;
create index SALES_TIME_TMP          ON SALES_TMP (TIME_ID)    ;

下为分区表的DDL语句:

-- Create table
create table SALES
(
UNI_ID        NUMBER NOT NULL,
PROD_ID       NUMBER not null,
CUST_ID       NUMBER not null,
TIME_ID       DATE not null,
CHANNEL_ID    NUMBER not null,
PROMO_ID      NUMBER not null,
QUANTITY_SOLD NUMBER(10,2) not null,
AMOUNT_SOLD   NUMBER(10,2) not null
)
partition by range (TIME_ID)
...............
create index SALES_CHANNEL       ON SALES (CHANNEL_ID) LOCAL;
create index SALES_CUST          ON SALES (CUST_ID)    LOCAL;
create UNIQUE index SALES_UNID   ON SALES (UNI_ID,TIME_ID) LOCAL;      -- 对应的索引是UNIQUE的
create index SALES_PROD          ON SALES (PROD_ID)    LOCAL;
create index SALES_PROMO         ON SALES (PROMO_ID)   LOCAL;
create index SALES_TIME          ON SALES (TIME_ID)    LOCAL;

解决ORA-14098错误的要点是要找出引发错误的原因。当我们交换分区的时候,我们要确保所有交换表上的索引和分区表上的本地索引匹配。这意味着如果在分区表上有N个LOCAL INDEXES,那么在交换表上就应当有N个等价的索引。这里的等价要求存在映射关系的2个索引,在列的位置、类型、大小及UNIQUE/NON-UNIQUE都要一致。

可以利用如下SQL语句来找出分区表和交换表上索引的差异:

set linesize 160 pagesize 1400
col TABLE_NAME for a30
col INDEX_NAME for a30
col COLUMN_NAME for a30
col COLUMN_POSITION for 99
col COLUMN_LENGTH for 99
col CHAR_LENGTH for 99
col DESCEND for a4
Select TABLE_NAME,INDEX_NAME, COLUMN_NAME,COLUMN_POSITION, COLUMN_LENGTH, CHAR_LENGTH, DESCEND
FROM SYS.DBA_IND_COLUMNS DICN
WHERE INDEX_OWNER = '&own'
and DICN.TABLE_NAME in ('&TABNAME1','&TABNAME2')
ORDER BY  INDEX_NAME, COLUMN_POSITION
/
select TABLE_NAME, INDEX_NAME, INDEX_TYPE, UNIQUENESS, PARTITIONED
from dba_indexes
where owner='&OWNER'
and TABLE_NAME in ('&TABNAME1', '&TABNAME2')
order by index_name
/

也可以使用Toad的Single Schema Object Compare功能来对比检验索引:
single_object_compare

对于存在主键的分区表,可以在主键上以DISABLE VALIDATE方式创建unique constraint约束,以代替全局的主键索引。若交换表(Exchange Table)上存在主键索引的话,那么建议在交换前暂时将该索引drop掉,待交换完成后再重建。

如果实在无法解决该ORA-14098错误,那么可以尝试使用EXCLUDING INDEXES子句以跳过索引维护,而在交换完成后重建相关失效索引。

owner = '&OWNER1'

从视图查询表分区的相关信息

分区表的分区键和分区类型都可以通过获取创建该分区表的DDL定义语句来了解,那么可不可以直接查询字典视图来获取这些信息呢?常用的dba_tab_partitions视图并没有包含我们想要的分区属性信息;这里我们可以用到dba_part_key_columns(describes the partitioning key columns for all partitioned objects in the database. Its columns are the same as those in ALL_PART_KEY_COLUMNS)和dba_part_tables(displays the object-level partitioning information for all partitioned tables in the database. Its columns are the same as those in ALL_PART_TABLES)这2个视图:
[Read more…]

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

如何设计分区索引

通过下图定义的规则,可确定分区索引类型:
design-partition-index

Hash Global分区索引介绍
HASH-Partitioned Global索引是Oracle 10g开始提供的新特性。而在以前的版本中
,Oracle只支持Range-Partitioned Global索引。HASH-Partitioned Global索引的好处如下:
比Range-Partitioned Global索引易于实施。HASH-Partitioned Global索引是根据
索引字段值,通过Oracle内部的HASH算法自动均匀散列到定义的分区中。而
Range-Partitioned Global索引需要根据索引字段值的范围进行分区,因此实施和
维护的难度都大。
HASH-Partitioned Global索引适合于在并发量、吞吐量很大的交易系统(OLTP)
中,对某些字段的访问冲突。尤其是sequence字段值。
HASH-Partitioned Global索引适合于大批量的数据查询。HASH-Partitioned Global索引不仅可以提供分区之间的并行查询,
而且在分区内也可进行并行查询的处理。
建立分区索引必须指定表空间,并且指定的表空间要与数据表空间分开,
这样便于管理,同时尽可能分开索引和数据的IO访问,提高效率

沪ICP备14014813号

沪公网安备 31010802001379号