PG分区表建立唯一索引

转载

技术博客达人 2024-10-16 16:15:57

文章标签 PG分区表建立唯一索引数据库 oracle sql 表空间 文章分类 数据仓库大数据

Oracle数据库应用

一.表空间

Oracle数据库包含逻辑构造和物理构造。数据库的物理构造是指构造成数据库的一组操作系统文件。数据库的逻辑构造是指描述数据组织方式的一组逻辑概念及它们之间的关系,表空间是数据库逻辑构造的一个重要组件,表空间可以存放各种。

1.表空间分类

永久表空间：存储数据库中需要永久化存储的对象，比如二维表、视图、存储过程、索引。
临时表空间：存储数据库的中间执行过程，如：保存order
by数据库排序，分组时产生的临时数据。操作完成后存储的内容会被自动释放。临时表空间是通用的，所的用户都使用TEMP作为临时表空间。一般只有temp一个临时表空间，如果还需要别的临时表空间时，可以自己创建。
UNDO表空间：保存数据修改前的副本。存储事务所修改的旧址，即被修改之前的数据。当我们对一张表中的数据进行修改的同时会对修改之前的信息进行保存，为了对数据执行回滚、恢复、撤销的操作。

2.表空间的目的

对不同用户分配不同的表空间，对不同的模式对象分配不同的表空间，方便对用户数据的操作，对模式对象的管理
可以将不同数据文件创建到不同的磁盘中，有利于管理磁盘空间，有利于提高I/O性能，有利于备份和恢复数据等。

3.创建表空间和删除表空间语法

-- 创建tp_orders表空间名字,文件大小为10MB
--'E:\Oracle\Oracle11g\WORKTBS01.DBF'   文件路径名称
CREATE TABLESPACE tp_orders
DATAFILE 'E:\Oracle\Oracle11g\WORKTBS01.DBF' 
SIZE 10M AUTOEXTEND ON;

--删除表空间之前最好提交对数据库备份
DROP TABLESPACE tp_orders

2.自定义用户管理

sys； //系统管理员，拥有最高权限
system；//本地管理员，次高权限
scott；//普通用户，密码默认为tiger,默认未解锁

1.创建用户和删除用户

-- 创建A_oe用户,默认空间tp_orders,临时空间temp 密码8898024
CREATE USER A_oe
IDENTIFIED BY 8898024
DEFAULT TABLESPACE tp_orders
TEMPORARY TABLESPACE temp;

--删除用户
DROP USER A_oe CASCADE；

3.数据库权限管理

1.系统权限

系统权限是指被授权用户是否可以链接到数据库上及在数据库中可以进行那些系统操作，系统权限是在数据库中执行某种系统级别的操作，或者针对某一类的对象执行某种操作的权利。
常见的系统权限如下：

CREATE SESSION 创建会话
CREATE SEQUENCE 创建序列
CREATE SYNONYM 创建同名对象
CREATE TABLE 在用户模式中创建表
CREATE ANY TABLE 在任何模式中创建表
DROP TABLE 在用户模式中删除表
DROP ANY TABLE 在任何模式中删除表
CREATE VIEW 创建视图

2.对象的权限

grant 权限 on 对象名 to username[with grant option 同时获得权限分配权]；

revoke 权限 on 对象名 from username;

不同的对象具有不同的对象权限,对象的拥有者拥有所有权限,对象的拥有者可以向外分配权限.关于授予系统权限与授予对象权限的语法差异：授予对象权限时需要指定关键字ON，从而能够确定权限所应用的对象.对于表和视图可以指定特定的列来授权。

授予和撤销用户权限示例：

--授予CONNECT和RESOURCE权限
GRANT CONNECT, RESOURCE TO A_oe;  
-- 撤销CONNECT和RESOURCE权限
REVOKE CONNECT, RESOURCE FROM A_oe;  
-- 允许用户查看EMPLOYEE表中的记录
GRANT SELECT ON SCOTT.EMPLOYEE TO A_oe; 
-- 允许用户修改EMPLOYEE表中的记录
GRANT UPDATE ON SCOTT.EMPLOYEE TO A_oe；

二.序列

Oracle提供了sequence对象,由系统提供自增长的序列号,用于生成数据库数据记录的自增长主键或序号的地方。如果当前的序列不存在，它会创建一个序列，如果存在，它首先要得到当前序列的最大值，然后再加一，实现自增长的功能。

