oracle分區表壓縮

1. oracle表壓縮後索引還有效不

通過PL/SQL可以直接查看某表是否建索引，通過SQL查詢select status,T.* from user_indexes Twhere table_name='表名'

oracle查看有效索引是這個：select status,T.* from user_indexes T，where table_name='TABLE1'

最好弄個圖像界面軟體，就能知道，比如：PL/SQLDeveloper

資料庫中的失效的索引、索引分區、子分區：如果不是失效的索引，那麼都是有效的。

2. oracle分區表的分區有幾種類型

oracle分區表的分區有四種類型：范圍分區、散列分區、列表分區和復合分區。

特點如下：

1、范圍分區

就是根據資料庫表中某一欄位的值的范圍來劃分分區。

數據中有空值，Oracle機制會自動將其規劃到maxvalue的分區中。

2、散列分區

根據欄位的hash值進行均勻分布，盡可能地實現各分區所散列的數據相等。

散列分區即為哈希分區，Oracle採用哈希碼技術分區，具體分區如何由Oracle說的算，也可能我下一次搜索就不是這個數據了。

3、列表分區

列表分區明確指定了根據某欄位的某個具體值進行分區，而不是像范圍分區那樣根據欄位的值范圍來劃分的。

4、復合分區

根據范圍分區後，每個分區內的數據再散列地分布在幾個表空間中，這樣我們就要使用復合分區。復合分區是先使用范圍分區，然後在每個分區同再使用散列分區的一種分區方法。

比如將part_date的記錄按時間分區，然後每個分區中的數據分三個子分區，將數據散列地存儲在三個指定的表空間中。

(2)oracle分區表壓縮擴展閱讀：

分區的恢復方法：

如果資料庫運行在archive 模式下，那麼一旦資料庫損壞則可以通過冷備份(熱備份)和歸檔備份將資料庫恢復到斷點狀態。

資料庫控制文件恢復（假設所有控制文件均被破壞）：

資料庫基於文件系統： 利用操作系統的tar、cp等命令即可。

資料庫基於裸設備：dd if=$ORACLE_BASE/con.bak of=/dev/rdrd/drd1 seek=12

3. oracle 分區交換難道不支持索引壓縮

不應該吧，我這邊版本Oracle11g，測試沒有問題。步驟如下：

_id,object_namefromuser_objectsWHEREobject_id<87500;
createindexobjects1_idxonobjects1(object_id)compress1;
createtableobjects2
(
object_idnumber,
object_namevarchar2(128)
)
partitionbyrange(object_id)
(
partitionT1valueslessthan(87500),
partitionT2valueslessthan(87600),
partitionT3valueslessthan(maxvalue));
createindexobjects2_idxonobjects2(object_id)LOCALcompress1;
;
SELECT*FROMuser_indexesWHEREtable_nameIN('OBJECTS1','OBJECTS2');

4. oracle如何查詢當前用戶分區表的最大分區，最小分區和當前分區的數量

最大最小分區，分區數量，可以通過user_segments獲得。
因為每一個表分區都是一個segment，所以可以通過這個獲得。
一般情況下，我們認為表中的bytes就是這個分區的大小。
user的視圖中歐式當前用戶的分區，通過table_name分組，paratition_name is not null 然後就可以求出最大分區和最小分區，count求出分區數量。

5. 如何壓縮oracle海量數據

