oracle里面的分区索引,oracle patition 分区和索引

oracle patition 分区 参考博文

简单的分区方法是 Hash Partitioning

Hash partitioning enables easy partitioning of data that does not lend itself to range or list partitioning. It does this with a simple syntax and is easy to implement. It is a better choice than range partitioning when:

You do not know beforehand how much data maps into a given range

The sizes of range partitions would differ quite substantially or would be difficult to balance manually

Range partitioning would cause the data to be undesirably clustered

Performance features such as parallel DML, partition pruning, and partition-wise joins are important

The concepts of splitting, dropping or merging partitions do not apply to hash partitions. Instead, hash partitions can be added and coalesced.

Hash Partitioning Example

CREATE TABLE sales_hash

(salesman_id NUMBER(5),

salesman_name VARCHAR2(30),

sales_amount NUMBER(10),

week_no NUMBER(2))

PARTITION BY HASH(salesman_id)

PARTITIONS 4

STORE IN (data1, data2, data3, data4);

The preceding statement creates a table sales_hash, which is hash partitioned on salesman_id field. The tablespace names are data1,data2, data3, and data4.

对已有表添加partition的方法

根据partition 查询语句写

跨分区查询

select sum( *) from

(select count(*) cn from t_table_SS PARTITION (P200709_1)

union all

select count(*) cn from t_table_SS PARTITION (P200709_2)

);

查询表上有多少分区

SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'

查询索引信息

select object_name,object_type,tablespace_name,sum(value)

from v$segment_statistics

where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX'

group by object_name,object_type,tablespace_name

order by 4 desc

显示数据库所有分区表的信息：

select * from DBA_PART_TABLES

显示表分区名称之类的信息：

select *

from DBA_PART_COL_STATISTICS

where table_name like '%MIGRAT%'

另外关于查询分区表的信息可以参考：

为表添加索引的方法

CREATE INDEX emp_ename ON emp(ename)

TABLESPACE users

STORAGE (INITIAL 20K

NEXT 20k

PCTINCREASE 75);

注意：当设置主键的时候，是默认会加上一个唯一索引的，是自动创建的。可以指定包含这个索引的用户空间。

Creating an Index Associated with a Constraint

Oracle Database enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key. This index is automatically created by the database when the constraint is enabled. No action is required by you when you issue the CREATE TABLE or ALTER TABLE statement to create the index, but you can optionally specify a USING INDEX clause to exercise control over its creation. This includes both when a constraint is defined and enabled, and when a defined but disabled constraint is enabled.

To enable a UNIQUE or PRIMARY KEY constraint, thus creating an associated index, the owner of the table must have a quota for the tablespace intended to contain the index, or the UNLIMITED TABLESPACE system privilege. The index associated with a constraint always takes the name of the constraint, unless you optionally specify otherwise.