Tag Experiments

  • 首页   /  
  • 标签： "Experiments"

Oracle索引组织表学习系列二

留下评论 , ,

4 索引组织表(IOT Index-Organized Table)适用什么场景

       在上一篇文章里，提到了IOT表的基本概念和创建的一些注意事项，接下来讨论一下关于IOT表和Heap使用的一个测试对比，用以说明IOT表适用于什么场景。

1 构建一个主表emp表：

[oracle@oracle11g arch_new]$ sqlplus 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 15 10:15:19 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning and Automatic Storage Management options

SQL> conn hr/hr
Connected.
SQL> create table emp
  2  as
  3  select object_id empno,
  4  object_name ename,
  5  created hiredate,
  6  owner job
  7  from all_objects
  8  ;

Table created.

SQL> select count(*) from emp;

  COUNT(*)
----------
     55636

SQL> alter table emp add constraint emp_pk primary key(empno);

Table altered.

SQL> exec dbms_stats.gather_table_stats(user,'EMP',cascade=>true);

PL/SQL procedure successfully completed.

SQL>

 

2  构建一张子表，子表分别通过Heap和IOT两种方式来实现：

构建Heap类型子表：

SQL> create table heap_addresses
  2  ( empno references emp(empno) on delete cascade,
  3  addr_type varchar2(10),
  4  street varchar2(20),
  5  city varchar2(20),
  6  state varchar2(2),
  7  zip number,
  8  primary key (empno,addr_type)
  9  );

Table created.

SQL>

构建IOT类型子表：

SQL> create table iot_addresses
  2  ( empno references emp(empno) on delete cascade,
  3  addr_type varchar2(10),
  4  street varchar2(20),
  5  city varchar2(20),
  6  state varchar2(2),
  7  zip number,
  8  primary key (empno,addr_type)
  9  )
 10  ORGANIZATION INDEX
 11  ;

Table created.

SQL>

3  分别向两种子表里构造数据，插入WORK，HOME，ORIGINAL，SCHOOL地址：

Heap类型子表插入：

SQL> insert into heap_addresses
  2  select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123
  3  from emp;

55636 rows created.

SQL> insert into heap_addresses
  2  select empno, 'HOME', '123 main street', 'Washington', 'DC', 20123
  3  from emp;

55636 rows created.

SQL> insert into heap_addresses
  2  select empno, 'ORIGINAL', '123 main street', 'Washington', 'DC', 20123
  3  from emp;

55636 rows created.

SQL> insert into heap_addresses
  2  select empno, 'SCHOOL', '123 main street', 'Washington', 'DC', 20123
  3  from emp;

55636 rows created.

SQL> select count(*) from heap_addresses;

  COUNT(*)
----------
    222544

SQL>

IOT类型子表插入：

SQL> insert into iot_addresses
  2  select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123
  3  from emp;

55636 rows created.

SQL> insert into iot_addresses
  2  select empno, 'HOME', '123 main street', 'Washington', 'DC', 20123
  3  from emp;

55636 rows created.

SQL> insert into iot_addresses
  2  select empno, 'ORIGINAL', '123 main street', 'Washington', 'DC', 20123
  3  from emp;

55636 rows created.

SQL> insert into iot_addresses
  2  select empno, 'SCHOOL', '123 main street', 'Washington', 'DC', 20123
  3  from emp;

55636 rows created.

SQL> select count(*) from iot_addresses;

  COUNT(*)
----------
    222544

SQL>

4 分别收集两种子表的统计信息：

SQL> exec dbms_stats.gather_table_stats(user,'HEAP_ADDRESSES');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'IOT_ADDRESSES');

PL/SQL procedure successfully completed.

SQL>

5 对比使用不同类型的子表，SQL的执行计划：

SQL> set line 200
SQL> set autot trace
SQL> select *
  2  from emp, heap_addresses
  3  where emp.empno = heap_addresses.empno
  4  and emp.empno = 100;


Execution Plan
----------------------------------------------------------
Plan hash value: 541875893

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     4 |   368 |     8   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                |     4 |   368 |     8   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    45 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | EMP_PK         |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| HEAP_ADDRESSES |     4 |   188 |     6   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | SYS_C00138141  |     4 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("EMP"."EMPNO"=100)
   5 - access("HEAP_ADDRESSES"."EMPNO"=100)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1351  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL> 
SQL> select *
  2  from emp, iot_addresses
  3  WHERE emp.empno = iot_addresses.empno
  4  and emp.empno = 100;


Execution Plan
----------------------------------------------------------
Plan hash value: 1475200359

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |     4 |   368 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                    |     4 |   368 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP                |     1 |    45 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | EMP_PK             |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN           | SYS_IOT_TOP_184800 |     4 |   188 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("EMP"."EMPNO"=100)
   4 - access("IOT_ADDRESSES"."EMPNO"=100)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1351  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL>

6 小结：通过分析上述2条“几乎”完全相同的SQL语句的执行计划，我们可以看到：

        对于第1个SQL(子表通过Heap来实现)，首先通过EMP_PK这个主键来访问主表EMP，得到主表的行记录，然后通过得到的主键EMPNO字段来访问子表HEAP_ADDRESS,通过子表HEAP_ADDRESS的索引SYS_C00138141来获取子表记录。整个SQL耗费了11次内存读，COST为8。

        而对于第2个SQL(子表通过IOT来实现)，首先通过EMP_PK这个主键来访问主表EMP，得到主表的行记录，然后直接通过EMPNO字段来访问子表IOT_ADDRESS，而EMPNO字段同时也是子表IOT_ADDRESS的主键字段，这样通过IOT表的主键字段来访问数据就非常快了。整个SQL耗费8次内存读，COST为4，是第1条SQL的1/2。

IOT表适用的场景：

① 应用中有完全由主键字段构成的表；

②  应用中有只会通过主键来访问数据的表；

③  希望数据以某种特定顺序存储的表；

IOT表不适用于使用非主键字段来查询数据。

Oracle索引组织表学习系列一

留下评论 ,
1 索引组织表(IOT Index-Organized Table)概述      
 

       在Oracle数据库中，有一类表被称之为索引组织表，即IOT(Index-Organized Table)。顾名思义，所谓的索引组织表，表面上看是一种表，实质上其数据是以索引的形式来存放的，也就是说IOT表不会占用表段，其占用的是索引段。

       相比较传统的堆表( heap-organized table，即常见的普通表)而言，IOT表的数据是以已经按主键字段排好序后存放在B-tree索引中的，而堆表的数据则是无序的存放在表段的数据块中。此外，在IOT的索引叶子节点块中，既存放主键字段数据，也存放非主键字段的值。

        一张IOT表同Heap表的对比示意图：

