[SOLVED] Speed up select statement

[stockton]

I have the following Oracle SQL statement which takes quite a while to execute.

SELECT * FROM (

                SELECT TRANS_CODE, Sum(TRANS_CASH_IN) AS SumOfTRANS_CASH_IN

                    FROM TRANSACT

                        WHERE TO_CHAR(TRANS_DATE,'MM')= TO_CHAR(sysdate,'MM')

                        AND ((TRANS_COMMENT) LIKE 'Dacom%')

                        AND ((TRANS_MACHINE_NO) NOT LIKE '7%')

                    GROUP BY TRANS_CODE

                    ORDER BY Sum(TRANS_CASH_IN) DESC;

                ) WHERE rownum <= 250;

 

Please make suggestions on how it could be made quicker.

[gizmola]

Did you do an explain plan and look at that first?

[Y.O.]

A simple way to speed it up is using hint:

    add hint comment /*+ALL_ROWS*/ after each SELECT

 

[phporaclehosting]

i agree about the explain plan, and look at where you could stick an index, or two. remember you can have binary or even three way indexes, but these add complexity and size.