GO DBMS: 7月 2011

2011年7月31日日曜日

[分区表] 将非分区表转换为分区表

# 方法1, 创建一个同样结构的分区表, 然后 exchange, 接着进行 split
# 方法2, 使用在线重定义

1. exchange + split方式

1-1. 原表
CREATE TABLE t (timestamp date,id int);
insert into t select to_date('2011-12-31','yyyy-mm-dd')-mod(rownum,360),object_id from all_objects;
select max(timestamp),min(timestamp) from t;

1-2. 创建分区表, 分区范围必须包含所有源数据
create table tt (timestamp date,id int)
partition by range(timestamp) (partition p1 values less than (to_date('2012-01-01','yyyy-mm-dd')));

1-3. exchange, 并确认数据
alter table tt exchange partition p1 with table t;
select max(timestamp),min(timestamp) from tt;
select count(*) from tt;

1-4. 分割表分区
# 分割时后面分区顺序不同, 导致数据分配也不同, 可能是按照分区顺序递增分布数据, 需要具体测试
alter table tt split partition p at (to_date('2011-07-01','yyyy-mm-dd')) into (partition p2, partition p1);
select min(timestamp),max(timestamp) from tt partition (p1)
union all
select min(timestamp),max(timestamp) from tt partition (p2);

1-5. 删除原表
drop table t purge;

1-6. 重命名新表
alter table tt rename to t;

2. 在线重定义

2-1. 原表与分区表
CREATE TABLE t (timestamp date,id int);
insert into t select to_date('2011-12-31','yyyy-mm-dd')-mod(rownum,360),object_id from all_objects;
create table tt ( timestamp date,id int)
partition by range(timestamp)
(
partition p1 values less than (to_date('2011-07-01','yyyy-mm-dd')),
partition p2 values less than (to_date('2012-01-01','yyyy-mm-dd'))
);

2-2. 开始在线重定义
# 源表必须有主键或唯一键
BEGIN dbms_redefinition.can_redef_table(user,'t'); END;
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE
(
uname => user,
orig_table => 't',
int_table => 'tt',
col_mapping => NULL,
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID
);
END;

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE
(
uname => user,
orig_table => 't',
int_table => 'tt'
);
END;

2-3. 创建的新分区表已经不再是分区表
select min(timestamp),max(timestamp) from tt partition (p1)
union all
select min(timestamp),max(timestamp) from tt partition (p2);
select min(timestamp),max(timestamp) from tt partition (p1)
*
ERROR at line 1:
ORA-14501: object is not partitioned

2-4. 原表变为分区表
select min(timestamp),max(timestamp) from t partition (p1)
union all
select min(timestamp),max(timestamp) from t partition (p2);

[分区表] 数据仓库滑动窗口技术

# 滑动窗口技术
# 对分区表做任何操作时, 同时维护全局索引

1. 分区表
CREATE TABLE t (timestamp date,id int)
PARTITION BY RANGE (timestamp)
(
PARTITION fy_2004 VALUES LESS THAN (to_date('01-jan-2005','dd-mon-yyyy')),
PARTITION fy_2005 VALUES LESS THAN(to_date('01-jan-2006','dd-mon-yyyy'))
);

2. 初始化数据
insert into t partition(fy_2004) select to_date('2004-12-31','yyyy-mm-dd')-mod(rownum,360),object_id from all_objects；
insert into t partition(fy_2005) select to_date('2005-12-31','yyyy-mm-dd')-mod(rownum,360),object_id from all_objects;

3. 创建局部索引
create index partitioned_idx_local on t (id) local;

4. 创建全局索引
create index partitioned_idx_global on t (timestamp) GLOBAL;

5. 创建旧数据交换空表
create table fy_2004 (timestamp date,id int);
create index fy_2004_idx on fy_2004(id);

6. 创建新数据交换空表
create table fy_2006 (timestamp date,id int)；

7. 插入新数据
insert into fy_2006 select to_date('2006-12-31','yyyy-mm-dd')-mod(rownum,360),object_id from all_objects;
create index fy_2006_idx on fy_2006(id) nologging；