2 索引组织表(IOT Index-Organized Table)特征

 

       通过IOT表的主键字段来访问数据可以快速完成，因为IOT表的数据全部存放在B-Tree索引上，只需定位到索引上的数据即可，而无需再去像访问heap表那样进一步通过索引去定位表段上的数据；对IOT表执行DML操作，只会影响到B-Tree索引；

      通过IOT表的主键字段快速范围访问数据很快，因为记录已经是事先按主键排好序的；

      IOT表可以有效的降低存储开销，因为主键字段的数据只是存放在B-Tree索引上，并没有像heap那样，主键字段数据既存放在表段上，也存放在索引上；

      IOT表除了像Heap表那样可以支持约束、触发器、LOB字段、对象字段、分区、并行操作、在线重定义、复制操作等，还支持主键字段压缩、提供溢出存储段(Overflow storage area )、二次索引(Secondary indexes, including bitmap indexes)

 

3 创建索引组织表(IOT Index-Organized Table)

 

   创建IOT表时，必须包含下列2个从句：

        1 ORGANIZATION INDEX，用来标识该表是IOT表；

        2 在建表的同时要指定主键约束，可以是单字段主键，也可以是复合主键约束。

    创建IOT表时，也可以同时指定下列3个从句：

        1 OVERFLOW从句，用于标识非主键字段存放在独立的溢出存储段数据区；

        2 PCTTHRESHOLD value，如果指定了溢出存储段的话，该值用于限定可以存放在索引数据块中的最大数据的百分比，即如果IOT表中的行记录超过该值的话，剩余的字段就存放在溢出存储段数据区。也就是说，IOT表中的一条记录有可能被拆分成两部分：头数据区(Head Piece)和尾数据区(Tail Piece)。将主键字段和不超过PCTTHRESHOLD限定的其它非主键字段存放在头数据区，而将其它的非主键字段存放在尾数据区。因此，此时的IOT表的索引记录存放的数据就成了主键字段+满足PCTTHRESHOLD限定的其它非主键字段+指针，指针指向剩余非主键字段存放的地址；PCTTHRESHOLD的取值范围是1-50，默认值是50；

        3 INCLUDING从句，用于显示声明哪些非主键字段可以和主键字段一起存放在索引数据块中。这样，剩下的非主键字段就会存放到独立的溢出存储段数据区。

     创建IOT表的一个示例：

OS版本：

[oracle@oracle11g ~]$ uname -rm
2.6.18-194.el5 x86_64
[oracle@oracle11g ~]$

Oracle版本：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>

建表：

SQL> conn hr/hr
Connected.
SQL> CREATE TABLE admin_docindex(
  2  token char(20), 
  3  doc_id NUMBER,
  4  token_frequency NUMBER,
  5  token_offsets VARCHAR2(2000),
  6  CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id))
  7  ORGANIZATION INDEX 
  8  TABLESPACE admin_tbs
  9  PCTTHRESHOLD 20
 10  OVERFLOW TABLESPACE admin_tbs2;

Table created.

SQL> insert into ADMIN_DOCINDEX values('oracleonlinux.cn',1,1,'test iot table');

1 row created.

SQL> commit;

Commit complete.

SQL>

查看该表的信息，可以看到该表不属于任何表空间，因为没有数据段：

SQL> select table_name,tablespace_name,iot_name,iot_type from user_tables where table_name='ADMIN_DOCINDEX';

TABLE_NAME                     TABLESPACE_NAME                IOT_NAME                       IOT_TYPE
------------------------------ ------------------------------ ------------------------------ ------------
ADMIN_DOCINDEX                                                                               IOT

SQL> select segment_name,segment_type,tablespace_name from user_segments where SEGMENT_NAME='ADMIN_DOCINDEX';

no rows selected

SQL>

查看索引的信息，我们能看到索引反而占有表空间：

SQL> select index_name,index_type,table_name,tablespace_name,table_type ,PCT_THRESHOLD from user_indexes where table_name='ADMIN_DOCINDEX';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     TABLESPACE_NAME                TABLE_TYPE  PCT_THRESHOLD
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- -------------
PK_ADMIN_DOCINDEX              IOT - TOP                   ADMIN_DOCINDEX                 ADMIN_TBS                      TABLE                  20

SQL> select segment_name,segment_type,tablespace_name from user_segments where SEGMENT_NAME='PK_ADMIN_DOCINDEX';

SEGMENT_NAME                                                                      SEGMENT_TYPE       TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------ ------------------------------
PK_ADMIN_DOCINDEX                                                                 INDEX              ADMIN_TBS

SQL>

       最后，关于IOT表的学习下篇继续。

Oracle splitting partitions简单小结

留下评论 , ,

            本文简单记录在Oracle 10g数据库上对范围分区表的Splitting Partitions测试过程和结论，并不涉及到Oracle数据库中分区技术的详细描述。

1 测试环境及平台：

OS：

[root@localhost ~]# uname -rm
2.6.18-164.el5 x86_64
[root@localhost ~]#

Oracle：

SQL> select * from v$version;

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

SQL>

2 创建范围分区表：

SQL> show user;
USER is "SYS"
SQL> create table part_range(id number,name varchar2(30))
  2  partition by range(id)
  3  (partition partmax values less than (maxvalue))
  4  tablespace users;

Table created.

SQL>

3 插入测试数据：

SQL> insert into part_range select object_id,object_name from dba_objects
  2  where object_id<2000;

1953 rows created.

SQL>

4 在分区表part_range上创建2种分区索引：

本地分区索引【Locally partitioned index】：

SQL> create index part_range_id_idx on part_range(id) local;

Index created.

SQL>

全局分区索引【Globally partitioned index】：

SQL> create index part_range_name_idx on part_range(name) tablespace users;

Index created.

SQL>

5 查看分区表信息：

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions
  2  where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        USERS

SQL>

6 查看索引信息：

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions
  2  where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              PARTMAX                        USERS                          USABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes
  2  where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          VALID    NO

SQL>

7 对分区表part_range执行Splitting partitions【分区分裂】操作：

