Oracle HCC简单测试

g_hk

HYBRID COLUMNAR COMPRESSION [HCC] is only for EXADATA.

Online Archival Compression Syntax：
CREATE TABLE emp (
  emp_id NUMBER
, first_name VARCHAR2(128)
, last_name VARCHAR2(128)
) COMPRESS FOR ARCHIVE [ LOW | HIGH ];


Warehouse Compression Syntax：
CREATE TABLE emp (
  emp_id NUMBER
, first_name VARCHAR2(128)
, last_name VARCHAR2(128)
) COMPRESS FOR QUERY [ LOW | HIGH ];


自己的测试：

1. SQL> create table stab(a int, b varchar2(20)) compress for query high;
   
2. 
   
3. Table created.
   
4. 
   
5. SQL> select owner,table_name,tablespace_name,compression,compress_for from all_tables where owner='GAN' and table_name='STAB';
   
6. 
   
7. OWNER TABLE_NAME TABLESPACE_NAME COMPRESS COMPRESS_FOR
   
8. ------------------------------ ------------------------------ ------------------------------ -------- ------------
   
9. GAN STAB GANTS ENABLED QUERY HIGH
   
10. 
    
11. SQL> insert into stab values(1, '1111111');
    
12. 
    
13. 1 row created.
    
14. 
    
15. SQL> c/1/2
    
16.   1* insert into stab values(2, '1111111')
    
17. SQL> /
    
18. 
    
19. 1 row created.
    
20. 
    
21. SQL> commit;
    
22. 
    
23. Commit complete.
    
24. 
    
25. SQL> select owner,table_name,tablespace_name,compression,compress_for from all_tables where owner='GAN' and table_name='STAB';
    
26. 
    
27. OWNER TABLE_NAME TABLESPACE_NAME COMPRESS COMPRESS_FOR
    
28. ------------------------------ ------------------------------ ------------------------------ -------- ------------
    
29. GAN STAB GANTS ENABLED QUERY HIGH
    
30. 
    
31. SQL> l
    
32.   1* select dbms_rowid.rowid_relative_fno(rowid) fno, dbms_rowid.rowid_block_number(rowid) blk, dbms_rowid.rowid_row_number(rowid) rnum, newtab.* from stab newtab
    
33. SQL> /
    
34. 
    
35.        FNO BLK RNUM A B
    
36. ---------- ---------- ---------- ---------- --------------------
    
37.          5 189 0 1 1111111
    
38.          5 189 1 2 1111111
    
39. SQL> l
    
40.   1* select dbms_rowid.rowid_relative_fno(rowid) fno, dbms_rowid.rowid_block_number(rowid) blk, dbms_rowid.rowid_row_number(rowid) rnum, newtab.* from stab newtab
    
41. SQL> /
    
42. 
    
43.        FNO BLK RNUM A B
    
44. ---------- ---------- ---------- ---------- --------------------
    
45.          5 189 0 1 1111111
    
46.          5 189 1 2 1111111
    
47. 
    
48. SQL> alter table stab move compress for archive high;
    
49. 
    
50. Table altered.
    
51. 
    
52. SQL> select owner,table_name,tablespace_name,compression,compress_for from all_tables where owner='GAN' and table_name='STAB';
    
53. 
    
54. OWNER TABLE_NAME TABLESPACE_NAME COMPRESS COMPRESS_FOR
    
55. ------------------------------ ------------------------------ ------------------------------ -------- ------------
    
56. GAN STAB GANTS ENABLED ARCHIVE HIGH
    
57. 
    
58. SQL> select dbms_rowid.rowid_relative_fno(rowid) fno, dbms_rowid.rowid_block_number(rowid) blk, dbms_rowid.rowid_row_number(rowid) rnum, newtab.* from stab newtab;
    
59. 
    
60.        FNO BLK RNUM A B
    
61. ---------- ---------- ---------- ---------- --------------------
    
62.          5 227 0 1 1111111
    
63.          5 227 1 2 1111111