8. 交换出旧数据分区
alter table t exchange partition fy_2004 with table fy_2004 including indexes without validation UPDATE GLOBAL INDEXES;

9. 删除旧数据分区, 同时维护全局索引
alter table partitioned drop partition fy_2004 UPDATE GLOBAL INDEXES;

10. 添加新数据分区
alter table partitioned add partition fy_2006 values less than (to_date('2007-01-01','yyyy-mm-dd'));

11. 滑入新数据, 同时维护全局索引
alter table partitioned exchange partition fy_2006 with table fy_2006 including indexes without validation UPDATE GLOBAL INDEXES;

[分区表] 局部分区索引

# 表分区与局部索引分区是 1 对 1 的关系, 局部索引不能跨分区去索引其他分区数据
# 局部前缀索引, 表分区键在索引定义的前几列上
# 局部非前缀索引, 表分区键不在索引定义的前几列上, 或索引列根本不包含表分区键
# 利用分区消除, 使查询操作高效

1. 分区表
CREATE TABLE partitioned_table(a int,b int,data char(20))
PARTITION BY RANGE (a)
(
PARTITION part_1 VALUES LESS THAN(2) tablespace p1,
PARTITION part_2 VALUES LESS THAN(3) tablespace p2
)

2. 创建前缀局部索引
create index local_prefixed on partitioned_table (a,b) local;

3. 创建非前缀局部索引
create index local_nonprefixed on partitioned_table (b) local;

4. 插入测试数据
insert into partitioned_table select mod(rownum-1,2)+1,rownum,'x' from all_objects;

5. 收集统计信息
begin dbms_stats.gather_table_stats (user,'PARTITIONED_TABLE',cascade=>TRUE);end;

6. 脱机 p2 表空间
alter tablespace p2 offline;

set autotrace on explain

-- 利用前缀局部索引查询, 消除 p2 分区, 查询成功
select * from partitioned_table where a = 1 and b = 1;
A B DATA
---------- ---------- ----------------------------------------
1 1 x

Execution Plan
----------------------------------------------------------
Plan hash value: 1622054381

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 28 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 28 | 2 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | LOCAL_PREFIXED | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
------------------------------------------------------------------------------------------------------

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

3 - access("A"=1 AND "B"=1)

7. 因为不确定 b=1 的值在哪个分区, 所以 p1 和 p2 的索引都要查看, 报错
select * from partitioned_table where b = 1;
ERROR:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: 'D:\ORACLE\ORADATA\DEMO\DATAFILES\P2.DBF'

Execution Plan
----------------------------------------------------------
Plan hash value: 440752652

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 1 | 28 | 4 (0)| 00:00:01 | 1 | 2 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 28 | 4 (0)| 00:00:01 | 1 | 2 |
|* 3 | INDEX RANGE SCAN | LOCAL_NONPREFIXED | 1 | | 3 (0)| 00:00:01 | 1 | 2 |
----------------------------------------------------------------------------------------------------

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

3 - access("B"=1)

8. 非前缀索引也可以利用分区消除, 主要要保证谓词中存在可以分区消除的键
drop index local_prefixed;
select * from partitioned_table where a = 1 and b = 1;
A B DATA
---------- ---------- ----------------------------------------
1 1 x

Execution Plan
----------------------------------------------------------
Plan hash value: 904532382

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 28 | 2 (0)| 00:00:01 | 1 | 1 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 28 | 2 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | LOCAL_NONPREFIXED | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------

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

2 - filter("A"=1)
3 - access("B"=1)

[分区表] 全局分区索引

# 全局索引必须是前缀索引
# 分区表通过全局索引来保证唯一性, 而局部索引不能保证唯一性
# 索引按区间或散列分区, 可以采用与表不同的分区方式, 索引分区数可能不同于表分区数
# 如果要使用列表或组合分区的索引, 必须使用局部索引