SQL> alter table part_range split partition partmax at (2000) 
  2  into (partition p1,partition partmax);

Table altered.

SQL>

注意，这里的分区分裂操作临界值是2000，即id<2000的记录将全部重组到p1分区，而partmax分区将为空，即0记录。

SQL> select count(*) from part_range;

  COUNT(*)
----------
      1953

SQL> select count(*) from part_range partition(p1);

  COUNT(*)
----------
      1953

SQL> select count(*) from part_range partition(partmax);

  COUNT(*)
----------
         0

SQL>

8 再次分别查看分区表、索引信息：

分区表：

SQL>  select table_name,partition_name,tablespace_name from user_tab_partitions
  2   where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        USERS
PART_RANGE                     P1                             USERS

SQL>

索引信息：

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions
  2  where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             USERS                          USABLE
PART_RANGE_ID_IDX              PARTMAX                        USERS                          USABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes
  2  where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          VALID    NO

SQL>

小结：对于分区分裂之后，如果包含有空分区的话，那么对于本地分区索引和全局分区索引都是可用的。这种分区分裂的方式通常也叫做快速分裂【Fast Splitting】，索引不需要rebuild。

9 如果在上述步骤7中，执行的分区分裂操作如下：

SQL> alter table part_range split partition partmax at (1000)
  2  into (partition p1,partition partmax);

Table altered.

SQL>

即分区分裂操作临界值是1000，id<1000的记录将重组到p1分区，id>=1000的记录将重组到partmax分区。也就是此时，分裂出来的p1和partmax这两个分区均不为空。

SQL> select count(*) from part_range;

  COUNT(*)
----------
      1953

SQL> select count(*) from part_range partition(p1);

  COUNT(*)
----------
       953

SQL> select count(*) from part_range partition(partmax);

  COUNT(*)
----------
      1000

SQL>

那么，查看到的分区表、索引信息如下：

分区表：

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions
  2  where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        USERS
PART_RANGE                     P1                             USERS

SQL>

索引信息：

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions
  2  where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             USERS                          UNUSABLE
PART_RANGE_ID_IDX              PARTMAX                        USERS                          UNUSABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes
  2  where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          UNUSABLE NO

SQL>

小结：对于分区分裂之后，如果不包含空分区的话，那么对于本地分区索引和全局分区索引都将不可用，索引的状态都变为UNUSABLE。均需要重建：

SQL> alter index PART_RANGE_ID_IDX rebuild partition p1;

Index altered.

SQL> alter index PART_RANGE_ID_IDX rebuild partition partmax;

Index altered.

SQL> alter index PART_RANGE_NAME_IDX rebuild;

Index altered.

SQL>

重建之后，本地分区索引、全局分区索引信息，已由UNUSABLE变为USABLE：

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions
  2  where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             USERS                          USABLE
PART_RANGE_ID_IDX              PARTMAX                        USERS                          USABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes
  2  where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          VALID    NO

SQL>

当然，如果在分裂分区的同时带上UPDATE INDEXES的话，可以在分裂分区的同时重建索引【包含本地分区索引和全局分区索引，状态均为USABLE、VALID】：

SQL> alter table part_range split partition partmax at (1000) into (partition p1,partition partmax) update indexes;

Table altered.

SQL>

分裂分区之后，表信息：

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions
  2  where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        USERS
PART_RANGE                     P1                             USERS

SQL>

索引信息：

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             USERS                          USABLE
PART_RANGE_ID_IDX              PARTMAX                        USERS                          USABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          VALID    NO

SQL>

而如果在分裂分区的同时带上UPDATE GLOBAL INDEXES的话，可以在分裂分区的同时重建全局分区索引【不包含本地分区索引，只有全局分区索引状态为VALID】，而本地分区索引需要重建：

SQL> alter table part_range split partition partmax at (1000) into (partition p1,partition partmax) update global indexes;

Table altered.

SQL>

分裂分区之后，表信息：

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions
  2  where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        USERS
PART_RANGE                     P1                             USERS

SQL>

索引信息：

SQL> select index_name,partition_name,tablespace_name,status from user_ind_partitions where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             USERS                          UNUSABLE
PART_RANGE_ID_IDX              PARTMAX                        USERS                          UNUSABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          VALID    NO

SQL>

需要注意的是，在分裂分区的同时重建索引，将会消耗更多时间来完成分裂工作，以及消耗更多的系统资源。如果系统资源较为充足的话，可以考虑带上UPDATE INDEXES选项。

10 最后，再看看另外一种比较特殊的情况。在分裂分区的时候，如果将新分区指向新的表空间【由USERS到EXAMPLE表空间】的话，并且分裂之后，包含空分区的情况。即，分裂的语句如下：

SQL> alter table part_range split partition partmax at (2000)
  2  into (partition p1 tablespace example,partition partmax tablespace example);

Table altered.

SQL> select count(*) from part_range;

  COUNT(*)
----------
      1953

SQL> select count(*) from part_range partition(p1);

  COUNT(*)
----------
      1953

SQL> select count(*) from part_range partition(partmax);

  COUNT(*)
----------
         0

SQL>

那么可以看到分区表：

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='PART_RANGE';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_RANGE                     PARTMAX                        EXAMPLE
PART_RANGE                     P1                             EXAMPLE

SQL>

索引分区信息：

SQL>  select index_name,partition_name,tablespace_name,status from user_ind_partitions where index_name='PART_RANGE_ID_IDX';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PART_RANGE_ID_IDX              P1                             EXAMPLE                        UNUSABLE
PART_RANGE_ID_IDX              PARTMAX                        EXAMPLE                        USABLE

SQL> select index_name,table_name,tablespace_name,status,partitioned from user_indexes where table_name='PART_RANGE';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS   PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
PART_RANGE_ID_IDX              PART_RANGE                                                    N/A      YES
PART_RANGE_NAME_IDX            PART_RANGE                     USERS                          UNUSABLE NO

SQL>

小结：在分裂分区的时候，如果将新分区指向新的表空间的话，并且分裂之后，即使包含空分区的情况下，只有新空分区的本地索引不需要重建，而含有数据的新分区的本地分区索引以及全局分区索引均需重建。这有别于快速分裂，或者说是快速分裂的一种特殊情况。

本文部分内容参考范大师

Oracle 11g Automatic Maintenance Tasks

