Usually, the application developer is responsible for designing the elements of an application, including the tables. Database administrators are responsible for establishing the attributes of the underlying tablespace that will hold the application tables. Either the DBA or the applications developer, or both working jointly, can be responsible for the actual creation of the tables, depending upon the practices for a site.
Working with the application developer, consider the following guidelines when designing tables:
-
Use descriptive names for tables, columns, indexes, and clusters.
-
Be consistent in abbreviations and in the use of singular and plural forms of table names and columns.
-
Document the meaning of each table and its columns with the
COMMENT
command. -
Normalize each table.
-
Select the appropriate datatype for each column.
-
Consider whether your applications would benefit from adding one or more virtual columns to some tables.
-
Define columns that allow nulls last, to conserve storage space.
-
Cluster tables whenever appropriate, to conserve storage space and optimize performance of SQL statements.
Before creating a table, you should also determine whether to use integrity constraints. Integrity constraints can be defined on the columns of a table to enforce the business rules of your database automatically.
Oracle至少有六种压缩的方法:
- BASIC compression, introduced in Oracle 8 already and only recommended for Data Warehouse
- OLTP compression, introduced in Oracle 11 and recommended for OLTP Databases as well
- QUERY LOW compression (Exadata only), recommended for Data Warehouse with Load Time as a critical factor
- QUERY HIGH compression (Exadata only), recommended for Data Warehouse with focus on Space Saving
- ARCHIVE LOW compression (Exadata only), recommended for Archival Data with Load Time as a critical factor
- ARCHIVE HIGH compression (Exadata only), recommended for Archival Data with maximum Space Saving
CREATE TABLE ml_test_normal AS SELECT * FROM all_objects
SQL> create table ml_test_basic compress as select * from all_objects where 1=2;
Table created
Executed in 0.281 seconds
SQL> insert /*+ append */ into ml_test_basic select * from all_objects;
1499285 rows inserted
Executed in 13.441 seconds
SQL> commit;
Commit complete
Executed in 0 seconds
SQL> create table ml_test_normal as select * from all_objects where 1=2;
Table created
SQL> insert into ml_test_normal SELECT * FROM all_objects;
1499286 rows inserted
SQL> set timi on
SQL> insert into ml_test_normal SELECT * FROM all_objects;
1499286 rows inserted
Executed in 15.584 seconds
SQL>
SQL> create table ml_test_basicnotappend compress nologging as select * from all_objects where 1=2;
Table created
Executed in 0.281 seconds
SQL> insert into ml_test_basicnotappend SELECT * FROM all_objects;
1499288 rows inserted
Executed in 12.168 seconds
SQL>
OLTP方式:(只适合oltp)
SQL> create table ml_test_oltp compress for oltp as select * from all_objects where 1=2;
Table created
Executed in 0.281 seconds
SQL> insert /*+ append */ into ml_test_oltp select * from all_objects;
1499287 rows inserted
Executed in 16.848 seconds
SQL> commit;
Commit complete
Executed in 0.016 seconds
SQL>
SQL> create table ml_test_query_low compress for query low as select * from all_objects where 1=2;
create table ml_test_query_low compress for query low as select * from all_objects where 1=2
ORA-64307: 只有位于 Exadata 存储上的表空间才支持混合柱状压缩
SQL> create table ml_test_query_high compress for query high as select * from all_objects where 1=2;
create table ml_test_query_high compress for query high as select * from all_objects where 1=2
ORA-64307: 只有位于 Exadata 存储上的表空间才支持混合柱状压缩
SQL> create table ml_test_archive_low compress for archive low as select * from all_objects where 1=2;
create table ml_test_archive_low compress for archive low as select * from all_objects where 1=2
ORA-64307: 只有位于 Exadata 存储上的表空间才支持混合柱状压缩
SQL> select segment_name,bytes/1024/1024 as mb from user_segments
2 where segment_Name IN ('ML_TEST_BASIC','ML_TEST_OLTP','ML_TEST_NORMAL','ML_TEST_BASICNOTAPPEND')
3 order by 2;
SEGMENT_NAME MB
--------------------------------------------- ----------
ML_TEST_BASIC 41
ML_TEST_OLTP 46
ML_TEST_BASICNOTAPPEND 192
ML_TEST_NORMAL 208
Executed in 0.437 seconds
SQL>
总结:
SQL> ALTER TABLE ml_test_basicnotappend ADD (ml NUMBER DEFAULT 0);
ALTER TABLE ml_test_basicnotappend ADD (ml NUMBER DEFAULT 0)
ORA-39726: 不支持对压缩表执行添加/删除列操作
SQL> ALTER TABLE ml_test_basicnotappend ADD (ml NUMBER );
Table altered
Executed in 0.125 seconds
SQL> ALTER TABLE ml_test_basicnotappend ADD (ml2 NUMBER default 0 not null);
Table altered
Executed in 0.141 seconds
SQL> alter table ml_test_basicnotappend drop column ml2;
alter table ml_test_basicnotappend drop column ml2
ORA-39726: 不支持对压缩表执行添加/删除列操作