1. 分区表
CREATE TABLE t (timestamp date,id int)
PARTITION BY RANGE (timestamp)
(
PARTITION part_1 VALUES LESS THAN (to_date('01-jan-2000','dd-mon-yyyy')),
PARTITION part_2 VALUES LESS THAN (to_date('01-jan-2001','dd-mon-yyyy'))
)

2. 在 id 列上创建全局分区索引
create index t_idx on t(id)
GLOBAL partition by range(id)
(
partition part_1 values less than(1000),
partition part_2 values less than (MAXVALUE)
)

3. 创建表主键, 此时会隐式使用先前创建的 id 列上的索引
alter table t add constraint t_pk primary key(id)

4. 索引不可删除
drop index partitioned_index;

ORA-02429: cannot drop index used for enforcement of unique/primary key

5. 可以创建前缀全局索引
create index t_idx1 on t (id,timestamp)
GLOBAL partition by range(id)
(
partition part_1 values less than(1000),
partition part_2 values less than (MAXVALUE)
)

6. 不能创建非前缀全局索引, 全局索引必须是前缀索引
create index t_idx2 on t (timestamp,id)
GLOBAL partition by range(id)
(
partition part_1 values less than(1000),
partition part_2 values less than (MAXVALUE)
)

ORA-14038: GLOBAL t index must be prefixed

create index t_idx3 on t (timestamp)
GLOBAL partition by range(id)
(
partition part_1 values less than(1000),
partition part_2 values less than (MAXVALUE)
)

ORA-14038: GLOBAL t index must be prefixed

2011年7月30日土曜日

[索引相关] 不使用索引的情况 - 4

-- 数据类型的隐式转换，导致索引失效
-- 应该把转换写在等式右边

go@XE> create table t (x char(1) constraint t_pk primary key,y date);

Table created.

go@XE> desc t
Name                  Null?    Type
--------------------- -------- ----------------------------------------
X                              NOT NULL CHAR(1)
Y                              DATE

go@XE> insert into t values ('5',sysdate);

1 row created.

go@XE> commit;

Commit complete.

go@XE> set autotrace traceonly explain
go@XE> select * from t where x=5;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    12 |     3   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    |     1 |    12 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER("X")=5) -- 列X隐式转换

Note
-----
   - dynamic sampling used for this statement

-- 强制使用索引，导致索引全扫描，而不是唯一扫描
go@XE> select /*+ index(t,t_pk) */* from t where x=5;

Execution Plan
----------------------------------------------------------
Plan hash value: 1399892806

------------------------------------------------------------------------------------
| Id | Operation                   | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    12 |    34   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T    |     1 |    12 |    34   (0)| 00:00:01 |
|* 2 |   INDEX FULL SCAN           | T_PK |     1 |       |    26   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER("X")=5)

Note
-----
   - dynamic sampling used for this statement

-- 正确的写法
go@XE> select * from t where x='5';

Execution Plan
----------------------------------------------------------
Plan hash value: 1303508680

------------------------------------------------------------------------------------
| Id | Operation                   | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    12 |     1   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T    |     1 |    12 |     1   (0)| 00:00:01 |
|* 2 |   INDEX UNIQUE SCAN         | T_PK |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("X"='5')

[索引相关] 唯一索引与非唯一索引

-- 非唯一索引是允许存在重复的索引键值，根据索引键查询可能得到多行，默认创建的索引为非唯一索引
-- 唯一索引则不允许创建重复的索引键值，根据索引键查询只能得到单行
-- 主键约束，唯一键约束创建隐藏索引均为非唯一键索引

go@DEMO> create table t (a number);

Table created.

-- 创建索引
go@DEMO> create index idx_t on t (a);

Index created.

-- 默认为非唯一索引
go@DEMO> select index_name,index_type,uniqueness from user_indexes where table_name='T';
INDEX_NAME     INDEX_TYPE     UNIQUENESS
----------     ----------     -------------
IDX_T          NORMAL         NONUNIQUE

1 row selected.

