oracle部分详解编程语言

***oracle 中 rownum 与 rowid 的理解

   rownum 和 rowid 都是伪列，但两者的根本是不同的。rownum 是随查询结果动态改变的，而 rowid 是一个物理地址，硬盘物理数据不变就不会变。

   rownum 是根据 sql 查询出的结果给每行分配一个逻辑编号，所以你的 sql 不同也就会导致最终 rownum 不同，一般常用在数据库分页中。

   rowid 是物理结构上的，在每条记录 insert 到数据库中时，都会有一个唯一的物理记录（不会变），开发中常到的是过滤重复数据。

 

***union 和 union all 有什么不同

   union 和 union all 都是对两个表的查询结果就归并到一起，所以首先要求两个查询语句的结构要完全一致（查询字段名称、顺序要一样）。不同点在于 union 是对两个查询的结果进行按默认规则排序并删除重复记录， 而 union all 只是简单的对两个查询结果进行合并返回。所以从效率上来说 union all 要快的多（如果说两个查询语句中确认没有重复的数据，建议优先使用 union

 

***truncate 和 delete 的区别

   truncate 和 delete 都可以对表中的数据进行删除处理。

   1、delete 属于 DML，一般是在应用程序中操作，删除数据时一般会加上 where条件进行过滤；truncate 一般是在 Oracle 客户端上执行，不需要过滤条件。

   2、delete 删除数据是一行一行的删除，同时会记录删除日志，如果出现误删除的数据，可以从删除日志中恢复；truncate 是按照页进行删除，不会记录操作日志，删除会数据将不能再被找回。truncate 的效率将高于 delete。

   3、delete 如果绑定了触发器，删除时将会触发触发器；而 truncate 不会。

   4、delete 删除数据时将会先锁定各行，truncate 将是暴力删除。

   5、如果有 identity 产生的自增 id 列，delete from 后仍然从上次的数开始增加， 即种子不变，而 truncate 后，种子会恢复初始。

   6、用 truncate 删除运行中的相关表数据时，一定要备份。

 

***谈谈你对 SQL 优化的看法

   SQL 优化主要分为两方面，一方面为 DB 服务端优化，一方面为应用中的 SQL 优化。

   DB 服务端优化：

   1、合理的创建索引，尤其是查询系统，数据量大的时候必须有索引。

   2、如果查询的 SQL 特别复杂的时候，而且应用端又不能优的时候，建议封装成存储过程调用。

   3、进可能使用数据库提供的原始函数。

   应用端的优化：

   1、尽量避免复杂的 SQL 语句，如果嵌套层次很深的语句，最好分成几个子语句进行操作。

   2、合理排列过滤条件，根据数据库的解析的不同，尽量把过滤性大的条件放到先解析的位置。mysql 是从左到右解析（从上到下），Oracle 是从右向左解析（从下到上）；合理的排列过滤条件。

   3、尽量少用 select * 的查询，精确到字段；这样不仅能优化，在 ORM 映射上将会给你带来很大的好处，尤其是字段增添的维护上。

   4、避免滥用 in,not in ，根据业务逻辑合理的选择 exist,not exist 来替代。

   5、尽量减少与数据服务器的交互，减少连接时间，不要在循环中频繁的和数据库服务器交互。

   6、尽量避免在建立了索引的数据列上进行计算，not，<>，!=，IS NULL，IS NOT NULL 和使用函数的操作，索引字段不要有 null 值。

   7、尽可能的用 in 查询来代替 or 查询。

   8、尽量少用 like“%name%”这样的查询将不走索引。

   9、尽可能的不要到系统出现问题了再想到按照上面的方式去优化。

 

***存储过程与函数的区别

   1、相同点，两者都在数据服务端执行，可以减少应用服务器压力；都可以传入参数，都可以有返回值。

   2、函数只能有一个返回值，而存储过程可以返回多个，或者不返回。

   3、函数一般用来完成某个特定的计算，过程一般用来封装某些比较复杂的SQL执行或者业务处理。

   4、函数一般在 SQL（DML语句）语句中调用，调用灵活，不可以单独执行；存储过程一般通过特定的方式特用，可以在 PLSQL 中单独调用。

   5、函数的声明用 function，过程的声明用 procedure。

 

***exist 与 in 的区别

   1、exists 适用于主表小而从表大的情况，而 in 相反用在主表大从表小或者固定集合的情况。

   2、in 确定给定的值是否与子查询或列表中的值相匹配。

   3、exists 指定一个子查询，检测行的存在，是一个存在判断,如果后面的查询中有结果,则 exists 为真,否则为假。

   4、not in 和 not exists 如果查询语句使用了 not in 那么内外表都进行全表扫描，没有用到索引；而 not extsts 的子查询依然能用到表上的索引；所以无论那个表大，用 not exists 都比 not in 要快。

 