INCREMENT BY：指定序列号之间的间隔，该值可为正的或负的整数，但不可为0。序列为升序。忽略该子句时，缺省值为1。

START WITH：指定生成的第一个序列号。在升序时，序列可从比最小值大的值开始，缺省值为序列的最小值。对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值。

MAXVALUE：指定序列可生成的最大值。

NOMAXVALUE：为升序指定最大值为1027，为降序指定最大值为-1。

MINVALUE：指定序列的最小值。

NOMINVALUE：为升序指定最小值为1。为降序指定最小值为-1026。

NOCYCLE：一直累加，不循环。

创建序列示例：

-- 创建序列从60开始,间隔10,最大1000,不循环,缓存30个序列号
CREATE SEQUENCE dept_seq
	START WITH 60
	INCREMENT BY 10
	MAXVALUE 1000
	NOCYCLE
	CACHE 30;

访问序列示例：

-- 序列中插入部门dept
INSERT INTO SCOTT.DEPT
VALUES(dept_seq.NEXTVAL,'SIIHDMNX','KJSKJS')
INSERT INTO SCOTT.DEPT
VALUES(dept_seq.NEXTVAL,'ISJKDKNDK','XMNJHXAK')

删除序列示例：

-- 删除序列号
DROP SEQUENCE dept_seq

序列CACHE的优缺点:
优点:快，高并发时不会频繁加锁，
缺点:服务器意外停机会导致序号丢失(跳号)

三.同义词

1.同义词的用途

简化SQL语句
隐藏对象的名称和所有者
为分布式数据库的远程对象提供了位置透明性
提供对对象的公共访问

2.同义词分类

同义词分为两类，私有同义词和公有同义词

公用Oracle同义词:由一个特殊的用户组Public所拥有。顾名思义，数据库中所有的用户都可以使用公用同义词。公用同义词往往用来标示一些比较普通的数据库对象，这些对象往往大家都需要引用。

-- 创建公有同义词  权限授予PUBLIC角色
CREATE PUBLIC SYNONYM  p_sy_dept FOR SCOTT.DEPT

私有Oracle同义词:它是跟公用同义词所对应，他是由创建他的用户所有。当然，这个同义词的创建者，可以通过授权控制其他用户是否有权使用属于自己的私有同义词。

--创建私有同义词 
CREATE SYNONYM SY_EMP FOR SCOTT.DEPT

共有同义词和私有同义词的区别：

私有同义词只能在当前模式下访问，且不能与当前模式的对象同名。
公有同义词可悲所有的数据库用户访问。

删除同义词示例

--删除私有同义词
DROP SYNONYM SCOTT.DEPT;
--删除公有同义词
DROP PUBLIC SYNONYM SCOTT.DEPT;

四.索引

1.什么是索引

在oracle索引是一种供服务器在表中快速查找一个行的数据库结构。合理使用索引能够大大提高数据库的运行效率。
在数据库中建立索引主要有以下作用。

快速存取数据。
既可以改善数据库性能，又可以保证列值的唯一性。
实现表与表之间的参照完整性
在使用orderby、groupby子句进行数据检索时，利用索引可以减少排序和分组的时间。

2.为什么能够提高查询速度

索引就是通过事先排好序，从而在查找时可以应用二分查找等高效率的算法。
一般的顺序查找，复杂度为O(n)，而二分查找复杂度为O(log2n)。当n很大时，二者的效率相差及其悬殊。

3.索引分类

B-TREE INDEX，B树索引：
在创建索引时他是默认的索引类型，B树索引可以使单一列（简单）的索引，也可以是多个列（组合/复合）的索引。最多可以包括32列。适合：1，访问表中占很小比例的行，这样就可以使用索引快速定位。 2，根本不访问表，所需查询的数据全部在索引中，比如查询的列就是索引，这样直接访问索引就可以得到结果。,多使用在OLTP系统中。

BITMAP-INDEX，位图索引：
是在列的值重复的非常多的情况下使用。当大多数条目比不会向位图添加新的值时，位图索引在加载表时比B树索引做的好。由于位图索引最大的用处就是当列值有很多重复的时候，所以他不能被声明成唯一索引。当使用alter table修改有位图索引的列的时候，位图索引失效，需重建索引。位图索引里是101010这类的，所以是不包含任何列数据的。

--在员工（employee）表中，为工种（job）列创建位图索引
CREATE BITMAP INDEX index_bit_job ON employee(job);