go@DEMO> select index_name,index_type,uniqueness from user_indexes where table_name='T';
INDEX_NAME     INDEX_TYPE    UNIQUENESS
----------     ----------    -------------
IDX_T          NORMAL        NONUNIQUE

1 row selected.

go@DEMO> insert into t values (1);

1 row created.

go@DEMO> insert into t values (1);

1 row created.

go@DEMO> commit;

Commit complete.

-- 允许插入索引键的重复值
go@DEMO> select * from t;
         A
----------
         1
         1

2 rows selected.

go@DEMO> analyze index idx_t validate structure;

Index analyzed.

go@DEMO> select name,lf_rows from index_stats;
NAME                                        LF_ROWS
---------------------------------------- ----------
IDX_T                                             2

1 row selected.

go@DEMO> drop table t purge;

Table dropped.

go@DEMO> create table t (a number);

Table created.

go@DEMO> create unique index idx_t on t(a);

Index created.

go@DEMO> select index_name,index_type,uniqueness from user_indexes where table_name='T';
INDEX_NAME    INDEX_TYPE    UNIQUENESS
----------    ----------    -----------
IDX_T         NORMAL        UNIQUE

1 row selected.

go@DEMO> insert into t values (1);

1 row created.

-- 不允许插如重复的索引键值
go@DEMO> insert into t values (1);
insert into t values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (GO.IDX_T) violated

go@DEMO> commit;

Commit complete.

go@DEMO> select * from t;
         A
----------
         1

1 row selected.

go@DEMO> analyze index idx_t validate structure;

Index analyzed.

go@DEMO> select name,lf_rows from index_stats;
NAME                                        LF_ROWS
---------------------------------------- ----------
IDX_T                                             1

1 row selected.

[索引相关] 函数索引实现有条件的唯一性约束

-- 原理是btree索引不会索引null值，通过case when、decode等把不想被索引的键值转换成null就可以
-- 判断是否重复时，不能保证另外的会话也在做相同判断。这样的算法就可能导致多条相同记录插入。
-- 使用Oracle的约束来实现。使用具有函数索引和唯一索引联合实现该需求。

SQL> create table t(a varchar2(10),b number);

Table created.

SQL> create unique index idx_t on t(decode(b,100,null,a),decode(b,100,null,b));

Index created.

SQL> insert into t values('a',1);

1 row created.

SQL> insert into t values('b',1);

1 row created.

SQL> insert into t values('c',1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t values('a',100);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t values('a',100);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

A                             B
-------------------- ----------
a                             1
b                             1
c                             1
a                           100
a                           100

SQL> insert into tt1 values('a',1);
insert into tt1 values('a',1)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.IND_AB) violated

-- 该索引的使用方式
go@DEMO> select * from t where decode(b,100,null,a)='a' and decode(b,100,null,b)=100;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id | Operation                   | Name | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    20 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T     |     1 |    20 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IDX_T |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access(DECODE("B",100,NULL,"A")='a')
       filter(TO_NUMBER(DECODE("B",100,NULL,TO_CHAR("B")))=100)

Note
-----
   - dynamic sampling used for this statement

[备份恢复] 唯一性索引误删除后的恢复

T 表中的 id 字段原来有唯一性索引, 被误删后导致数据有重复
在不能停机的情况下, 按照下面的步骤删除重复数据, 并重建唯一性索引

1. 建立普通索引
create index t_idx on t(id) online;
select index_name,table_name from user_indexes where table_name='T';

2. 创建主键
因为原数据中存在主键重复的键值, 直接创建主键会报错, 需要加 enable novalidate 来忽略旧数据的重复验证
之后插入的新数据会做验证
利用 using index 来使用之前创建的索引而不由主键创建隐含索引, 此处也可以创建唯一索引

alter table t add constraint t_pk primary key (id) using index t_idx enable novalidate;
select index_name,table_name from user_indexes where table_name='T';
select constraint_name,constraint_type,table_name from user_constraints where table_name='T';

3. 删除重复数据
delete from t a where a.rowid not in (select min(b.rowid) from t b group by b.id having count(b.id)>1) and a.id in (select id from t b group by b.id having count(b.id)>1)

