ORACLE实用总结

 

 

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)

 

 

 

优化分页查询示例:

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

 

 

 

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;

 

 

 

查看缓冲区内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

 

 

 

定期重构索引:

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文件中配置的实例名。