留下评论 ,

            从Oracle 11g开始，Oracle的自动化维护任务更智能化了，默认情况下，在安装数据库的过程中，如果启用自动化维护任务的话，数据库会在工作日的每晚22:00到第二天的凌晨2:00，周末的凌晨6:00到第二天的凌晨2:00,自动对数据库进行诸如优化器的统计信息收集、自动SQL的优化。且在自动化维护的过程中，数据库会使用较少的CPU资源，以防止自动化维护任务使用到过多的资源而影响到用户的正常使用。

           其一，在使用DBCA建库的时候，我们可以看到这一自动维护任务的选项：

           其二，如果启用了自动化维护任务的话，也可以从数据库的alert日志里看到下述信息：

Fri Mar 23 22:00:00 2012
Setting Resource Manager plan SCHEDULER[0x3007]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Fri Mar 23 22:00:00 2012
Starting background process VKRM
Fri Mar 23 22:00:00 2012
VKRM started with pid=24, OS id=10743
Fri Mar 23 22:00:33 2012
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Fri Mar 23 22:01:32 2012
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Fri Mar 23 22:05:56 2012
Thread 1 cannot allocate new log, sequence 8
Private strand flush not complete
  Current log# 1 seq# 7 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669
  Current log# 1 seq# 7 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673
Thread 1 advanced to log sequence 8 (LGWR switch)
  Current log# 2 seq# 8 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 8 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Sat Mar 24 02:00:00 2012
Clearing Resource Manager plan via parameter
Sat Mar 24 06:00:00 2012
Setting Resource Manager plan SCHEDULER[0x3008]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sat Mar 24 06:00:00 2012
Starting background process VKRM
Sat Mar 24 06:00:01 2012
VKRM started with pid=29, OS id=15277
Sat Mar 24 06:00:21 2012
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Sat Mar 24 06:00:49 2012
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Sat Mar 24 06:02:05 2012
Thread 1 cannot allocate new log, sequence 9
Private strand flush not complete
  Current log# 2 seq# 8 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 8 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Thread 1 advanced to log sequence 9 (LGWR switch)
  Current log# 3 seq# 9 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677
  Current log# 3 seq# 9 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677
Sat Mar 24 08:56:14 2012
Thread 1 advanced to log sequence 10 (LGWR switch)
  Current log# 1 seq# 10 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669
  Current log# 1 seq# 10 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673
Sat Mar 24 14:06:51 2012
Thread 1 cannot allocate new log, sequence 11
Private strand flush not complete
  Current log# 1 seq# 10 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669
  Current log# 1 seq# 10 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673
Thread 1 advanced to log sequence 11 (LGWR switch)
  Current log# 2 seq# 11 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 11 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Sat Mar 24 18:11:25 2012
Thread 1 advanced to log sequence 12 (LGWR switch)
  Current log# 3 seq# 12 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677
  Current log# 3 seq# 12 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677
Sun Mar 25 02:00:00 2012
Clearing Resource Manager plan via parameter
Sun Mar 25 02:00:30 2012
Thread 1 advanced to log sequence 13 (LGWR switch)
  Current log# 1 seq# 13 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669
  Current log# 1 seq# 13 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673
Sun Mar 25 06:00:00 2012
Setting Resource Manager plan SCHEDULER[0x3009]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sun Mar 25 06:00:00 2012
Starting background process VKRM
Sun Mar 25 06:00:00 2012
VKRM started with pid=29, OS id=21059
Sun Mar 25 06:00:15 2012
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Sun Mar 25 06:00:33 2012
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Sun Mar 25 08:56:21 2012
Thread 1 advanced to log sequence 14 (LGWR switch)
  Current log# 2 seq# 14 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 14 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Sun Mar 25 14:08:09 2012
Thread 1 cannot allocate new log, sequence 15
Private strand flush not complete
  Current log# 2 seq# 14 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 14 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Thread 1 advanced to log sequence 15 (LGWR switch)
  Current log# 3 seq# 15 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677
  Current log# 3 seq# 15 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677
Sun Mar 25 22:08:35 2012 
Thread 1 cannot allocate new log, sequence 16
Private strand flush not complete
  Current log# 3 seq# 15 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677
  Current log# 3 seq# 15 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677
Thread 1 advanced to log sequence 16 (LGWR switch)
  Current log# 1 seq# 16 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669
  Current log# 1 seq# 16 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673
Mon Mar 26 02:00:00 2012 
Clearing Resource Manager plan via parameter
Mon Mar 26 10:14:25 2012 
Thread 1 advanced to log sequence 17 (LGWR switch)
  Current log# 2 seq# 17 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 17 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Mon Mar 26 22:00:00 2012 
Setting Resource Manager plan SCHEDULER[0x3003]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Mon Mar 26 22:00:00 2012 
Starting background process VKRM 
Mon Mar 26 22:00:00 2012 
VKRM started with pid=24, OS id=9542 
Mon Mar 26 22:00:30 2012 
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Mon Mar 26 22:00:59 2012 
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Mon Mar 26 22:02:17 2012 
Thread 1 cannot allocate new log, sequence 18
Private strand flush not complete
  Current log# 2 seq# 17 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 17 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Thread 1 advanced to log sequence 18 (LGWR switch)
  Current log# 3 seq# 18 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677
  Current log# 3 seq# 18 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677
Tue Mar 27 02:00:00 2012 
Clearing Resource Manager plan via parameter

           从上，上周五晚上22:00(Fri Mar 23 22:00:00 2012)到上周六凌晨2:00(Sat Mar 24 02:00:00 2012)数据库执行自动化维护任务，上周六、日凌晨6:00到次日凌晨2:00数据库执行自动化维护任务，本周一自动化维护任务的窗口又开始回到晚上22:00到次日凌晨2：00点之间。可见，Oracle 11g相比以前版本的数据库更加自动化、智能化。

Oracle 11g Administrator’s Guide笔记1：DDL_LOCK_TIMEOUT初始化参数介绍

