oracle数据库查询-oracle数据库查询语句 收藏 0

Java干货 | 一文搞懂ORACLE数据库

ORACLE数据库系统是美国ORACLE公(甲骨文)提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。

ORACLE通常应用于大型系统的数据库产品。

ORACLE数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能;作为一个关系数据库，它是一个完备关系的产品;作为分布式数据库它实现了分布式处理功能。

ORACLE数据库具有以下特点：

(1)支持多用户、大事务量的事务处理

(2)数据安全性和完整性控制

(3)支持分布式数据处理

(4)可移植性

1.数据库

Oracle数据库是数据的物理存储。这就包括(数据文件 ORA 或者 DBF、控制文件、联机日志、参数文件)。其实 Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是Oracle 就只有一个大数据库。

2.实例

一个Oracle实例(Oracle Instance)有一系列的后台进程(Backguound Processes)和内存结构(Memory Structures)组成。一个数据库可以有n个实例。

3.数据文件(dbf)

数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

4.表空间

表空间是Oracle对物理数据库上相关数据文件(ORA 或者 DBF 文件)的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为 system表空间)。

每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。

注：表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。

由于oracle的数据库不是普通的概念，oracle是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表!这里区分就是用户了!

5.用户

用户是在表空间下建立的。用户登陆后只能看到和操作自己的表, ORACLE的用户与MYSQL的数据库类似，每建立一个应用需要创建一个用户。

-END-

优化 Oracle 数据库分页查询：让查询更高效

一、Oracle数据库的分页操作

对于Oracle数据库的分页查询语句一般采用如下的方式。

二、Oracle数据库的分页操作的性能分析和调优

按照分页操作的SQL语句的复杂度，可以将分页操作的种类分为只包含排序操作的分页、包含过滤操作和排序操作的单表查询分页、包含过滤操作和排序操作的单分区表查询分页、包含过滤操作和排序操作的表关联查询分页等几种类型。

1.只包含排序操作的分页

一般地，分页操作的SQL语句带有排序操作，即：order by。例如：在客户访问记录表中查询最新访问的30个客户，这就需要按照客户的访问时间进行降序排序。

对于这类分页操作的调优，需要利用索引是有序的特性，为order by中的排序列设置索引。

如果是对多个列进行排序，需要为所有的排序列创建一个复合索引，创建时需要注意复合索引中的各列的顺序需要与order by中指定的各排序列的顺序一致，且还需注意索引是升序还是降序，以下示例1展示了该类SQL语句的调优。

【示例1】

对名为user的用户信息表执行分页查询，查询最近访问但积分最低的前10位用户，分页语句如下所示。

user表中的记录数为300万左右。

为排序列创建一个复合索引，usercore列在前，lasttime列在后，且将lasttime列的索引设置为降序，即：

创建索引后，该语句的真实执行计划中的核心内容如下图所示。

通过以上的执行计划可以知：

（1）该SQL语句执行时访问User表时采用了INDEX FULL SCAN（索引快速全扫）的访问方式（因为未指定过滤条件，所以只能采用INDEX FULL SCAN的访问方式）；

（2）执行INDEX FULL SCAN时访问10条记录后，已经满足分页要求，扫描终止，该SQL语句执行完毕。即上图执行计划中的COUNT STOPKEY（COUNT STOPKEY一旦获取到满足分页条件所需的记录后即停止SQL语句的执行）操作；

（3）执行计划中没有诸如SORT ORDER BY、SORT ORDER BY ROWNUM之类的排序操作，说明以上SQL语句执行时利用了索引消除了排序操作。

综上所述可知，该SQL的执行计划是正确的，高效的。

此外，对于只有1个列的降序排序操作，也可以不将该列的索引设置为降序索引，而是创建一般的升序索引，在查询执行时使用index_desc提示器，使得执行对该索引的扫描时采用降序扫描的方式。

如果创建符合索引时，排序列的顺序与order by中的排序列顺序不一致，在查询执行时，排序列的索引将仅用于通过索引访问记录，之后对获取的结果集再执行一次额外的排序操作，执行计划中将出现SORT ORDER BY或SORT ORDER BY ROWNUM之类的排序操作提示，说明并未利用索引消除排序操作。

