chencvgk

浏览: 15505 次
性别:
来自: 苏州

最近访客更多访客>>

loginboot

大家都说我很棒

wangpengxiao

417755712

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

Oralce 索引

选择性

索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。

一个索引的选择性越接近于1，这个索引的效率就越高。

如果是使用基于cost的最优化，优化器不应该使用选择性不好的索引。如果是使用基于rule的最优化，优化器在确定执行路径时不会考虑索引的选择性（除非是唯一性索引），并且不得不手工优化查询以避免使用非选择性的索引。

确定索引的选择性，可以有两种方法：手工测量和自动测量。

1)手工测量索引的选择性

如果要根据一个表的两列创建两列并置索引，可以用以下方法测量索引的选择性：

列的选择性=不同值的数目/行的总数 /* 越接近1越好 */

select count(distinct 第一列||'%'||第二列)/count(*) from 表名

如：

select count(distinct status||'%'||owner)/count(*)from test;

如果我们知道其中一列索引的选择性（例如其中一列是主键），那么我们就可以知道另一列索引的选择性。

手工方法的优点是在创建索引前就能评估索引的选择性。

2)自动测量索引的选择性

如果分析一个表，也会自动分析所有表的索引。

第一，为了确定一个表的确定性，就要分析表。

analyze table 表名 compute statistics

第二，确定索引里不同关键字的数目：

select distinct_keys

from user_indexes

where table_name='表名'

and index_name='索引名'

第三，确定表中行的总数：

select num_rows

from user_tables

where table_name='表名'

第四，索引的选择性=索引里不同关键字的数目/表中行的总数：

select i.distinct_keys/t.num_rows

from

user_indexes i,

user_tables t

where i.table_name='表名'

and i.index_name='索引名'

and i.table_name=t.table_name

第五，可以查询USER_TAB_COLUMNS以了解每个列的选择性。

表中所有行在该列的不同值的数目：

select

column_name,

num_distinct

from user_tab_columns

where table_name='表名'

列的选择性=NUM_DISTINCT/表中所有行的总数，查询USER_TAB_COLUMNS有助测量每个列

的选择性，但它并不能精确地测量列的并置组合的选择性。要想测量一组列的选择性，需要

采用手工方法或者根据这组列创建一个索引并重新分析表。

集群因子

http://www.cnblogs.com/Richardzhu/articles/2874972.html

一、本文说明：

今天在做测试的时候发现字段上有索引，但是执行计划就是不走索引，经过在网上查找才发现原来是索引的集群因子过高导致的。本文属于转载+模拟。

二、官网说明

The index clustering factor measures row order in relation to an indexed value suches employee last name.The more order that exists in rowstorage for this value,the lower the clustering factor.

----row存储的越有序，clustering factor的值越低。

The clustering factor is useful as a rough measure of the number of I/Os required to read an entire table by means of an index:

(1)、If the clustering factor is high,then Oracle Database performs a relatively high number of I/Os during a large index range scan.The index entriespoint to random table blocks,so the database may have to read and reread the same blocks over and over again to retrieve the data pointed to by the index.

----当clustering factor很高时，说明index entry (rowid) 是随机指向一些block的，在一个大的index range scan时，这样为了读取这些rowid指向的block，就需要一次又一次重复的去读这些block。

(2)、If the clustering factor is low,then Oracle Database performs a relatively low number of I/Os during a large index range scan.The index keys in arange tend to point to the same data blcok,so the database does not have to read and reread the same blocks over and over.

----当clustering factor值低时，说明index keys (rowid) 是指向的记录是存储在相同的block里，这样去读row时，只需要在同一个block里读取就可以了，这样减少重复读取blocks的次数。

The clustering factor is relevant for index scans because it can show:

(1)、Whether the database will use an index for large range scans;

(2)、The degree of table organization in relation to the index key;

(3)、Whether you should consider using an index-organized table,partitioning,or table cluster if rows must be ordered by the index key.

