2020-07-13-oracle 分页优化-使用rowid

SELECT
    acm.ROWID,
    abm.BATCH_NUM,
    abm.BATCH_DESC,
    ( SELECT he.name FROM happs.hr_employee he WHERE he.employee_id = acm.MANAGER_ID ) MANAGER,
    ( SELECT he.name FROM happs.hr_employee he WHERE he.employee_id = acm.USER_ID ) USER_NAME,
    acm.ASSET_CARD_ID,
    acm.BATCH_ID,
    acm.ASSET_ID,
    acm.ASSET_NUMBER,
    acm.ASSET_NUMBER_DESC,
    acm.ORGANIZATION_NAME,
    acm.CATEGORY_NAME,
    acm.CATEGORY_DESCRIPTION,
    acm.ASSET_TYPE,
    acm.UNIT,
    acm.PARENT_ASSET_NUMBER,
    acm.PARENT_ASSET_NUMBER_DESC,
    acm.SUPPLIER_WARRANTY_EXP_DATE,
    acm.LOCATION_DESC,
    acm.OWNING_DEPARTMENT_DESC,
    acm.ASSET_CRITICALITY,
    acm.FA_ASSET_NUMBER,
    acm.FA_ASSET_CATEGORY,
    acm.MANAGEMENT_DEPT_DESC,
    acm.EAM_SOURCE,
    acm.MANUFACTURER,
    acm.SERIAL_NUMBER,
    acm.SPE_MODEL,
    acm.SCOPE_OF_USE,
    acm.CAR_BOTTOM_NUMBER,
    acm.MANUFACTURE_DATE,
    acm.PURCHASE_DATE,
    acm.USE_DATE,
    acm.SOURCE_ACCEPTANCE_ORDER,
    acm.UNIT_PRICE,
    acm.LICENSE_NUMBER,
    acm.WHETHER_PRODUCTIVE,
    acm.PURCHASING_DEPT,
    acm.ASSET_MANAGEMENT_DEPT,
    acm.INSTANCE_STATUS,
    acm.STATUS,
    acm.ASSET_GROUP_NUM,
    acm.MANAGER_ID,
    acm.USER_ID,
    acm.EAM_SOURCE_CODE,
    acm.USABLE,
    acm.INSTANCE_STATUS_CODE,
    acm.LOCATION_CODE,
    acm.MANAGE_DEPT_ID,
    acm.USER_DEPT_ID,
    acm.USER_SUB_DEPT_ID,
    acm.OWNING_SUB_DEPARTMENT_DESC,
    acm.OBJECT_VERSION_NUMBER 
FROM
    EAM_ASSET_CARD_MANAGEMENT acm
    LEFT JOIN EAM_ASSET_BATCH_MANAGEMENT abm ON acm.BATCH_ID = abm.BATCH_ID
    LEFT JOIN APPS.csi_item_instances cii ON cii.instance_id = acm.asset_Id
    LEFT JOIN APPS.okc_rep_contracts_all okc ON okc.CONTRACT_ID = CII.ATTRIBUTE13
    LEFT JOIN APPS.po_headers_all poh ON POH.PO_HEADER_ID = CII.ATTRIBUTE14 
WHERE
    1 = 1 
    AND acm.ROWID IN (
    SELECT
        row_id 
    FROM
        (
        SELECT
            tmp_page.row_id,
            ROWNUM rn 
        FROM
            (
            SELECT
                acm.ROWID row_id
            FROM
                EAM_ASSET_CARD_MANAGEMENT acm
                LEFT JOIN EAM_ASSET_BATCH_MANAGEMENT abm ON acm.BATCH_ID = abm.BATCH_ID
                LEFT JOIN APPS.csi_item_instances cii ON cii.instance_id = acm.asset_Id
                LEFT JOIN APPS.okc_rep_contracts_all okc ON okc.CONTRACT_ID = CII.ATTRIBUTE13
                LEFT JOIN APPS.po_headers_all poh ON POH.PO_HEADER_ID = CII.ATTRIBUTE14 
            WHERE
                1 = 1 
            ) tmp_page 
        WHERE
            ROWNUM <= 349450 
        ) 
    WHERE
    rn > 308440 
    )