留下评论 ,

           今天在看Oracle 11gR2 Administrator‘s Guide(文档编号E25494-01)时，在第102页读到一个新的初始化参数：DDL_LOCK_TIMEOUT，该参数是从11g Release 1开始已经引入的，用来控制执行DDL语句时需要获取独占锁(exclusive locks)的延时时间，如果在该参数指定的时间范围内获取不到独占锁的话，那么DDL语句就会失败。

           DDL_LOCK_TIMEOUT参数的取值范围是0~1000000秒，是一个整形参数，既可以在会话级别(ALTER SESSION)也可以在系统级别(ALTER SYSTEM)动态调整。其默认值是0，也就是说，执行DDL语句是要求立即获取独占锁资源，如果无法获取，那么所执行的DDL语句将立即报错。

           我们也知道，执行DML语句时需要获取2个锁资源：

  •            ① 针对正在更新的一行或者多行的一个行级独占锁EXCLUSIVE，防止在事务结束之前有其它会话修改了被锁定行的数据，也叫排他锁；
  •            ② 针对要更新的表的 ROW EXCLUSIVE 表级锁定。这可避免在进行更改时由另一会话锁定整个表(可能会删除、截断该表，或修改了该表的表结构)，也叫共享锁；

          正是由于锁的特征，结合DDL_LOCK_TIMEOUT初始化参数，实验如下：

          会话1，创建一张表，并在该表上执行DML事务，且不提交，使该DML事务获取上述的两个锁资源：

SQL> conn / as sysdba;
Connected.
SQL> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> show parameter ddl_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     FALSE
SQL> conn hr/hR;
Connected.
SQL> create table t(id number);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> select * from t;

        ID
----------
         1

SQL>

          会话2，会话级别修改DDL_LOCK_TIMEOUT初始化参数超出0~1000000范围值，直接报错：

SQL> conn hr/hR;
Connected.
SQL> alter session set ddl_lock_timeout=1000001;
ERROR:
ORA-00068: invalid value 1000001 for parameter ddl_lock_timeout, must be
between 0 and 1000000


SQL>

          会话2，执行DDL语句，使之需要获取独占锁，立即抛出错误，因为DDL_LOCK_TIMEOUT初始化参数默认值是0：

SQL> alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn';
alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn'
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL>

          会话2，调整DDL_LOCK_TIMEOUT初始化参数为30秒，在30秒内获取不到独占锁，最终抛出错误：

SQL> alter session set ddl_lock_timeout=30;

Session altered.

SQL> set time on; 
17:00:26 SQL> alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn';
alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn'
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


17:00:58 SQL>

          会话2重新执行DDL，当会话1提交事务后，会话2在30秒内获取到独占锁，DDL执行成功：

17:03:13 SQL> alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn';

Table altered.

17:03:21 SQL> select * from t;

        ID NAME
---------- --------------------
         1 www.OracleOnLinux.cn

17:03:32 SQL>

         

         Oracle 11gR2 Administrator's Guide，仍然需要多读多看，多做实验。

Oracle 数据逻辑恢复及权限控制一例

Oracle 数据逻辑恢复及权限控制一例有1条评论 , ,

近日，应公司内部东南亚Regional Integrated Management Information System项目组需求，完成下述测试环境的搭建：

1 在现有开发库中，创建一个新的名为RIMISADMIN的schema，将现有RIMIS这个schema下的所有数据逻辑备份出来并导入到新建的RIMISADMIN这个schema下；

2 另外创建一个新的测试schema 名为RIMISTEST，该测试schema与RIMISADMIN公用一份数据，并限制其权限仅可以执行DML语句，对数据表有增删改查功能，不允许执行DDL语句，同时不允许对如下表执行DML语句操作：
cd_menu;cd_functionpoint;cd_menu_function;cd_office_fmenu;cd_role_fmenu;cd_office_biz_config;
cd_serial_no;cd_codedict;cd_codetype;edi_code_table;edi_code_type;pr_report_config;
上述内容是公司同事发给我的一封邮件，当然是我与需求同事沟通确认后，整理出来的需求信息。

我的解决思路：
1 在现有RAC数据库中将RIMIS schema用户下的所有数据用EXPDP按照schema模式全部逻辑导出；

2 准备使用IMPDP工具附带remap_schema=rimis:rimisadmin参数全部导入，在这一步执行之前确认RAC数据库的存储信息时，发现ASM磁盘组的总空间为236G，剩余空间只有区区的30G左右。而原有RIMIS schema下的所有数据在数据库中占用将近55G的空间，看来不能直接在这台RAC数据库上搭建这个测试环境，除非先给ASM磁盘组添加磁盘，而开发库又需要给各个项目组使用，不好直接停服务加磁盘，怕影响其他项目组进度。只好选择一种迂回的方案，先将测试环境搭建到另外一套数据库中，等有机会的话，再停RAC数据库，然后添加存储之后另作处理。

3 在新的测试环境上执行逻辑导入，导入之前，在新环境下创建必要的表空间，表空间名需要与原环境下RIMIS用户对象所在表空间名相同，否则，在导入时就得指定REMAP_TABLESPACE选项了，不然，就不可能顺利导入。具体的导入的命令如下：

impdp directory=mig_dir dumpfile=expdp_rimis_20111114.dmp logfile=impdp_rimis.log  remap_schema=rimis:rimisadmin exclude=table_statistics

4 导入之后，发现有些对象编译失效，图省事，执行下述命令直接编译RIMISADMIN schema下的所有对象：

SQL> exec dbms_utility.compile_schema('RIMISADMIN');

5 以SYS用户登录数据库，修改RIMISADMIN schema的口令，并确认RIMISADMIN 可以正常访问数据库；

[oracle@OEL511gR2 mig_dir]$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Nov 16 16:10:42 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn / as sysdba
Connected.
SQL> alter user rimisadmin identified by fjxm;
User altered.
SQL> conn rimisadmin/fjxm;
Connected.
SQL>

6 新环境下创建RIMIS schema,原来需求是创建RIMISTEST，又经更改需求要命名为RIMIS，并只给RIMIS用户赋予最小CREATE SESSION的权限及CREATE SYNONYM(见第8步，具体用处)的权限：

SQL> conn / as sysdba
Connected.
SQL> create user rimis identified by rimis;
User created.
SQL> grant create session to rimis;
Grant succeeded.
SQL> conn rimis/rimis
Connected.
SQL>

7 给RIMIS用户赋予可以对RIMISADMIN对象的增删改查的权限，见下述SQL：

SQL> conn rimisadmin/fjxm
Connected.
SQL> spool /home/oracle/privs.sql
SQL> select 'grant select,insert,update,delete on '||table_name||' to rimis;' from user_tables;
SQL> spool off;

简单修改/home/oracle/privs.sql后，由RIMISADMIN用户执行/home/oracle/privs.sql赋权脚本。