如果创建的索引的顺序与实际排序的顺序不一致，例如：降序排序，升序索引，执行时也未使用index_desc提示器，则在查询执行时，排序列的索引也将仅用于通过索引访问记录，之后也要对获取的结果集再执行一次额外的排序操作。

2. 包含过滤条件和排序操作的分页

对于包含过滤条件和排序的分页，除了为排序列创建索引外，还需要为过滤条件创建索引。根据过滤条件的不同，又分为等值过滤和非等值过滤。

Ⅰ.等值过滤

如果分页语句中包含等值过滤和排序，此时需要为等值过滤列和排序列创建复合索引，该复合索引的顺序为等值过滤列在前，排序列在后，即：

(等值过滤列1,等值过滤列2,…,排序列1,排序列2,…)

以下示例2展示了该类SQL语句的调优。

【示例2】

对名为user的用户信息表执行分页查询，查询最近访问但积分最低的前10位类别为C类用户，分页语句如下所示。

为等值过滤列，排序列创建一个复合索引，按userclass，userscore，lasttime的顺序创建，即：

创建索引后，该语句的真实执行计划中的核心内容如下图所示。

通过以上的执行计划可以知：

（1）该SQL语句执行时访问User表时采用了INDEX RANGE SCAN（索引范围扫描）的访问方式，因为已经指定了过滤条件，且为过滤条件列设置了索引。

（2）执行INDEX RANGE SCAN时访问10条记录后，已经满足分页要求，扫描终止，该SQL语句执行完毕。即上图执行计划中的COUNT STOPKEY操作；

（3）执行计划中没有诸如SORT ORDER BY、SORT ORDER BY ROWNUM之类的排序操作，说明以上SQL语句执行时利用了索引消除了排序操作。

综上所述可知，该SQL的执行计划是正确的，高效的。

需要注意的是，创建复合索引时等值过滤条件列要放在排序列之前，如果将其放在排序列之后，即：按userscore，lasttime，userclass，的顺序创建上述分页语句的真实执行计划将变为如下图所示的。

通过以上的执行计划可以发现，对user表的访问方式由INDEX RANGE SCAN变为INDEX FULL SCAN方式。

过滤执行时为执行计划中id为7中对应的access和filter两种方式的结合，该方式说明在使用userclass列上索引访问user表时在执行索引扫描的同时又执行了对记录的过滤操作。具体操作为：扫描索引时，同时对每条索引按照过滤条件，如果符合过滤条件的记录达到分页操作指定的10条后，扫描停止。

这种边扫描边过滤的访问方式的执行效率较直接执行INDEX RANGE SCAN的访问方式稍低，因为这种方式的逻辑读多于INDEX RANGE SCAN方式。

Ⅱ.等值过滤和非等值过滤

如果分页语句中包含等值过滤非等值过滤和排序，创建复合索引时，需要将等值过滤列放在前部，排序列放在中部，非等值过滤列放在后部，即：

(等值过滤列1,等值过滤列2,…,排序列1,排序列2,…,非等值过滤列1,非等值过滤列2…)

同样地，如果分页语句中只有非等值过滤和排序，则需要将非等值过滤列放到复合索引的后部，以下示例2展示了该类SQL语句的调优。

【示例3】

对名为user的用户信息表执行分页查询，查询最近访问但积分最低的且注册时间大于18个月的前10位类别为C类用户，分页语句如下所示。

为等值过滤列，非等值过滤列，排序列创建一个复合索引，按userclass，userscore，lasttime，regtime的顺序创建，即：

创建索引后，该语句的真实执行计划中的核心内容如下图所示。

通过以上的执行计划可以知，该分页SQL查询执行时已经消除了排序操作，且对user的访问方式为INDEX RANGE SCAN（索引范围扫描），因为创建复合索引时将非等值过滤列regtime放到了排序列之后，所以执行INDEX RANGE SCAN时又进行了过滤操作，虽然这种方式的执行性能稍低于直接执行INDEX RANGE SCAN，但如果创建复合索引时将非等值过滤列regtime放到了排序列之后，该分页SQL查询的真实执行计划将变为如下图所示的形式。

