Oracle笔记

Oracle笔记

建表SQL

-- 删除表
DROP TABLE T_TESTA;
-- 创建表
CREATE TABLE T_TESTA (
    ID              NVARCHAR2(32)                  NOT NULL,
	TEST_COLUMN1	NVARCHAR2(10) DEFAULT '0'      NOT NULL,
    TEST_COLUMN2	NVARCHAR2(20)                          ,
    TEST_COLUMN3	NVARCHAR2(30)                  NOT NULL,
	TM_SMP          NVARCHAR2(16)                  NOT NULL
) 
-- 创建表分区(范围表 其他分区表的自行查找)
PARTITION BY RANGE(TM_SMP) (
	PARTITION "PAR_RANGE_QUARTER1" VALUE LESS THAN (TO_DATE('2022-04-01','YYYY-MM-DD')),
    PARTITION "PAR_RANGE_QUARTER2" VALUE LESS THAN (TO_DATE('2022-07-01','YYYY-MM-DD')),
    PARTITION "PAR_RANGE_QUARTER3" VALUE LESS THAN (TO_DATE('2022-10-01','YYYY-MM-DD')),
    PARTITION "PAR_RANGE_QUARTER4" VALUE LESS THAN (TO_DATE('2023-01-01','YYYY-MM-DD')),
)
TABLESAPCE TS_TEST;

-- 创建主键、索引
ALTER TABLE T_TESTA ADD PK_T_TESTA CONSTRAINT PRIMARY KEY (ID);
CREATE UNIQUE INDEX UN1_T_TESTA ON T_TESTA(TEST_COLUMN1, TEST_COLUMN3);
CREATE INDEX NI1_T_TESTA ON T_TESTA(TEST_COLUMN2);

-- 注释信息
COMMENT ON TABLE T_TESTA IS '测试表';
COMMENT ON COLUMN T_TESTA.ID IS '主键';
COMMENT ON COLUMN T_TESTA.TEST_COLUMN1 IS '测试字段1';
COMMENT ON COLUMN T_TESTA.TEST_COLUMN2 IS '测试字段2';
COMMENT ON COLUMN T_TESTA.TEST_COLUMN3 IS '测试字段3';

索引

1. 概念和作用

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

• 快速存取数据。

• 既可以改善数据库性能，又可以保证列值的唯一性。

• 实现表与表之间的参照完整性

• 在使用orderby、groupby子句进行数据检索时，利用索引可以减少排序和分组的时间。

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

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

3. 建立索引

目的：提高对表的查询速度；对表有关列的取值进行检查。

CREATE [unique] INDEX [user.]index
ON [user.]table (column [ASC | DESC] [,column
[ASC | DESC] ] ... )
[CLUSTER [scheam.]cluster]
[INITRANS n]
[MAXTRANS n]
[PCTFREE n]
[STORAGE storage]
[TABLESPACE tablespace]
[NO SORT]
Advanced

例如：

create index big_index on big_data_tbl(id);

其中：

schema：ORACLE模式，缺省即为当前帐户

index：索引名

table：创建索引的基表名

column：基表中的列名，一个索引最多有16列，long列、long raw列不能建索引列

DESC、ASC：缺省为ASC即升序排序

CLUSTER：指定一个聚簇（Hash cluster不能建索引）

INITRANS、MAXTRANS：指定初始和最大事务入口数

Tablespace：表空间名

STORAGE：存储参数，同create table 中的storage.

PCTFREE：索引数据块空闲空间的百分比(不能指定pctused)

NOSORT：不（能）排序（存储时就已按升序，所以指出不再排序）

注意：

• 一个基表不能建太多的索引；

• 空值不能被索引；

• 只有唯一索引才真正提高速度,一般的索引只能提高30%左右。

4. 修改索引

修改索引名

-- 修改索引名 INDEX_NAME1 改为 INDEX_NAME2
ALTER INDEX  INDEX_NAME1 RENAME TO INDEX_NAME2

修改索引的主要任务是修改已存在索引的存储参数适应增长的需要或者重新建立索引。

ALTER [UNIQUE] INDEX [user.]index
[INITRANS n]
[MAXTRANS n] 
REBUILD 
[STORAGE n]

其中：
  REBUILD是根据原来的索引结构重新建立索引，实际是删除原来的索引后再重新建立。

提示：
  DBA经常用REBUILD来重建索引可以减少硬盘碎片和提高应用系统的性能。

5. 删除索引

当不需要时可以将索引删除以释放出硬盘空间。命令如下：

DROP INDEX [schema.]indexname

注：当表结构被删除时，有其相关的所有索引也随之被删除。

6. 索引建立原则总结

• 如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引。

• 至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）。

• 小表不要建立索引。

• 对于基数大的列适合建立B树索引，对于基数小的列适合简历位图索引。

• 列中有很多空值，但经常查询该列上非空记录时应该建立索引。

• 经常进行连接查询的列应该创建索引。

• 使用create index时要将最常查询的列放在最前面。

• LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引。

• 限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）。

数据迁移

1. 授权表的操作权限

-- 授权test01的test_table的SELECT,INSERT,UPDATE,DELETE(ALL是指所有)权限给用户test02
GRANT SELECT,INSERT,UPDATE,DELETE,ALL ON test01.T_TEST_A TO test02

