在oracle的分页查询中，常用的写法是这样的
select * from (
select pageStart.*,rownum as rn from (
select
consign.ID AS id
,consign.CONSIGN_CODE AS consignCode
,consign.ORDER_ID AS orderId
,consign.SHIPPER_CODE AS shipperCode
,consign.SHIPPER_NAME AS shipperName
,consign.SHIPPER_LINKMAN AS shipperLinkman
,consign.SHIPPER_MOBILE AS shipperMobile
,consign.SHIPPER_ADDRESS AS shipperAddress
,consign.SHIPPER_FAX AS shipperFax
,consign.SHIPPER_TEL AS shipperTel
,consign.CONSIGNEE_CODE AS consigneeCode
,consign.CONSIGNEE_NAME AS consigneeName
,consign.CONSIGNEE_LINKMAN AS consigneeLinkman
,consign.CONSIGNEE_MOBILE AS consigneeMobile
,consign.CONSIGNEE_ADDRESS AS consigneeAddress
,consign.CONSIGNEE_FAX AS consigneeFax
,consign.CONSIGNEE_TEL AS consigneeTel
,consign.REMARK AS remark
,consign.END_STATION AS endStation
,consign.BEGIN_STATION AS beginStation
,consign.DEDESTINATION AS dedestination
,consign.BAR_CODE AS barCode
,consign.MOSTLY_GOODS AS mostlyGoods
,consign.TRANSPORT_FEE AS transportFee
,consign.INSURANCE_ACCOUNTS AS insuranceAccounts
,consign.INSURANCE_FEE AS insuranceFee
,consign.DELIVER_COMM_FEE AS deliverCommFee
,consign.TRANSER_FEE AS transerFee
,consign.PACKGAE_FEE AS packgaeFee
,consign.HURRYING_FEE AS hurryingFee
,consign.BACK_HANDER AS backHander
,consign.REBATE AS rebate
,consign.CASH AS cash
,consign.ARREARAGE AS arrearage
,consign.PICKUP_ACCOUNTS AS pickupAccounts
,consign.PICKUP_GOODS_FEE AS pickupGoodsFee
,consign.UNLOAD_FEE AS unloadFee
,consign.TOTAL_ACCOUNTS AS totalAccounts
,consign.OTHER_FEE AS otherFee
,consign.ACCOUNTS_REC AS accountsRec
,consign.BALANCE_TYPE AS balanceType
,consign.ACCOUNTS_OF_GOODS AS accountsOfGoods
,consign.AGENT_FEE AS agentFee
,consign.AP_TRANSER_FEE AS apTranserFee
,consign.AP_DELIVER_FEE AS apDeliverFee
,consign.TRANSIT_ITEM AS transitItem
,consign.TRANSIT_MODE AS transitMode
,consign.TRANSFER_MODE AS transferMode
,consign.NOTE_RETURNED_TYPE AS noteReturnedType
,consign.PRIORITY AS priority
,consign.SALE_MAN AS saleMan
,consign.ACCEPT_ORGANIZE AS acceptOrganize
,consign.TRANSFER_SITE AS transferSite
,consign.SPEC_LINE_ID AS specLineId
,consign.NOT_CHARGE AS notCharge
,consign.REASON AS reason
,consign.CUST_TYPE AS custType
,consign.ENVELOP_NUM AS envelopNum
,consign.TOTAL_NUM AS totalNum
,consign.IS_SHORTWAY AS isShortway
,consign.SHORTWAY_ID AS shortwayId
,consign.GATHER_DATE AS gatherDate
,consign.GATHER_MAN AS gatherMan
,consign.FACT_FEE AS factFee
,consign.CONSIGN_TYPE AS consignType
,consign.TEMPLATE_ID AS templateId
,consign.ARRIVE_INFORM AS arriveInform
,consign.DELIVERY_INFORM AS deliveryInform
,consign.CONSIGN_TIME AS consignTime
,consign.ARRIVE_TIME AS arriveTime
,consign.UPDATE_NUM AS updateNum
,consign.COMPANY_ID AS companyId
,consign.IS_DELETE AS isDelete
,consign.DELETE_MAN AS deleteMan
,consign.DELETE_TIME AS deleteTime
,consign.CREATE_TIME AS createTime
,consign.CREATE_MAN AS createMan
,consign.LST_UPDATE_TIME AS lstUpdateTime
,consign.LST_UPDATE_MAN AS lstUpdateMan
,consign.REL_ID AS relId
from CONSIGN consign
) pageStart where rownum <= 1000000
) pageEnd where pageEnd.rn > 9999990
当CONSIGN 表中有上百万条数据时，这种方法的查询效率明显降低，原因是再最内层的查询I/O次数过多，如果改成下面的写法，效率将会有显著的提高
select
consign.ID AS id
,consign.CONSIGN_CODE AS consignCode
,consign.ORDER_ID AS orderId
,consign.SHIPPER_CODE AS shipperCode
,consign.SHIPPER_NAME AS shipperName
,consign.SHIPPER_LINKMAN AS shipperLinkman
,consign.SHIPPER_MOBILE AS shipperMobile
,consign.SHIPPER_ADDRESS AS shipperAddress
,consign.SHIPPER_FAX AS shipperFax
,consign.SHIPPER_TEL AS shipperTel
,consign.CONSIGNEE_CODE AS consigneeCode
,consign.CONSIGNEE_NAME AS consigneeName
,consign.CONSIGNEE_LINKMAN AS consigneeLinkman
,consign.CONSIGNEE_MOBILE AS consigneeMobile
,consign.CONSIGNEE_ADDRESS AS consigneeAddress
,consign.CONSIGNEE_FAX AS consigneeFax
,consign.CONSIGNEE_TEL AS consigneeTel
,consign.REMARK AS remark
,consign.END_STATION AS endStation
,consign.BEGIN_STATION AS beginStation
,consign.DEDESTINATION AS dedestination
,consign.BAR_CODE AS barCode
,consign.MOSTLY_GOODS AS mostlyGoods
,consign.TRANSPORT_FEE AS transportFee
,consign.INSURANCE_ACCOUNTS AS insuranceAccounts
,consign.INSURANCE_FEE AS insuranceFee
,consign.DELIVER_COMM_FEE AS deliverCommFee
,consign.TRANSER_FEE AS transerFee
,consign.PACKGAE_FEE AS packgaeFee
,consign.HURRYING_FEE AS hurryingFee
,consign.BACK_HANDER AS backHander
,consign.REBATE AS rebate
,consign.CASH AS cash
,consign.ARREARAGE AS arrearage
,consign.PICKUP_ACCOUNTS AS pickupAccounts
,consign.PICKUP_GOODS_FEE AS pickupGoodsFee
,consign.UNLOAD_FEE AS unloadFee
,consign.TOTAL_ACCOUNTS AS totalAccounts
,consign.OTHER_FEE AS otherFee
,consign.ACCOUNTS_REC AS accountsRec
,consign.BALANCE_TYPE AS balanceType
,consign.ACCOUNTS_OF_GOODS AS accountsOfGoods
,consign.AGENT_FEE AS agentFee
,consign.AP_TRANSER_FEE AS apTranserFee
,consign.AP_DELIVER_FEE AS apDeliverFee
,consign.TRANSIT_ITEM AS transitItem
,consign.TRANSIT_MODE AS transitMode
,consign.TRANSFER_MODE AS transferMode
,consign.NOTE_RETURNED_TYPE AS noteReturnedType
,consign.PRIORITY AS priority
,consign.SALE_MAN AS saleMan
,consign.ACCEPT_ORGANIZE AS acceptOrganize
,consign.TRANSFER_SITE AS transferSite
,consign.SPEC_LINE_ID AS specLineId
,consign.NOT_CHARGE AS notCharge
,consign.REASON AS reason
,consign.CUST_TYPE AS custType
,consign.ENVELOP_NUM AS envelopNum
,consign.TOTAL_NUM AS totalNum
,consign.IS_SHORTWAY AS isShortway
,consign.SHORTWAY_ID AS shortwayId
,consign.GATHER_DATE AS gatherDate
,consign.GATHER_MAN AS gatherMan
,consign.FACT_FEE AS factFee
,consign.CONSIGN_TYPE AS consignType
,consign.TEMPLATE_ID AS templateId
,consign.ARRIVE_INFORM AS arriveInform
,consign.DELIVERY_INFORM AS deliveryInform
,consign.CONSIGN_TIME AS consignTime
,consign.ARRIVE_TIME AS arriveTime
,consign.UPDATE_NUM AS updateNum
,consign.COMPANY_ID AS companyId
,consign.IS_DELETE AS isDelete
,consign.DELETE_MAN AS deleteMan
,consign.DELETE_TIME AS deleteTime
,consign.CREATE_TIME AS createTime
,consign.CREATE_MAN AS createMan
,consign.LST_UPDATE_TIME AS lstUpdateTime
,consign.LST_UPDATE_MAN AS lstUpdateMan
,consign.REL_ID AS relId
from (
select * from (
select pageStart.*,rownum as rn from (
select
ID
from CONSIGN
) pageStart where rownum <= 1000000
) pageEnd where pageEnd.rn > 9999990
) temp , CONSIGN consign
where temp.ID = consign.ID
原因是在内查询中减少I/O次数，而在外查询中做一次关联，取得所有数据，因为关联条件是用主键，所以命中率100%，会达到很好的效果
分享到：
相关推荐
3. **报表生成**：生成复杂的报表时，往往需要从多个表中抽取数据并进行复杂的联接操作，从而构建出超长SQL语句。 4. **历史数据分析**：在分析历史数据时，为了确保数据准确性，可能会涉及到大量的JOIN操作以及复杂...
SQL 语句书写与性能调优规范是数据库管理系统中至关重要的一环，特别是在处理大量数据时。随着数据库中数据量的不断增长，系统的响应速度和性能成为衡量系统优劣的关键因素。SQL 语句的优化不仅关乎系统可用性，还...
C#具有丰富的类库和强大的.NET框架支持，可以与数据库进行交互，但编写SQL语句的过程相对繁琐，尤其是在处理大量数据操作时。 "SQL语句辅助工具"通过自动化这个过程，大大提升了开发效率。它能够解析SQL语句，并...
SQL 语句规范是数据库性能优化的重要一环，以下是 SQL 语句书写和建表规范的相关知识点： 1. Select 语句规范 * 避免使用通配符，选择需要的列而不是所有列 * 在可能的情况下，限制结果集的行数，以减少网络负担 *...
10. **考虑分批处理**：对于大量数据的操作，如批量更新或删除，可以考虑分批处理，以减小对系统资源的影响。 11. **监控和分析SQL执行计划**：通过数据库提供的工具（如Oracle的EXPLAIN PLAN）分析SQL的执行计划，...
例如，当数据集中大量记录具有相同的值时，>操作符的效率可能低于>=。 **推荐方案**：根据数据分布特性，选择适当的比较操作符，以最小化索引查找成本。 #### LIKE操作符：通配符的双刃剑 LIKE操作符提供强大的...
为了书写高性能的SQL语句，我们应当遵循一系列的优化原则和技术。首先，理解高性能SQL语句的重要性是优化的第一步。一篇关于如何优化SQL语句的文章中，作者孙雪燕和耧雪松分析了造成数据库性能问题的常见原因，并就...
由于在生成SQL语句之前需要确保数组是空的，因此需要有一个过程来清空数组元素，避免在生成SQL语句时出现多余的数据。 2. 构造SQL语句生成函数（如strMakeSQL）。该函数的实现细节需要根据具体的应用需求来设计。它...
书写高质量、高性能的SQL语句对于数据库的效率至关重要。以下是一些关键的建议，可以帮助你提升SQL编写技巧，优化查询性能： 1. **了解你的数据**：在编写SQL之前，深入理解表结构、字段类型以及数据分布是基础。这...
而当子查询返回大量数据时，`EXISTS`通常更有效。对于`NOT IN`和`NOT EXISTS`，由于`NOT IN`无法利用索引，`NOT EXISTS`通常在任何情况下都更快。 5. **避免使用非操作符**：像`NOT`, `!=`, `<>`等非操作符无法利用...
这在处理大量数据或高并发系统时尤其重要。 3. 正确使用数据库提示（HINT）。数据库提示可以指导优化器如何执行SQL语句。例如，/*+FULL(table_name)*/提示Oracle以全表扫描的方式读取数据；/*+INDEX(table_name ...
Oracle SQL语句优化是提升数据库性能的关键环节，尤其是在处理大量数据时。本文主要从两个方面分析了SQL语句优化的技术：正确使用索引和合理安排SQL语句的书写方式。 1. 正确使用索引 - 索引是提高查询效率的有效...
SQL语句优化是数据库管理...通过以上优化策略，我们可以改进SQL查询的性能，尤其在处理大量数据时，效果更为显著。优化SQL语句是数据库管理员和开发人员必备的技能，能够有效提升系统响应速度，降低数据库的维护成本。
- **UNION ALL**：同样用于合并结果集，但不会去除重复行，因此在处理大量数据时可能会更快。 ### 7. SQL语句的书写规范与执行效率 - **规范化书写**：遵循一定的命名规则和书写风格可以提高代码的可读性和可维护...
### 四、书写高效率SQL语句的建议 1. **避免全表扫描**：尽可能使用索引来减少数据扫描。 2. **选择合适的连接方式**：根据表大小和数据分布选择JOIN类型。 3. **使用索引覆盖**：确保查询只涉及索引列，避免回表。...
Oracle在执行SQL语句时，会进行一系列内部操作，如SQL语句解析、索引利用评估、变量绑定、数据块读取等。为了提高效率，应尽量减少这些操作的次数。例如，可以通过合并多个简单的查询语句成为一个复杂的查询来实现这...
4. SQL 语句的缩进风格，一行有多列，超过 80 个字符时，基于列对齐原则，采用下行缩进。 5. 多表连接时，使用表的别名来引用列。 6. 供别的文件或函数调用的函数，绝不应使用全局变量交换数据。 7. 变量命名不能...
SQL语句优化是数据库性能提升的关键环节，尤其是在大型企业级应用中，高效的SQL查询能够显著减少资源消耗，提高系统响应速度。以下是对标题和描述中所提及的SQL语句优化技术的详细分析： 1. **操作符优化**： - **...
写出高质量、高性能的SQL语句对于数据库的效率至关重要。以下是对"书写高质量SQL的30条建议"的详细解读： 1. **理解SQL基础**：熟悉SQL的基本语法，包括SELECT、INSERT、UPDATE、DELETE等操作，以及JOIN、WHERE、...