8 RIMIS用户创建一系列的同义词：
RIMISADMIN先生成创建同义词的脚本：

SQL> conn rimisadmin/fjxm
Connected.
SQL> spool /home/oracle/create_synonym.sql
SQL> select 'create synonym '||table_name||' for rimisadmin.'||table_name||';' from user_tables;
SQL> spool off;

然后交由RIMIS用户执行上述创建同义词的脚本；
至此，RIMIS用户就可以正常访问RIMISADMIN用户下的对象。只是，还有一个特殊的上述那些表的权限控制，不能执行DML操作。

9 移除对特定表的增删改的权限：

SQL> conn rimisadmin/fjxm
Connected.
SQL> select 'revoke insert,update,delete on '||table_name||' from rimis;'
   2 from user_tables
   3 where lower(table_name) in ('cd_menu','cd_functionpoint','cd_menu_function',
   4 'cd_office_fmenu','cd_role_fmenu','cd_office_biz_config','cd_serial_no',
   5 'cd_codedict','cd_codetype','edi_code_table','edi_code_type','pr_report_config');

至此，完成RIMIS项目组的数据逻辑恢复、及相关权限的控制。以前，在数据库日常管理中，很少有遇到权限比较细化的场景，所以简单做一记录。
在发布本博文前，收到同事的邮件，说权限已经控制OK！

解析Oracle rowid系列三(完)

解析Oracle rowid系列三(完)有1条评论 ,

承接解析Oracle rowid系列一、系列二。今天，我们来探讨特定场景下的Oracle rowid,大文件表空间下的Oracle rowid。大文件的表空间是Oracle 10g的新特性,关于该特性暂且不作过多表述，简单一句话来说，同小文件类型的表空间(数据库默认的表空间类型)相比，该类型的表空间只能包含一个而且最多只能有一个数据文件。正是因为如此，所以位于大文件类型表空间下的表的rowid显得有些特殊，接下来我们探讨究竟特殊在哪儿？

首先，准备场景，建立大文件表空间，并在该表空间下建立一张普通的Heap表。

SQL> conn / as sysdba;
Connected.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create bigfile tablespace big_tbs datafile size 10m autoextend on;
Tablespace created.
SQL> select d.file_id,d.tablespace_name,t.bigfile
2  from dba_data_files d,dba_tablespaces t
3  where d.tablespace_name=t.tablespace_name
4  order by 1
5  ;
FILE_ID   TABLESPACE_NAME        BIG
---------- ----------------- ---
1 SYSTEM                         NO
2 SYSAUX                         NO
3 UNDOTBS1                       NO
4 USERS                          NO
5 EXAMPLE                        NO
6 UNDOTBS2                       NO
7 TEST_TBS                       NO
8 TEST_TBS                       NO
9 BIG_TBS                        YES
9 rows selected.
SQL> alter user hr quota unlimited on big_tbs;
User altered.
SQL> conn hr/hr
Connected.
SQL> create table bigfile_tab(id number,name varchar2(10))
2  tablespace big_tbs
3  ;
Table created.
SQL> insert into bigfile_tab values(1,'oracle');
1 row created.
SQL> insert into bigfile_tab values(2,'oracle');
1 row created.
SQL> commit;
Commit complete.
SQL>

然后，我们来查询bigfile_tab表中的rowid：

SQL> select id,rowid from bigfile_tab;
ID ROWID
---------- ------------------
1 AAAUHfAAAAAAACGAAA
2 AAAUHfAAAAAAACGAAB
SQL> select rowid,
2  dbms_rowid.rowid_object(rowid) object_id,
3  dbms_rowid.rowid_relative_fno(rowid)  file_id,
4  dbms_rowid.rowid_block_number(rowid)  block_id ,
5  dbms_rowid.rowid_row_number(rowid)   num
6  from bigfile_tab
7  ;
ROWID               OBJECT_ID    FILE_ID   BLOCK_ID        NUM
------------------ ---------- ---------- ---------- ----------
AAAUHfAAAAAAACGAAA      82399          0        134          0
AAAUHfAAAAAAACGAAB      82399          0        134          1
SQL>

这时，我们发到貌似诡异的相对文件号竟然为0，本来我们的这张测试表位于bigfile_tbs表空间下，而bigfile_tbs表空间的file_id为9，我们从上述的第一次查询结果可以验证。可是为什么从rowid中查询的相对文件号为什么为0呢？

原来，对于大文件表空间下的rowid有如下特定的格式，这有别于小文件表空间下的rowid格式：

OOOOOOBBBBBBBBBRRR

即，6位的数据库对象号+9位的数据块号+3位的行号，同样也是以18位的64进制值来表示80位的二进制数。只不过，在这里少了相对文件号，其中6位的数据库对象号用32位的二进制数来存放(即一个数据库最多可以拥有232=4G个数据块对象)，9位的数据块号同样用32位的二进制数来存放(即一个大文件表空间可以拥有232个数据块儿)，最后3位的行号占用剩余的16位的二进制数，正好占满80位。

最后，我们就可以很容易理解为什么本实验中bigfile_tab表的相对文件号为0了？因为该表的rowid格式中根本就不存在相对文件号的信息，最本质的原因是大文件表空间下永远只能有且仅有1个数据文件，也就没有相对文件号的概念了。

同时，我们也可以推算出为什么官方文档中说，对于大文件的表空间，如果数据块大小为32K的话，那么这个表空间的上限是128Tb？因为大文件表空间下最多可以有232个数据块，那么该表空间大小=232*32K=237K=227M=217G=27T=128T,答案也就在于此。

至此，关于Oracle rowid的探讨一、二、三系列结束。如果，大家对该系列有不同的理解，或认为本人理解有误的地方，还请不吝指正！！！