4. 使主键的验证生效 (针对旧数据)
alter table t modify constraint t_pk enable validate;

[索引相关] 唯一索引与非唯一索引

[基础知识] 主键唯一键约束唯一键索引的区别

-- 主键约束、唯一键约束和唯一索引的区别

-- 主键约束和唯一键约束均会隐式创建同名的唯一索引，当主键约束或者唯一键约束失效时，隐式创建的唯一索引会被删除
-- 主键约束要求列值不能为null，而唯一键约束和唯一索引不做要求，但如果需要保持唯一性，则需要所建的列中至少有一个为not null
-- 相同字段序列不允许重复创建索引
-- 索引是存储在数据库中的一个物理结构，键纯粹是一个逻辑概念。键代表创建来实施业务规则的完整性约束。索引和键的混淆通常是由于数据库使用索引来实施完整性约束。
-- 主键和唯一键约束可以建立主外键关系，而唯一索引不行

SQL> create table t (
id int,name varchar2(20),
constraint pk_t primary key(id)
) tablespace users;

Table created.

SQL> select constraint_name,constraint_type from user_constraints;

CONSTRAINT_NAME                C
------------------------------ -
PK_T                           P

-- 在t表中，我们指定了ID列作为主键，Oracle数据库会自动创建一个同名的唯一索引：

SQL> select index_name,index_type,uniqueness,tablespace_name
from user_indexes
where table_owner='GO'
and table_name = 'T';

INDEX_NAME           INDEX_TYPE           UNIQUENES TABLESPACE_NAME
-------------------- -------------------- --------- ----------------
PK_T                 NORMAL               UNIQUE    USERS

-- 此时，如果我们再试图在ID列上创建一个唯一索引，Oracle会报错，因为该列上已经存在一个唯一索引：

SQL> create unique index idx_test_uk on test(id);
create unique index idx_test_uk on test(id)
                                        *
ERROR at line 1:
ORA-01408: such column list already indexed

-- 即使创建非唯一索引也不行：
SQL> create index idx_test_id on test(id);
create index idx_test_id on test(id)
                                 *
ERROR at line 1:
ORA-01408: such column list already indexed

-- 那么唯一键约束的情况是怎样的呢？

SQL> drop table t purge;

Table dropped.

SQL> create table t(
id int,
name varchar2(20),
constraint uk_t unique(id));

Table created.

SQL> select constraint_name,constraint_type from user_constraints;

CONSTRAINT_NAME                C
------------------------------ -
UK_T                           U

-- 查看此时的索引情况：

SQL> select index_name,index_type,uniqueness,tablespace_name
from user_indexes
where table_owner='GO'
and table_name = 'T';

INDEX_NAME           INDEX_TYPE           UNIQUENES TABLESPACE_NAME
-------------------- -------------------- --------- ------------------------------
UK_T                 NORMAL               UNIQUE    USERS

-- Oracle同样自动创建了一个同名的唯一索引，而且也不允许再在此列上创建唯一索引或非唯一索引。

-- 我们知道，主键约束要求列值非空（NOT NULL），那么唯一键约束是否也要求非空呢？

SQL> insert into t values(1, 'Sally');

1 row created.

SQL> insert into t values(null, 'Tony');

1 row created.

SQL> insert into t values(null, 'Jack');

1 row created.

SQL> select * from t;

        ID NAME
---------- -------------------
         1 Sally
           Tony
           Jack

-- 从实验结果来看，唯一键约束并没有非空要求。

-- 接下来我们看看唯一索引对列值的非空要求有什么不同。

SQL> drop table t purge;

Table dropped.

SQL> create table t(
id int,
name varchar2(20));

Table created.

SQL> create unique index idx_t_id on t (id);

Index created.

SQL> insert into test values(1, 'Sally');

1 row created.

SQL> insert into test values(null, 'Tony');

1 row created.

SQL> insert into test values(null, 'Jack');

1 row created.

SQL> select * from t;

        ID NAME