三、Index Clustering Factor说明

简单的说，Index Clustering Factor是通过一个索引扫描一张表，需要访问的表的数据块的数量，即对I/O的影响，也代表索引键存储位置是否有序。

(1)、如果越有序，即相邻的键值存储在相同的block，那么这时候Clustering Factor的值就越低；

(2)、如果不是很有序，即键值是随机的存储在block上，这样在读取键值时，可能就需要一次又一次的去访问相同的block，从而增加了I/O。

Clustering Factor的计算方式如下：

(1)、扫描一个索引(large index range scan)；

(2)、比较某行的rowid和前一行的rowid，如果这两个rowid不属于同一个数据块，那么cluster factor增加1；

(3)、整个索引扫描完毕后，就得到了该索引的clustering factor。

如果clustering factor接近于表存储的块数，说明这张表是按照索引字段的顺序存储的。

如果clustering factor接近于行的数量，那说明这张表不是按索引字段顺序存储的。

在计算索引访问成本的时候，这个值十分有用。Clustering Factor乘以选择性参数(selectivity)就是访问索引的开销。

如果这个统计数据不能真实反映出索引的真实情况，那么可能会造成优化器错误的选择执行计划。另外如果某张表上的大多数访问是按照某个索引做索引扫描，那么将该表的数据按照索引字段的顺序重新组织，可以提高该表的访问性能。

四、测试

4.1、产生问题：

  ----查看一下数据库的版本----
  1 SQL> select * from v$version where rownum=1;
  2 
  3 BANNER
  4 --------------------------------------------------------------------------------
  5 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  6 
   ----创建一张测试表jack----
  7 SQL> create table jack as select * from dba_objects where 1=2;
  8 
  9 Table created.
 10 
  ----将数据无序的插入jack表中----
 11 SQL> begin
 12   2      for i in 1..10 loop
 13   3        insert /*+ append */ into jack select * from dba_objects order by i;
 14   4      commit;
 15   5    end loop;
 16   6  end;
 17   7  /
 18 
 19 PL/SQL procedure successfully completed.
 20 
 21 SQL> select count(*) from jack;
 22 
 23   COUNT(*)
 24 ----------
 25     725460
 26 
  ----查看一下表的大小-----
 27 SQL> set wrap off
 28 SQL> col owner for a10;
 29 SQL> col segment_name for a15;
 30 SQL> select segment_name,blocks,extents,bytes/1024/1024||'M' "size" from user_segments where segment_name='JACK';
 31 
 32 SEGMENT_NAME     BLOCKS    EXTENTS   size
 33 ------------- ---------- ---------- --------
 34 JACK             11264       82      88M
 35 
  ----在object_id上创建索引----
 36 SQL> create index jack_ind on jack(object_id);
 37 
 38 Index created.
 39 
  ----查看一下索引的大小----
 40 SQL> select segment_name,segment_type,blocks,extents,bytes/1024/1024||'M' "size" from user_segments where segment_name='JACK_IND';
 41 
 42 SEGMENT_NAME    SEGMENT_TYPE      BLOCKS     EXTENTS     size
 43 ------------ ------------------ ---------- ---------- ---------
 44 JACK_IND           INDEX           1664         28        13M
  ----在没有收集相关的统计信息之前，查看一下index clustering factor----
 45 SQL> select index_name,clustering_factor,num_rows from user_indexes where index_name='JACK_IND';
 46 
 47 INDEX_NAME      CLUSTERING_FACTOR   NUM_ROWS
 48 --------------- ----------------- ----------
 49 JACK_IND              725460         725460
 50 
  ----简单的收集一下统计信息----
 51 SQL> exec dbms_stats.gather_table_stats(user,'jack',cascade=>true);
 52 
 53 PL/SQL procedure successfully completed.
 54 
  ----再次查看index clustering factor----
 55 SQL> select index_name,clustering_factor,num_rows from user_indexes where index_name='JACK_IND';
 56 
 57 INDEX_NAME     CLUSTERING_FACTOR   NUM_ROWS
 58 -------------- ----------------- ----------
 59 JACK_IND              725460       725460       ----显然统计信息收集前和后，clustering factor值不变，说在创建索引的时候，会收集表中的数据真正的行数。并且这里的clustering factor等num_rows，也说明表的clustering factor是无序的。
 60 
  ----查看一个确定值，然后查看执行计划----
 61 SQL> explain plan for select * from jack where object_id=1501;
 62 
 63 Explained.
 64 
 65 SQL> select * from table(dbms_xplan.display);
 66 
 67 PLAN_TABLE_OUTPUT
 68 --------------------------------------------------------------------------------
 69 Plan hash value: 2860868395
 70 
 71 --------------------------------------------------------------------------------
 72 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Ti
 73 --------------------------------------------------------------------------------
 74 |   0 | SELECT STATEMENT        |           |    10 |   970 |    13     (0)| 00
 75 |   1 |  TABLE ACCESS BY INDEX ROWID| JACK     |    10 |   970 |    13     (0)| 00
 76 |*  2 |   INDEX RANGE SCAN        | JACK_IND |    10 |       |     3     (0)| 00
 77 --------------------------------------------------------------------------------
 78 
 79 Predicate Information (identified by operation id):
 80 
 81 PLAN_TABLE_OUTPUT
 82 --------------------------------------------------------------------------------
 83 
 84 
 85    2 - access("OBJECT_ID"=1501)
 86 
 87 14 rows selected.                 ----在这里走了索引，cost为13.
 88 
 89 SQL> alter system flush buffer_cache;
 90 
 91 System altered.
 92 
 93 SQL> set autotrace traceonly;
  ----查询一个范围的执行计划----
 94 SQL> select * from jack where object_id>1000 and object_id<2000;
 95 
 96 9880 rows selected.
 97 
 98 
 99 Execution Plan
