大量数据时，SQL语句的书写区别

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