ホームページ データベース mysql チュートリアル 关于Oracle数据库分页查询的小技巧

关于Oracle数据库分页查询的小技巧

Jun 07, 2016 pm 04:35 PM
oracle について ページネーション スキル データベース お問い合わせ

对于Oracle中的相关优化,有许多常用的方法,像大家都知道的如:select *不提倡使用,效率极差,建议显式获取列,即使是所有字段也应罗列。而取总数时使用count(*),为提高cache的命中率,尽量做到SQL重用。另外,提高性能的好选择可能就是把所有的字符数据都

对于Oracle中的相关优化,有许多常用的方法,像大家都知道的如:select *不提倡使用,效率极差,建议显式获取列,即使是所有字段也应罗列。而取总数时使用count(*),为提高cache的命中率,尽量做到SQL重用。另外,提高性能的好选择可能就是把所有的字符数据都保存为Unicode,Java以Unicode形式处理所有数据,因此,数据库驱动程序不必再执行转换过程。

而这篇文章我们主要来了解下关于用SQL来实现分页的很多种实现方式,有些语句可能并不是很通用,只能用在一些特殊场景之中,而一些虽然功能强大、使用方便,带来的缺陷就是效率会慢得掉渣,接下来是IT男经过亲身的开发经验,分别讲解下这些常用的sql查询语句的作用,但愿对你有帮助!

以下介绍三种比较通用的实现方案;在以下各种实现中,ROWNUM是一个最核心的关键词,在查询时他是一个虚拟的列,取值为从1到记录总数的序号。

1、首先来介绍我们工作中最常使用的一种实现方式:

