oracle实用,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

a.col1=b.col1 and a.col2=b.col2…)

2

行记录中某一列不一致

Delete tablename a where a.col3

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

REBUILD

总的来说,写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

文件中配置的实例名。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
SQL中的单记录函数 1.ASCII 返回与指定的字符对应的十进制数; SQL> select ascii(’A’) A,ascii(’a’) a,ascii(’0’) zero,ascii(’ ’) space from dual; A A ZERO SPACE --------- --------- --------- --------- 65 97 48 32 2.CHR 给出整数,返回对应的字符; SQL> select chr(54740) zhao,chr(65) chr65 from dual; ZH C -- - 赵 A 3.CONCAT 连接两个字符串; SQL> select concat(’010-’,’88888888’)||’转23’ 高乾竞电话 from dual; 高乾竞电话 ---------------- 010-88888888转23 4.INITCAP 返回字符串并将字符串的第一个字母变为大写; SQL> select initcap(’smith’) upp from dual; UPP ----- Smith 5.INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置; C1 被搜索的字符串 C2 希望搜索的字符串 I 搜索的开始位置,默认为1 J 出现的位置,默认为1 SQL> select instr(’oracle traning’,’ra’,1,2) instring from dual; INSTRING --------- 9 6.LENGTH 返回字符串的长度; SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst; NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL)) ------ ------------ ---------------- ------------ --------- -------------------- 高乾竞 3 北京市海锭区 6 9999.99 7 7.LOWER 返回字符串,并将所有的字符小写 SQL> select lower(’AaBbCcDd’)AaBbCcDd from dual; AABBCCDD -------- aabbccdd 8.UPPER 返回字符串,并将所有的字符大写 SQL> select upper(’AaBbCcDd’) upper from dual; UPPER -------- AABBCCDD 9.RPAD和LPAD(粘贴字符) RPAD 在列的右边粘贴字符 LPAD 在列的左边粘贴字符 SQL> select lpad(rpad(’gao’,10,’*’),17,’*’)from dual; LPAD(RPAD(’GAO’,1 ----------------- *******gao******* 不够字符则用*来填满 10.LTRIM和RTRIM LTRIM 删除左边出现的字符串 RTRIM 删除右边出现的字符串 SQL> select ltrim(rtrim(’ gao qian jing ’,’ ’),’ ’) from dual; LTRIM(RTRIM(’ ------------- gao qian jing
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值