Oracle使用心得

1 截取的几种方法
round()trunc() trim(to_char(xxx,'9999.99'))

2 锁
oracle的锁普通是乐观锁（不用主动加，通过版本来约束）
还有可以主动加的悲观锁，分6级
Oracle一般都是行级锁（MYSQL只有innodb类型表才支持）

3 trigger mutating table问题解决方法
使用表级触发器
用自治事务PRGMA AUTO_NOMOUS_TRANSACTION

4 Oracle中NULL等价于'' 但是在Mysql等数据库不是等价的

5 Oracle5种约束（ENABLE|DISABLE * VALIDATE|NOVALIDATE交叉后有4种状态）：
Not null , unique , check , 主键 , 外键
（用check实现约束比触发器效率高）

6 什么时相关子查询？ 就是子查询用到外查询的查询

7 能写清字段就不要用select *（多个查数据字典的时间）

8 Oracle 数据表间拷贝的语法
建新表：Create table XXX as select .....
已有表：Insert into xxxx select .....

而sql server 用 select into ...

9 启动参数配在init.ora里面， 客户端连接配在ora90/network/admin/tnsnames.ora里面
init.ora告诉oracle instance 控制文件在哪里，而控制文件告诉instancedbfile和redofile在哪里
没有raid和镜像时，应把控制文件放到不同磁盘

10 Oracle至少要两个在线重做日志，当日志切换时，会引发写db文件（另外一般是check point时）

11 sga区；pga区（进程私有存储器区）；uga区（存session状态）

12 表空间由多个段，也由多个db文件组成，段区块中只有段可以跨db文件
区是一个连续存储单元，而块是一次disk io的单位

13 LGWR写redo日志的时机（每3秒；commit 时；buffer满）

14 Oracle的游标支持update 和 delete，但这时会产生独占排他锁别人不能update

15 专用临时表有两种（事务型：事务内可见，会话型：会话内可见）

16 SHARED_POOL_SIZE 用于缓冲SQL语句
DB_BLOCK_BUFFERS 才是数据缓冲（9i以上这个参数改成DB_CACHE_SIZE）

17 Oracle 的一个database ＝ os files，一个instance = 一套SGA + processes
一个instance只能属于一个database，而一个database可能有几个instance（多服务器时）

18 访问速度优化办法：
图片等大对象应该单独建表存储，用主外键关联
alter table emp cache 全表缓冲
簇比主外键的关联有效的多
物化视图
索引表（针对那些经常通过主键来查询整条记录的情况，这时rowid不再代表物理存储位置，索引和记录存放在一起的）
关闭logging
使用临时表 global temporary
分区表PARTITION

