当前位置：文档之家 › Oracle分页查询优化

Oracle分页查询优化

  • 格式：doc
  • 大小：165.50 KB
  • 文档页数：49

Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。

Oracle分页查询语句（一）分页查询格式：SELECT * FROM(SELECT A.*, ROWNUM RNFROM (SELECT * FROM TABLE_NAME) AWHERE ROWNUM <= 40)WHERE RN >= 21其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。

ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。

上面给出的这个分页查询语句，在大多数情况拥有较高的效率。

分页的目的就是控制输出结果集大小，将结果尽快的返回。

在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 40这句上。

选择第21 到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。

而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句，在查询的最外层控制分页的最小值和最大值。

这是，查询语句如下：SELECT * FROM(SELECT A.*, ROWNUM RNFROM (SELECT * FROM TABLE_NAME) A)WHERE RN BETWEEN 21 AND 40对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。

这是由于CBO 优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。

对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。

因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。

数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

这里就不对包含排序的查询进行说明了，下一篇文章会通过例子来详细说明。

下面简单讨论一下多表联合的情况。

对于最常见的等值表连接查询，CBO 一般可能会采用两种连接方式NESTED LOOP和HASH JOIN （MERGE JOIN效率比HASH JOIN效率低，一般CBO不会考虑）。

在这里，由于使用了分页，因此指定了一个返回的最大记录数，NESTED LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层，而HASH JOIN必须处理完所有结果集（MERGE JOIN也是）。

那么在大部分的情况下，对于分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率（分页查询的时候绝大部分的情况是查询前几页的数据，越靠后面的页数访问几率越小）。

因此，如果不介意在系统中使用HINT的话，可以将分页的查询语句改写为：SELECT /*+ FIRST_ROWS */ * FROMSELECT A.*, ROWNUM RNFROM (SELECT * FROM TABLE_NAME) AWHERE ROWNUM <= 40)WHERE RN >= 21Oracle分页查询语句（二）这篇文章用几个例子来说明分页查询的效率。

首先构造一个比较大的表作为测试表：SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS, DBA_SEQUENCES;表已创建。

SQL> SELECT COUNT(*) FROM T;COUNT(*)----------457992首先比较两种分页方法的区别：SQL> SET AUTOT ONSQL> COL OBJECT_NAME FORMA T A30SQL> EXEC DBMS_STATS.GATHER_TABLE_STA TS(USER, 'T')PL/SQL 过程已成功完成。

SQL> SELECT OBJECT_ID, OBJECT_NAME2 FROM3 (4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME5 FROM6 (7 SELECT OBJECT_ID, OBJECT_NAME FROM T8 )9 )10 WHERE RN BETWEEN 11 AND 20;OBJECT_ID OBJECT_NAME---------- ------------------------------5807 ALL_APPLY_PROGRESS1769 ALL_ARGUMENTS2085 ALL_ASSOCIATIONS4997 ALL_AUDIT_POLICIES4005 ALL_BASE_TABLE_MVIEWS5753 ALL_CAPTURE5757 ALL_CAPTURE_PARAMETERS5761 ALL_CAPTURE_PREPARED_DA TABASE5765 ALL_CAPTURE_PREPARED_SCHEMAS5769 ALL_CAPTURE_PREPARED_TABLES已选择10行。

Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=457992 Bytes=42135264)1 0 VIEW (Cost=864 Card=457992 Bytes=42135264)2 1 COUNT3 2 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)Statistics----------------------------------------------------------0 recursive calls0 db block gets8979 consistent gets7422 physical reads0 redo size758 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)10 rows processedSQL> SELECT OBJECT_ID, OBJECT_NAME2 FROM3 (4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME5 FROM6 (7 SELECT OBJECT_ID, OBJECT_NAME FROM T8 )9 WHERE ROWNUM <= 2010 )11 WHERE RN >= 11;OBJECT_ID OBJECT_NAME---------- ------------------------------5807 ALL_APPLY_PROGRESS1769 ALL_ARGUMENTS2085 ALL_ASSOCIATIONS4997 ALL_AUDIT_POLICIES4005 ALL_BASE_TABLE_MVIEWS5753 ALL_CAPTURE5757 ALL_CAPTURE_PARAMETERS5761 ALL_CAPTURE_PREPARED_DA TABASE5765 ALL_CAPTURE_PREPARED_SCHEMAS5769 ALL_CAPTURE_PREPARED_TABLES已选择10行。

Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=20 Bytes=1840)1 0 VIEW (Cost=864 Card=20 Bytes=1840)2 1 COUNT (STOPKEY)3 2 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)Statistics----------------------------------------------------------0 recursive calls0 db block gets5 consistent gets0 physical reads0 redo size758 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)10 rows processed二者执行效率相差很大，一个需要8000 多逻辑读，而另一个只需要5个逻辑读。