一、應用場合
答：正如上面所說，壓縮技術一般應用在「海量數據」的范疇，在大數據量下並且重復率高的情境下應該的效果才好，適合於OLAP（報表系統）不適合OLTP（在線交易系統），當數據量小時作用還不是很大，只要數據量達到一定程度的時候使用這門技術才有意義。
二、壓縮技術的好處
1.節約空間：我認為壓縮技術的最大好處就是節約空間，在存儲設備空間很緊張的情況下更需要使用壓縮技術來緩解
2.減少memory/IO開銷：當使用了壓縮技術後，同等記錄對應的數據塊相對較少，自然而然IO開銷就小了
3.提高數據處理效率：原本10個數據塊的，經過壓縮後變成5個數據塊，但還是那些數據，可效率提高了一倍。
三、壓縮原理
答：我們都用過rar壓縮zip壓縮，其實這些壓縮的原理都很相似，當然在細微處理上有不同，數據塊壓縮原理上是使用長度較短符號來代替列中的每個值，由於符號佔用空間小於實際值，從而達到節約空間的效果，當列中重復值越多壓縮效果越好。
四、數據壓縮不適合OLTP系統
1.OLTP是一種多事務短時間片系統，有大量的DML操作，而DML操作我們都知道是有鎖lock的（insert update delete）當數據塊中記錄數多起來時，導致訪問這個數據塊的用戶就多，就是產生爭用和等待，從而降低了效率。
2.數據插入、更新和索引鍵值更新時，都要先解壓縮再更新，等待的時間會更長。
五、分區表的壓縮
1.分區表的每個分區都可以獨立壓縮
2.有些時候，分區表在創建時不設置分區壓縮屬性，當某個分區需要壓縮時，再獨立進行壓縮
3.如果在表級設置壓縮屬性的話，那麼所有分區都會啟動壓縮功能，反正如果是某幾個分區獨立壓縮，表級屬性上什麼也不寫的。
實驗
create table leo_partition1 (object_id,object_name) 創建一個六個分區的分區表，沒有指定壓縮
partition by range (object_id)
(
partition leo_p1 values less than (1000),
partition leo_p2 values less than (2000),
partition leo_p3 values less than (3000),
partition leo_p4 values less than (4000),
partition leo_p5 values less than (5000),
partition leo_max values less than (maxvalue)
)
as select object_id,object_name from dba_objects;
LS@LEO> select count(*) from leo_partition1;
COUNT(*)
----------
10351
LS@LEO> select partition_name,compression from user_tab_partitions where table_name='LEO_PARTITION1';
PARTITION_NAME COMPRESS
------------------------------ --------
LEO_MAX DISABLED
LEO_P1 DISABLED
LEO_P2 DISABLED
LEO_P3 DISABLED
LEO_P4 DISABLED
LEO_P5 DISABLED
LS@LEO> alter table leo_partition1 compress; 例如 定義表級壓縮屬性，此時所有分區都壓縮
LS@LEO> alter table leo_partition1 modify partition leo_p1 compress; 我們測試准對leo_p1 和 leo_p5分區進行壓縮，來說明分區可以獨立壓縮
Table altered.
LS@LEO> alter table leo_partition1 modify partition leo_p5 compress;
Table altered.
LS@LEO> select partition_name,compression from user_tab_partitions where table_name='LEO_PARTITION1';
PARTITION_NAME COMPRESS
------------------------------ -----------------------
LEO_MAX DISABLED
LEO_P1 ENABLED

6. ORACLE分區表用什麼方法刪除一個分區的所有數據

這個要看你的業務要不要備份保留這個分區的數據，如果不需要保留，可以直接truncate/drop分區的，如果要保留，可以用交換分區方法；
具體步驟：
1.不保留，直接刪除：
alter
table
table_name
drop/truncate
partition
partition_name;
具體用drop還是truncate，得你自己衡量，drop的話原來的分區和數據直接就沒有了，truncate的話，只是數據沒有了，分區還在。
2.保留數據作為備份，交換分區
2.1創建一個表結構一樣的表：
create
table
teable_bak
as
select
*
from
table_name
where
1=2;
--只要表結構，不要數據；
2.2分區交換
alter
table
table_name
exchange
partition
partition_name
with
table
table_bak;
這樣執行後，分區partition_name的數據就會交換到table_bak中，table_bak的數據（剛剛建的空表）就會到分區裡面去。

7. oracle 中user_tables表各欄位意思是什麼

table_name -表名
tablespace_name -表空間名
cluster_name -群集名稱
iot_name -IOT（Index Organized Table）索引組織表的名稱
status -狀態
pct_free -為一個塊保留的空間百分比
pct_used -一個塊的使用水位的百分比
ini_trans -初始交易的數量
max_trans -交易的最大數量
initial_extent -初始擴展數
next_extent -下一次擴展數
min_extents -最小擴展數
max_extents -最大擴展數
pct_increase -表在做了第一次extent後，下次再擴展時的增量，它是一個百分比值
freelists -可用列表是表中的一組可插入數據的可用塊
freelist_groups -列表所屬組
logging -是否記錄日誌
backed_up -指示自上次修改表是否已備份（Y）或否（N）的
num_rows -表中的行數
blocks -所使用的數據塊數量
empty_blocks -空數據塊的數量
avg_space -自由空間的平均量
chain_cnt -從一個數據塊，或遷移到一個新塊鏈接表中的行數
avg_row_len -行表中的平均長度
avg_space_freelist_blocks -一個freelist上的所有塊的平均可用空間
num_freelist_blocks -空閑列表上的塊數量
degree -每個實例的線程數量掃描表
instances -跨表進行掃描的實例數量
cache -是否是要在緩沖區高速緩存
table_lock -是否啟用表鎖
sample_size -分析這個表所使用的樣本大小
last_analyzed -最近分析的日期
partitioned -表是否已分區
iot_type -表是否是索引組織表
temporary -表是否是暫時的
secondary -表是否是次要的對象
nested -是否是一個嵌套表
buffer_pool -緩沖池的表
flash_cache -智能快閃記憶體緩存提示可用於表塊
cell_flash_cache -細胞快閃記憶體緩存提示可用於表塊
row_movement -是否啟用分區行運動
global_stats -作為一個整體（全球統計）表的統計的是否准確
user_stats -是否有統計
ration -臨時表的時間
skip_corrupt -是否忽略損壞的塊標記在表和索引掃描（ENABLED）狀態的或將引發一個錯誤（已禁用）。