100 ----------------------------------------------------------
101 Plan hash value: 949574992
102 
103 --------------------------------------------------------------------------
104 | Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
105 --------------------------------------------------------------------------
106 |   0 | SELECT STATEMENT  |     |  9657 |   914K|  1824   (1)| 00:00:22 |
107 |*  1 |  TABLE ACCESS FULL| JACK |  9657 |   914K|  1824   (1)| 00:00:22 |
108 --------------------------------------------------------------------------
109 
110 Predicate Information (identified by operation id):
111 ---------------------------------------------------
112 
113    1 - filter("OBJECT_ID"<2000 AND "OBJECT_ID">1000)
114 
115 
116 Statistics
117 ----------------------------------------------------------
118       0   recursive calls
119       0   db block gets
120     10993 consistent gets
121     10340 physical reads
122       0   redo size
123    471945 bytes sent via SQL*Net to client
124     7657  bytes received via SQL*Net from client
125     660   SQL*Net roundtrips to/from client
126       0   sorts (memory)
127       0   sorts (disk)
128     9880  rows processed       ----注意，object_id上是有索引的，但是这里并没有使用索引，而是使用了全表扫描。
129 
130 SQL> alter system flush buffer_cache;
131 
132 System altered.
133 
 ----强制走索引，查看执行计划----