SQL> conn / as sysdba;
Connected.
SQL> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
PL/SQL Release 11.2.0.1.0 – Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production
SQL> create bigfile tablespace big_tbs datafile size 10m autoextend on;
Tablespace created.
SQL> select d.file_id,d.tablespace_name,t.bigfile
2  from dba_data_files d,dba_tablespaces t
3  where d.tablespace_name=t.tablespace_name
4  order by 1
5  ;
FILE_ID TABLESPACE_NAME                BIG
———- —————————— —
1 SYSTEM                         NO
2 SYSAUX                         NO
3 UNDOTBS1                       NO
4 USERS                          NO
5 EXAMPLE                        NO
6 UNDOTBS2                       NO
7 TEST_TBS                       NO
8 TEST_TBS                       NO
9 BIG_TBS                        YES
9 rows selected.
SQL> alter user hr quota unlimited on big_tbs;
User altered.
SQL> conn hr/hr
Connected.
SQL> create table bigfile_tab(id number,name varchar2(10))
2  tablespace big_tbs
3  ;
Table created.
SQL> insert into bigfile_tab values(1,’oracle’);
1 row created.
SQL> insert into bigfile_tab values(2,’oracle’);
1 row created.
SQL> commit;
Commit complete.
SQL>

解析Oracle rowid系列二

解析Oracle rowid系列二有1条评论 ,

在解析Oracle rowid系列一里，我们简单探讨了Oracle rowid的格式及获取rowid的方法，在本篇中我们探讨的主题是：通过Oracle rowid真的可以唯一定位到表的一条记录吗？或者说在同一Oracle数据库中，rowid就一定唯一、没有重复的吗？
实验步骤：
1 构建cluster table并插入测试数据：

SQL> conn / as sysdba;
Connected.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> conn hr/hr;
Connected.
SQL> create cluster dept_emp_cluster(department_id number(4));
Cluster created.
SQL> create index idx_dept_emp_cluster on cluster dept_emp_cluster;
Index created.
SQL> create table dept cluster dept_emp_cluster(department_id)
2  as select * from departments;
Table created.
SQL> create table emp cluster dept_emp_cluster(department_id)
2  as select * from employees;
Table created.
SQL> select count(*) from dept;
COUNT(*)
----------
27
SQL> select count(*) from emp;
COUNT(*)
----------
107

2 接下来我们分别查询emp、dept表中department_id=10的rowid：

SQL> select department_id,rowid from dept where department_id=10;
DEPARTMENT_ID ROWID
------------- ------------------
           10 AAAUB3AAEAAAAK3AAA
SQL> select department_id,rowid from emp where department_id=10;
DEPARTMENT_ID ROWID
------------- ------------------
           10 AAAUB3AAEAAAAK3AAA
SQL> select department_id,rowid,
  2  dbms_rowid.rowid_object(rowid) object_id,
  3  dbms_rowid.rowid_relative_fno(rowid)  file_id,
  4  dbms_rowid.rowid_block_number(rowid)  block_id,
  5  dbms_rowid.rowid_row_number(rowid)   num
  6  from dept where department_id=10;
DEPARTMENT_ID ROWID               OBJECT_ID    FILE_ID   BLOCK_ID        NUM
------------- ------------------ ---------- ---------- ---------- ----------
           10 AAAUB3AAEAAAAK3AAA      82039          4        695          0
SQL> select department_id,rowid,
  2  dbms_rowid.rowid_object(rowid) object_id,
  3  dbms_rowid.rowid_relative_fno(rowid)  file_id,
  4  dbms_rowid.rowid_block_number(rowid)  block_id,
  5  dbms_rowid.rowid_row_number(rowid)   num
  6  from emp where department_id=10;
DEPARTMENT_ID ROWID               OBJECT_ID    FILE_ID   BLOCK_ID        NUM
------------- ------------------ ---------- ---------- ---------- ----------
           10 AAAUB3AAEAAAAK3AAA      82039          4        695          0
SQL>

从上，我们看到对于dept、emp这两张不同的表，对于department_id=10的两条记录的rowid完全一样，均为AAAUB3AAEAAAAK3AAA。
其中AAAUB3为数据库对象号，即转成10进制后位82309；数据文件号为AAE=4，位于第4号数据文件上；位于第4号文件的第AAAAK3=695个数据块上。
为什么会这样呢？不是说，rowid是唯一的吗，通过rowid可以唯一定位表里的一条记录吗？可是，现在却有两张完全不同的表中的rowid竟然完全重复？
3 原来，我们这个场景比较特殊，对象号为82309的对象是一个聚簇表，而dept,emp是位于该cluster下的。那么，emp、dept表中拥有完全重复的rowid也就不足为奇了。因为，这本身就是聚簇表的特征。Oracle的Cluster Table就是要将不同表中的数据放在同一个数据块中存放。关于Cluster Table我们将在后续探讨。

SQL> select object_name,object_id,object_type
  2  from user_objects where object_id='82039';
OBJECT_NAME           OBJECT_ID OBJECT_TYPE
-------------------- ---------- -------------------
DEPT_EMP_CLUSTER          82039 CLUSTER
SQL> select table_name,tablespace_name,cluster_name from user_tables
  2  where table_name in ('DEPT','EMP');
TABLE_NAME               TABLESPACE_NAME         CLUSTER_NAME
------------------------ ----------------------- --------------------
DEPT                     USERS                   DEPT_EMP_CLUSTER
EMP                      USERS                   DEPT_EMP_CLUSTER
SQL>

4 结论：对于普通的Heap Table，我们说rowid的确可以精确定位到表里的记录，并且任何两张表中得rowid绝对不会重复。而对于Cluster Table就不一样了，完全有可能会出现rowid重复的情况。

解析Oracle rowid系列一

解析Oracle rowid系列一有7条评论 ,

搞Oracle的人，都知道表里有一个叫做rowid的伪列，也知道可以通过rowid来快速定位表里的记录，至少通过oracle的索引来快速查找表里的数据就是通过rowid来定位的。可是，我们真的对rowid很了解吗？至少，我之前存在了一些误区，甚至有点儿荒谬！在此，对rowid作一个全面解析，同大家分享，以飨网友。

1 Oracle rowid格式：

Oracle rowid format

rowid

OOOOOO

FFF

BBBBBB

RRR

说明

数据对象号

相对文件号

数据块号

行号

上述表格是Oracle 9i及以上版本数据库中的rowid格式：6位对象号+3位相对文件号+6位数据块号+3位行号，是一个18位的64进制值。这个18位的64进制值在数据库内却是以10个bytes合计80个bit的二进制数存放的，和我们直接看到的结果有所区别。这里的64进制和10进制的对应编码如下表：

64进制-10进制转换表

64进制编码

A

B

…

Z

a

b

…

z

0

1

…

9

+

/

10进制值

0

1

…

25

26

27

…

51

52

53

…

61

62

63

2 那么这个18位的64进制值又是如何同80位的二进制数对应的呢？