8. 如何更新oracle表中的分區欄位

1、按時間分區表創建： 其中add_date_time為分區欄位，每一年一個分區。插入100W數據。

9. oracle怎麼更新分區表的數據

默認情況下，oracle的分區表對於分區欄位是不允許進行update操作的，如果有對分區欄位行進update，就會報錯——ORA-14402: 更新分區關鍵字列將導致分區的更改。但是可以通過打開表的row movement屬性來允許對分區欄位的update操作。
例：創建分區表test_part進行實驗
create table TEST_PART
(
A1 NUMBERnot null,
A2 DATE not null,
A3 VARCHAR2(6) not null,
A4 DATE not null,
A5 NUMBER not null,
)
partition by range (A1)
(
partition P1 values less than (1000),
partition P2 values less than (2000),
partition P3 values less than (3000),
partition P4 values less than (4000),
partition P5 values less than (5000),
partition P6 values less than (MAXVALUE)
);
插入如下的數據
SQL> select * from test_part;
A1 A2 A3 A4 A5
---------- ----------- ------ ----------- ----------
123 2006-06-30 123456 2006-06-30 123
456 2006-06-30 asdfgh 2006-06-30 456
1 2006-06-30 234123 2006-06-30 1
2 2006-06-30 234234 2006-06-30 2
1234 2006-06-30 456789 2006-06-30 1234
1111 2006-06-30 ewrqwe 2006-06-30 1111
2222 2006-06-30 fdafda 2006-06-30 2222
3333 2006-06-30 342342 2006-06-30 3333
5678 2006-06-30 qwerty 2006-06-30 5678
9 rows selected
分區P1、P2的數據分別為：
SQL> select rowid,t.* from test_part partition(p1) t;
ROWID A1 A2 A3 A4 A5
------------------ ---------- ----------- ------ ----------- ----------
AAAGLoAAGAAAtsEAAB 456 2006-06-30 asdfgh 2006-06-30 456
AAAGLoAAGAAAtsEAAC 1 2006-06-30 234123 2006-06-30 1
AAAGLoAAGAAAtsEAAD 2 2006-06-30 234234 2006-06-30 2
AAAGLoAAGAAAtsEAAE 123 2006-06-30 123456 2006-06-30 123
SQL> select rowid,t.* from test_part partition(p2) t;
ROWID A1 A2 A3 A4 A5
------------------ ---------- ----------- ------ ----------- ----------
AAAGLwAAGAAA+8MAAC 1234 2006-06-30 456789 2006-06-30 1234
AAAGLwAAGAAA+8MAAD 1111 2006-06-30 ewrqwe 2006-06-30 1111
直接update提示錯誤
SQL> update test_part set a1=1123 where a1=123;
update test_part set a1=1123 where a1=123
ORA-14402: 更新分區關鍵字列將導致分區的更改
打開row movement屬性
SQL> alter table test_part enable row movement;
Table altered
再次執行update操作
SQL> update test_part set a1=1123 where a1=123;
1 row updated
執行是成功的並遷移到分區P2上了，且這時候rowid也發生了變化
SQL> select rowid,t.* from test_part partition(p2) t;
ROWID A1 A2 A3 A4 A5
------------------ ---------- ----------- ------ ----------- ----------
AAAGLwAAGAAA+8MAAC 1234 2006-06-30 456789 2006-06-30 1234
AAAGLwAAGAAA+8MAAD 1111 2006-06-30 ewrqwe 2006-06-30 1111
AAAGLwAAGAAA+8PAAB 1123 2006-06-30 123456 2006-06-30 123
SQL>
enable row movement可以允許數據段的壓縮、update分區欄位的數據（跨分區的）

10. oracle11g怎麼建立壓縮表

在數據量變的很大的情況下，可以考慮表壓縮，減少磁碟的存儲，減少buffer cache的消耗，加快查詢的速度，壓縮需要在數據載入和dml的時候消耗一些cpu.表壓縮對於應用是透明的。

