Oracle数据库查询十个小技巧（三）

第八个技巧 慎用Like等通配符

Like关键字 从技术上来说 是一个非常友善的通配符 利用这个通配符 我们可以实现很多模糊查询 如现在在一个人事档案系统中 用户想知道身份z号码以 开头的人事信息 此时 就可以利用Like语句实现 我们可以利用下面的条件语句 实现我们的需求 where 身份z号码 like % 通过这个条件语句 可以查到所有身份z以 开头的号码

但是 当用户在一张大表中采用这个LIKE语句的话 就会发现这个查询语句的运行效率非常的慢 这是什么原因造成的呢其实 不管是Like 关键字 若采用MATCHES关键字的话 若在大量数据中查找符合条件的记录 则其运行效率也比较低 这主要是其技术特性所造成的

Like与Matches两个关键字 其支持通配符匹配 在有些专业书籍上把这个叫做 正规表达式 不过由于在利用这些关键字查询的时候 数据库系统不是通过索引来查询 而是采用顺序扫描的方式来查询 显然 真是这种技术特性 造成了Like与Mateches两个关键字查询效率的低下 特别是在复杂查询或者大表查询中 用户可以明显感觉到速度比较慢

索引是数据库中的一个重要的数据结构 索引如果利用的合理的话 可以大幅度的提升数据库的查询性能 一般情况下 我们在数据库设计的时候 要充分的利用索引 来提高数据库的运行效率 如对于一些经常需要用到的查询功能 我们需要为没有指定外键的列建立索引;如有查询大表数据 而且又需根据好几个字段的值对其进行排序 也需要在这些列上建立复合索引 特别是在一些应用系统上 往往可以按以下字段的名称 就会对这个字段进行排序 遇到这种情况的话 更加需要在这些频繁进行排序的列上建立索引 以提高重新排序的效率 可见 若在查询的时候 若不能利用索引提高查询效率的话 则就好像跑车失去四轮驱动 速度会大受影响

所以 在数据库系统设计中 要尽量避免采用Like或者Matche关键字 有时候 我们可以利用其他运算符号来代替 如我们可以利用〉(大于)或者<(小于)符号来达到类似的需求 若真的要采用这两个关键字的话 则就需要做好查询优化方面的工作 如不要在基础表中直接利用这个两个关键字 而是通过报表视图 或者临时表等来查询 以减少其不良影响

第九个技巧 利用注释提高查询语句的可读性

在数据库设计中 有一个非常奇怪的现象 一些专家级的数据库设计人员 在写查询语句的时候 非对语句进行详细的注释 有时会 注释的内容大大超过了查询代码本身的篇幅 可是 往往一些入门不久的数据库设计人员 不喜欢写注释语句 这是一个很反常的现象

笔者刚开始接触数据库的时候 也不喜欢写注释语句 觉得写注释语句太浪费时间 但是 一个偶然的事件让笔者改掉了这个坏习惯 那时笔者在观摩一个专家设计数据库的时候 被其密密麻麻的注释惊呆了 看了其代码之后 笔者可以非常轻松的阅读完其所有的代码 不愧为是专家级的人物 看了他的注释之后 在看看自己编写的代码注视 那真是大巫见小巫了 从此之后 笔者也在慢慢培养自己编写代码的习惯 现在笔者在数据库设计的时候 注释已经写的很详细了 至少笔者的同事在看到我的注释之后 不用看源代码就知道笔者要实现的目的了

在编写注释的时候 要注意几个问题

一是注释越详细越好 其实 注释最多牺牲一点磁盘空间 而不会对数据库的性能产生任何不良的影响 相反 注释详细的话 对于后续数据库维护与管理 系统二次开发的等等 都会提供很大的帮助

二是最好采用英文注释 若采用中文注释的话 有时候数据库语言设置不当 在数据库实例安装的时候 不会把中文的注释带过去 所以 作为数据库管理员 要有一定的英语基础 学会利用英语写注释 其实 这也不是很难 只要多看看别人设计的数据库注释 把他们常用的注释复制下来 通过选择合适的进行复制 粘贴就可以完成任务