该执行计划中出现了 SORT ORDER BY排序操作，且执行INDEX RANGE SCAN时扫描了6389条记录，说明查询执行时未利用索引是有序的特性，对查询结果结果集又执行了排序操作并只获取了前10条记录，显然，这种方式的执行效率和性能相比INDEX RANGE SCAN和过滤方式是较差的。

2. 包含过滤操作和排序操作的单分区表查询分页

上述介绍的通过创建适当的索引调优分页操作的方法同样适用于分区表的分页查询。

Oracle数据库的分区表的索引分为本地索引（Local Index）和全局（Global Index）索引两种类型。

本地索引（Local Index）的分区与表的分区完全相同，每个分区都有自己的本地索引分区。本地索引的维护由Oracle自动进行，通常本地索引的维护代价较低。

全局索引（Global Index）与表的分区无关，因此它只有一个分区。全局索引的维护和更新对于表的分区变化的响应速度可能较慢，但是在全局索引分区上可以执行跨分区的查询操作。

通过以上对两种索引的介绍可知，为分区表创建索引时需要在本地索引与全局索引中做出选择，选则原则如下。

（1）如果分页查询操作只访问一个分区，则可以为相应的列创建本地索引；

（2）如果分页查询操作需要访问多个分区（跨分区访问），则需要为相应的列创建全局索引，否则无法确保分页操作中排序操作的顺序也索引的顺序一致，从而无法利用索引的有序性消除分页中的排序操作；

（3）在分页查询操作中没有过滤条件的情景下，查询执行时将扫描分区表中的所有分区，如果该分页查询中的排序列就是创建分区表时的范围分区列，且范围分区中每个分区中的数据是递增的，此时可以为排序列创建本地索引。如果创建的分区是HASH分区或LIST分区，因为这两类分区是无序的，所以需要为排序列创建全局索引。

2. 表关联查询分页

对于多表关联排序的调优，需要遵循以下的原则。

（1）将表关联方式调整为嵌套循环关联，需要对哪个表排序，则应该该表作为驱动表，注意驱动表返回的数据顺序应与排序的顺序一致。如果采用外连接，则采用嵌套循环关联时，驱动表只能为主表；

（2）根据（1）可知，排序列只来自于嵌套循环关联的驱动表，即：只能对一个表排序，如果排序列即来自驱动表也来自被驱动表，则需要等表关联全部执行完成后，对结果集执行一次排序，导致排序操作无法消除；

（3）关联分页查询语句中不能出现max、min、avg之类的聚合函数，也不能出现去重（distinct）、分组（group by）、并（union/union all）等操作，这些操作会使得全部关联操作完成之后再进行分页，执行性能较低。

【示例4】

对 CD_U_MC_MODEL_REMINDER_ORGCUST（A）和CD_U_ MC MODEI_REMINDER（B）表执行关联，并进行分页操作，排序后获取前50条记录，SQL语句如下。

其中CD_U_MC_MODEL_REMINDER_ORGCUST表记录较多，达到3千万左右，该SQL语句执行较慢，平均执行用时16秒左右。

经对执行计划进行分析，语句中存在SORT ORDER BY ROWNUM排序操作，该SQL执行表表关联后，返回的结果集较大，达到300万条记录，对300万条进行排序，造成了较大的开销，且因为PGA空间有限，排序占用了临时表空间，同时造成了一定的I/O开销（出现direct path readtemp 和direct path write temp事件）。

对该SQL查询调优的首要就是消除排序，因排序列为CD_U_MC_MODEL_REMINDER_ORGCUST表，所以，为该表创建一个联合索引，按“(等值过滤列,关联列,排序列)”的顺序依次创建，需要注意的是，该SQL查询按CD_U_MC_MODEL_REMINDER_ORGCUST表的reach_date列降序排序，reach_date列需要创建降序索引，创建索引的语句如下。

创建索引后，该SQL语句的执行计划如下所示。