其中，6位的对象号在数据库中是用32位二进制来存放的，也就意味着一个oracle数据库中最多可以有232个对象，即4G个对象；3位的相对文件号在数据库中是用10位二进制来存放的，也就意味着一个表空间中最多可以容纳210=1024去掉全0和全1个数据文件，即1022个数据文件；6位的数据块号用22位二进制来存放的，也就意味着一个数据文件最多可以包含222=4M个数据块；3位的行号在数据库中是用16位二进制来存放的，也就意味着一个数据块上最多可以容纳216=65536行记录。

3 我们已经对rowid有了基本认识，我们又该如何获取rowid呢？见下例：

SQL> show user;
USER is "HR"
SQL> select employee_id,last_name,rowid from employees where employee_id=100;

EMPLOYEE_ID LAST_NAME                 ROWID
----------- ------------------------- ------------------
100 King                      AAAR5pAAFAAAADPAAA

SQL>

我们可以在查询列表中，把rowid伪列当做普通的字段来查询。上例中，AAAR5p就是HR.employees在数据库中的对象号，AAF表示相对文件号，AAAADP则表示数据块编号，最后的3个AAA则表示行号。当然，我们完全可以根据上述的转换表，将这些64进制值直接转成对应的10进制值。分别就是HR.employees表在数据库内的对象号是73321，而该表在数据库内部是存放在第5号文件(AAF=0*642+0*641+5*640=5)上的，employee_id=100的记录则是放在第5号文件的第207个数据块上的；最后的AAA表示该记录是位于第5号文件上的第207个数据块上的第1条记录。

4 除了上述方法，我们还可以通过数据库提供给我们的工具包来获取该信息：

SQL> select rowid,
2  dbms_rowid.rowid_object(rowid) object_id,
3  dbms_rowid.rowid_relative_fno(rowid)  file_id,
4  dbms_rowid.rowid_block_number(rowid)  block_id ,
5  dbms_rowid.rowid_row_number(rowid)   num ,
6  rowidtochar(rowid) from employees where employee_id=100
7  ;

ROWID               OBJECT_ID    FILE_ID   BLOCK_ID        NUM ROWIDTOCHAR(ROWID)
------------------ ---------- ---------- ---------- ---------- ------------------
AAAR5pAAFAAAADPAAA      73321          5        207          0 AAAR5pAAFAAAADPAAA

SQL>

很显然，我们通过调用系统的dbms_rowid包，获得的rowid信息同我们手工将64进制转成10进制得到的结果完全一致！

这是探讨Oracle rowid系列之一，后续将有系列二探讨Cluster table rowid、系列三探讨Bigfile tablespace rowid，敬请关注。

Oracle 11g Concepts 笔记2：什么是唯一、非唯一索引及索引分类

Oracle 11g Concepts 笔记2：什么是唯一、非唯一索引及索引分类有1条评论 ,

从Oracle索引的特征上，我们可以简单的把索引分为Unique Indexes and Nonunique Indexes，即唯一索引和非唯一索引。

1      对于唯一索引，唯一索引确保被索引的字段或多个联合字段在表中绝对不会有重复值；通常，我们在建表时，创建唯一约束或者主键约束，再或者建表以后给表添加唯一、主键约束时，Oracle会自动在主键、唯一约束的字段上创建唯一索引，并且索引的名字跟约束的名字一样，如：

SQL> create table tt(id1 number primary key,id2 number,id3 number);
Table created.
SQL> select index_name,table_name,uniqueness from user_indexes where table_name='TT';
INDEX_NAME                     TABLE_NAME                     UNIQUENESS
------------------------------ ------------------------------ ----------
SYS_C0015420                   TT                             UNIQUE
SQL> select table_name,constraint_name,index_name,constraint_type
2  from user_constraints
3  where table_name='TT';
TABLE_NAME     CONSTRAINT_NAME  INDEX_NAME    CONSTRAINT_TYPE
-------------- ---------------- ------------- ---------------
TT             SYS_C0015420     SYS_C0015420  P

我们在建测试表tt时在id1字段上创建了一个主键约束，当然该约束名字SYS_C0015420是Oracle自动分配给我们的，同时我们也看到tt表上有1个名为SYS_C0015420的索引。
接下来，在id2字段上添加一个唯一约束，并且手动指定名字：

SQL> alter table tt add constraint tt_id2_uk unique(id2);
Table altered.
SQL> select table_name,constraint_name,index_name,constraint_type
2  from user_constraints
3  where table_name='TT';
TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME                     C
------------------------------ ------------------------------ ------------------------------ -
TT                             SYS_C0015420                   SYS_C0015420                   P
TT                             TT_ID2_UK                      TT_ID2_UK                      U
SQL>  select index_name,table_name,uniqueness from user_indexes where table_name='TT';
INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
TT_ID2_UK                      TT                             UNIQUE
SYS_C0015420                   TT                             UNIQUE
SQL>

我们发现，这tt表上的两个索引都是唯一索引，且名字都等同于各自对应的约束名字。
然后，在该表上创建一个索引：

SQL> create index idx_tt_id3 on tt(id3);
Index created.
SQL> select index_name,table_name,uniqueness from user_indexes where table_name='TT';
INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
IDX_TT_ID3                     TT                             NONUNIQUE
TT_ID2_UK                      TT                             UNIQUE
SYS_C0015420                   TT                             UNIQUE
SQL>

此时，发现该索引IDX_TT_ID3是一个非唯一索引，我们也可以使用create unique index…语句显示创建一个唯一索引。
就唯一索引存储而言，每一个rowid会唯一对应表中的一条记录，在索引的叶子块儿中，数据按照索引字段升序存放。
2     对于非唯一索引，允许被索引的字段有重复值。并且，非唯一索引的存放有别于唯一索引，它的数据在索引叶子块儿中是按照索引字段和rowid进行升序存放的。
3    在Oracle数据库中，索引分类有以下几种：

  • B树索引 B-tree indexes；B树索引又可分为以下子类：
    • 索引组织表Index-organized tables；
    • 反转索引Reverse key indexes；
    • 降序索引Descending indexes；
    • B树聚簇索引B-tree cluster indexes；
  • 位图和位图联合索引Bitmap and bitmap join indexes；
  • 基于函数的索引Function-based indexes；
  • 应用域索引Application domain indexes；

4    关于Oracle的索引还有更多知识需要了解和探索，下篇继续，To Be Continued…