[20130206]关于分区的简单探究.txt
http://uhesse.com/2013/01/25/brief-introduction-into-partitioning-in-oracle/
拿一个范围分区做一个简单的探究:
create table sales (id number, name varchar2(20),
amount_sold number, shop varchar2(20), time_id date)
partition by range (time_id)
(
partition q1 values less than (to_date('01.04.2012','dd.mm.yyyy')),
partition q2 values less than (to_date('01.07.2012','dd.mm.yyyy')),
partition q3 values less than (to_date('01.10.2012','dd.mm.yyyy')),
partition q4 values less than (to_date('01.01.2013','dd.mm.yyyy'))
);
insert into sales values ( 1, 'John Doe', 5000, 'London', date'2012-02-16' );
commit ;
select sum(amount_sold) from sales where time_id between date'2012-01-01' and date'2012-03-31';
--注意对象sales并没有占用表空间。
--可以发现第一行仅仅有object_id,而DATA_OBJECT_ID为NULL，仅仅分区上在有DATA_OBJECT_ID。
SQL> create index sales_id on sales (id);
Index created.
SQL> create index sales_name on sales (name) local;
Index created.
--第1个索引是全局的(global),而第2个索引是本地(local),两者的区别可以看原链接的图。
SQL> select rowid x ,a.* from sales a ;
X ID NAME AMOUNT_SOLD SHOP TIME_ID
------------------------------ ---------- ---------- ----------- -------------------- -------------------
AABBtRAAEAAAACGAAA 1 John Doe 5000 London 2012-02-16 00:00:00
SQL> @ lookup_rowid AABBtRAAEAAAACGAAA
OBJECT FILE BLOCK ROW
---------- ---------- ---------- ----------
269137 4 134 0
--data_object_id=269137, 说明在q1分区。
3.再来看看索引有什么区别？
-- 可以发现本地索引是在每个分区上都建立单独的索引。
--从dba_objects视图也可以发现，索引sales_name在每个分区上都有不同的DATA_OBJECT_ID。而sales_id仅仅一个。
4.看看两个索引的差别一些细节:
--users表空间是assm,我仅仅插入1条数据,其对应的信息应该保存在索引的root节点，在HEADER_BLOCK+1的位置。
SQL> alter system dump datafile 4 block 547;
System altered.
SQL> alter system dump datafile 4 block 1419;
System altered.
--SALES_ID是全局索引,col0:c1 02 ，正好对上。注意看col1: len=10,比普通索引的长度要长，以前一直是6.
--这是因为全部索引里面的数据data_object_id不一样，来自不同的分区。所以在索引键值中要记录它，这样
--长度会变大。
--与以上查询一致。
--再来看看本地索引:
--可以发现col1占用长度6,因为本地索引都是来自同一个段，col1不需要记录data_object_id.长度变小。
http://uhesse.com/2013/01/25/brief-introduction-into-partitioning-in-oracle/
拿一个范围分区做一个简单的探究:
create table sales (id number, name varchar2(20),
amount_sold number, shop varchar2(20), time_id date)
partition by range (time_id)
(
partition q1 values less than (to_date('01.04.2012','dd.mm.yyyy')),
partition q2 values less than (to_date('01.07.2012','dd.mm.yyyy')),
partition q3 values less than (to_date('01.10.2012','dd.mm.yyyy')),
partition q4 values less than (to_date('01.01.2013','dd.mm.yyyy'))
);
insert into sales values ( 1, 'John Doe', 5000, 'London', date'2012-02-16' );
commit ;
select sum(amount_sold) from sales where time_id between date'2012-01-01' and date'2012-03-31';
SQL> select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALLSTATS LAST PEEKED_BINDS advanced cost'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID adcg3z0z3sr6h, child number 0
-------------------------------------
select sum(amount_sold) from sales where time_id between
date'2012-01-01' and date'2012-03-31'
Plan hash value: 642363238
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 22 | | | | |
| 2 | PARTITION RANGE SINGLE| | 1 | 22 | 3 (0)| 00:00:01 | 1 | 1 |
|* 3 | TABLE ACCESS FULL | SALES | 1 | 22 | 3 (0)| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / SALES@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "SALES"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("TIME_ID">=TO_DATE(' 2012-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"TIME_ID"
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) SUM("AMOUNT_SOLD")[22]
2 - "AMOUNT_SOLD"[NUMBER,22]
3 - "AMOUNT_SOLD"[NUMBER,22]
--虽然做全表扫描，但是仅仅扫描一个分区。
SQL> select table_name, tablespace_name from user_tables where table_name='SALES';
TABLE_NAME TABLESPACE_NAME
-------------------- --------------------
SALES
--注意对象sales并没有占用表空间。
SQL> select table_name, partitioning_type, partition_count from user_part_tables where table_name='SALES';
TABLE_NAME PARTITION PARTITION_COUNT
-------------------- --------- ---------------
SALES RANGE 4
SQL> column edition_name noprint
SQL> column SUBOBJECT_NAME format a10
SQL> select * from dba_objects where wner=user and object_name='SALES';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE
------ -------------------- ---------- --------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ----------
SCOTT SALES 269136 TABLE 2013-02-06 17:12:49 2013-02-06 17:12:49 2013-02-06:17:12:49 VALID N N N 1
SCOTT SALES Q1 269137 269137 TABLE PARTITION 2013-02-06 17:12:49 2013-02-06 17:12:49 2013-02-06:17:12:49 VALID N N N 1
SCOTT SALES Q2 269138 269138 TABLE PARTITION 2013-02-06 17:12:49 2013-02-06 17:12:49 2013-02-06:17:12:49 VALID N N N 1
SCOTT SALES Q3 269139 269139 TABLE PARTITION 2013-02-06 17:12:49 2013-02-06 17:12:49 2013-02-06:17:12:49 VALID N N N 1
SCOTT SALES Q4 269140 269140 TABLE PARTITION 2013-02-06 17:12:49 2013-02-06 17:12:49 2013-02-06:17:12:49 VALID N N N 1
--可以发现第一行仅仅有object_id,而DATA_OBJECT_ID为NULL，仅仅分区上在有DATA_OBJECT_ID。
SQL> create index sales_id on sales (id);
Index created.
SQL> create index sales_name on sales (name) local;
Index created.
--第1个索引是全局的(global),而第2个索引是本地(local),两者的区别可以看原链接的图。
SQL> select rowid x ,a.* from sales a ;
X ID NAME AMOUNT_SOLD SHOP TIME_ID
------------------------------ ---------- ---------- ----------- -------------------- -------------------
AABBtRAAEAAAACGAAA 1 John Doe 5000 London 2012-02-16 00:00:00
SQL> @ lookup_rowid AABBtRAAEAAAACGAAA
OBJECT FILE BLOCK ROW
---------- ---------- ---------- ----------
269137 4 134 0
--data_object_id=269137, 说明在q1分区。
3.再来看看索引有什么区别？
SQL> select index_name,tablespace_name,status from user_indexes where table_name='SALES' and table_owner=user;
INDEX_NAME TABLESPACE_NAME STATUS
------------------------------ -------------------- --------
SALES_ID USERS VALID
SALES_NAME N/A
SQL> select index_name, partitioning_type, partition_count from user_part_indexes;
INDEX_NAME PARTITION PARTITION_COUNT
------------------------------ --------- ---------------
SALES_NAME RANGE 4
SQL> select index_name, partition_name, tablespace_name,status from user_ind_partitions;
INDEX_NAME PARTITION_NAME TABLESPACE_NAME STATUS
------------------------------ -------------------- -------------------- --------
SALES_NAME Q1 USERS USABLE
SALES_NAME Q2 USERS USABLE
SALES_NAME Q4 USERS USABLE
SALES_NAME Q3 USERS USABLE
-- 可以发现本地索引是在每个分区上都建立单独的索引。
SQL> SELECT object_name, subobject_name, object_id, data_object_id, object_type, status
FROM dba_objects WHERE object_name IN ('SALES_NAME', 'SALES_ID') AND wner = USER;
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE STATUS
-------------------- -------------------- --------- -------------- ------------------- -------
SALES_ID 269141 269141 INDEX VALID
SALES_NAME 269142 INDEX VALID
SALES_NAME Q1 269143 269143 INDEX PARTITION VALID
SALES_NAME Q2 269144 269144 INDEX PARTITION VALID
SALES_NAME Q3 269145 269145 INDEX PARTITION VALID
SALES_NAME Q4 269146 269146 INDEX PARTITION VALID
6 rows selected.
--从dba_objects视图也可以发现，索引sales_name在每个分区上都有不同的DATA_OBJECT_ID。而sales_id仅仅一个。
4.看看两个索引的差别一些细节:
SQL> SELECT segment_name, partition_name, segment_type, header_file, header_block, relative_fno
FROM dba_segments WHERE wner = USER AND segment_name IN ('SALES_NAME', 'SALES_ID') ORDER BY 1, 2;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK RELATIVE_FNO
------------------------ -------------------- ------------------ ----------- ------------ ------------
SALES_ID INDEX 4 546 4
SALES_NAME Q1 INDEX PARTITION 4 1418 4
SALES_NAME Q2 INDEX PARTITION 4 1426 4
SALES_NAME Q3 INDEX PARTITION 4 1498 4
SALES_NAME Q4 INDEX PARTITION 4 1506 4
SELECT * FROM dba_extents WHERE wner = USER AND segment_name IN ('SALES_NAME', 'SALES_ID') ORDER BY 1, 2;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------- -------------- --------------- ------------------ ---------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT SALES_ID INDEX USERS 0 4 544 65536 8 4
SCOTT SALES_NAME Q4 INDEX PARTITION USERS 0 4 1504 65536 8 4
SCOTT SALES_NAME Q3 INDEX PARTITION USERS 0 4 1496 65536 8 4
SCOTT SALES_NAME Q1 INDEX PARTITION USERS 0 4 1416 65536 8 4
SCOTT SALES_NAME Q2 INDEX PARTITION USERS 0 4 1424 65536 8 4
--users表空间是assm,我仅仅插入1条数据,其对应的信息应该保存在索引的root节点，在HEADER_BLOCK+1的位置。
SQL> alter system dump datafile 4 block 547;
System altered.
SQL> alter system dump datafile 4 block 1419;
System altered.
Block header dump: 0x01000223
Object id on Block? Y
seg/obj: 0x41b55 csc: 0x00.c00d2a3b itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000220 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.c00d2a3b
Leaf block dump
===============
header address 182921775716=0x2a96fcca64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 8016=0x1f50
kdxcoavs 7978
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8016] flag: ------, lock: 0, len=16
col 0; len 2; (2): c1 02
col 1; len 10; (10): 00 04 1b 51 01 00 00 86 00 00
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 547 maxblk 547
SQL> select dump(id,16) from sales;
DUMP(ID,16)
-----------------------------------
Typ=2 Len=2: c1,2
--SALES_ID是全局索引,col0:c1 02 ，正好对上。注意看col1: len=10,比普通索引的长度要长，以前一直是6.
--这是因为全部索引里面的数据data_object_id不一样，来自不同的分区。所以在索引键值中要记录它，这样
--长度会变大。
SQL> @16to10 00041b51
16 to 10 DEC
------------
269137
--正好与select * from dba_objects where wner=user and object_name='SALES';查询到的分区q1的data_object_id一致。
SQL> select rowid x ,a.* from sales a ;
X ID NAME AMOUNT_SOLD SHOP TIME_ID
------------------------------ ---------- --------- ----------- -------------------- -------------------
AABBtRAAEAAAACGAAA 1 John Doe 5000 London 2012-02-16 00:00:00
SQL> host cat dfb.sql
select
dbms_utility.data_block_address_file(to_number('&1','xxxxxxxxxxxxxxxx')) rfile#,
dbms_utility.data_block_address_block(to_number('&&1','xxxxxxxxxxxxxxxx')) block#
from dual;
SQL> @dfb 01000086
RFILE# BLOCK#
---------- ----------
4 134
--与以上查询一致。
--再来看看本地索引:
Block header dump: 0x0100058b
Object id on Block? Y
seg/obj: 0x41b57 csc: 0x00.c00d2a50 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000588 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.c00d2a50
Leaf block dump
===============
header address 182921775716=0x2a96fcca64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 8014=0x1f4e
kdxcoavs 7976
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8014] flag: ------, lock: 0, len=18
col 0; len 8; (8): 4a 6f 68 6e 20 44 6f 65
col 1; len 6; (6): 01 00 00 86 00 00
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 1419 maxblk 1419
--可以发现col1占用长度6,因为本地索引都是来自同一个段，col1不需要记录data_object_id.长度变小。