唯一索引和非唯一索引：
唯一索引：定义索引的列中任何两行都没有重复值，唯一索引中的索引关键字只能指向表中的一行。在创建主键唯一约束都会创建一个与之对应的唯一索引

--  在薪水级别（salgrade）表中，为级别编号（grade）列创建唯一索引
CREATE UNIQUE INDEX index_unique_grade ON salgrade(grade);

非唯一索引：单个关键字可以有多哥与其关联的行

反向键索引：
与常规B输索引相反，反向键索引在保持列顺序的同事反转索引列的字节。反向键索引通过反转索引键的数据值来实现。其优点是对于连续增长的索引列，反转索引列可以将索引数据分散在多个索引块间，减少I/O瓶颈的发生。

--   在员工（employee）表中，为员工编号（empno）列创建反向键索引
CREATE INDEX index_reverse_empno ON employee(empno) REVERSE;

删除索引：

1. 删除索引
DROP INDEX index_bit_job;

五.分区表

1.什么是分区表

随着表中行数的增多，管理和性能性能影响也将随之增加。备份将要花费更多时间，恢复也将要花费更说的时间，对整个数据表的查询也将花费更多时间。通过把一个表中的行分为几个部分，可以减少大型表的管理和性能问题，以这种方式划分发表数据的方法称为对表的分区。
分区表的优势：

改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度；
方便数据管理：因为分区表的数据存储在多个部分中，所以按分区加载和删除数据比在大表中加载和删除数据更容易；
方便备份恢复：因为分区比被分区的表要小，所以针对分区的备份和恢复方法要比备份和恢复整个表的方法多。

2.分区表的分类

范围分区：
范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等（联通每个月的账单记录就用的分区表存储）。

使用范围分区：

-- 分区跟新建表一起使用
create table graderecord  
  (  
  sno varchar2(10),  
  sname varchar2(20),  
  dormitory varchar2(3),  
  grade int  
)  
partition by range(grade)  
(  
  partition bujige values less than(60), --不及格  
  partition jige values less than(85), --及格  
  partition youxiu values less than(maxvalue) --优秀  maxvalue表示不在指定范围内记录
)  
-- 新增数据
insert into graderecord values('511601','魁','229',92);  
insert into graderecord values('511602','凯','229',62);  
insert into graderecord values('511603','东','229',26);  
insert into graderecord values('511604','亮','228',77);  
insert into graderecord values('511605','敬','228',47);  
insert into graderecord(sno,sname,dormitory) values('511606','峰','228');  
insert into graderecord values('511607','明','240',90);  
insert into graderecord values('511608','楠','240',100);  
insert into graderecord values('511609','涛','240',67);  
insert into graderecord values('511610','博','240',75);  
insert into graderecord values('511611','铮','240',60);  

-- 查看分区
select * from graderecord;  
select * from graderecord partition(bujige);  
select * from graderecord partition(jige);  
select * from graderecord partition(youxiu);  

-- 删除分区
DELETE FROM graderecord partition(jige)

散列分区（hash分区：
除范围分区外，Oracle还支持散列分区。散列分区通过在分区键值上执行一个散列函数来说决定数据的物理位置。在范围分区中，分区键的连续值通常储存在相同的分区中。而在散列分区中，连续的分区键值不必储存在相同的分区中。散列分区把记录分布在比范围分区更多的分区上，这减少了I/O争用的可能性。

列表分区：
列表分区告诉Oracle所有可能的值，并指定应该插入相应行的分区，它适用于表的数据量很大但是某一列的值只有少量几种。

复合分区（子分区：
有时候我们需要根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区内再使用散列分区/列表分区的一种分区方法。

间隔分区(Interval):
是范围分区的一种增强功能，可以实现范围分区的自动化
它的优点为在不需要创建表时就将所有分区划分清楚。间隔分区随着数据的增加会划分更多分区，并自动创建新的分区

使用间隔分区(Interval)：

--1.利用现有表Orders创建间隔分区表intervalOrders
CREATE TABLE intervalOrders
		PARTITION BY RANGE(order_date)
		INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
		(PARTITION P1 VALUES LESS THAN (to_date('2005-01-01','yyyy/mm/dd')))
        AS SELECT * FROM Orders;

--2.查询分区情况
	SELECT table_name,partition_name 
   	  FROM user_tab_partitions 
	 WHERE table_name=UPPER('intervalOrders');
--3.向表插入'2013/01/01'数据
insert into intervalOrders values (1001,to_date('2013-01-01','yyyy-mm-dd'),'direct',101,0,1000,153,null);

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。