你可以在表空間，表或分區級別指定壓縮，如果在表空間級別指定壓縮，那麼這個表空間中的所有表默認都是壓縮的。

壓縮會發生在數據插入，更新或批量載入到表時。有幾種類型的壓縮。基本的，oltp的，dss的，歸檔的。他們的cpu消耗見文檔。

當使用基本壓縮，數據倉庫壓縮，或歸檔壓縮的時候，壓縮僅僅是在批量載入到一個表的時候發生。當你使用了oltp壓縮，壓縮發生在數據插入，更新，批量載入。

如果你使用了alter table命令讓表啟用壓縮，那麼對於已存在的數據是不受影響的，只有在啟用壓縮後的插入，或更新會受影響。也可以使用ALTER TABLE…NOCOMPRESS將一個表設置成非壓縮，對於已經的壓縮的表是沒有影響的，壓縮的數據還是壓縮的，只是新插入的數據是不被壓縮。

創建一個oltp壓縮類型的表
CREATE TABLE orders … COMPRESS FOR OLTP;

如果你不指定壓縮的類型，那麼默認的情況下是基本壓縮類型。下面的2個語句是等價的。
CREATE TABLE sales_history … COMPRESS BASIC;

CREATE TABLE sales_history … COMPRESS;

下面的表是一個數據倉庫壓縮類型的表，對於經常查詢，並且沒有dml的表適用。
CREATE TABLE sales_history … COMPRESS FOR QUERY;

創建一個歸檔壓縮類型的表
CREATE TABLE sales_history … COMPRESS FOR ARCHIVE;

表可以包含壓縮和非壓縮的分區，並且不同的分區的壓縮的類型可以是不同的，如果在分區上指定的壓縮的類型與表上指定的壓縮的類型不同，那麼分區上的優先。

查看錶是否是壓縮的，及分區是否是壓縮的。
SQL> SELECT table_name, compression, compress_for FROM user_tables;

SELECT table_name, partition_name, compression,compress_for
FROM user_tab_partitions;

查看錶中的行是什麼壓縮類型
SELECT DECODE(DBMS_COMPRESSION.GET_COMPRESSION_TYPE(
ownname => 『HR』,
tabname => 『EMPLOYEES』,
row_id => 『AAAVEIAAGAAAABTAAD』),
1, 『No Compression』,
2, 『Basic or OLTP Compression』,
4, 『Hybrid Columnar Compression for Query High』,
8, 『Hybrid Columnar Compression for Query Low』,
16, 『Hybrid Columnar Compression for Archive High』,
32, 『Hybrid Columnar Compression for Archive Low』,
『Unknown Compression Type』) compression_type
FROM DUAL;

修改壓縮的級別

如果表是分區表，使用在線重定義可以修改表的壓縮級別。如果表是分分區表，那麼可以使用alter table ..move.. compress for語句修改表的壓縮級別，這個語句會阻塞dml操作。

對於壓縮表的限制：
1基本壓縮，你不能為添加的列指定默認的值。刪除列是不被支持的。
2oltp壓縮，如果要添加一個帶默認值的列，那麼需要指定not null，可以刪除列，但是數據只是在內部做了一個不被使用的標記而已。

3在線段收縮是不被支持的。

4上面的壓縮的方式不適合於lobs類型，他們有自己的壓縮方式。

5基本壓縮類型的表的pct_free參數自動的設置成0.

在上面的描述中可以看到對於基本類型的壓縮，沒有使用直接路徑插入的行及更新的行是不會被壓縮的。對於我們的oltp系統基本是沒有用處的，只測試下oltp的壓縮方式，這種方式對於沒有使用直接路徑插入的行及被更新的行都會壓縮。

SQL> select count(*) from xyu2;

COUNT(*)

22096384

SQL> set serveroutput on
SQL> exec show_space(『BAIXYU2』,』T』,』AUTO』);
Total Blocks……………………….334336
Total Bytes………………………..2738880512
Unused Blocks………………………1916
Unused Bytes……………………….15695872
Last Used Ext FileId………………..4
Last Used Ext BlockId……………….671872
Last Used Block…………………….6276

CREATE TABLE xyu3 COMPRESS FOR OLTP as select * from xyu2;

SQL> exec show_space(『BAIXYU3』,』T』,』AUTO』);
Total Blocks……………………….107520
Total Bytes………………………..880803840
Unused Blocks………………………112
Unused Bytes……………………….917504
Last Used Ext FileId………………..4
Last Used Ext BlockId……………….116224
Last Used Block…………………….912

看到使用的塊由33w下降到10w。壓縮效果還是不錯，但是是否使用還要結果上面的限制來根據實際情況評估。