***oracle冷备份与热备份的区别

   冷备份发生在数据库已经正常关闭的情况下，将需要备份的文件进行拷备处理（最简单的方式就是整个库进行 COPY）；

   热备份是在数据库运行的情况下，采用归档方式备份数据，必须是在 archive log 模式下进行。

   冷备份的优点>>>

      1．备份方法速度快,容易归档

      2．容易恢复到某个时间点上

      3．与数据库归档的模式相结合可以使数据库很好地恢复。

      4．维护量少，安全度高。

   冷备份缺点>>>

      1．单独使用时，只能提供到“某一时间点上”的恢复。

      2．在实施备份的全过程中，数据库必须要作备份而不能作其它工作。也就是说，在冷备份过程中，数据库必须是关闭状态。

      3．若磁盘空间有限，只能拷贝到磁带等其它外部存储设备，速度会很慢。

      4．不能按表或按用户恢复。

 

   热备的优缺点>>>

      1．可在表空间或数据文件级备份，备份时间短。

      2．备份时数据库仍可使用。

      3．可达到秒级恢复。

      4．可对几乎所有数据库实体作恢复。

      5．恢复是快速的，在大多数情况下在数据库仍工作时恢复。

   热备份的缺点>>>                                                                                        

　  1．不能出错，否则后果严重。

      2．若热备份不成功，所得结果不可用于时间点的恢复。

      3．因难于维护，所以要特别仔细小心，不允许“以失败而告终”。

 

***oracle存储过程：

   存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集。存储过程是数据库中的一个重要对象。

   存储过程分类：

      1系统存储过程

      2本地存储过程

      3临时存储过程

      4远程存储过程

      5扩展存储过程

   存储过程优点：

      ①重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。

      ②减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。

      ③安全性。参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。

   存储过程缺点：

      1：调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。

      2：移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。

      3：重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。

      4： 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

 

***oracle索引

   1、索引概念：索引是类似于书本目录，用于加速数据存取的数据对象，是一种供服务器在表中快速查找一个行的数据库结构（简单说就是可以更快的查询表中数据）。

   2、索引作用：加速度数据库存取数据；合理使用可以减少磁盘 IO 的读取次数，提高查询效率；可以对列进行唯 一性约束；实现表与表之间的参照性完整性。

   3、索引类型：

      按存储分类：

        B*索引和位图索引（B*树索引的存储结构类似书的索引结构，有分支和叶两种类型的存储数据块，分支块相当于书的大目录，叶块相当于索引到的具体的书页。位图索引存储主要用于节省空间，减少oracle对数据块的访问。它采用位图偏移方式来与表的行 ID 号对应，采用位图索引一般是重复值太多的表字段）。

      按功能分类：

        唯一索引（不会有两行相同记录）；

        非唯一索引（不对索引列进行唯一限制）；

        分区索引（索引分散地存在多个不同的表空间，可以提高查询效率）；

        复合索引（对多个字段进行联合建立索引）；

        正向索引（创建索引时不必指定对其排序而使用默认的顺序）；

        反向索引（该索引同样保持列按顺序排列，但是颠倒已索引的每列的字节）等。

   4、索引的创建原则：

      索引字段建议建立 NOT NULL 约束 ；

      经常与其他表进行连接的表，在连接字段上应该建立索引；

      经常出现在 Where 子句中的字段且过滤性很强的，特别是大表的字段，应该建立索引；

      可选择性高的关键字 ，应该建立索引；

      可选择性低的关键字，但数据的值分布差异很大时，选择性数据比较少时仍然可以利用索引提高效率；

      复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替。

   5、索引创建语法：(顺序不同，索引不同)

      单索引：create index 索引名 on 表名(列名);

      复合索引：create index 索引名 on 表名(列名,列名…);

   6、索引的优缺点：

   优点：

      1）. 创建唯一性索引，保证数据库表中每一行数据的唯一性

      2）. 大大加快数据的检索速度，这也是创建索引的最主要的原因

      3）. 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

      4）. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

   缺点：

      1）. 索引创建在表上，不能创建在视图上

      2）. 创建索引和维护索引要耗费时间， 这种时间随着数据量的增加而增加

      3）.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大

      4）. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

 