19 oracle 10g有个最小的客户端instant client，可以用来和应用一起发布(basic版30兆，basiclite版才10兆！，主要是一些dll，不太好配）

20 物化或实体化视图的使用
CREATE MATERIALIZED VIEW LOG ON T_EQUIPMENT_WARN
tablespace TS_GEPON_DATA
WITH ROWID ;

create materialized view V_CUR_ALARM
TABLESPACE TS_GEPON_DATA
BUILD IMMEDIATE
refresh FORCE ON DEMAND --本来想做成fast，但是复杂查询不能快速刷新
as
select * from
( select a.ID,a.EID,a.STARTTIME,a.ENDDATETIME,a.WARNINFO,a.NBOARDINDEX,
a.STATIONNAME,a.ISREMOTE,a.NCHANNELNUM,a.CHANDIRECT, a.NSUBCHNNUM,
b.ALARMTYPEDESP,c.SENAME,d.EType,e.BoardTypeDesp, b.blevel,
h.AlarmLevelName,h.AlarmColor from T_Equipment_Warn a
left join T_ALARM_TYPE b on (a.AlarmType=b.AlarmType)
left join T_Equipment c on (a.EID = c.EID)
left join T_Equipment_Type d on (c.ETypeID = d.ETypeID)
left join T_BOARD_TYPE e on (a.NBoardType=e.BoardType)
left join t_equipuser_info f on (c.userid= f.userid)
left join T_ALARM_LEVEL h on (b.Blevel=h.AlarmLevel)
where (a.Flag = 0) order by a.ID desc
)
where rownum < 2000 order by id

每次select前应该执行exec dbms_mview.refresh('v_cur_alarm');命令（当sql语句执行是不行的）

后来不想要了，我直接删除了视图日志表M$Log_xxxx以及视图本身还有创建视图时附带创建的表，结果部分电脑上插入主表记录时出现
HY000(State Code)-[Oracle][ODBC][Ora]ORA-12096: "GEPON"."T_EQUIPMENT_WARN" 上的实体化视图日志中存在错误
ORA-00942: 表或视图不存在
的错误；
后来发现执行了DROP MATERIALIZED VIEW LOG ON t_equipment_warn就没有问题了。

21 lock table
锁表不会阻止任何查询操作，查询绝对不会锁表，reader不会阻塞writer，writer也不会阻塞reader
plsql的写操作使用exclusive锁，这比row exclusive锁更严格，不允许除了查询以外的对该表的任何其他任何操作(有一次我在plsql做了数据改变却没有提交，导致其他地方对该表的非查询操作一直阻塞！)

22位图索引
最好创建在值少（如只有男女），另外最好用于静态数据，因为不对表和索引做分析的话，它不起作用（后来发现用暗示的方法是可以的select /*+ index(t_equipment_warn IDX_EQUIPMENT_WARN_FLAG) */* from t_equipment_warn where flag=0）

分析的命令如下：
SQL> analyze table t_equipment_warn compute statistics;
SQL> analyze index IDX_EQUIPMENT_WARN_FLAG compute statistics;

22 序列是不放在表空间的，所以重建表空间时不会导致序列重建，另外重建表空间时原来管理此表空间的用户仍然会在此默认表空间上

23 取得第90行到100行数据，不能用select * from xx where rownum>=90 and rownum <=100
应该select * from xx where rownum < 101 minus select * from xx where rownum < 90
或者select * from (select rownum as rid,xx.field1,xx.field2 from xx) where rid>=90 and rid<=100
这样效率更高select * from (select rownum as rid,xx.field1,xx.field2 from xx where rid<=100) where rid>=90
如果还要按某种顺序取，应该把排好序的结果集加上rownum组成新表
select ID,EID,STARTTIME,ENDDATETIME,WARNINFO,NBOARDINDEX, STATIONNAME,ISREMOTE,NCHANNELNUM,CHANDIRECT, NSUBCHNNUM,
ALARMTYPEDESP,SENAME,EType,BoardTypeDesp, alarmlevel, AlarmLevelName,AlarmColor,name
from (select rownum as rid,tt.* from (select a.ID,a.EID,a.STARTTIME,a.ENDDATETIME,a.WARNINFO,a.NBOARDINDEX, a.STATIONNAME,a.ISREMOTE,a.NCHANNELNUM,a.CHANDIRECT, a.NSUBCHNNUM,
b.ALARMTYPEDESP,c.SENAME,d.EType,e.BoardTypeDesp, a.alarmlevel,
h.AlarmLevelName,h.AlarmColor,f.name from T_Equipment_Warn a left join T_ALARM_TYPE b on (a.AlarmType=b.AlarmType) left join T_Equipment c on (a.EID = c.EID) left join T_Equipment_Type d on (c.ETypeID = d.ETypeID) left join T_BOARD_TYPE e on (a.NBoardType=e.BoardType) left join t_equipuser_info f on (c.userid= f.userid) left join T_ALARM_LEVEL h on (a.alarmlevel=h.AlarmLevel)
where (a.Flag = 0) and a.eid in (select r.eid from t_equipment_region r where r.REGIONID in (1,7)) order by ID desc) tt
) where rid <= 1000

24 建复合索引注意顺序，如（COL1,COL2,COL3），那么仅访问COL1或COL1+COL2都可以利用索引，而单独访问COL2是不能利用索引的

25 Oracle的表分区有以下几种类型：
　　1）范围分区：将表按某一字段或若干个字段的取值范围分区。
　　2）hash分区：将表按某一字段的值均匀地分布到若干个指定的分区。
　　3）复合分区：结合了前面两种分区类型的优点，首先通过值范围将表进行分区，然后以hash模式将数据进一步均匀分配至物理存储位置。
关键字PARTITION

26 trigger要声明变量，必须用declare关键字隔开头部，而存储过程不用，两者都可以直接使用exception段

27 没加条件时rid和id是一致的，一加条件就不一致了（rid有跳越而且顺序也不一致），不知何故？
select rownum as rid,t.* from t_equipment_warn t where order by id desc
select rownum as rid,t.* from t_equipment_warn t where eid =260 order by id desc

28 Oracle 为基于 COM、.NET 和 Win32/64 的编程语言提供了很多数据访问方法。根据所采用的 Windows 编程语言，Oracle 开发人员可以使用下面任何一种数据访问驱动程序：

* .NET：Oracle Data Provider for .NET；对于 OLE DB.NET，使用Oracle Provider for OLE序；对于 ODBC.NET，使用 Oracle ODBC Driver
* COM：用于 OLE 的 Oracle 对象 (OO4O)、Oracle Provider for OLE DB(通过 Microsoft 的 ActiveX 数据对象 (ADO))
* PL/SQL 和 Java（服务器端编程）到 COM Automation 服务器（例如 Microsoft Office）：COM Automation 特性
* Win32/64：ODBC 驱动程序、Oracle 调用接口 (OCI)、Oracle Provider for OLE DB

29 当varchar大于255时，存储方式改变，效率会降低

30 可以用instead of触发器(建在视图上)实现取消插入等目的

31 只有运行在归档模式，才能进行联机备份（热备份）。
冷备份（关闭oracle后对物理文件的备份）有关文件：参数文件，控制文件，数据文件，联机重做日志文件（oracle启动时按顺序使用它们）。
rman的备份，恢复时注意备份的等级0级，1级，2级，归档日志，在差异增量备份和累积增量备份之间的区别

32 mysql默认情况下root只能用来连接本机的mysql服务。如果是远程数据库，如果填写了root帐号，即使你填了远程主机，也会变成连本机（SQLyog和Navicat都是如此）;
如果是其他用户，倒是可以远程连接，但是连接后看不到存储过程的内容（即使为该用户选中该数据库所有权限）,除非对他赋予系统库mysql的权限;
其他用户可以访问mysql库的话，可以把user表里面的root的登录位置从localhost改为%

33 mysql的自增字段比较智能（它必须是key，不能有默认值），如果你自己填该字段，只要不冲突就用你填的，如果插入时，你不填，会自动给你一个最大值+1

34 更改oracle数据库的系统时间，可能导致job不能正确执行，可以手工调用以激活call sys.dbms_job.run( job #)
job # 可以通过select job,abs(last_date - sysdate)*24*60*60 from all_jobs where what like '%set active%';这样的方式得到