134 SQL> select /*+ index(jack jack_ind) */ * from jack where object_id>1000 and object_id<2000;
135 
136 9880 rows selected.
137 
138 
139 Execution Plan
140 ----------------------------------------------------------
141 Plan hash value: 2860868395
142 
143 ----------------------------------------------------------------------------------------
144 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
145 ----------------------------------------------------------------------------------------
146 |   0 | SELECT STATEMENT        |           |  9657 |   914K|  9683     (1)| 00:01:57 |
147 |   1 |  TABLE ACCESS BY INDEX ROWID| JACK     |  9657 |   914K|  9683     (1)| 00:01:57 |
148 |*  2 |   INDEX RANGE SCAN        | JACK_IND |  9657 |       |    24     (0)| 00:00:01 |
149 ----------------------------------------------------------------------------------------
150 
151 Predicate Information (identified by operation id):
152 ---------------------------------------------------
153 
154    2 - access("OBJECT_ID">1000 AND "OBJECT_ID"<2000)
155 
156 
157 Statistics
158 ----------------------------------------------------------
159       0    recursive calls
160       0    db block gets
161     10561  consistent gets
162     164    physical reads
163       0    redo size
164    988947  bytes sent via SQL*Net to client
165     7657   bytes received via SQL*Net from client
166     660    SQL*Net roundtrips to/from client
167       0    sorts (memory)
168       0    sorts (disk)
169     9880   rows processed    
----强制走索引之后，使用了index range scan,但是cost变成了9683，而全表扫描时是1824.
----还有比较一下两次查询中物理读的情况：全表扫描的物理读明显比索引的要高很多，但是Oracle却没有使用索引。
----因此Oracle认为走索引的Cost比走全表扫描大，而是大N倍，CBO是基于Cost来决定执行计划的。
----由此得出，对于索引的Cost，Oracle是根据clustering factor参数来计算的，而该实验中的clustering factor参数是很高的，数据存储无序。这就造成了Oracle认为走索引的cost比全表扫描的大。

4.2、解决问题：

  ----通过上面的分析，可以看出，要降低clustering factor才能解决问题，而要解决clustering factor，就需要重新对表的存储位置进行排序。----
  ----重建jakc表----
  1 SQL> create table echo as select * from jack where 1=0;
  2 
  3 Table created.
  4 
  5 SQL> insert /*+ append */ into echo select * from jack order by object_id;
  6 
  7 725460 rows created.
  8 
  9 SQL> commit;
 10 
 11 Commit complete.
 12 
 13 SQL> truncate table jack;
 14 
 15 Table truncated.
 16 
 17 SQL> insert /*+ append */ into jack select * from echo;
 18 
 19 725460 rows created.
 20 
 21 SQL> commit;
 22 
 23 Commit complete.
 24 
  ----查看表和索引的信息----
 25 SQL> select segment_name,blocks,extents,bytes/1024/1024||'M' "size" from user_segments where segment_name='JACK';
 26 
 27 SEGMENT_NAME    BLOCKS    EXTENTS     size
 28 ------------- ---------- ---------- -----------
 29 JACK             11264       82        88M
 30 
 31 SQL> select segment_name,segment_type,blocks,extents,bytes/1024/1024||'M' "size" from user_segments where segment_name='JACK_IND';
 32 
 33 SEGMENT_NAME    SEGMENT_TYPE      BLOCKS     EXTENTS    size
 34 ------------ ------------------ ---------- ---------- -------------
 35 JACK_IND            INDEX          1536          27     12M
 36 
 37 SQL> select index_name,clustering_factor,num_rows from user_indexes where index_name='JACK_IND';
 38 
 39 INDEX_NAME     CLUSTERING_FACTOR NUM_ROWS
 40 ------------- ----------------- ----------
 41 JACK_IND             725460      725460
 42 
  ----对索引进行rebuild----
 43 SQL> alter index jack_ind rebuild;
 44 
 45 Index altered.
 46 
  ----查看cluster factor----
 47 SQL> select index_name,clustering_factor,num_rows from user_indexes where index_name='JACK_IND';
 48 
 49 INDEX_NAME      CLUSTERING_FACTOR  NUM_ROWS
 50 --------------- ----------------- ----------
 51 JACK_IND               10327       725460    ------注意这里的Factor，已经变成10327，我们收集一下表的统计信息，然后与表的block进行一次比较。
 52 
 53 SQL> exec dbms_stats.gather_table_stats(user,'jack',cascade=>true);
 54 
 55 PL/SQL procedure successfully completed.
 56 
 57 SQL> select blocks from dba_tables where table_name='JACK';
 58 
 59     BLOCKS
 60 ----------
 61      10474   ----表jack实际使用的block是10474，clustering factor是10327基本还是比较接近了，这也说明相邻的row是存储在相同的block里。
 62 
 63 SQL> select index_name,clustering_factor,num_rows from user_indexes where index_name='JACK_IND';
 64 
 65 INDEX_NAME               CLUSTERING_FACTOR   NUM_ROWS
 66 ------------------------------ ----------------- ----------
 67 JACK_IND                   10327     725460
 68 
 69 SQL> alter system flush buffer_cache;
 70 
 71 System altered.
 72 
 73 SQL> set autotrace traceonly;
  ----再次查看之前sql的执行计划----
 74 SQL> select * from jack where object_id>1000 and object_id<2000;
 75 
 76 9880 rows selected.
 77 
 78 
 79 Execution Plan
 80 ----------------------------------------------------------
 81 Plan hash value: 2860868395
 82 
 83 ----------------------------------------------------------------------------------------
 84 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
 85 ----------------------------------------------------------------------------------------
 86 |   0 | SELECT STATEMENT        |           |  9657 |   914K|   162     (0)| 00:00:02 |
 87 |   1 |  TABLE ACCESS BY INDEX ROWID| JACK     |  9657 |   914K|   162     (0)| 00:00:02 |
 88 |*  2 |   INDEX RANGE SCAN        | JACK_IND |  9657 |       |    24     (0)| 00:00:01 |
 89 ----------------------------------------------------------------------------------------
 90 
 91 Predicate Information (identified by operation id):
 92 ---------------------------------------------------
 93 
 94    2 - access("OBJECT_ID">1000 AND "OBJECT_ID"<2000)
 95 
 96 
 97 Statistics
 98 ----------------------------------------------------------
 99       1   recursive calls
