SQL & PL/SQL

Hi,

I have the below query which is doing FTS and is very expensive causing load to timeout.

I did my analysis and found that table is having large number of records and hence FTS is taking long time causing timeout from app side.

I proposed to have this table partitioned but this is still pending with business and they in meantime want some solution other solution to fix this issue.

below is the query and plan

SELECT TRANSACTION_LOG.ID, TRANSACTION_LOG.USER_IDENTIFIER, TRANSACTION_LOG.START_TIME, TRANSACTION_LOG.END_TIME, TRANSACTION_LOG.REQUEST, TRANSACTION_LOG.RESPONSE
FROM
WP61PD1COLTRDB.TRANSACTION_LOG
WHERE TRANSACTION_LOG.END_TIME > TO_DATE('07/26/2012 10:16:41','MM/DD/YYYY hh24:mi:ss')

Plan hash value: 2462480644

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 5360 | 1480K (1)| 04:56:09 |
|* 1 | TABLE ACCESS FULL| TRANSACTION_LOG | 5 | 5360 | 1480K (1)| 04:56:09 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("TRANSACTION_LOG"."END_TIME">TIMESTAMP' 2012-07-26 10:16:41')


Count of no. of records:

select count(*) from WP61PD1COLTRDB.TRANSACTION_LOG

COUNT(*)
--------
43537767


There is only one index on ID column of the table which is normal index.

desc WP61PD1COLTRDB.TRANSACTION_LOG
Name Null Type
----------------- -------- --------------
ID NOT NULL NUMBER(38)
USER_IDENTIFIER NOT NULL VARCHAR2(255)
CLIENT_IP_ADDRESS VARCHAR2(255)
START_TIME NOT NULL TIMESTAMP(6)
END_TIME NOT NULL TIMESTAMP(6)
REQUEST NOT NULL VARCHAR2(4000)
RESPONSE VARCHAR2(4000)

Is there any other way we can tune this query?

Thanks
JP