《数据库知识培训》由会员分享，可在线阅读，更多相关《数据库知识培训（14页珍藏版）》请在金锄头文库上搜索。
1、Oracle数据库知识培训 时间：2014年10月 目录 一、连接数据库 二、常用SQL语句 三、常用对象 四、编写查询语句 五、认识执行计划 六、常见错误分析 七、常用数据字典和V$视图 一、连接数据库 1、工具 sqlplus、pl/sql developer、Toad 2、连接数据库的配置 CRMADB= (DESCRIPTION= (ADDRESS_LIST= (LOAD_BALANCE=NO) (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521) (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521) ) (CONNECT_D
2、ATA= (FAILOVER_MODE=(TYPE=session)(METHOD=basic)(RETRIES=30)(DELAY=5) (SERVER=dedicated) (SERVICE_NAME=CRMADB) ) ) 测试：tnsping crmadb 简易连接：sqlplus username/passwdip:port/service_name 协议协议 、地址（域名 ）、端口号 服务务名（service_name ） 实实例名（SID） 为什么要尽量避免短连接？ 3、账号 申请或者创建账号需要提供的几个要素： 名称、初始密码、默认永久表空间、临时表空间、概要文件、空间限额 C
3、reate user username identified by “oracle” default tablespace tbs temporary tablespace temp profile default 4、权限 系统权限：需要数据库管理员授权，比如 create session、create table、create procedure、create * Alter system、alter session、*any*等 对象权限：由对象所有者（Owner）管理的权限，比如： 对象的insert、delete、update、select权限； Execute procedure
4、提示：数据字典和动态性能视图（V$视图）的权限也属于对象权限，只是 对象Owner=SYS 二、常用SQL语句 u DML语句有日志，可以回滚， 会产生各种锁，需要及时提交； u DDL语句隐含自动提交，无法 回滚； u Delete和Truncate table的区别 : Truncate table必须是表的所有 者或者具有drop any table系统 权限； Truncate更快但是 风险更大。 u回收站（recyclebin） 三、常用对象 1、表 显式指定存储的表空间； 字符尽量使用varchar2类型； 程序使用的表尽量不用clob类型； 不要为表指定并行度； 分区表：hash
5、、list、range、间隔 组合分区： Range-Range Range-Hash Range-List List-Range List-Hash List-List DROP表最好不要带PURGE选项。 2、序列 必须指定合适的cache值； rac环境尽量不要指定order选项； 表什么时候需要重建？ 分区表带来哪些好处？ （性能、管理性、高可用性） 3、索引 索引分类：B树索引、位图索引（OLAP） 索引选项： 唯一性索引（unique） 索引按升序或者降序排列 反向键索引（reverse）：解决递增ID的争用 组合索引 函数索引 分区表的索引：全局索引和本地索引（LOCAL） 加快
6、索引创建的速度：nologging、parallel n，最后一定要取消parallel 索引什么时候 需要重建？ 什么时候重建 ？ 索引的选择性：主键选择性最好 组合索引的字段顺序：选择性好的列靠前 索引对DML操作的影响：更新记录同时更新索引，索引条目 的维护是先删除再增加，索引过多会影响增、删、改性能 四、编写查询语句 软解析、硬解析对性能的影响： 增加CPU开销； 共享内存library容易出现争用，高并 发更容易出问题 OLTP系统如何避免 硬解析？ 1、尽量避免使用For update，尤其是程序中要避免for update的使用； commit或者rollback千万不能忘记了！
7、 2、表名尽量使用别名，明确字段，宁愿多敲几个字符也不要使用*； 尤其是放在程序中使用更要注意！ 3、10g以后Group by采用hash方式，结果默认是不排序的 4、group by或者order by会消耗更多的内存或者临时表空间，所以尽量避免使用或 者减少汇总排序字段数量； 在生产库上一定要避免执行大的排序操作！ 5、union、union all的区别； 明确知道自己要返回的结果是否有重复，减少排序和剔重！ 6、多个大表关联查询尽量分布进行，减少表关联的数量和大小会显著提高性能； 尝试使用临时表！ 7、一定要避免在两个表关联查询的时候出现笛卡尔积； where条件一定要写全！ 8、查
8、询只取自己需要的列，充分利用索引返回记录，减少回表的次数； 9、如果只是为了看看返回的结果示例，可以限制查询返回的记录行数，例如使用 rownumn； 尤其是在大表关联查询的时候会显著提高性能！ 10、注意索引是排序了的，使用索引查询返回的记录是排序了的，可以不用再order by 四、编写查询语句 11、理解常用的hint提示用途和用法： /*+index(table_name index_name)*/ /*+full(table_name)*/ /*+no_index(table_name index_name)*/ /*+use_hash(t1,t2)*/ /*+use_nl(t1,t
9、2)*/ /*+parallel(t 4)*/ 对分区表效果最好 /*+append*/ 提高插入效率insert /*+append*/ into /*+driving_site(t1)*/:在分布式查询中，将t1表发送到远端进行连接查询，再返 回结果； 12、子查询避免出现order by Select * from t1 where a in (select b from t2 where b=1 order by b) Select count(*) from (select a,b from t1,t2 where t1.a=t2.b order by a,b) 13、SQL改写要等
10、价 select a.username from tab_users a where username in (select owner from tab_obj b where b.object_name like TAB%); -返回3行记录 select a.username from tab_users a,tab_obj b where a.username=b.owner and b.object_name like TAB% -返回750行记录 14、where条件要避免隐式类型转换； 15、条件使用函数不要出现在等号的右边； 五、认识执行计划 连连接方法 访问访问 路径基数 成
11、本 六、常见错误分析 u ORA-01555: snapshot too old: rollback segment number string with name string too small 1、查询语句效率不高，优化语句，缩短执行时间； 2、表上数据变化频繁，导致undo不够用，需要修改undo保留时间或者增大undo空间； u ORA-01652: unable to extend temp segment by string in tablespace string 1、减少排序操作或者hash join操作； 2、避开高峰期查询； 3、增加临时表空间； u 误操作drop表或者
12、修改了数据怎么办？ 1、drop一般不要带purge，删除后发现问题立即闪回； flashback table tab_1 to before drop 2、truncate 、delete、update误操作后立即做闪回查询恢复数据： select versions_startscn scn, to_char(versions_starttime, yyyy-mm-dd hh24:mi:ss) oprtime, versions_xid xid, versions_operation oprtype, id from t1.tab1 versions between timestamp to
13、_timestamp(2014-10-22 15:02:10,yyyy-mm-dd hh24:mi:ss) and to_timestamp(2014-10-22 15:06:29,yyyy-mm-dd hh24:mi:ss) 注意查询出来后导入临时表，不要在原表上直接做闪回，会锁定表的！ 七、常用数据字典和V$视图 空间类： dba_data_files、dba_temp_files、dba_free_space dba_segments、dba_extents 对象类： dba(alluser)_objects、 dba(alluser)_tables、 dba(alluser)_indexes dba(alluser)_tab_columns 会话类： v$session、v$session_wait、v$sessstat 锁相关类： v$lock、v$locked_object SQL类： v$sqltext、v$sqlarea、v$sql、v$sql_bind_capture、 v$sql_plan