2.创建存储过程进行表的数据迁移

-- 创建名叫'DATA_MIGRATE' 的存储过程
CREATE OR REPLACE PROCEDURE DATA_MIGRATE IS 
BEGIN 
    -- A表字段迁移到B表,其中T_TEST_B表INSERT的顺序要和
	INSERT INTO T_TEST_B (ID,NAME,TYPE)
	SELECT ID,
	-- 如果NAME为空就赋默认值'姓名为空'
	CASE WHEN (NAME IS NULL OR NAME = '') THEN '姓名为空' END AS NAME,
	-- TYPE类型转义
    CASE 
    WHEN TYPE = '1' THEN 'A'
    WHEN TYPE = '2' THEN 'B' END AS TYPE
    FROM test01.T_TEST_A
    -- 迁移条件
    WHERE DATE = '20220418';
END;	 

注意：其中T_TEST_B表字段的数据类型、数据长度、是否非空要特别注意，非常容易在这里出问题。

表备份

-- 只备份表结构
CREATE TABLE NEW_TABLE_NAME AS SELECT * FROM ORIG_TABLE_NAME WHERE 1 <> 1;

-- 表结构、表数据都备份
CREATE TABLE NEW_TABLE_NAME AS SELECT * FROM ORIG_TABLE_NAME;

-- 备份表数据
INSERT INTO NEW_TABLE_NAME SELECT * FROM ORIG_TABLE_NAME;

-- 1. CREATE TABLE ... AS SELECT * FROM ... 不能用这个语句向已经存在的表中插入数据，因为插入时会自动创建一张新表;目标表的结构和数据依赖于来源表,会复制来源表的基本结构（列名，数据类型，是否允许为null及identity属性）和数据,语句不会复制：约束，索引以及触发器
-- 2. 语句是将一组由select查询返回的结果插入到目标表（已经存在）。两张字段数量不一致，选择相同字段或者只针对性的选择部分数据。

表分区

-- 查看用户的表信息分区
SELECT * FROM ALL_PART_TABLES WHERE OWNER = 'USERNAME';

-- 查看某表的表分区
SELECT * FROM ALL_TAB_PARTITIONS WHERE TABLE_OWNER = 'USERNAME' AND TABLE_NAME = 'T_TEST';

-- 新增表分区
ALTER TABLE T_TEST ADD PARTITION PAR_20220624 VALUES LESS THAN ('20220624');

常用的SQL

1.Oracle数据库锁表时执行

-- Oracle数据库锁表时执行
exec kill_user_table_lock('schema','tableName');

2.查看Oracle表数据为空的表

-- 查看Oracle表数据为空的表
select * from ALL_ALL_TABLES where 
(TABLE_NAME like '%table_filter%' and OWNER = 'schemaName' and NUM_ROWS = '0');

3.查询符合相应条件的表名和表注释

-- 查询符合相应条件的表名和表注释(这里过滤条件是是'T_TST')
select a.TABLE_NAME, b.COMMENTS 
from user_tables a,user_tab_comments b
where (a.TABLE_NAME = b.TABLE_NAME and a.TABLE_NAME like '%T_TST%')
order by TABLE_NAME;

4.删除表数据

-- 清空表 如果数据量大且没有删除条件，建议使用truncate，效率比delete... from高很多
truncate table 表名

-- 带条件删除
delete from 表名 where 条件

5.修改表结构

-- 已存在的表，新增表字段和表注释
alter table t_test add (test_column varchar2(50) default '0' not null);
comment on column 't_test'.'test_column' is '测试字段';

-- 已存在的表,修改表字段和表注释
alter table t_test modify (test_column varchar2(60));
comment on column 't_test'.'test_column' is '测试字段-已修改';

-- 已存在的表,删除表中某列
alter table t_test drop column test_column;

-- 已存在的表,修改字段名
alter table t_test rename column test_column to test_column1;

6.创建序列

-- 创建序列
create sequence "USER_TEST"."S_TEST" minvalue 1 maxvalue 99999999 increment by 1 start with 1 cache 100 noorder cycle;

7.删除表中重复数据

-- 查询/删除某行重复的数据
-- 比如表t_tst_proerty有两个字段:property_key,property_value,现在要找出perperty_value重复数据
-- 方法一:数据量不是很大的情况(小于十万条数据)
select property_key, property_value from t_tst_proerty
where property_value in (
select property_value from t_tst_proerty group by property_value having count(property_value) > 1)
order by property_key asc;

-- 删除重复的数据
delete from (select property_key, property_value from t_tst_proerty
where property_value in (
select property_value from t_tst_proerty group by property_value having count(property_value) > 1)
order by property_key asc); 

-- 方法二:数据量比较大的情况(数据量大于十万条)
select * from t_tst_proerty a where 
rowid != (select max(rowid) from t_tst_proerty b
where a.property_value = b.property_value and a.property_key != b.property_key)
order by property_key asc;

8.查询表中索引的信息(表名、表注释、索引名、索引字段、索引中文注释 )

select column_table.table_name,tc.comment,inx_col.index_column,ind
(select table_name from user_tab_columns group by table_name order by table_name) column_table