通过执行计划可以发现，排序操作已被消除，优化器选择了嵌套循环关联，并以排序列所属的CD_U_MC_MODEL_REMINDER_ORGCUST表作为嵌套循环关联的驱动表。

实际执行该SQL语句，查询用时降至0.6秒左右。

继续对该查询进行优化，通过进一步分析CD_U_MC_MODEL_REMINDER_ORGCUST表的数据分布，发现该表的记录按procod列的数据分布，整个表中procod列的值约有30个，每个值对应的记录数在100万-300万条左右，查询执行时对CD_U_MC_MODEL_REMINDER_ORGCUST表按procod的值进行了过滤。因此，我们可以将CD_U_MC_MODEL_REMINDER_ORGCUST表按procod的值进行分区，创建非HASH/LIST分区。此外，每次查询时procod只指定一个值，不存在跨分区查询，所以，为procod列创建本地索引即可。

经过以上第二次调优后，该SQL语句的执行计划如下所示。

通过以上的执行计划可以发现，对CD_U_MC_MODEL_REMINDER_ORGCUST表的访问只扫描了一个分区，该SQL语句的执行时间进一步降低至10毫秒左右。

45 个非常有用的 Oracle 查询语句

这里我们介绍的是 40+ 个非常有用的 Oracle 查询语句，主要涵盖了日期操作，获取服务器信息，获取执行状态，计算数据库大小等等方面的查询。这些是所有 Oracle 开发者都必备的技能，所以快快收藏吧！

1. 获取当前月份的第一天运行这个命令能快速返回当前月份的第一天。
2. 你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

获取当前月份的第一天运行这个命令能快速返回当前月份的第一天

1. 获取当前月份的最后一天这个查询语句类似于上面那个语句，而且充分照顾到了闰年，所以当二月份有 29 号，那么就会返回 29/2 。
2. 你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

1. 获取当前年份的第一天每年的第一天都是1 月1日，这个查询语句可以使用在存储过程中，需要对当前年份第一天做一些计算的时候。
2. 你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

1. 获取当前年份的最后一天类似于上面的查询语句。
2. 你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

1. 获取当前月份的天数这个语句非常有用，可以计算出当前月份的天数。
2. 你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

1. 获取当前月份剩下的天数下面的语句用来计算当前月份剩下的天数。
2. 你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

1. 获取两个日期之间的天数使用这个语句来获取两个不同日期之间的天数。

这个例子是计算员工入职的天数。

1. 显示当前年份截止到上个月每个月份开始和结束的日期这个是个很聪明的查询语句，用来显示当前年份每个月的开始和结束的日期，你可以使用这个进行一些类型的计算。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

1. 获取直到目前为止今天过去的秒数（从 00：00 开始算）

1. 获取今天剩下的秒数（直到 23：59：59 结束）

1. 检查在当前数据库模式下是否存在指定的表这是一个简单的查询语句，用来检查当前数据库是否有你想要创建的表，允许你重新运行创建表脚本，这个也可以检查当前用户是否已经创建了指定的表（根据这个查询语句在什么环境下运行来查询）。

1. 检查在当前表中是否存在指定的列这是个简单的查询语句来检查表里是否有指定的列，在你尝试使用 ALTER TABLE 来添加新的列新到表中的时候非常有用，它会提示你是否已经存在这个列。

1. 显示表结构这 个查询语句会显示任何表的 DDL 状态信息。请注意我们已经将‘TABLE’作为第一个信息提交了。这个查询语句也可以用来获取任何数据库对象的 DDL 状态信息。举例说明，只需要把第一个参数替换成‘VIEW’，第二个修改成视图的名字，就可以查询视图的 DDL 信息了。

1. 获取当前模式这是另一个可以获得当前模式的名字的查询语句。

1. 修改当前模式这是另一个可以修改当前模式的查询语句，当你希望你的脚本可以在指定的用户下运行的时候非常有用，而且这是非常安全的一个方式。

1. 数据库版本信息返回 Oracle 数据库版本

1. 数据库默认信息返回一些系统默认的信息

