ORACLE
实用总结
1、 PLSQLDEV
使用
a.
查看数据库实例连接配置:
打开help
菜单,
选择Support Info…
b.
查看最近使用过的SQL
语句:
快捷键:control+E
大约半个月中使用过的SQL
语句。
查看数据库会话生存情况和存在的死锁:
打开tools
菜单,
选择Sessions
选择下面的Locks:
可以看到当前存在的表被死锁的情况,并加以处理。
在用某个用户登录plsqldev
后,可以在左上侧的组合框中选择My objects,
这样可以过滤掉其他不相关用户的对象;
点击望远镜按钮可根据对象名称和查找数据库对象:
All
按钮会选择所有的对象类型,None
按钮会取消选择所有对象,Invert
反向选择已选取的对象。
在这里顺带提下数据库的各种对象及用途:
1. functions
函数,
有返回值,
可以不依赖任何表,ORACLE
中自带就有很多工具函数,
如
decode([
匹配值],[
待匹配值],[
条件匹配返回值],[
条件不匹配的返回值]),to_date(),to_char()
等等….
2. procedures
存储过程,
存储过程没有返回值,取而代之的是输出参数,存储过程的编写和函数类似,都遵循动态SQL
的语法,函数可以和静态SQL
配合使用。
3. package Specifications
包定义,定义一个ORACLE
包,可以包含各种数据库其他对象,使用包的好处在于:可以在修改某个数据库对象后,自动编译与他相关的对象(
如某个函数修改了,
调用他的其他函数或存储过程会自动编译)
,没有在一个包中,相关对象就不会自动编译,从而出现失效的问题,需要重新编译,这是在数据库开发中需要注意的。
4. package Bodies
包体,包含具体的各种数据库对象。
5. Type Specifications
类型定义,如游标引用,数组等用户自定义的变量类型声明。
6. Type Bodies
类型体,类型的具体描述。
7. Tiggers
触发器。
8. Java source
在实际开发中很少用到,我也不了解。
9. Queues
在实际开发中很少用到,我也不了解。
10. Tables
这个大家都知道了。
11. Views
同上。
12. Materialized Views
物化视图,这个对象和视图类似,但性能更好,一般和DBLINK
以及job
配合使用。
13. Sequences
序列,用于自动生成一个标识号,比如在话单表里生成一个流水号,就要使用序列,可以使用select
序列名.nextval from dual
来查询下一个序列值。
14. Synonyms
同义词,一般和dblink
配合使用,作为dblink
的别名。
查看SQL
语句性能:
点击灯泡按钮:
可以看到开销cost,Cardinality,Bytes,IO,CPU
等
点击扳手按钮,
可以选择需要查看的各种性能指标:
在Optimizer goal
中选择性能指标范围:
首行或所有行的查询性能。
数据文件导入,使用Tools
àtext importer
可以用来导入带有如”|”
、”,”,tab
等分割符号的文件。
或者使用oracle
自带的sqlldr
工具,效率更高,运行一个后缀为.ctl
的倒入脚本,格式如下:
Load data
--
需要导入的文件名
infile '/opt/oracle/sysen/subscription.out.2'
--
对应的表
Append into table src_subscribeinfo
--
文件中各字段含义(
也可以使用oracle
函数或运算符转换,
如下的to_char,”||”)
及分割符
fields terminated by "|"
(
spid,
userid "'86' || :userid",
character,
ordertime "to_char(to_date(:ordertime,'yyyy-mm-dd:hh24:mi:ss'),'yyyymmddhh24mmss')"
)
然后在sql command
下执行:sqlldr xxxx.ctl;
就可以导入了。
2、
静态SQL
a
连接查询:
普通的连接查询
Select a.col1,b.col2… from a,b where a.col3=b.col3
Select a.col1,b.col2… from a join b on a.col3=b.col3
左连接
Select a.col1,b.col2… from a left join b on a.col3=b.col3
右连接
Select a.col1,b.col2… from a right join b on a.col3=b.col3
内连接
Select a.col1,b.col2… from a inner join b on a.col3=b.col3
b
集合查询
求两个表中相同字段的交集
求两个表中相同字段的交集
Select a.col1… from a intersect select b.col1… from b
求两个表中相同字段的差集
Select a.col1… from a minus select b.col1… from b
求两个表中相同字段的并集
Select a.col1… from a union select b.col2.. from b
如果自己和自己求并集,可以用来过滤重复数据
Select a.col1… from a union select a.col2.. from a
或者把不同表中具有相同属性的记录合成一个集合
Select a.col1,a.col2,a.col3 from a union select a.col1,a.col2,a.col3
c
子查询
Select c.bcol1,c.bcol2 from (select b.col1 bcol1,b.col2 bcol2 from b) c
d In
和 exists
Select a.col1… from a where a.col2 in (select b.col2 where a.col3=b.col3)
Select a.col1… from a where exists (select 1 from a.col2 = b.col2 and a.col3=b.col3)
e
关联更新、插入,删除
Update a set a.col1= (select b.col1 from b where a.col2=b.col2) where
Exists (select 1 from b where a.col2=b.col2)
Insert into a (select b.* from b where a.col1=b.col1)
Delete a where a.col1=( select b.col1 from b where a.col2=b.col2) where
Exists (select 1 from b where a.col2=b.col2)
f
删除重复的行
2
行记录完全一致:
Delete tablename a where a.rowid <(select max(b.rowid) from tablename b where
a.col1=b.col1 and a.col2=b.col2…)
2
行记录中某一列不一致
Delete tablename a where a.col3 <(select max(b.col3) from tablename b where
a.col1=b.col1 and a.col2=b.col2…)
g
性能优化的分页查询
Select a.col1,a.col2… from tablename a,(select
rid from (select rowed rid,rownum num from tablename b order by b.col1) c where c.rownum<15) d where d.num>=0) e where a.rowid = e.rid
更多使用说明具体可参考:oracle8i_9i
数据库基础.doc
3、 Sql
优化
并行插入示例(10g
以后支持,9i
支持有问题):
MERGE
INTO BASETAB A USING BOSSCUSTINFO B ON (B.MSISDN = A.MSISDN) WHEN NOT MATCHED THEN
INSERT(A.Fakeid,A.MSISDN,A.NickName,A.AccountLeft,A.validDate,A.onlineState,A.lastOnlineDate,A.IMSI,
A.Password,A.Question,A.Answer,A.TradePassword,A.accountType,A.PayType,A.PayTypeSwitchTime,A.UserType,
A.UserNetType,A.Name,A.SID,A.UserState,A.UserStateSwitchTime,A.UserCredit,A.MaxFeeLimit,A.UAProfile,
A.BankAccount,A.Bank,A.Address,A.ZIPCode,A.HomePhone,A.OfficePhone,A.FaxPhone,A.BP,A.LoginDate,
A.WebLangId,A.PIM,A.UserBrand,A.NetworkType,A.Email,A.BillingDate,A.USERGROUPLIST,A.LANG,A.ProvinceId,
A.LOGOUTTIME,A.logoutdate,A.UserBalanceProperty,A.NumberType,A.CityID,A.AREAID,A.Isacceptadver)
VALUES
( '-1' ,B.MSISDN, 'NICK' ,NULL,NULL,NULL,NULL,B.IMSI,B.PASSWORD,B.QUESTION,B.ANSWER,NULL, 0 ,B.PAYTYPE,
B.PAYTYPESWITCHTIME,B.USERTYPE, 0 ,B.NAME,B.SID,B.USERSTATE, '99991231235959' ,B.USERCREDIT,
B.MAXFEELIMIT,B.UAPROFILE,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,TO_CHAR(SYSDATE, 'YYYMMDDHHMMSS' ),
0 ,NULL,NULL, 20 ,NULL, 1 ,NULL, 'zh' ,B.PROVINCEID,NULL,B.LOGOUTDATE, 1023 , '1' ,B.CITYID,NULL, 0 )
WHERE NOT exists (SELECT 1 FROM BASETAB A WHERE A.MSISDN = B.MSISDN)
INSERT(A.Fakeid,A.MSISDN,A.NickName,A.AccountLeft,A.validDate,A.onlineState,A.lastOnlineDate,A.IMSI,
A.Password,A.Question,A.Answer,A.TradePassword,A.accountType,A.PayType,A.PayTypeSwitchTime,A.UserType,
A.UserNetType,A.Name,A.SID,A.UserState,A.UserStateSwitchTime,A.UserCredit,A.MaxFeeLimit,A.UAProfile,
A.BankAccount,A.Bank,A.Address,A.ZIPCode,A.HomePhone,A.OfficePhone,A.FaxPhone,A.BP,A.LoginDate,
A.WebLangId,A.PIM,A.UserBrand,A.NetworkType,A.Email,A.BillingDate,A.USERGROUPLIST,A.LANG,A.ProvinceId,
A.LOGOUTTIME,A.logoutdate,A.UserBalanceProperty,A.NumberType,A.CityID,A.AREAID,A.Isacceptadver)
VALUES
( '-1' ,B.MSISDN, 'NICK' ,NULL,NULL,NULL,NULL,B.IMSI,B.PASSWORD,B.QUESTION,B.ANSWER,NULL, 0 ,B.PAYTYPE,
B.PAYTYPESWITCHTIME,B.USERTYPE, 0 ,B.NAME,B.SID,B.USERSTATE, '99991231235959' ,B.USERCREDIT,
B.MAXFEELIMIT,B.UAPROFILE,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,TO_CHAR(SYSDATE, 'YYYMMDDHHMMSS' ),
0 ,NULL,NULL, 20 ,NULL, 1 ,NULL, 'zh' ,B.PROVINCEID,NULL,B.LOGOUTDATE, 1023 , '1' ,B.CITYID,NULL, 0 )
WHERE NOT exists (SELECT 1 FROM BASETAB A WHERE A.MSISDN = B.MSISDN)
优化分页查询示例:
SELECT
* FROM BASETAB U,
(SELECT RID
FROM (SELECT ROWID RID ,
ROWNUM num
FROM (SELECT ROWID rid
FROM BASETAB t
ORDER BY t.MSISDN,
t.provinceid) t
WHERE ROWNUM <= 15 ) Y
WHERE Y.num >= 0 ) t
WHERE U.ROWID = t. RID
(SELECT RID
FROM (SELECT ROWID RID ,
ROWNUM num
FROM (SELECT ROWID rid
FROM BASETAB t
ORDER BY t.MSISDN,
t.provinceid) t
WHERE ROWNUM <= 15 ) Y
WHERE Y.num >= 0 ) t
WHERE U.ROWID = t. RID
BULK COLLECT
使用示例:
一般和oracle
中的数组配合使用;
先定义一个数组类型:
Create or replace package PKG_MY_TYPE as
Type chartab is table of varchar2(200) index by binary_integer;
End PKG_MY_TYPE;
存储过程示例:
CREATE
OR REPLACE FUNCTION F_MY_GETUSERLIST
RETURN VARCHAR2 AS
v_userinfo VARCHAR2( 2000 );
v_userinfoTab PKG_MY_TYPE.CHARTAB;
BEGIN
SELECT T.Msisdn BULK COLLECT
INTO v_userinfoTab
FROM basetab T;
FOR i IN 1 .. v_userinfoTab.COUNT LOOP
v_userinfo := v_userinfo || v_userinfoTab(i) || '~' ;
END LOOP;
RETURN v_userinfo;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END F_MY_GETUSERLIST;
RETURN VARCHAR2 AS
v_userinfo VARCHAR2( 2000 );
v_userinfoTab PKG_MY_TYPE.CHARTAB;
BEGIN
SELECT T.Msisdn BULK COLLECT
INTO v_userinfoTab
FROM basetab T;
FOR i IN 1 .. v_userinfoTab.COUNT LOOP
v_userinfo := v_userinfo || v_userinfoTab(i) || '~' ;
END LOOP;
RETURN v_userinfo;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END F_MY_GETUSERLIST;
查看缓冲区内SQL
的执行效率:
SELECT
EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS, 2 ) Hit_radio,
ROUND(DISK_READS/EXECUTIONS, 2 ) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS> 0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS, 2 ) Hit_radio,
ROUND(DISK_READS/EXECUTIONS, 2 ) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS> 0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC
定期重构索引:
ALTER
INDEX
<INDEXNAME>
REBUILD
<TABLESPACENAME>
总的来说,写SQL
时不能太随意,要多考虑性能方便的问题,一般操作对象超过1K
行以上,就需要考虑下性能的问题,而不是简单的实现,以下是个人的一些总结,不一定全对:
a
在where
条件的运算符左侧,避免使用函数和运算符,如果该条件字段是索引字段,会导致索引被屏蔽。
b
避免潜在的强转,也会使索引失效。
c
在where
条件中尽量使用索引和分区。
d
小表和索引选择性不高的情况下,放心使用全表扫描。
e
尽量减少子查询和嵌套查询。
f
如果in
的条件字段包含索引,而exists
的条件不包含索引,in
的性能更优。同等条件时,exists
性能更优。
g
业务逻辑难度太大的SQL
,可以考虑用存储过程或函数来实现。
h
避免使用not
和or,
都会引起全表扫描;可以用not exists
和union
代替。
i
避免在索引列上使用is null
和is not null
,都会引起全表扫描。
j
使用union all
替代union
,如果有可能的话;union all
效率明显高于union
,但不会过滤掉2
个结果集中的相同记录。
k
使用>=
,<=
代替>,<
。
l
避免使用distinct,order by,minus,intersect
,除非v$parameter
中的sort_area_size
参数调整的比较好。
4、
杂七杂八
Job
性能调优:
Select * from v$parameter where name=‘job_quere_process’;
Job
队列的大小可调整,用以提高job
性能
Eg:Alter system set job_quere_process=n;
SCN
:
Select DBMS_FLASHBACK.get_system_change_number SCN from dual;
备份表:
9i
下: create table newtablename as select * from tablename;
重命名表:
Rename tablename1 to tablename2;
删除数据库用户及所有相关对象:
Drop username cascade;
关于参数文件initxxxx.ora
和系统视图v$parameter,
及nls
视图:
运行期间修改:
Alter system set xxx=yyy;
或者Alter session set xxx=yyy;
或者,对于spfile
中的参数:alter system set xxx=yyy scope=’spfile’;
查看系统参数xxx
的值:
Show parameter xxx;
或者
Select * from v$parameter;
Select * from v$parameter;
nls_database_parameters,nls_instance_parameters,nls_session_parameters
这3
个视图包含了数据库的字符集,时间区域和格式信息等。
启动、停止数据库实例:
Startup mount/startup immediate…
Shutdown force/shutdown…
查看数据库版本、是否支持分区特性:
Select * from v$version;
select * from v$option where parameter='Partitioning';
大家可以经常用v$
开头,通过提示来查看这些系统视图,了解数据库的各种信息。
查找当前数据库用户的对象、表等信息,可以查询user_objects
、user_all_tables
等等…
通过DBlink
在2
个数据库间导数据:
CREATE DATABASE LINK linkname CONNECT TO username IDENTIFIED BY password USING instancename;
Select * from tablename@linkname;
注意: instancename
必须是你当前数据库服务器上tnsname.ora
文件中配置的实例名。