Table of Contents
- 表，數據庫中最基本的數據存儲單元，數據在數據庫中的存儲形式是行和列。
- 一個行，就是由表中定義的列相應的具體值組成的。
- 理論屬性
- 表的字段數最大可達到1000。
- 表的記錄行，可以無限。
- 一個表可以有無限個索引。
- 數據庫可以存儲的表數量不受限制。
注意:理論上是這樣的，至少數據庫本身是支持的，但實際上可能受到操作系統或其他方面的限制。
表的分類
1）常規表
- 常規表（通常稱為 “表”）是存儲用戶數據最常用的形式，它是缺省表。
- 數據庫管理員對表中行分布的控制很有限。
- 行可能按任意順序存儲，具體順序取決于在表中進行的操作。
- 表里的每行數據可以按用戶指定的順序排序。
- 每個表在數據庫中對應一個segment。
- 數據存儲方式是堆表形式。
- 堆組織表中，數據以堆的方式管理。
- 增加數據時，會使用段中找到的第一個能放下此數據的自由空間。
- 從表中刪除數據后，允許以后的
INSERT和UPDATE重用這部分空間。 - 堆（heap）是一組空間，以一種隨機的方式使用。因此，無法保證按照放入表中的順序取得數據。
- 堆表具有的唯一優點是 插入數據不需要采取任何措施，只需要順其自然地插入的順序存儲。
2）分區表
- 每個分區表有一個或多個分區，每個分區存儲已分區（使用范圍分區、散列分區、組合分區或列表分區）的行。
- 分區表中的每個分區為一個段，可各自位于不同的表空間中。
- 對于能夠同時使用幾個進程進行查詢或操作的大型表，分區非常有用。
- 分區表最主要的一個作用就是大表轉化為小表的形式存儲數據，
- 不同分區的數據是存儲在不同的segment，分區上可以建立分區索引，稱為本地索引。
- 分區表可以提高表的訪問速度，特別是大表，因為數據是存儲在不同分區segment。
# 查詢某個分區的數據：
SQL> select * from time_range_sales partition(SALES_2001);
# 查看與分區相關的字典：
SQL> select table_name from dict where lower(table_name) like '%part%' order by 1;
# 分區表本身的信息：
SQL> select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT
2 from dba_part_tables
3 where table_name='TIME_RANGE_SALES';
# 查詢分區信息：
SQL> select table_name,partition_name,tablespace_name,high_value
2 from dba_tab_partitions where table_name = 'TIME_RANGE_SALES'
3 order by 2;
分區類型有：
1）range partition 范圍分區
CREATE TABLE time_range_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
(PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')),
PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE)
);
2）hash partition 哈希分區
CREATE TABLE hash_products
( product_id NUMBER(6) PRIMARY KEY
, product_name VARCHAR2(50)
, product_description VARCHAR2(2000)
, category_id NUMBER(2)
, weight_class NUMBER(1)
, warranty_period INTERVAL YEAR TO MONTH
, supplier_id NUMBER(6)
, product_status VARCHAR2(20)
, list_price NUMBER(8,2)
, min_price NUMBER(8,2)
, catalog_url VARCHAR2(50)
, CONSTRAINT product_status_lov_demo
CHECK (product_status in ('orderable'
,'planned'
,'under development'
,'obsolete')
) )
PARTITION BY HASH (product_id)
PARTITIONS 4
STORE IN (tbs_01, tbs_02, tbs_03, tbs_04);
3）list partition 列表分區
CREATE TABLE list_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY LIST (channel_id)
( PARTITION even_channels VALUES (2,4),
PARTITION odd_channels VALUES (3,9)
);
4）composite partition 組合分區
組合分區是指最開始選定以上三種中的其中一種初步分區, 再選一種劃分子分區
所以根據初步分區的方式組合分區又可以細分為三種:
composite_range_partitionscomposite_list_partitionscomposite_hash_partitions
分區索引
A partitioned index is an index that, like a partitioned table,has been decomposed into smaller and more manageable pieces.
Like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability.
Global indexes are partitioned independently of the table on which they are created
Local indexes are automatically linked to the partitioning method for a table.
# Local Partitioned Indexes 局部分區索引
CREATE INDEX time_range_sales_idx ON time_range_sales(time_id) LOCAL;
# Global Partitioned Index 全局分區索引
CREATE INDEX time_channel_sales_idx ON time_range_sales (channel_id)
GLOBAL PARTITION BY RANGE (channel_id)
( PARTITION p1 VALUES LESS THAN (3),
PARTITION p2 VALUES LESS THAN (4),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
注意:
在整個分區上可以建立全局索引，但是當有創建新分區或刪除分區時全局索引失效，必須重建全局索引，否則全局索引失效。
3）按索引組織的表
與普通表（堆表）不同，在索引組織表中數據是按主鍵存儲在B-tree樹中的，當創建索引時，B-tree中每個節點不僅存儲了行數據也存儲了索引值。
這樣當按主鍵查詢時，因為索引和表數據是存儲在一個節點中的，查詢速度會相應提高。
- 按索引組織的表就像在一個或多個列中具有主鍵索引的堆表。
- 按索引組織的表并不為表和B樹索引維護兩個單獨的存儲空間，而是僅維護一個包含表主鍵和其它列值的B樹。
- 由于設置
PCTTHRESHOLD值以及較長的行長度需要溢出區域，所以可能存在溢出段。 - 按索引組織的表為進行涉及精確匹配和范圍搜索的查詢，提供基于鍵的、對表數據的快速訪問。
- 存儲要求降低了，因為鍵列在表和索引中不重復。
- 除非索引條目變得非常大，否則其余的非鍵列就存儲在索引中；在此情況下，Oracle服務器提供 OVERFLOW 子句來處理此問題。
4）集簇表
- 集簇表為存儲表數據提供另一種可選的方法。
- 簇由一個表或共享相同數據塊的一組表構成，它們之所以被組織在一起，是因為它們共享共同的列并且經常一起使用。
- 簇具有以下特點：
- 簇有一個集簇鍵，用來標識需要存儲在一起的多個行。
- 集簇鍵可由一個或多個列組成。
- 簇中的表具有與集簇鍵相對應的列。
- 集簇是一種對使用表的應用程序透明的機制，像操作存儲在常規表中的數據那樣操作集簇表中的數據。
- 更新集簇鍵中的一列可能需要移植該行。
- 集簇鍵獨立于主鍵。簇中的表可有一個主鍵，它可以是集簇鍵，也可以是另一組列。
- 創建簇通常是為了改善性能。隨機訪問集簇數據更快，而對集簇表進行全表掃描通常較慢。
- 簇會重新規范表的物理存儲，但不影響其邏輯結構。
表的行結構
行數據作為長度可變的記錄存儲在數據庫塊中。
通常，一個行的各列按其定義時的順序存儲，并且不存儲尾隨的NULL列。
對于非尾隨的NULL列，列長度需要占用一個字節。
表中的每行具有：
行頭：用來存儲行中的列數、鏈接信息和行鎖定狀態
行數據：對于每一列，存儲列的長度和值。
如果該列不超過250個字節，則需要一個字節來存儲列長度；
如果該列超過250個字節，則需要三個字節來存儲列長度。
列值在緊靠列長度字節后面存儲。
相鄰的行之間不需要任何空格。
塊中的每一行在行目錄中都有一個位置。目錄位置指向行首。
ROWID格式
- ROWID是每個行記錄在數據庫中的唯一標識，它是Oracle根據行的物理位置自動算出來的。
- 表中并沒有存儲ROWID，只是個偽列，在select查詢時使用它。
- 雖然ROWID并沒有直接給出行的物理位置，但能定位一個行。
- 快速訪問一個行可以通過ROWID，因為rowid能快速定位一個行記錄。
如何通過ROWID確定一行記錄:
- Oracle服務器使用數據對象編號來確定包含某一行的表空間
- 相關文件編號用來定位表空間中的文件
- 塊編號用來定位包含該行的塊
- 行編號用來定位該行的行目錄條目
- 行目錄條目可以用來定位行首
- 根據行首內容獲取到行記錄
ROWID 是 64 進制的,由0-9 a-z A-Z + /組成 。
一個塊8KB，最多存儲733個行。
行遷移與行鏈接
行遷移 Row Migration
在一個塊中可能沒有足夠的空間來容納更新后增長的行。
出現這種情況時，Oracle服務器就會把整個行移到一個新塊，并創建一個從原塊指向新位置的指針。
行鏈接 Row Chaining
如果一個行過大而任何一個塊都容納不下，就會發生行鏈接。
如果行包含的列太長，就可能發生這種情況。
在這種情況下，Oracle服務器將該行拆分成更小的數據塊，稱為 “行片段”。
每個行片段存儲在一個塊中，并帶有檢索和組合整行所需要的指針。
注意: 若發生行遷移或行鏈接，與該行相關聯的I/O性能會降低，因為Oracle服務器必須掃描兩個數據塊才能檢索該數據。
如果可能，可通過選擇較大的塊大小 或 將一個表拆分成包含更少列的多個表來最大限度地減少行鏈接。
表重組
- 將表遷移到另一個表空間
- 重組表，以減少表的行遷移記錄數
注意: 當表運行一段時間后，表中的數據會被刪除，導致了高水位下有空的數據塊，或者不滿的數據塊，數據庫在處理全表掃描的時候總是讀高水位下所有的數據塊。降低了全表掃描的效率，我們將表挪動表空間后，數據會緊密的碼放，釋放多余的空間，回收了高水位線。提高了全表掃描的性能，節約了存儲空間。
遷移過程
- 創建新表空間
SQL> select file_name from dba_data_files;
SQL> create tablespace data1 datafile '/oradata/zzdb1/data1_1.dbf' size 10m;
- 查看表所在的表空間
SQL> select owner,table_name,tablespace_name
2 from dba_tables where table_name='EMPLOYEES';
- 將表的表空間更改為 data1
SQL> alter table hr.employees move tablespace data1;
- 解決插入數據時索引失效
SQL> insert into hr.employees(employee_id,last_name,email,hire_date,job_id)
2 values (500,'zhansan','KCHUNG11',to_date('20011201','yyyymmdd'),'SA_REP');
insert into employees(employee_id,last_name,email,hire_date,job_id)
*
ERROR at line 1:
ORA-01502: index 'HR.EMP_EMAIL_UK' or partition of such index is in unusable state
# 查看表的所有索引
SQL> select index_name,status from dba_indexes where table_name='EMPLOYEES';
# 生成索引rebuild語句并執行
alter index xx rebuild online;
SQL> select 'alter index hr.'||index_name||' rebuild online;'
2 from dba_indexes where table_name='EMPLOYEES';
SQL> select index_name,status from dba_indexes where table_name='EMPLOYEES';
# 再次插入數據
SQL> insert into
2 hr.employees(employee_id,last_name,email,hire_date,job_id)
3 values (500,'zhansan','KCHUNG11',to_date('20011201','yyyymmdd'),'SA_REP');
SQL> commit;
SQL> select employee_id,last_name,email,to_char(hire_date,'yyyy-mm-dd')
2 as hire_date,job_id
3 from hr.employees where employee_id > 300;
# 查看修改后表的表空間
SQL> select owner,table_name,tablespace_name
2 from dba_tables where table_name='EMPLOYEES';
表刪除及清空數據
Truncate表
SQL> TRUNCATE TABLE scott.t1;
僅僅清空數據，很少的redo。
清空表，表中記錄都刪除了，且釋放表使用的空間。
相應的索引也清空了。
未產生undo數據。
存在外鍵的表不能清空。
觸發器不啟用。
DELETE表數據
delete from scott.t1
如果表數據量大，delete會很長時間，產生大量redo日志。
Drop表
SQL> DROP TABLE scott.t1 CASCADE CONSTRAINTS;
表不存在了，從Oracle數據庫刪除掉。
刪除表，不僅僅是清空表的記錄，表不存在了。空間也釋放了。
表的壓縮
使用壓縮的方法存儲表中的數據，使表的占用空間少，從而提高內存的使用率。
- 創建普通表
SQL> conn scott/tiger
SQL> drop table t1 purge;
SQL> create table t1 as select * from emp;
SQL> insert into t1 select * from t1;
- 創建壓縮表
SQL> drop table t2 purge;
SQL> create table t2 compress as select * from t1 order by ename;
- 查看占用空間
SQL> col segment_name for a30
SQL> select segment_name,blocks
2 from user_segments where segment_name in('T1','T2');
SEGMENT_NAME BLOCKS
------------------------------ ----------
T1 3200
T2 768
注意:
壓縮存儲的原理是每個塊內相同的數據只存放一次，所以我們在壓縮表的時候最好要排序。
我們把大的靜態表壓縮存儲，這樣既可以節約存儲空間又提高了 I/O 的效率，還節約了內存的使用，
但是經常 update 的表我們不要壓縮存儲會下降 DML 的性能。
臨時表
創建臨時表空間的動作不涉及存儲空間的分配, Oracle不會為此分配初始區段
除了在特定情況下臨時表會刪除
一般情況下, 創建應用用戶時, 會自動分配兩個默認的表空間:
appdata_tbs 用戶數據表空間
apptemp_tbs 用戶臨時表空間
# 創建on commit preserve rows臨時表
# on commit preserve rows 提交后數據不刪除，中斷會話后刪除
SQL> create global temporary table test1(id number,name varchar2(10))
2 on commit preserve rows;
# 創建on commit delete rows臨時表
# on commit delete rows 提交或回滾就刪除數據
SQL> create global temporary table test2(id number,name varchar2(10))
2 on commit delete rows;
#
