在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%，会达到很好的效果
