两个思考题，SQL优化的2个常识

SQL1：

CDMBAS.BAS_PAPPVACTINFO表的PA_REQUNO ， PA_ORDER 两个字段上存在复合索引，但是下面SQL却使用了全表扫描，即使使用了index的hint也不行。想想可能是什么原因？（3300万记录的表，全表扫描执行一次19秒；问题解决后使用索引只需1.7毫秒）：

SELECT B2.* FROM

( SELECT B1.*, ROWNUM rnum

FROM ( SELECT

/*+ index(t1 IDX_BAS_PAPPVACTINFO_0001) */

T1.PA_CLINAME AS T1__PA_CLINAME ,

......

T1.PA_OPTIME AS T1__PA_OPTIME

FROM CDMBAS.BAS_PAPPVACTINFO T1

WHERE 1=1

ORDER BY PA_REQUNO DESC , PA_ORDER DESC

) B1

WHERE ROWNUM <= 21

) B2

WHERE rnum > 0 ;

SQL2：

下面SQL从写法上看看该如何优化一下？

SELECT *

FROM (

SELECT oracle_table.* ,ROWNUM AS startrownum

FROM (

SELECT ......

FROM mocha_oa_intramsg_message t1

INNER JOIN (

SELECT C_MESSAGE_ID,max(C_ID) AS C_ID

FROM mocha_oa_intramsg_message

GROUP BY C_MESSAGE_ID

) t2

ON t1.C_ID = t2.C_ID

WHERE (C_DEL_STATUS = 0 OR C_DEL_STATUS = 1) AND

C_FROM_ID = : 1 AND : 2 <= C_SEND_TIME AND : 3 >= C_SEND_TIME

ORDER BY C_SEND_TIME DESC

) oracle_table

WHERE ROWNUM <= 14

)

WHERE startrownum > 0;