三是不仅在写查询语句的时候 要做好代码的注释工作 在编写其他代码的时候 如过程 函数等等 也要添加详细的代码 以增加这些复杂功能的可读性

四是注释的内容 一般注释应该包含如下几方面的内容 一是这段代码要实现的功能;二是这段代码需要调用的参数;三是这段代码输出的结果 若是多表关联查询的话 最好能够说明表之间的对应关系 若在查询语句中 直接调用了函数的话 则最好能够注明这个函数的功能;等等 终止一个原则就是 让其他人看到这个注释 不再需要去查询其他的资料 就可以明白这段代码的含义

详细的注释内容 不仅不会降低数据库的运行性能 而且还可以提高数据库的管理与维护的效率;同时也可以加快前台应用程序开发设计的速度 又因为查询语句是系统中利用的最多的语句 也是引用的最频繁的语句 故在查询语句中 更加需要做好相关的注释

第十个技巧 必要的时候 限制用户所使用的行

在使用一些大型管理系统的时候 如ERP系统 我们若查询产品信息 默认的情况下 若记录比较多的时候 其不会把所有的记录都查询出来 如在ERP系统的数据库中 其有 个产品信息 而端子类的产品信息就占据到 个 此时 我们在查询条件中 若产品类别限制为 端子 的话 则其查询出来的结果也可能不是所有的端子 默认显示的话 可能只有前面的 个端子类产品 若用户需要看到全部的产品信息 就需要点击 显示全部 按钮 才可以显示出全部的信息

其实 不管是一些应用程序如此设计 在Oracle数据库中 本身也有这方面的限制 如直接在PL/SQL客户端中查询数据的话 其显示的记录默认情况下也是有限制的 而不会把所有符合条件的语句查询出来 若用户需要查询所有符合条件的记录 则需要点击 继续 按钮 以让数据库显示所有的记录

为什么要做类似的限制呢这主要就是为了提高数据库查询的性能 我们直接在数据库服务器上 在几百万条记录中查询的话 显示几百条记录跟现实几千条记录所花费的时间明显不同 前者可能只需要 秒即可 而后者可能需要 分钟 所以 为了减少用户等待的时间 我们往往需要限制首次查询默认显示的记录数字

如我们往往在查询语句中 利用top 来让数据库只显示前 条记录 如此的话 可以明显的缩短用户的等待时间 默认情况下 是根据记录创建的时间顺序 来显示记录的 最迟创建的记录 其显示在最前 以此类推

lishixinzhi/Article/program/Oracle/201311/16756

可以使用数据库链接,例如:

create public database link link2 connect to

user1 identified by password using 'remote'

前提是在要连接的数据库上有user1这个用户，并且在本地建立local naming

dblink建立后，就可以在本地数据库访问remote库了，例如

select from link2tablename;

create public batabase link a_to_b

connect to (被 *** 作表所在库的用户名) identified by (被 *** 作表所在库的密码)

using ‘(被 *** 作表所属实例名)’;

然后在 *** 作实例中进行测试：

select 1 from dual@a_to_b;

如果有正确结果 那么你就建立了一个dblink了。

这个时候就可以 跨库查询了。

oracle不能垮库 *** 作的，只能是垮用户进行 *** 作。

解决办法：

首先，在B库中将需要读取的内容读取出来。

其次，将读取的内容保存到相应的存储位置。

最后，将保存的数据导入到A库的相应用户下。

备注：可以通过GoldenGate、dblink 等辅助工具实现，但一般都是花钱的，并且维护也需要专业人员，所以也就不推荐了。

--创建 CREATE public database link test_link CONNECT TO scott IDENTIFIED BY tiger using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127001)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = LEE) ) )'; --使用 select ename from emp@test_link; --删除 drop public database link TEST_LINK;

一：dblink创建：

1、已经配置本地服务

