 | | |
| | |
|
Spaces home  Fan's SpacePhotosProfileFriendsMore |  Tools   |
Fan's SpaceWelcome !
|
July 03 oracle 查找数据存储学习oracle,知道数据放入表中，表存储在datafile里面，
datafile是一如何的形式组织数据的呢？
对于表test:
SQL> select rowid,col1,col2 from test;
ROWID COL1 COL2
-------------------------------- ---------- -------- AAARJ+AAFAAAAANAAA 1 a AAARJ+AAFAAAAANAAB 2 a AAARJ+AAFAAAAANAAC 3 a AAARJ+AAFAAAAANAAD 4 a AAARJ+AAFAAAAANAAE 5 a AAARJ+AAFAAAAANAAF 6 b AAARJ+AAFAAAAANAAG 7 b AAARJ+AAFAAAAANAAH 8 c AAARJ+AAFAAAAANAAI 9 c AAARJ+AAFAAAAANAAJ d AAARJ+AAFAAAAANAAK e 我们要知道其datafile以及数据所在的的block：
而从ROWID中很容易得出为该表所在的数据文件id为： 5
SQL> run
1 begin 2 dbms_output.put_line(get_rowid('AAARJ+AAFAAAAANAAA')); 3* end; Object# is :70270 Relative_fno is :5 Block number is :13 Row number is :0 从而知道block id为 13
SQL> alter system dump datafile 5 block 13;
System altered.
查看trace 文件：
block_row_dump:
tab 0, row 0, @0x1f90 tl: 8 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 02 col 1: [ 1] 61 tab 0, row 1, @0x1f88 tl: 8 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 03 col 1: [ 1] 61 tab 0, row 2, @0x1f80 tl: 8 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 04 col 1: [ 1] 61 tab 0, row 3, @0x1f78 tl: 8 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 05 col 1: [ 1] 61 tab 0, row 4, @0x1f70 tl: 8 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 06 col 1: [ 1] 61 tab 0, row 5, @0x1f68 tl: 8 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 07 col 1: [ 1] 62 tab 0, row 6, @0x1f60 tl: 8 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 08 col 1: [ 1] 62 tab 0, row 7, @0x1f58 tl: 8 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 09 col 1: [ 1] 63 tab 0, row 8, @0x1f50 tl: 8 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 0a col 1: [ 1] 63 tab 0, row 9, @0x1f4a tl: 6 fb: --H-FL-- lb: 0x0 cc: 2 col 0: *NULL* col 1: [ 1] 64 tab 0, row 10, @0x1f44 tl: 6 fb: --H-FL-- lb: 0x1 cc: 2 col 0: *NULL* col 1: [ 1] 65 end_of_block_dump End dump data blocks tsn: 6 file#: 5 minblk 13 maxblk 13 第一行数据（1，a）的存储格式为：
tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02 col 1: [ 1] 61 数据 '1' 的存储为 'c1 02' :
SQL> SELECT DUMP(1,16) FROM DUAL;
DUMP(1,16)
--------------------------------- Typ=2 Len=2: c1,2 数据 'a' 的存储是 '61' (6*96+1=97 97 正好对应字符a) 。
July 02 IOT类型的表空间要想查询表所在的表空间，一般通过数据字典
dba_tables/user_tables/all_tables
就可以查到。
但是，IOT类型的表空间通过user_tables居然查不到？
SQL> run
1* select table_name, tablespace_name from user_tables t
where t.table_name='DOCINDEX'
TABLE_NAME TABLESPACE_NAME
---------- ------------------------------------------------------------ DOCINDEX SQL>
方法一：
通过DBMS_METADATA.GET_DDL()函数来查看。
BEGIN
DBMS_OUTPUT.put_line(dbms_metadata.get_ddl('TABLE','DOCINDEX')) ; END; 输出：
CREATE TABLE "WANGFAN"."DOCINDEX"
( "TOKEN" CHAR(20), "DOC_ID" NUMBER, "TOKEN_FREQUENCY" NUMBER, "TOKEN_OFFSETS" VARCHAR2(512), CONSTRAINT "PK_DOCINDEX" PRIMARY KEY ("TOKEN", "DOC_ID") ENABLE ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TEST_SPACE" PCTTHRESHOLD 50 可以很清楚的看到tablespace为： test_space
方法二：
对于IOT表，我们知道该表类型的表值是存在index里的，
所以我们知道其index所在的tablespace即可。
SQL> SELECT tablespace_name
2 FROM user_indexes WHERE table_name='DOCINDEX' 3 AND index_type like 'IOT%'; TABLESPACE_NAME
------------------------------------------------------ TEST_SPACE B-Tree索引的优势Advantages of B-tree Structure
The B-tree structure has the following advantages:
■ All leaf blocks of the tree are at the same depth, so retrieval of any record from anywhere in the index takes approximately the same amount of time. 所有的叶子节点都在同一深度，所以对每个叶子节点的访问时间几乎一样。
■ B-tree indexes automatically stay balanced. 该索引自动保持平衡。树的平衡对于数据结构“树”这个概念有着很特殊的意义 。
■ All blocks of the B-tree are three-quarters full on the average. 所有的B树block平均承载量为3/4*BLOCK ■ B-trees provide excellent retrieval performance for a wide range of queries, including exact match and range searches.B-Tree为ｒａｎｇｅ 查询提高性能 ■ Inserts, updates, and deletes are efficient, maintaining key order for fast retrieval. ■ B-tree performance is good for both small and large tables and does not degrade as the size of a table grows. index block structure索引结构特点 The two kinds of blocks: Branck block用于路径查询 Leaf block用于存储索引值 Branck block 的存储内容如下 这个不太好翻译，我的理解就是一个东东，用来决定怎么找下面的叶子节点。 指向叶子节点的键值 Leaf block 的存储内容 All leaf blocks are at the same depth from the root branch block. Leaf June 30 create table as 是有局限性的大家一般习惯用
create table **
as
select ....
from
sourcetable;
很容易的创建一个表，
但是并不是所有的表都能够通过CREATE TABLE AS
来轻松的创建，今天下午我就碰到了个问题。
SQL> desc test_long;
Name Null? Type ----------------------------------------- -------- ---------------------------- COL1 LONG COL2 NUMBER SQL> run
1* select * from test_long COL1 COL2
---------- ---------- 1000 1 1002 1 1003 1 1004 1 SQL> create table backup_test_long
as
select * from test_long;
create table backup_test_long as select * from test_long * ERROR at line 1: ORA-00997: illegal use of LONG datatype 解决方法：
SQL> copy from wangfan/wangfan@orcl to wangfan/wangfan@orcl
append backup_test_long using select * from test_long;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0) Maximum long size is 80. (long is 80) Table BACKUP_TEST_LONG created. 4 rows selected from wangfan@orcl.
4 rows inserted into BACKUP_TEST_LONG. 4 rows committed into BACKUP_TEST_LONG at wangfan@orcl. SQL> select * from backup_test_long;
COL1 COL2
---------- ---------- 1000 1 1002 1 1003 1 1004 1
|
|||||||||||||
|
|
