压缩表
SQL> create table t1 as select * from corporation;
Table created.
SQL> select TABLE_NAME,TABLESPACE_NAME,COMPRESSION from all_tables where table_name=upper('T1');
TABLE_NAME TABLESPACE_NAME COMPRESS
------------------------------ ------------------------------ --------
T1 XIAOYAO DISABLED
SQL> select SEGMENT_NAME,TABLESPACE_NAME,BYTES from dba_segments where SEGMENT_NAME=upper('t1');
SEGMENT_NAME TABLESPACE_NAME BYTES
--------------------------------------------------------------------------------- ------------------------------ ----------
T1 XIAOYAO 243269632
SQL> exec show_space('T1');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ...................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ............................ 29,202
Total Blocks............................ 29,696
Total Bytes............................. 243,269,632
Total MBytes............................ 232
Unused Blocks........................... 275
Unused Bytes............................ 2,252,800
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 121,865
Last Used Block......................... 749
PL/SQL procedure successfully completed.
SQL> alter table t1 enable row movement;
Table altered.
SQL> alter table t1 move COMPRESS;
Table altered.
SQL> select TABLE_NAME,TABLESPACE_NAME,COMPRESSION from all_tables where table_name=upper('T1');
TABLE_NAME TABLESPACE_NAME COMPRESS
------------------------------ ------------------------------ --------
T1 XIAOYAO ENABLED
SQL> select SEGMENT_NAME,TABLESPACE_NAME,BYTES from dba_segments where SEGMENT_NAME=upper('t1');
SEGMENT_NAME TABLESPACE_NAME BYTES
--------------------------------------------------------------------------------- ------------------------------ ----------
T1 XIAOYAO 159383552
SQL> exec show_space('T1');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ...................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ............................ 18,472
Total Blocks............................ 19,456
Total Bytes............................. 159,383,552
Total MBytes............................ 152
Unused Blocks........................... 805
Unused Bytes............................ 6,594,560
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 175,113
Last Used Block......................... 219
PL/SQL procedure successfully completed.
SQL> create table t2 as select * from corporation;
Table created.
SQL> select CUSTOMERNAME from t1 where CUSTOMERNAME in ('anson','1234567');
CUSTOMERNAME
--------------------------------------------------
1234567
anson
********************************************************************************
select CUSTOMERNAME
from
t1 where CUSTOMERNAME in ('anson','1234567')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.49 3.20 17650 18484 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.50 3.20 17650 18485 0 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28
Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS FULL T1 (cr=18484 pr=17650 pw=0 time=12145 us)
********************************************************************************
SQL> select CUSTOMERNAME from t2 where CUSTOMERNAME in ('anson','1234567');
CUSTOMERNAME
--------------------------------------------------
1234567
anson
********************************************************************************
select CUSTOMERNAME
from
t2 where CUSTOMERNAME in ('anson','1234567')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.46 6.09 27750 29215 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.46 6.09 27750 29216 0 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28
Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS FULL T2 (cr=29215 pr=27750 pw=0 time=7752 us)
********************************************************************************
SQL> select count(*) from t1;
COUNT(*)
----------
687447
********************************************************************************
select count(*)
from
t1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.36 4.67 17649 18483 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.37 4.67 17649 18484 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=18483 pr=17649 pw=0 time=4673585 us)
687447 TABLE ACCESS FULL T1 (cr=18483 pr=17649 pw=0 time=2074562 us)
********************************************************************************
SQL> select count(*) from t2;
COUNT(*)
----------
687447
********************************************************************************
select count(*)
from
t2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.51 6.20 27722 29214 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.51 6.20 27722 29215 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=29214 pr=27722 pw=0 time=6205623 us)
687447 TABLE ACCESS FULL T2 (cr=29214 pr=27722 pw=0 time=8255039 us)
********************************************************************************
SQL> select CUSTOMERNAME,count(*) from t1 where CUSTOMERNAME in ('anson','1234567') group by CUSTOMERNAME;
CUSTOMERNAME COUNT(*)
-------------------------------------------------- ----------
anson 1
1234567 1
********************************************************************************
select CUSTOMERNAME,count(*)
from
t1 where CUSTOMERNAME in ('anson','1234567') group by CUSTOMERNAME
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.53 4.36 17649 18483 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.54 4.36 17649 18484 0 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28
Rows Row Source Operation
------- ---------------------------------------------------
2 HASH GROUP BY (cr=18483 pr=17649 pw=0 time=4362120 us)
2 TABLE ACCESS FULL T1 (cr=18483 pr=17649 pw=0 time=7100 us)
********************************************************************************
SQL> select CUSTOMERNAME,count(*) from t2 where CUSTOMERNAME in ('anson','1234567') group by CUSTOMERNAME;
CUSTOMERNAME COUNT(*)
-------------------------------------------------- ----------
anson 1
1234567 1
********************************************************************************
select CUSTOMERNAME,count(*)
from
t2 where CUSTOMERNAME in ('anson','1234567') group by CUSTOMERNAME
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.39 0.68 27747 29214 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.40 0.68 27747 29215 0 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28
Rows Row Source Operation
------- ---------------------------------------------------
2 HASH GROUP BY (cr=29214 pr=27747 pw=0 time=681635 us)
2 TABLE ACCESS FULL T2 (cr=29214 pr=27747 pw=0 time=132 us)
********************************************************************************
SQL> insert into t1 select * from corporation;
687447 rows created.
********************************************************************************
insert into t1 select * from corporation
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 18.62 63.21 29391 54731 889255 687447
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 18.62 63.21 29391 54731 889255 687447
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 28
Rows Row Source Operation
------- ---------------------------------------------------
687447 TABLE ACCESS FULL CORPORATION (cr=29214 pr=29202 pw=0 time=49523978 us)
********************************************************************************
SQL> insert into t2 select * from corporation;
687447 rows created.
********************************************************************************
insert into t2 select * from corporation
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 26.96 178.13 57054 58469 874734 687447
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 26.96 178.13 57054 58469 874734 687447
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 28
Rows Row Source Operation
------- ---------------------------------------------------
687447 TABLE ACCESS FULL CORPORATION (cr=29214 pr=29202 pw=0 time=10122783 us)
********************************************************************************
SQL> commit
2 /
Commit complete.
SQL> exec show_space('T1');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ...................... 1
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 26,194
Full Blocks ............................ 44,590
Total Blocks............................ 71,680
Total Bytes............................. 587,202,560
Total MBytes............................ 560
Unused Blocks........................... 512
Unused Bytes............................ 4,194,304
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 257,033
Last Used Block......................... 512
PL/SQL procedure successfully completed.
SQL> exec show_space('T2');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ...................... 1
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 253
Full Blocks ............................ 58,291
Total Blocks............................ 59,392
Total Bytes............................. 486,539,264
Total MBytes............................ 464
Unused Blocks........................... 512
Unused Bytes............................ 4,194,304
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 230,409
Last Used Block......................... 512
PL/SQL procedure successfully completed.
SQL> alter table t1 move COMPRESS;
Table altered.
SQL> exec show_space('T1');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ...................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ............................ 37,068
Total Blocks............................ 37,888
Total Bytes............................. 310,378,496
Total MBytes............................ 296
Unused Blocks........................... 569
Unused Bytes............................ 4,661,248
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 62,601
Last Used Block......................... 455
PL/SQL procedure successfully completed.
SQL> update t1 set CUSTOMERNAME='anson1' where CUSTOMERNAME='anson';
2 rows updated.
********************************************************************************
update t1 set CUSTOMERNAME='anson1'
where
CUSTOMERNAME='anson'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.92 7.60 36311 37081 4 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.92 7.61 36311 37082 4 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE T1 (cr=37081 pr=36311 pw=0 time=7608969 us)
2 TABLE ACCESS FULL T1 (cr=37081 pr=36311 pw=0 time=3720342 us)
********************************************************************************
SQL> commit;
Commit complete.
SQL> update t2 set CUSTOMERNAME='anson1' where CUSTOMERNAME='anson';
2 rows updated.
********************************************************************************
update t2 set CUSTOMERNAME='anson1'
where
CUSTOMERNAME='anson'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 1.27 26.84 50835 87619 4 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.28 26.85 50835 87621 4 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE T2 (cr=87619 pr=50835 pw=0 time=26844137 us)
2 TABLE ACCESS FULL T2 (cr=87619 pr=50835 pw=0 time=13306112 us)
********************************************************************************
SQL> commit;
Commit complete.
SQL> delete from t1 where CUSTOMERNAME='anson1';
2 rows deleted.
********************************************************************************
delete from t1
where
CUSTOMERNAME='anson1'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 2 0 0
Execute 1 0.91 8.13 36344 37081 4 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.92 8.14 36344 37083 4 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE T1 (cr=37081 pr=36344 pw=0 time=8138754 us)
2 TABLE ACCESS FULL T1 (cr=37081 pr=36344 pw=0 time=4550335 us)
********************************************************************************
SQL> delete from t2 where CUSTOMERNAME='anson1';
2 rows deleted.
********************************************************************************
delete from t2
where
CUSTOMERNAME='anson1'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.84 7.96 49614 58560 4 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.85 7.96 49614 58561 4 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE T2 (cr=58560 pr=49614 pw=0 time=7961437 us)
2 TABLE ACCESS FULL T2 (cr=58560 pr=49614 pw=0 time=5911434 us)
********************************************************************************
SQL> commit;
Commit complete.
SQL> exec show_space('T1');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ...................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ............................ 37,068
Total Blocks............................ 37,888
Total Bytes............................. 310,378,496
Total MBytes............................ 296
Unused Blocks........................... 569
Unused Bytes............................ 4,661,248
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 62,601
Last Used Block......................... 455
PL/SQL procedure successfully completed.
SQL> select city,count(*) from t1 where city='广州市' group by city;
no rows selected
********************************************************************************
select city,count(*)
from
t1 where city='??????' group by city
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 1 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 3.00 13.19 72863 74162 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 3.01 13.20 72863 74163 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT GROUP BY NOSORT (cr=37081 pr=36175 pw=0 time=6456329 us)
0 TABLE ACCESS FULL T1 (cr=37081 pr=36175 pw=0 time=6456316 us)
********************************************************************************
SQL> select city,count(*) from t2 where city='广州市' group by city;
no rows selected
********************************************************************************
select city,count(*)
from
t2 where city='??????' group by city
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 1 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 2.09 22.37 101261 117120 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 2.10 22.37 101261 117121 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT GROUP BY NOSORT (cr=58560 pr=50599 pw=0 time=10975522 us)
0 TABLE ACCESS FULL T2 (cr=58560 pr=50599 pw=0 time=10975510 us)
********************************************************************************
SQL> create table t3 partition by hash(id) (partition test1,partition test2) as select * from corporation;
Table created.