观察二者的执行计划可以发现，两个执行计划唯一的区别就是第二个查询在COUNT这步使用了STOPKEY，也就是说，Oracle 将ROWNUM <= 20推入到查询内层，当符合查询的条件的记录达到STOPKEY的值，则Oracle结束查询。

因此，可以预见，采用第二种方式，在翻页的开始部分查询速度很快，越到后面，效率越低，当翻到最后一页，效率应该和第一种方式接近。

SQL> SELECT OBJECT_ID, OBJECT_NAME2 FROM3 (4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME5 FROM6 (7 SELECT OBJECT_ID, OBJECT_NAME FROM T8 )9 WHERE ROWNUM <= 45799010 )11 WHERE RN >= 457980;OBJECT_ID OBJECT_NAME---------- ------------------------------7128 XCF_I_HANDLE_STA TUS7126 XCF_P7127 XCF_U17142 XDF7145 XDF_I_DF_KEY7146 XDF_I_HANDLE_STA TUS7143 XDF_P7144 XDF_U1TEST.Y ANGTINGKUNTEST4.Y ANGTINGKUNY ANGTK.Y ANGTINGKUN已选择11行。

合集下载

oracle优化方法总结

oracle优化方法总结

千里之行，始于足下。

oracle优化方法总结Oracle优化是提高数据库性能和响应能力的重要步骤。

本文总结了一些常见的Oracle优化方法。

1. 使用索引：索引是提高查询性能的主要方法。

通过在表中创建适当的索引，可以加快查询速度，并减少数据访问的开销。

但是要注意不要过度使用索引，因为过多的索引会增加写操作的开销。

2. 优化查询语句：查询语句的效率直接影响数据库的性能。

可以通过合理地编写查询语句来提高性能。

例如，使用JOIN来替代子查询，尽量避免使用通配符查询，使用LIMIT来限制结果集的大小等。

3. 优化表结构：表的设计和结构对数据库的性能也有很大的影响。

合理的表设计可以减少数据冗余和不必要的数据存储，提高查询速度。

例如，适当地使用主键、外键和约束，避免过多的数据类型和字段等。

4. 优化数据库参数设置：Oracle有很多参数可以用来调整数据库的性能。

根据具体的应用场景和需求，可以根据情况调整参数的值。

例如，调整SGA和PGA的大小，设置合适的缓冲区大小，调整日志写入方式等。

5. 使用分区表：当表的数据量很大时，可以考虑将表分成多个分区。

分区表可以加速查询和维护操作，提高数据库的性能。

可以按照时间、地域、业务等来进行分区。

6. 优化存储管理：Oracle提供了多种存储管理选项，如表空间和数据文件管理。

合理地分配存储空间和管理数据文件可以提高数据库的性能。

例如，定期清理无用的数据文件，使用自动扩展表空间等。

第1页/共2页锲而不舍，金石可镂。

7. 数据压缩：对于大量重复数据或者冷数据，可以考虑使用Oracle的数据压缩功能。

数据压缩可以减少磁盘空间的使用，提高IO性能。

8. 使用并行处理：对于大型计算或者批处理任务，可以考虑使用Oracle的并行处理功能。

并行处理可以将任务分成多个子任务，并行执行，提高处理能力和效率。

9. 数据库分区：对于大型数据库，可以考虑将数据库分成多个独立的分区。

数据库分区可以提高数据的并行处理能力，减少锁竞争和冲突，提高数据库的性能。

oracle 分页sql写法

oracle 分页sql写法

一、概述在进行数据库查询时，经常会遇到需要分页展示数据的情况。

而在Oracle数据库中，需要用到分页查询的SQL语句。

本文将就Oracle 中的分页SQL写法进行详细介绍。

二、基本分页SQL语句在Oracle数据库中，可以使用ROWNUM来实现分页查询。

以下是基本的分页SQL语句示例：```sqlSELECT * FROM (SELECT t.*, ROWNUM rnFROM (SELECT * FROM your_table ORDER BY order_column) t WHERE ROWNUM <= pageSize * pageNum)WHERE rn > pageSize * (pageNum - 1)```其中，your_table是要查询的表名，order_column是用来排序的字段，pageSize是每页展示的数据条数，pageNum是要查询的页数。

三、分页SQL写法解析1. 内部查询和外部查询分页SQL语句中，有一个内部查询和一个外部查询。

内部查询用来获取排序后的数据和每行数据对应的行号，外部查询用来根据行号来筛选需要的数据并展示。

