首先创建一个基于虚拟列的分区表：
SQL> CREATE TABLE products (
2 product_id NUMBER(3) PRIMARY KEY,
3 product_desc VARCHAR2(25),
4 qty NUMBER(8,2),
5 rate NUMBER(10,2),
6 total_value AS ( qty * rate)
7 )
8 PARTITION BY RANGE (total_value)
9 (PARTITION p1 VALUES LESS THAN (100000),
10 PARTITION p2 VALUES LESS THAN (150000),
11 PARTITION p3 VALUES LESS THAN (MAXVALUE)
12 )
13 COMPRESS FOR ALL OPERATIONS;
Table created.
SQL> insert into products(product_id,product_desc,qty,rate) values(1,'a',12,2.5);
1 row created.
SQL> insert into products(product_id,product_desc,qty,rate) values(2,'a',12,250);
1 row created.
SQL> insert into products(product_id,product_desc,qty,rate) values(3,'a',12,25000000);
1 row created.
SQL> commit;
SQL> select * from products partition(p1);
PRODUCT_ID PRODUCT_DESC QTY RATE TOTAL_VALUE
---------- ------------------------- ---------- ---------- -----------
1 a 12 2.5 30
2 a 12 250 3000
SQL> select * from products partition(p3);
PRODUCT_ID PRODUCT_DESC QTY RATE TOTAL_VALUE
---------- ------------------------- ---------- ---------- -----------
3 a 12 25000000 300000000
SQL> select product_id,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from products partition(p3);
PRODUCT_ID DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------------------------ ------------------------------------
3 8 206
SQL> alter system dump datafile 8 block 206;
System altered.
----trace文件内容：
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.00e.000003fc 0x00c0078d.0114.2b --U- 1 fsc 0x0000.00168c88
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x020000ce
data_block_dump,data header at 0x7f9e6690c064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x7f9e6690c064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f8a
avsp=0x1f76
tosp=0x1f76
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] ffs=0x1f8a
block_row_dump:
tab 0, row 0, @0x1f8a
tl: 14 fb: --H-FL-- lb: 0x1 cc: 4
*** 2012-12-07 16:22:35.238
col 0: [ 2] c1 04
col 1: [ 1] 61
col 2: [ 2] c1 0d
col 3: [ 2] c4 1a
end_of_block_dump
End dump data blocks tsn: 4 file#: 8 minblk 206 maxblk 206
可见，在数据块中，只存储了前四列，第五列是虚拟的，没有进行物理存储
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-753396/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
