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。压缩效果还是不错，但是是否使用还要结果上面的限制来根据实际情况评估。