***oracle视图

   视图是数据库的一个对象，封装了比较复杂查询的虚拟表 。

   Oracle 中的视图主要分为关系视图、内嵌视图、对象视图、物化视图。

      关系视图：最常用的视图，创建之后可以通过工具或数据字典来查看视图的相关信息。

      内嵌视图：内嵌的意义在于，视图定义嵌入在复杂查询语句中，其角色与普通数据表或关系视图相同。内嵌视图不必使用 create view 命令进行创建，因此， 在数据字典中也无法获得相应信息。

      对象视图：Oracle 中的对象数据实际仍然以关系数据的形式存储, 利用Oracle 内置视图 user_views 可以获得对象视图相关信息,具有封装、继承等对象的牲，可以构造复杂的对象类型来封装复杂的多表查询。

      物化视图：常用于数据库的容灾，不是传统意义上虚拟视图，是实体化视图， 和表一样可以存储数据、查询数据。主备数据库数据同步通过物化视图实现，主备数据库通过 data link 连接，在主备数据库物化视图进行数据复制。

   视图的优点：

   1.对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。

   2.用户通过简单的查询可以从复杂查询中得到结果。

   3.维护数据的独立性，试图可从多个表检索数据。

   4.对于相同的数据可产生不同的视图。

   5.视图可以屏蔽表的内部结构，提高安全。

 

***oracle游标

   游标定义就是从表中检索出结果集，从中每次指向一条记录进行交互的机制（或者可以理解为存放select集的一块内存区域）。

   Oracle 中的游标可以分为静态游标和REF游标（我比较喜欢用），静态游标又分为显示游标（必须声明定义）和隐式游标；

   游标的常用属性%rowcount 记录数，%found  影响到了记录，%notfound 没有影响到记录，%isopen 是否打开 。

 

***oracle触发器

   触发器概念：它是一个特殊的存储过程，它是基于事件由oracle系统自动触发，常用来加强数据的完事性约束和业务规则验证。

   触发器的种类：DML 触发器、替代触发器、系统触发器。

      1.DML触发器

        ORACLE 可以在 DML 语句进行触发，可以在 DML 操作前或操作后进行触发即为 BEFORE 触发器和 AFTER 触发器，并且可以对每个行或语句操作上进行触发又为语句级触发器和行级触发器。

      2.替代触发器

        由于在 ORACLE 里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。

      3. 系统触发器

        系统触发器在发生如数据启动或关闭等系统事件时触发，包括数据库服务器的启动或关闭、用户登录与退出、数据库服务错误等。

 

***死锁

   死锁产生的原因，主要是加了锁而又忘记提交事务或者忘记数据回滚造成的。

   Oracle中的死锁一般是行级死锁和表级死锁，如果是行级死锁，被锁定的行将不能再操作；如果是表级死锁，那么会看到应用端一直在等待。

   死锁的解决方法（万能方法，重启数据库）：

   1、定位产生死锁的位置

   2、杀死相应的进程。（下面这些表的操作往往是要管理员权限）

      1).   查找出被锁的表

      select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

      select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time; –这里会列出 SID

      2).   杀进程中的会话

      alter system kill session "sid,serial#";

 

***数据库中，delete、drop与truncate的区别
　　（1）DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
　　（2）表和索引所占空间。
当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，
DELETE操作不会减少表或索引所占用的空间。
drop语句将表所占用的空间全释放掉。
　　（3）一般而言，drop > truncate > delete
　　（4）应用范围。
　　TRUNCATE 只能对TABLE； DELETE可以是table和view
　　（5）TRUNCATE 和DELETE只删除数据， DROP则删除整个表（结构和数据）。
　　（6）truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。
　　（7）delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。
　　（8）truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚
　　（9）在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。
　　（10） Truncate table 表名 速度快,而且效率高,因为: 
truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。 
　　（11） TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。 
　　（12） 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
　　一、delete
　　1、delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。
　　2、delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。
　　3、delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。
　　二、truncate
　　1、truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
　　2、truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。
　　3、对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。
　　4、truncatetable不能用于参与了索引视图的表。
　　三、drop
　　1、drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
　　2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放。
　　3、drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

　　总结：
　　　　1、在速度上，一般来说，drop> truncate > delete。
　　　　2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。
　　　　3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；
　　　　如果想删除表，当然用drop； 
　　　　如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；
　　　　如果和事务有关，或者想触发trigger，还是用delete；
　　　　如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

 

***where、groupby、having、orederby的执行顺序
　　1、order by是按照某个字段进行排序，desc代表降序，esc代表升序。

　　2、group by是按照某个字段进行分组。

　　3、“Where” 是一个约束声明，使用Where来约束数据库的数据，Where是在结果返回之前起作用的，且Where中不能使用聚合函数。

　　4.“Having”是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。

