物理rowid两种格式:
受限rowid (8i之前)
扩展rowid (8i以后)
Extended rowids use a base 64 encoding of the
physical address for each row selected. The
encoding characters are A-Z, a-z, 0-9, +, and /
rowid :在oracle中称伪列,不是创建表的时候的某个列。如果要删除两行相同的数据,可以运用rowid。
rowid记录这一行数据在磁盘上的实际存储的物理位置。desc t和 select * from t均查不出来rowid
SQL> connect scott/tiger;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
SQL>
SQL>
SQL> select t3.* ,rowid from t3;
ID NAME ROWID
-- -------------------------------
1 a AAAM40AAGAAAAAMAAA
2 b AAAM40AAGAAAAAMAAB
3 c AAAM40AAGAAAAAMAAC
--------------------------------
以扩展rowid格式为例: AAAM40AAGAAAAAMAAA
rowid一共18位,分为四个部分
AAAM40: 数据对象编号(data object number) :表示的是t2这个表段的编号,在dba_objects这个视图中,用 data_object_id表示。注意 object_id是对象编号,data_object_id是数据段编号,
它跟物理存储位置有关。
The data object number that identifies the database segment (AAAM40 in the example). Schema objects in thesame segment, such as a cluster of tables,have the same data object number.
AAG : 相对文件编号 (relative file number)
AAAAAM: 包含数据行的数据块编号(block number)
AAA : 行编号( row number)
SQL> select object_id,data_object_id from dba_objects where object_name='T2';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
52657 52786
SQL> alter table t2 rename to t3;
Table altered
SQL>
SQL> select object_id,data_object_id from dba_objects where object_name='T3';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
52657 52786
SQL>
SQL> select TABLESPACE_NAME from user_tables where table_name='T3';
TABLESPACE_NAME
------------------------------
USERS
SQL> alter table t3 move tablespace TT;
Table altered
可以看到,在移动表空间后,数据段发生了变化,因此对象编号也发生了变化,如下:
SQL> select t3.* ,rowid from t3;
ID NAME ROWID
-- -------------------------------
1 a AAAM40AAGAAAAAMAAA
2 b AAAM40AAGAAAAAMAAB
3 c AAAM40AAGAAAAAMAAC
SQL> select object_id,data_object_id from dba_objects where object_name='T3';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
52657 52787
=====================================================
哪些操作会使数据对象(段)编号发生变化呢 ?
SQL> truncate table t3;
Table truncated
SQL> select object_id,data_object_id from dba_objects where object_name='T3';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
52657 52788
truncate操作是ddl命令,只修改元数据(元数据放在数据字典中,存放在system表空间中),不涉及到修改表数据。
truncate t3 时,只修改system表空间对t3表的描述。实际上,把原来的T3表重命名,然后重新创建了
一个新的空表T3 ,所以truncate 很快,truncate一个几百G的表和truncate 几条数据的表时间差不多。
====================================================
delete 表的某一列数据,oracle到底做了什么?
先把这一列所占的空间释放掉,其次要把元数据删除掉,所以在大表上删除某一列,非常慢。
SQL> show user;
User is "scott"
SQL> select count(*) from t;
COUNT(*)
----------
1611552
SQL> select bytes/1024/1024 M from dba_segments where segment_name='T';
M
----------
178
SQL> desc t;
Name Type Nullable Default Comments
-------------- ------------- -------- ------- --------
OWNER VARCHAR2(30) Y
OBJECT_NAME VARCHAR2(128) Y
SUBOBJECT_NAME VARCHAR2(30) Y
OBJECT_ID NUMBER Y
DATA_OBJECT_ID NUMBER Y
OBJECT_TYPE VARCHAR2(19) Y
CREATED DATE Y
LAST_DDL_TIME DATE Y
TIMESTAMP VARCHAR2(19) Y
STATUS VARCHAR2(7) Y
TEMPORARY VARCHAR2(1) Y
GENERATED VARCHAR2(1) Y
SECONDARY VARCHAR2(1) Y
SQL> execute dbms_stats.gather_table_stats('SCOTT','T');
SQL> alter table t drop column OBJECT_TYPE;
Table altered
SQL>
SQL>
SQL> execute dbms_stats.gather_table_stats('SCOTT','T');
PL/SQL procedure successfully completed
SQL> select num_rows,blocks from user_tables where table_name='T';
NUM_ROWS BLOCKS
---------- ----------
3225195 44287
SQL>
可以看到blocks没有发生变化,但是num_rows发生了变化(有可能比之前的值更大)
=========================
大表删除列
1:删除列,但不释放空间
SQL> desc t;
Name Type Nullable Default Comments
-------------- ----------- -------- ------- --------
OBJECT_ID NUMBER Y
DATA_OBJECT_ID NUMBER Y
CREATED DATE Y
LAST_DDL_TIME DATE Y
STATUS VARCHAR2(7) Y
TEMPORARY VARCHAR2(1) Y
GENERATED VARCHAR2(1) Y
SECONDARY VARCHAR2(1) Y
SQL>
SQL>
SQL> alter table t set unused column status;
oracle执行这句话,不释放空间,它只重命名了列,可以从select * from dba_tab_cols where wner='SCOTT' 验证
SQL> desc t;
Name Type Nullable Default Comments
-------------- ----------- -------- ------- --------
OBJECT_ID NUMBER Y
DATA_OBJECT_ID NUMBER Y
CREATED DATE Y
LAST_DDL_TIME DATE Y
TEMPORARY VARCHAR2(1) Y
GENERATED VARCHAR2(1) Y
SECONDARY VARCHAR2(1) Y
2:使用这个命令以后,就将原来的status列删除掉了。(释放了空间)
SQL> alter table t drop unused columns;
Table altered
然后用 select * from dba_tab_cols where wner='SCOTT' 可以验证
========
3:将表空间offline后,可以删除表中的数据 (为什么?)
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT USERS
EMP USERS
BONUS USERS
SALGRADE USERS
EMPLOYEES USERS
T USERS
PLCH_COMMISSIONS USERS
PLCH_SALARIES USERS
T3 TT
9 rows selected
SQL> alter tablespace USERS offline;
Tablespace altered
SQL> select count(*) from t;
select count(*) from t
ORA-00376: 此时无法读取文件 4
ORA-01110: 数据文件 4: 'C:\ORADATA\ORCL\USERS01.DBF'
SQL>
SQL> desc t;
Name Type Nullable Default Comments
-------------- ----------- -------- ------- --------
OBJECT_ID NUMBER Y
DATA_OBJECT_ID NUMBER Y OBJECT_ID
CREATED DATE Y
LAST_DDL_TIME DATE Y
TEMPORARY VARCHAR2(1) Y
GENERATED VARCHAR2(1) Y
SECONDARY VARCHAR2(1) Y
SQL>
SQL> drop table t;
Table dropped
SQL>
SQL> flashback table t to before drop;
Done
SQL> alter tablespace users online;
Tablespace altered
SQL> select count(*) from t;
COUNT(*)
----------
3222976
将表空间offline以后,删除表是ddl操作,操作的是元数据,它存放在system表空间中。
====================================================================
AAG : 相对文件编号 (relative file number) ,用dba_data_files 中的relative_fno字段表示。
一个database最多包含65536个数据文件,一个表空间最多包含1023个数据文件。
因为G 转换为十进制就是6,那么AAG就表示6号数据文件
SQL> select file_name,relative_fno from dba_data_files where tablespace_name='TT';
FILE_NAME RELATIVE_FNO
-------------------------------------------------
C:\ORADATA\ORCL\TT.DBF 6
AAAAAM : 数据块编号(block number) ,M转换为十进制是12
可以看到
SQL> select t3.* ,rowid from t3;
ID NAME ROWID
-------------------------
1 a AAAM40AAGAAAAAMAAA
(1,'a')这条数据存放在6号数据文件的第12号块上
AAA : 行编号( row number)
可以看到该数据文件块大小为8k ,一个数据块可以存放很多行数据,它表示在块上的第几条数据。
AAA转换后是0,0 表示在这个块上的第1条数据
SQL> show parameter db_block_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
rowid是64进制,编码格式
A~Z 0~25
a~z 26~51
0~9 52~61
+ 62
/ 63
SQL> select t3.* ,rowid from t3;
ID NAME ROWID
-------------------------
1 a AAAM40AAGAAAAAMAAA
AAAM40 AAG AAAAAM AAA
通过dba_objects 视图,查看到data_object_id 为52788 ,那么现在看下64进制数和十进制如何转换的,
AAAM40 对应的十进制编码为
A A A M 4 0
0 0 0 12 56 52
SQL> select 12*64*64+56*64+52 from dual;
12*64*64+56*64+52
-----------------
52788
其实oracle也提供了一些包,计算rowid
包 dbms_rowid 里面含有很多函数
SQL> desc dbms_rowid;
Element Type
---------------------- ---------
ROWID_TYPE_RESTRICTED CONSTANT
ROWID_TYPE_EXTENDED CONSTANT
ROWID_IS_VALID CONSTANT
ROWID_IS_INVALID CONSTANT
ROWID_OBJECT_UNDEFINED CONSTANT
ROWID_CONVERT_INTERNAL CONSTANT
ROWID_CONVERT_EXTERNAL CONSTANT
ROWID_INVALID EXCEPTION
ROWID_BAD_BLOCK EXCEPTION
ROWID_CREATE FUNCTION
ROWID_INFO PROCEDURE
ROWID_TYPE FUNCTION
ROWID_OBJECT FUNCTION
ROWID_RELATIVE_FNO FUNCTION
ROWID_BLOCK_NUMBER FUNCTION
ROWID_ROW_NUMBER FUNCTION
ROWID_TO_ABSOLUTE_FNO FUNCTION
ROWID_TO_EXTENDED FUNCTION
ROWID_TO_RESTRICTED FUNCTION
ROWID_VERIFY FUNCTION
SQL>
SQL> select t3.*,rowid from t3;
ID NAME ROWID
----------------------------------
1 a AAAM40AAGAAAAAMAAA
2 b AAAM40AAGAAAAAMAAB
3 c AAAM40AAGAAAAAMAAC
SQL>
SQL> select dbms_rowid.rowid_object(rowid) obj, dbms_rowid.rowid_relative_fno(rowid) fno,
2 dbms_rowid.rowid_block_number(rowid) bno, dbms_rowid.rowid_row_number(rowid) rno
3 from t3;
OBJ FNO BNO RNO
---------- ---------- ---------- ----------
52788 6 12 0
52788 6 12 1
52788 6 12 2
也可以通过dbms_rowid这个包来构造rowid 。
如下就构造了t3表的第一行数据的rowid信息
SQL> select dbms_rowid.rowid_create(1,52788,6,12,0) from dual;
DBMS_ROWID.ROWID_CREATE(1,5278
------------------------------
AAAM40AAGAAAAAMAAA
其中1表示rowid的两种类型(0表示受限制,1表示扩展)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24862808/viewspace-730784/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24862808/viewspace-730784/