SELECT *
FROM (SELECT IT_NAN_.*, ROWNUM R_
  FROM (SELECT * 
      FROM IT_NAN T 
      ORDER BY T.IT_CAT) IT_NAN_
  WHERE ROWNUM = 49991;
ログイン後にコピー

其中最内层的查询SELECT为不进行翻页的原始查询语句,可以用自己的任意Select SQL替换;ROWNUM = 49991控制分页查询的每页的范围。分页的目的就是控制输出结果集大小,将结果尽快的返回;上面的SQL语句在大多数情况拥有较高的效率,主要体现在WHERE ROWNUM

2、上面例子中展示的在查询的第二层通过ROWNUM

SELECT *
FROM (SELECT IT_NAN_.*, ROWNUM R_
  FROM (SELECT *
    FROM IT_NAN T
    ORDER BY T.IT_CAT) IT_NAN_ )
WHERE R_ BETWEEN 49991 AND 50000;
ログイン後にコピー

由于Oracle可以将外层的查询条件推到内层查询中,以提高内层查询的执行效率,但不能跨越多层。
对于第一个查询语句,第二层的查询条件WHERE ROWNUM

而第二个查询语句,由于查询条件BETWEEN 49991 AND 50000 是存在于查询的第三层,而Oracle无法将第三层的查询条件推到最内层(即使推到最内层也没有意义,因为最内层查询不知道ROWNUM代表什么)。因此,对于第二个查询语句,Oracle最内层返回给中间层的是所有满足条件的数据,而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成,显然这个效率要比第一个查询低得多。(这种查询语句经过本IT男测试和以上相同的数据时,测试的时间大约为第一种查询时间的两倍,并也随着50000这个数值的变大,时间略微增长。)

3、以上两种方案完全是通过ROWNUM来完成,下面一种则采用ROWID和ROWNUM相结合的方式,SQL语句如下:

SELECT *
FROM (SELECT RID
  FROM (SELECT IT_NAN_1.RID, ROWNUM R_
    FROM (SELECT T.ROWID RID
      FROM IT_NAN T
      ORDER BY T.IT_CAT) IT_NAN_
    WHERE ROWNUM = 49991) IT_NAN_1_ , IT_NAN IT_NAN_2_
WHERE IT_NAN_1_.RID = IT_NAN IT_NAN_2_.ROWID ;
ログイン後にコピー

从语句上看,共有嵌套查询了四层Select,最内层为可替换的不分页原始SQL语句,但是他查询的字段只有ROWID,而没有任何待查询的实际表字段,具体查询实际字段值是在最外层实现的。

这种方式的原理大致为:首先通过ROWNUM查询到分页之后的10条实际返回记录的ROWID,最后通过ROWID将最终返回字段值查询出来并返回;

和前面两种实现方式相比,该SQL的实现方式更加繁琐,通用性也不是非常好,因为要将原始的查询语句分成两部分(查询字段在最外层,表及其查询条件在最内层);
但这种实现在特定场景下还是有优势的:比如我们经常要翻页到很后面,比如100000条记录中我们经常需要查90000-91000及其以后的数据;此时该方案效率可能要比前面的高;
因为前面的方案中是通过ROWNUM

从不断向后翻页这个角度来看,第一种实现方案的成本会越来越高,基本上是线性增长,而第三种方案的成本则不会像前者那样快速,他的增长只体现在通过查询条件读取ROWID的部分;(这种查询语句IT男同样进行了测试,测试的时间大约为第一种查询时间的一半,并也随着50000这个数值的变大,体现出的效果会更可观。而后我又测试了查看页数为1-10的查询速度,结果发现这个查询方法,仍旧比第一种要快将近一半…不知道具体原因,不过事实胜于雄辩啊…)

这个查询方法真的很给力,如果你们的项目中应用了hibernate的二级缓存对实体对象进行了缓存的话,那样的话,效率将会是更加的可观。

4、当然,除了以上提了这些方案,我们还可以用以下的SQL来实现:

SELECT *
FROM IT_NAN
WHERE ROWID NOT IN
(SELECT ROWID FROM IT_NAN WHERE ROWNUM 
<p>以上两种查询语句IT男也测试了下,大概是8s左右。所以说如果项目数据量不大的话,可以尝试使用,如果百万或以上的数据,还是考虑上面的比较好些。</p>
<p>………………<br>
注意:当ROWNUM作为查询条件时,他是在order by之前执行,所以要特别小心;<br>
比如我们想查询IT_NAN中按IT_CAT倒序排列的前10条记录不能用如下的SQL来完成:</p>
<pre class="brush:php;toolbar:false">SELECT * 
FROM IT_NAN T 
WHERE ROWNUM 
<p>当然对于这个需求,可以参考上面的例子那样的子查询来实现,也可以采用Oracle的排序函数:ROW_NUMBER()函数根据OrderID这列来生成一个新的数据列。当然对ROW_NUMBER感兴趣的可以请教下度娘或谷哥,这里就不再细说,效果是差不多。</p>
<p>关于数据的优化还有很多事情要做,要考虑,要研究,这也只是一点儿小技巧,有时间我会继续在博客中分享相关的经验,当然也希望有大虾们尽情指正、放荡的拍砖、一起探讨分享你的优化经验,共同成长!</p>
    <p class="copyright">
        原文地址:关于Oracle数据库分页查询的小技巧, 感谢原作者分享。
    </p>
    
    


ログイン後にコピー
このウェブサイトの声明
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。

ホットな記事タグ

メモ帳++7.3.1

メモ帳++7.3.1

使いやすく無料のコードエディター

SublimeText3 中国語版

SublimeText3 中国語版

中国語版、とても使いやすい

ゼンドスタジオ 13.0.1

ゼンドスタジオ 13.0.1

強力な PHP 統合開発環境

ドリームウィーバー CS6

ドリームウィーバー CS6

ビジュアル Web 開発ツール

SublimeText3 Mac版

SublimeText3 Mac版

神レベルのコード編集ソフト(SublimeText3)

Oracle データベースのログはどのくらいの期間保存されますか? Oracle データベースのログはどのくらいの期間保存されますか? May 10, 2024 am 03:27 AM

Oracle データベースのログはどのくらいの期間保存されますか?

Oracle データベース サーバーのハードウェア構成要件 Oracle データベース サーバーのハードウェア構成要件 May 10, 2024 am 04:00 AM

Oracle データベース サーバーのハードウェア構成要件

Oracle にはどれくらいのメモリが必要ですか? Oracle にはどれくらいのメモリが必要ですか? May 10, 2024 am 04:12 AM

Oracle にはどれくらいのメモリが必要ですか?

Oracle データベースを使用するために必要なメモリの量 Oracle データベースを使用するために必要なメモリの量 May 10, 2024 am 03:42 AM

Oracle データベースを使用するために必要なメモリの量

Oracle のスケジュールされたタスクは、作成ステップを 1 日に 1 回実行します。 Oracle のスケジュールされたタスクは、作成ステップを 1 日に 1 回実行します。 May 10, 2024 am 03:03 AM

Oracle のスケジュールされたタスクは、作成ステップを 1 日に 1 回実行します。

PHP で MySQLi を使用してデータベース接続を確立するための詳細なチュートリアル PHP で MySQLi を使用してデータベース接続を確立するための詳細なチュートリアル Jun 04, 2024 pm 01:42 PM

PHP で MySQLi を使用してデータベース接続を確立するための詳細なチュートリアル

Oracleでリスニングプログラムを開始する方法 Oracleでリスニングプログラムを開始する方法 May 10, 2024 am 03:12 AM

Oracleでリスニングプログラムを開始する方法

iOS 18では、紛失または破損した写真を復元するための新しい「復元」アルバム機能が追加されます iOS 18では、紛失または破損した写真を復元するための新しい「復元」アルバム機能が追加されます Jul 18, 2024 am 05:48 AM

iOS 18では、紛失または破損した写真を復元するための新しい「復元」アルバム機能が追加されます

See all articles