1. 数据库字符设置信息显示数据库的字符设置信息

1. 获取 Oracle 版本

1. 存储区分大小写的数据，但是索引不区分大小写某些时候你可能想在数据库中查询一些独立的数据，可能会用 UPPER(..) = UPPER(..) 来进行不区分大小写的查询，所以就想让索引不区分大小写，不占用那么多的空间，这个语句恰好能解决你的需求 。

1. 调整没有添加数据文件的表空间另一个 DDL 查询来调整表空间大小

1. 检查表空间的自动扩展开关在给定的表空间中查询是否打开了自动扩展开关

1. 在表空间添加数据文件在表空间中添加数据文件

1. 增加数据文件的大小给指定的表空间增加大小

1. 查询数据库的实际大小给出以 GB 为单位的数据库的实际大小

1. 查询数据库中数据占用的大小或者是数据库使用细节给出在数据库中数据占据的空间大小

1. 查询模式或者用户的大小以 MB 为单位给出用户的空间大小

1. 查询数据库中每个用户最后使用的 SQL 查询此查询语句会显示当前数据库中每个用户最后使用的 SQL 语句。

1. 查询用户 CPU 的使用率 这个语句是用来显示每个用户的 CPU 使用率，有助于用户理解数据库负载情况

1. 查询数据库长查询进展情况显示运行中的长查询的进展情况

1. 获取当前会话 ID，进程 ID，客户端 ID 等这个专门提供给想使用进程 ID 和 会话 ID 做些 voodoo magic 的用户。

• V$SESSION.SID AND V$SESSION.SERIAL# 是数据库进程 ID
• V$PROCESS.SPID 是数据库服务器后台进程 ID
• V$SESSION.PROCESS 是客户端 PROCESS ID, ON windows it IS : separated THE FIRST # IS THE PROCESS ID ON THE client AND 2nd one IS THE THREAD id.

1. 查询特定的模式或者表中执行的最后一个 SQL 语句

1. 查询每个执行读取的前十个 SQL

1. 在视图中查询并显示实际的 Oracle 连接

1. 查询并显示通过打开连接程序打开连接的组

1. 查询并显示连接 Oracle 的用户和用户的会话数量

1. 获取拥有者的对象数量

1. 把数值转换成文字更多信息可以查看： Converting number into words in Oracle

1. 输出:one thousand five hundred twenty-six

1. 在包的源代码中查询字符串这个查询语句会在所有包的源代码上搜索‘FOO_SOMETHING’ ，可以帮助用户在源代码中查找特定的存储过程或者是函数调用。

1. 把用逗号分隔的数据插入的表中当 你想把用逗号分隔开的字符串插入表中的时候，你可以使用其他的查询语句，比如 IN 或者是 NOT IN 。这里我们把‘AA,BB,CC,DD,EE,FF’转换成包含 AA,BB，CC 等作为一行的表，这样你就很容易把这些字符串插入到其他表中，并快速地做一些相关的操作。

1. 查询表中的最后一个记录这个查询语句很直接，表中没有主键，或者是用户不确定记录最大主键是否是最新的那个记录时，就可以使用这个语句来查询表中最后一个记录。

1. 在 Oracle 中做行数据乘法这个查询语句使用一些复杂的数学函数来做每个行的数值乘法。更多内容请查阅：

1. 在 Oracle 生成随机数据每个开发者都想能轻松生成一堆随机数据来测试数据库多好，下面这条查询语句就可以满足你，它可以在 Oracle 中生成随机的数据插入到表中。详细信息可以查看 Random Data in Oracle

1. 在 Oracle 中生成随机数值这是 Oracle 普通的旧的随机数值生成器。这个可以生成 0-100 之间的随机数值，如果你想自己设置数值范围，那么改变乘数就可以了。

1. 检查表中是否含有任何的数据这个可以有很多种写法，你可以使用 count(*) 来查看表里的行的数量，但是这个查询语句比较高效和快速，而且我们只是想知道表里是否有任何的数据。

如果你知道一些很好用的查询语句，可以减轻 Oracle 开发者的负担，那么在评论分享一下吧：）

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com