2. 内部查询内部查询中使用了ROWNUM来标记行号，并通过ORDER BY语句来对数据进行排序。

内部查询的结果会被外部查询筛选。

3. 外部查询外部查询使用了WHERE语句来筛选出需要展示的数据，并且通过pageSize和pageNum来计算需要查询的数据范围。

四、使用样例假设有一个名为employee的表，包含字段id、name、age，现需要从该表中查询第2页的数据，每页展示10条数据，并按id字段进行排序。

则对应的分页SQL语句为：```sqlSELECT * FROM (SELECT t.*, ROWNUM rnFROM (SELECT id, name, age FROM employee ORDER BY id) t WHERE ROWNUM <= 10 * 2)WHERE rn > 10 * (2 - 1)```这条SQL语句将返回employee表中第11-20条数据，并按id字段排序。

oracle优化方案

oracle优化方案

千里之行，始于足下。

oracle优化方案Oracle优化方案Oracle数据库是当今企业界最受欢迎的关系型数据库管理系统之一。

但是，随着数据量的不断增加和业务需求的不断增长，数据库的性能问题也会渐渐变得突出。

因此，对Oracle数据库进行优化是提高系统性能和运行效率的关键。

本文将介绍几个常见的Oracle数据库优化方案，挂念您更好地管理和优化您的数据库环境。

1. 索引优化索引是提高查询性能的关键。

可以通过以下几个方面对索引进行优化：（1）合理选择索引类型：依据查询的特点和数据分布选择合适的索引类型，如B-tree索引、位图索引等。

（2）避开过多的索引：过多的索引会增加数据插入、更新和删除的成本，并降低查询性能。

只保留必要的索引，可以有效提高性能。

（3）定期重建和重新组织索引：定期重建和重新组织索引可以提高索引的查询效率，削减碎片和冗余。

2. SQL优化SQL语句是Oracle数据库的核心，对SQL进行优化可以显著提高数据库的性能。

以下是一些SQL优化的建议：第1页/共3页锲而不舍，金石可镂。

（1）优化查询语句：避开使用不必要的子查询，尽量使用连接查询代替子查询，削减查询次数。

同时，避开使用全表扫描，可以通过创建合适的索引来提高查询效率。

（2）避开使用不必要的OR运算符：OR运算符的查询效率较低，应尽量避开使用。

可以通过使用UNION或UNION ALL运算符代替OR运算符来提高性能。

（3）避开使用ORDER BY和GROUP BY子句：ORDER BY和GROUP BY子句会造成排序和分组操作，对于大数据集来说是格外耗时的。

假如可能，可以考虑使用其他方式来实现相同的功能。

3. 系统资源优化合理配置和管理系统资源是确保数据库运行稳定和高效的重要因素。

以下是一些建议：（1）合理安排内存：依据系统和数据库的实际需求，合理安排内存资源。

调整SGA（System Global Area）区域的大小，确保适当的内存安排给缓冲池和共享池。

oracle 分页写法

oracle 分页写法

oracle 分页写法Oracle数据库是一种关系型数据库管理系统，它支持SQL查询语言并提供了用于创建、管理和操作数据库的工具和技术。

在实际应用中，分页是一项非常常见的需求，它允许我们将查询结果分为多个页面显示，提升用户体验和查询效率。

本文将介绍Oracle数据库中的分页写法，并详细解释如何在查询中使用分页功能。

在Oracle数据库中，我们可以使用ROWNUM或ROW_NUMBER函数来实现分页。

这两种方法在概念上有所不同，下面将分别介绍。

1.使用ROWNUM进行分页ROWNUM是Oracle数据库中的一个伪列，它按照查询结果的顺序分配一个唯一的行数。

在使用ROWNUM进行分页时，我们需要在查询语句中添加额外的条件和子查询。

语法：SELECT *FROM (SELECT column(s), ROWNUM AS row_numFROM table_nameWHERE conditionsORDER BY column(s))WHERE row_num >= start_row AND row_num <= end_row;说明：- column(s):需要查询的列名或表达式- table_name:需要查询的表名- conditions:查询条件- row_num:为ROWNUM指定一个别名，用于在外部查询中进行筛选- start_row:分页的起始行数- end_row:分页的结束行数步骤：1.编写内部查询，该查询会为每一行分配一个唯一的ROWNUM。

2.编写外部查询，使用ROWNUM作为条件进行分页。

示例：SELECT *FROM (SELECT employee_id, first_name, last_name, ROWNUM AS row_numFROM employeesWHERE department_id = 50ORDER BY employee_id)WHERE row_num >= 1 AND row_num <= 10;说明：在示例中，我们从employees表中查询department_id为50的员工信息，并按照employee_id进行排序。