---------- --------------------
         1 Sally
           Tony
           Jack

-- 唯一索引与唯一键约束一样对列值非空不做要求

-- 如果我们让主键约束或者唯一键约束失效，Oracle自动创建的唯一索引是否会受到影响？

SQL> drop table t purge;

Table dropped.

SQL> create table t(
id int,
name varchar2(20),
constraint uk_t unique(id));

Table created.

SQL> select index_name,index_type,uniqueness from user_indexes;

INDEX_NAME                     INDEX_TYPE                  UNIQUENES
------------------------------ --------------------------- ---------
UK_T                           NORMAL                      UNIQUE

SQL> alter table t disable constraint uk_t;

Table altered.

SQL> select index_name,index_type,uniqueness from user_indexes;

no rows selected

-- 当主键约束或者唯一键约束失效时，Oracle会删除隐式创建的唯一索引。

-- 如果我们先创建唯一索引，再创建主键或者唯一键约束，情况又会怎样呢？

SQL> drop table t purge;

Table dropped.

SQL> create table t(
id int,
name varchar2(20));

Table created.

SQL> create unique index idx_t_id on t (id);

Index created.

SQL> select index_name,index_type,uniqueness
from user_indexes
where table_owner = 'GO'
and table_name = 'T';

INDEX_NAME                     INDEX_TYPE                  UNIQUENES
------------------------------ --------------------------- ---------
IDX_T_ID                       NORMAL                      UNIQUE

SQL> alter table t add constraint uk_t unique (id);

Table altered.

SQL> select index_name, index_type, uniqueness
from user_indexes
where table_owner = 'GO'
and table_name = 'T';

INDEX_NAME                     INDEX_TYPE                  UNIQUENES
------------------------------ --------------------------- ---------
IDX_T_ID                       NORMAL                      UNIQUE

SQL> select constraint_name,constraint_type
from user_constraints
where table_name = 'T';

CONSTRAINT_NAME                C
------------------------------ -
UK_T                           U

SQL> alter table t disable constraint uk_t;

Table altered.

SQL> select constraint_name,constraint_type,status
from user_constraints
where table_name = 'T';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
UK_T                           U DISABLED

SQL> select index_name,index_type,uniqueness,status
from user_indexes
where table_owner = 'GO'
and table_name = 'T';

INDEX_NAME                     INDEX_TYPE                  UNIQUENES STATUS
------------------------------ --------------------------- --------- --------
IDX_TEST_ID                    NORMAL                      UNIQUE    VALID

--- 实验结果表明，先创建的唯一索引不受约束失效的影响

-- 创建主键时可以指定创建的隐式索引所在表空间
-- 也可以直接指定已经存在的唯一或非唯一索引
SQL> alter table t add constraint pk_t primary key (a) using index tablespace idx;

[索引相关] 索引的分类

逻辑上：

Single column 单行索引

Concatenated 多行索引

Unique 唯一索引

NonUnique 非唯一索引

Function-based函数索引

Domain 域索引

物理上：

Partitioned 分区索引

NonPartitioned 非分区索引

B-tree：

Normal 正常型B树

Rever Key 反转型B树

Bitmap 位图索引

索引结构：

B-tree：

适合与大量的增、删、改（OLTP）；

不能用包含OR操作符的查询；

适合高基数的列（唯一值多）

典型的树状结构；

每个结点都是数据块；

大多都是物理上一层、两层或三层不定，逻辑上三层；

叶子块数据是排序的，从左向右递增；

在分支块和根块中放的是索引的范围；

Bitmap:

适合与决策支持系统；

做UPDATE代价非常高；

非常适合OR操作符的查询；

基数比较少的时候才能建位图索引；

树型结构：

索引头

开始ROWID，结束ROWID（先列出索引的最大范围）

BITMAP

每一个BIT对应着一个ROWID，它的值是1还是0，如果是1，表示着BIT对应的ROWID有值

登録: 投稿 (Atom)

2011年7月31日日曜日

2011年7月30日土曜日

自己紹介