100       0   db block gets
101     1457  consistent gets
102     151   physical reads
103       0   redo size
104   988947  bytes sent via SQL*Net to client
105     7657  bytes received via SQL*Net from client
106     660   SQL*Net roundtrips to/from client
107       0   sorts (memory)
108       0   sorts (disk)
109     9880  rows processed

----注意这里的cost已经降到了162，性能提升还是非常明显。

五、小结

通过以上说明和测试，可以看到clustering factor也是索引健康的一个重要判断的标准。其值越低越好。它会影响CBO选择正确的执行计划。但是注意一点，clustering factor总是趋势与不断恶化的。

ROWID

搞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*64²+0*64¹+5*64⁰=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 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,大文件表空间下的Oracle rowid。大文件的表空间是Oracle 10g的新特性,关于该特性暂且不作过多表述，简单一句话来说，同小文件类型的表空间(数据库默认的表空间类型)相比，该类型的表空间只能包含一个而且最多只能有一个数据文件。正是因为如此，所以位于大文件类型表空间下的表的rowid显得有些特殊，接下来我们探讨究竟特殊在哪儿？

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

01

SQL>
 conn /assysdba;

02

Connected.

03

SQL>select*fromv$version;

04

BANNER

05

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

06

OracleDatabase11g
 Enterprise Edition Release 11.2.0.1.0 - 64bit Production

07

PL/SQL
 Release 11.2.0.1.0 - Production

08

CORE
 11.2.0.1.0 Production

09

TNSforLinux:
 Version 11.2.0.1.0 - Production

10

NLSRTL
 Version 11.2.0.1.0 - Production

11

SQL>createbigfile
 tablespace big_tbs datafilesize10m
 autoextendon;

12

Tablespace
 created.

13

SQL>selectd.file_id,d.tablespace_name,t.bigfile

14

2fromdba_data_files
 d,dba_tablespaces t

15

3whered.tablespace_name=t.tablespace_name

16

4orderby1

17

5
 ;

18

FILE_ID
 TABLESPACE_NAME BIG

