第二周周报

要求：

1、了解索引、序列、触发器等；

2、学习存储过程，并要求熟练掌握；

3、了解sql语句优化，数据库表空间查询管理，session管理；

 

简要说明我的学习过程和问题及解决

1，group by来分组数据时特别说明了select 列表项中不存在的列可以出现在group by的列表项中，但反过来就不行了，在select列表项中出现的列必须全部出现在group by后面

2，FIRST_VALUE返回组中数据窗口的第一个值。有些类似于SQL Server中的first()。oracle 中很多函数与SQL92标准中的函数不一致，出现关键字无效这样的错误，可以首先查询是不是内置函数不一致。

3，在进行SQL语句编写时，会遇到大量的同时进行Insert/Update的语句，也就是说当存在记录时，就更新(Update)，不存在数据时，就插入(Insert)。merge into 语法不仅没有if exists语法啰嗦，而且比if exists还要高效很多。

4，查询一张表的列数目！selectcount(column_name) from user_tab_columns where table_name=upper('qj_test');

网上查找大量资料，都是错误的解法，目前只了解到此方法查询出列数目！

5，快速粘贴复制excel的方法，SELECT * FROM qj_test8 FOR UPDATE前提是表的字段和excel的字段要匹配上打开锁,再按一下加号.鼠标点到第一列的列头，使全列成选中状态，然后粘贴，最后commit提交即可

6，过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据

7，存储过程的写法中，(qj_autoid IN qj_test5.qj_autoid%TYPE) 修改为(qj_autoid IN NUMBER)如果不修改，则需要在相应的位置处添加：，SQL%NOTFOUND 是一个布尔值。与最近的sql语句（update,insert,delete,select）发生交互，当最近的一条sql语句没有涉及任何行的时候，则返回true。否则返回false。这样的语句在实际应用中，是非常有用的。例如要update一行数据时，如果没有找到，就可以作相应操作。

8，当数据库或PL/SQL在运行时发生错误时，一个异常被PL/SQL运行时引擎自动抛出。异常也可以通过RAISE语句抛出RAISE exception_name;

9，符号的说明，||用来连接字符串

10，使用过程与函数的原则：1、如果需要返回多个值和不返回值，就使用过程；如果只需要返回一个值，就使用函数。2、过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。3、可以SQL语句内部（如表达式）调用函数来完成复杂的计算问题，但不能调用过程。所以这是函数的特色。

11，体验索引的作用，先写了一个存储过程在一张表中插入了10W数据，结论是加索引和不加索引几乎没有区别，于是重新写了一个插入500W随机生成数据的4张表，索引添加之后，效率果然大幅度提升了，结果：17 18表没有建立索引，17表是15表的复制  18表是16表的复制数据完全一样，执行查询同样的语句，加索引的15 16表只用了0.032s而没加索引的 17 18 表却用了24.149s。（用一张加索引的表和一张没有加索引表多表查询，效率和两张都没有加索引的表基本没有差别）

12,oracle 随机生成函数

1、小数( 0 ~ 1) select dbms_random.value from dual

2、指定范围内的小数 ( 0 ~ 100 ) select dbms_random.value(0,100) from dual

3、指定范围内的整数 ( 0 ~ 100 )select trunc(dbms_random.value(0,100)) from dual

4、长度为20的随机数字串select substr(cast(dbms_random.value as varchar2(38)),3,20) from dual

5、正态分布的随机数select dbms_random.normal from dual

6、随机字符串select dbms_random.string(opt, length) from dual

13，select * from user_indexes where table_NAME ='QJ_TEST15';可以查看表添加的索引  ！！！select * from user_ind_columns where index_name='QJ_TESTINDEX'建好的索引，查看它建立在哪个表的哪个列上

14，回话和JOB的查询，select * from v$session  WHERE machine LIKE '%QIJIN%'

SELECT * FROM DBA_JOBS_RUNNING

15，sql优化，当一个sql语句提交后，oracle会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。

16，基于oracle的应用系统很多性能问题，是由应用系统sql性能低劣引起的，所以，sql的性能优化很重要，分析与优化sql的性能我们一般通过查看该sql的执行计划，本文就如何看懂执行计划，以及如何通过分析执行计划对sql进行优化做相应说明。

17，学会了查询动态性能表，select *

from v$db_object_cache

where name LIKE 'SELECT * FROM qj_test5%'

v$db_object_cache中的常用列：OWNER：对象拥有者
NAME：对象名称
TYPE：对象类型(如，sequence,procedure,function,package,package body,trigger)
KEPT：告知是否对象常驻shared pool(yes/no)，有赖于这个对象是否已经利用PL/SQL 过程DBMS_SHARED_POOL.KEEP“保持”（永久固定在内存中）
SHARABLE_MEM：共享内存占用
PINS：当前执行对象的session数
LOCKS：当前锁定对象的session数

18，用Where子句替换HAVING子句，避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

19，在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历).  使用NOT EXISTS 子句可以有效地利用索引。尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查询效率更高。

20，尽量多使用COMMIT回滚段上用于恢复数据的信息.被程序语句获得的锁redo log buffer 中的空间ORACLE为管理上述3种资源中的内部花费，通过使用>=、<=等，避免使用NOT命令，避免在索引列上使用计算

21，分区表，当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。