where、having、group by、order by的顺序是：where -> group by -> having -> order by

 

***oracle中单引号和双引号分别用在什么地方
　　一.单引号

　　1.单引号用于标识字符与数字的区别;

　　例如

　　 　　 select * from table_name where id=1;这种代表查询的是数字

　　 　　 select * from table_name where id='1';这种代表查询的是字符

　　2.当指定字符串文本时，必须用单引号将字符串文本引注

　　3.两个单引号表示一个单引号（两个'' 表示了一个'）
　　二.双引号

　　1.如果创建对象的时候，对象名、字段名加双引号，则表示Oracle将严格区分大小写，否则Oracl都默认大写。

　　　　示例：

　　　　select '字段1' As "列a",'字段2' "小写b",'字段c' 默认大写c from dual; 
　　　　列名分别是：列a,小写b,默认大写C

　　2.双引号用在字符串中当成普通字符处理

当表中字段与oracle数据库关键字相同时，可以用双引号引注，这样可以避免冲突

　　3.双引号用在to_char函数里的字符串中时，有特殊作用，可以将特殊字符包起来，避免出现ORA-01821: date format not recognized错误。也就是说，去掉双引号和其包含的字符后，剩下的应该是一个合法的格式串。

　　例如：select to_char(sysdate, 'hh24"小时"mi"分"ss"秒"') ASRESULT from dual;

 

 ***解释归档和非归档模式之间的不同和它们各自的优缺点

　　归档模式是指你可以备份所有的数据库 transactions并恢复到任意一个时间点。

　　非归档模式则相反，不能恢复到任意一个时间点。但是非归档模式可以带来数据库性能上的少许提高.

 

***解释data block , extent 和 segment的区别(这里建议用英文术语)

　　data block是数据库中最小的逻辑存储单元。当数据库的对象需要更多的物理存储空间时，连续的data block就组成了extent . 一个数据库对象拥有的所有extents被称为该对象的segment.

 

***解释$ORACLE_HOME和$ORACLE_BASE的区别?

　　ORACLE_BASE是oracle的根目录，ORACLE_HOME是oracle产品的目录。

 

***oracle启动后的服务
　　1. Oracle ORCL VSS Writer Service：Oracle卷映射拷贝写入服务，VSS（Volume Shadow Copy Service）能够让存储基础设备（比如磁盘，阵列等）创建高保真的时间点映像，即映射拷贝（shadow copy）。可以在多卷或者单个卷上创建映射拷贝，同时不会影响到系统的系统能。（非必须启动）

　　2. OracleDBConsoleorcl：Oracle数据库控制台服务，orcl是Oracle的实例标识，默认的实例为orcl。在运行Enterprise Manager（企业管理器OEM）的时候，需要启动这个服务。（非必须启动）

　　3. OracleJobSchedulerORCL：Oracle作业调度（定时器）服务，ORCL是Oracle实例标识。（非必须启动）

　　4. OracleMTSRecoveryService：服务端控制。该服务允许数据库充当一个微软事务服务器MTS、COM/COM+对象和分布式环境下的事务的资源管理器。（非必须启动）

　　5. OracleOraDb11g_home1ClrAgent：Oracle数据库.NET扩展服务的一部分。 （非必须启动）

　　6. OracleOraDb11g_home1TNSListener：监听器服务，服务只有在数据库需要远程访问的时候才需要。（非必须启动，下面会有详细详解）。

　　7. OracleServiceORCL：数据库服务(数据库实例)，是Oracle核心服务该服务，是数据库启动的基础， 只有该服务启动，Oracle数据库才能正常启动。(必须启动)

 

***如何查看oracle的表结构
　　方式一：按住ctrl键不放，鼠标左键点击表名称，即显示表的一切详细情况（表空间，表名称，索引，列，键，权限，触发器 …）

　　方式二：在命令窗口（command）输入sql命令：desc table_name; 显示表每一列（字段）的详细情况

　　方式三：在sql窗口，鼠标右键点击表名称，选择鼠标左键点击‘属性（attribute)’可以查看该表的建表，建主键，建索引的脚本。

　　方式四：在sql窗口，鼠标右键点击表名称，选择鼠标左键点击‘描述（describe）’显示表每一列（字段）的详细情况

　　方式五：在sql窗口，鼠标右键点击表名称，选择鼠标左键点击‘查看（view）’即显示表的一切详细情况（表空间，表名称，索引， 列，键，权限，触发器 …）

　　方式六：在sql窗口，鼠标右键点击表名称，选择鼠标左键点击‘编辑（edit）’即显示表的一切详细情况（表空间，表名称，索引， 列，键，权限，触发器 …）