oracle

一、SQL简介 SQL 全名是结构化查询语言（Structured Query Language），是用于数据库中的标准数据查询语言，

IBM 公司最早使用在其开发的数据库系统中。1986年10月，美国 ANSI 对 SQL 进行规范后，以此作为关系式数据库

管理系统的标准语言 (ANSI X3. 135-1986)，1987年得到国际标准组织的支持下成为国际标准。


二、SQL的分类： DML语句（数据操作语言）Insert、Update、 Delete、Merge 、Select

DDL语句（数据定义语言）Create、Alter、 Drop、Truncate

DCL语句（数据控制语言）Grant、Revoke 事务控制语句Commit 、Rollback、Savepoint


三、SQL运算符 算术表达式 + - / *

连接运算符 || 空值 is null

比较运算符 > < (!= or <>) between and

in操作 not in 模糊查询 like 去除重复行

distinct 逻辑运算符 or and not


四、通用函数 NVL (exp1, exp2)：如果表达式exp1 为空，返回exp2；

NULLIF (exp1, exp2) ：如果表达式exp1与exp2的值相等则返回null，否则 返回exp1的值；


五、分组函数 Count:返回结果集的记录数；(如果结果是为空会怎样？)

Avg: 返回平均数； Max: 返回最大值； Min: 返回最小值； Sum: 返回数值和；

六、转换函数 ：

To_char将日期转化为字符串；

To_number将给出的字符转换为数字；

To_date将字符串转化为日期 。
---------------------------------------------------------------------------------

一、什么是索引？

一种用于提升查询效率的数据库对象； 通过快速定位数据的方法，减少磁盘I/O操作；

索引信息与表独立存放； ORACLE数据库自动使用和维护索引；


二、索引的分类 唯一性索引； 非唯一性索引；


三、索引的创建 ：

自动创建：在定义主键或唯一键约束时系统会自动在相应的字段创建唯一性索引；

手动创建：用户可以在其他列上创建非唯一性索引；


四、索引优缺点 ：

优点：1.大大加快数据的检索速度; 2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接; 4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点： 1.索引需要占物理空间。 2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度；


五、创建索引的原则：

创建索引：创建索引一般有以下两个目的：维护被索引列的唯一性和提供快速访问表中数据的策略。

1.在select操作占大部分的表上创建索引； 2.在where子句中出现最频繁的列上创建索引；

3.在选择性高的列上创建索引（补充索引选择性，最高是1，eg：primary key）

4.复合索引的主列应该是最有选择性的和where限定条件最常用的列，并以此类推第二列……。

5.小于5M的表，最好不要使用索引来查询，表越小，越适合用全表扫描。


六、使用索引的原则：

1.查询结果是所有数据行的5%以下时，使用index查询效果最好；

2. where条件中经常用到表的多列时，使用复合索引效果会好于几个单列索引。

因为当sql 语句所查询的列，全部都出现在复合索引中时，

此时由于 Oracle 只需要查询索引块即可获得所有数据，当然比使用多个单列索引要快得多；

3.索引利于select，但对经常insert，delte尤其update的表，会降低效率。


七、创建索引：

create index abc on student(sid,sname); create index abc1 on student(sname,sid);

这两种索引方式是不一样的 索引abc对Select * from student where sid=1;

这样的查询语句更有效 索引abc1对Select * from student where sname=‟louis‟;

这样的查询语句更有效 因此建立索引的时候，字段的组合顺序是非常重要的。

一般情况下，需要经常访问的字段放在组合字段的前面 。


八、管理索引 ：

1）先插入数据后创建索引 向表中插入大量数据之前最好不要先创建索引，因为如果先建立索引。那么在插入每行数据的时候都要更改索引。

这样会大大降低插入数据的速度； 2）设置合理的索引列顺序 ； 3）限制每个表索引的数量 ； 4）删除不必要的索引；

5）为每个索引指定表空间； 6）经常做insert，delete尤其是update的表最好定期exp/imp表数据，整理数据，降低碎片；

有索引的最好定期rebuild索引（rebuild期间只允许表的select操作，可在数据库较空闲时间提交），以降低索引碎片，提高效率 。
---------------------------------------------------------------------------------

共享SQL语句 为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.

这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享.

因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,

ORACLE就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用.

ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询.


数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.

当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句. 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,

要达成共享,SQL语句必须完全相同(包括空格,换行等)。


共享的语句必须满足三个条件：

A. 字符级的比较: B. 两个语句所指的对象必须完全相同。

C. 两个SQL语句中必须使用相同的名字的绑定变量(bind variables) 。

-------------

1、选择最有效率的表名顺序(只在基于规则的优化器中有效)

2、如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

3、WHERE子句中的连接顺序． ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他

WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

4、SELECT子句中避免使用 ‘ * ‘ 当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用

‘*' 是一个方便的方法. 这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名,

这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间. 减少访问数据库的次数 当执行每条SQL语句时,

ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见,

减少访问数据库的次数 , 就能实际上减少ORACLE的工作量。

5、使用DECODE函数来减少处理时间 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

6、用TRUNCATE替代DELETE 当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息.

如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时,

回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短。

7、使用表的别名 当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上。

这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误 。


8、用Where子句替换HAVING子句 避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤.

这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。

9、尽量多使用COMMIT 只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:

COMMIT所释放的资源: a. 回滚段上用于恢复数据的信息. b. 被程序语句获得的锁 c. redo log buffer 中的空间 d. ORACLE为管理

上述3种资源中的内部花费 (在使用COMMIT时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼) 。

10、减少对表的查询 在含有子查询的SQL语句中,要特别注意减少对表的查询。

11、尽量用EXISTS替代IN、用NOT EXISTS替代NOT IN 在许多基于基础表的查询中，为了满足一个条件,往往需要对另一个表进行联接。

在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。在子查询中，NOT IN子句将执行一个内部的排序和合并。

无论在哪种情况下，NOT IN都是低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ，

我们可以把它改写成外连接(Outer Joins)或NOT EXISTS，下面的可以作为参考。

12、1、查询表与子查询表的数据量不多，两者的性能差异不大； 2、查询表数据量少，子查询数据量大，利用索引情况下，

则采用exists一定较in性能好； 3、查询表数据量大，子查询数据量小，采用in效果会更佳； 4、两者数据量都较大，采用exists会更好些 。


13、用EXISTS替换DISTINCT 当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT子句中使用DISTINCT。

一般可以考虑用EXIST替换，EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果 SQL语句用大写的；

因为Oracle总是先解析SQL语句，把小写的字母转换成大写的再执行 在Java代码中尽量少用连接符“＋”连接字符串 。


14、避免在索引列上使用NOT通常，我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响。

当Oracle“遇到”NOT,他就会停止使用索引转而执行全表扫描 。

15、避免在索引列上使用计算。WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描

16、用>=替代>

17、高效：SELECT * FROM EMP WHERE DEPTNO >=4 低效: SELECT * FROM EMP WHERE DEPTNO >3 两者的区别在于，

前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录 。

18、避免在索引列上使用IS NULL和IS NOT NULL 。

19、总是使用索引的第一个列： 如果索引是建立在多个列上, 只有在它的第一个列被where子句引用时,优化器才会选择使用该索引。

这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引 ；

尽量不用通配符“%”或者“_”作为查询字符串的第一个字符。

20、总是使用索引的第一个列： 如果索引是建立在多个列上, 只有在它的第一个列被where子句引用时,优化器才会选择使用该索引。

这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引 。