19

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

20

1
 SYSTEMNO

21

2
 SYSAUXNO

22

3
 UNDOTBS1NO

23

4
 USERSNO

24

5
 EXAMPLENO

25

6
 UNDOTBS2NO

26

7
 TEST_TBSNO

27

8
 TEST_TBSNO

28

9
 BIG_TBS YES

29

9rowsselected.

30

SQL>alteruserhr
 quota unlimitedonbig_tbs;

31

Useraltered.

32

SQL>
 conn hr/hr

33

Connected.

34

SQL>createtablebigfile_tab(id
 number,namevarchar2(10))

35

2
 tablespace big_tbs

36

3
 ;

37

Tablecreated.

38

SQL>insertintobigfile_tabvalues(1,'oracle');

39

1
 row created.

40

SQL>insertintobigfile_tabvalues(2,'oracle');

41

1
 row created.

42

SQL>commit;

43

Commitcomplete.

44

SQL>

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

01

SQL>selectid,rowidfrombigfile_tab;

02

ID
 ROWID

03

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

04

1
 AAAUHfAAAAAAACGAAA

05

2
 AAAUHfAAAAAAACGAAB

06

SQL>selectrowid,

07

2
 dbms_rowid.rowid_object(rowid) object_id,

08

3
 dbms_rowid.rowid_relative_fno(rowid) file_id,

09

4
 dbms_rowid.rowid_block_number(rowid) block_id ,

10

5
 dbms_rowid.rowid_row_number(rowid) num

11

6frombigfile_tab

12

7
 ;

13

ROWID
 OBJECT_ID FILE_ID BLOCK_ID NUM

14

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

15

AAAUHfAAAAAAACGAAA
 82399 0 134 0

16

AAAUHfAAAAAAACGAAB
 82399 0 134 1

17

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的探讨一、二、三系列结束。如果，大家对该系列有不同的理解，或认为本人理解有误的地方，还请不吝指正！！！

**B*Tree索引**

B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。B-Tree索引是基于二叉树的，由分支块（branch block）和叶块（leaf block）组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列（关键字）范围和另一索引块的地址。

假设我们要找索引中值为80的行，从索引树的最上层入口开始，定位到大于等于50，然后往左找，找到第2个分支块，定位为75－100，最后再定位到叶块上，找到80所对应的rowid，然后根据rowid去读取数据块获取数据。如果查询条件是范围选择的，比如where column >20 and column <80，那么会先定位到第一个包含20的叶块，然后横向查找其他的叶块，直到找到包含80的块为止，不用每次都从入口进去再重新定位。

各种索引使用场合及建议

（1）B*Tree索引。

常规索引，多用于oltp系统，快速定位行，应建立于高cardinality列（即列的唯一值除以行数为一个很大的值，存在很少的相同值）。

（2）反向索引。

B*Tree的衍生产物，应用于特殊场合，在ops环境加序列增加的列上建立，不适合做区域扫描。

（3）降序索引。

B*Tree的衍生产物，应用于有降序排列的搜索语句中，索引中储存了降序排列的索引码，提供了快速的降序搜索。

（4）位图索引。

位图方式管理的索引，适用于OLAP（在线分析）和DSS（决策处理）系统，应建立于低cardinality列，适合集中读取，不适合插入和修改，提供比B*Tree索引更节省的空间。

（5）函数索引。

B*Tree的衍生产物，应用于查询语句条件列上包含函数的情况，索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率。

分享到：

Java 插入mysql中文乱码解决 | 模板方法(Template Method)

2013-05-20 11:44
浏览 185
评论(0)
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

Oralce 索引

选择性

集群因子

ROWID

**B*Tree索引**

各种索引使用场合及建议

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

Oralce 索引

选择性

集群因子

ROWID

B*Tree索引

各种索引使用场合及建议

评论

发表评论

相关推荐

最近访客更多访客>>

**B*Tree索引**