create public database

link toBeJing connect to scott

identified by tiger using 'BEJING'

数据库连接字符串'BEJING'是当前客户端数据库中TNSNAMESORA文件里定义的别名名称可以用NET8 EASY CONFIG或者直接修改TNSNAMESORA里定义

2、直接建立链接

create database link toBeJing

connect to scott identified by tiger

using '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = XXXX )(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = BJORCL)

)

)';

host=数据库的ip地址，service_name=数据库的ssid。

其实两种方法配置dblink是差不多的，我个人感觉还是第二种方法比较好，这样不受本地服务的影响。

注意: 假如创建全局dblink，则必须使用systm或sys用户，在database前加public。

1先创建PUBLIC DATABASE LINK，不用指定用户和密码　

scott@TEST>conn system/test

Connected

system@TEST>CREATE PUBLIC DATABASE LINK orcl USING 'BEJING';

Database link created

2再在各个用户下创建私有DATABASE LINK(同PUBLIC DATABASE LINK名称相同)，指定用户和密码

system@TEST>conn scott/tiger

Connected

scott@TEST>CREATE DATABASE LINK orcl CONNECT TO scott IDENTIFIED BY tiger;

Database link created

实际上相当于：　

　CREATE DATABASE LINK orcl USING 'BEJING' CONNECT TO scott IDENTIFIED BY tiger;

db link 有3种类型，我这里只讨论其中两种，connected user和fixed user。

connected user，简单来说，连接方数据库以connected的用户来连接远程数据库。

fixed user,简单来说，连接方数据库以fixed(指定的)用户来连接远程数据库。

看看创建db link语法，你对这两种类型就比较清楚了。

创建connected user的db link语法：

create databas link foo connect to scott identified by tiger using 'BEJING' ;

创建fixed user的db link语法：

create databas link foo using 'BEJING' ;

db link 的命名和global_names有关,global_name是数据库全局名称，global_name在你所管理的数据库中要保证唯一。数据库名称是db_name。数据库名称一般都取得比较短，我的习惯一般取长度4个字符，重名概率高。

所以，oracle模仿域名搞出一个global_name,global_name=db_name+db_domain。

有了global_name,就可以实现数据库命名的全局唯一。例:ORCLREGRESSRDBMSDEVUSORACLECOM

数据库全局名称可以用以下命令查出

SELECT FROM GLOBAL_NAME;

如果global_names=true,那么db link的命名要和远程数据库的global_name相同；

如果global_names=false,那么你可以随便命名db link。

查询global_names是true还是false,在pl/sql中的命令窗口(不是sql窗口)执行:show parameter global_names

二、dblink查询：

查看所有的数据库链接，进入系统管理员SQL> *** 作符下，运行命令：

SQL>select owner,object_name from dba_objects where object_type='DATABASE LINK';

三、dblink删除：

DROP PUBLIC DATABASE LINK toBeJing

四、dblink使用：

SELECT……FROM表名@数据库链接名;

查询、删除和插入数据和 *** 作本地的数据库是一样的，只不过表名需要写成“表名@dblink服务器”而已。

例：查询北京数据库中emp表数据 select from emp@toBeJing;

五、同义词配合：

第四点中from emp@toBeJing可以创建同义词来替代：

CREATE SYNONYM同义词名FOR 表名;

CREATE SYNONYM同义词名FOR 表名@数据库链接名;

如：create synonym bj_scott_emp for emp@toBeJing;

于是就可以用bj_scott_emp来替代带@符号的分布式链接 *** 作emp@toBeJing

DB LINK是独立于创建用户（USER_DB_LINKS的USERNAME）起作用的，其他用户无法使用这个连接，无权限也不能删除它。

以上就是关于Oracle数据库查询十个小技巧（三）全部的内容，包括:Oracle数据库查询十个小技巧（三）、oracle如何从一个数据库中转接到另一个数据库、请问oracle数据库中两张表分别在两个数据库中怎么能建关联等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！