Retrieving logs takes forever
Posted on 2013-01-03
Takes forever to pull logs.
The logging is based on msg_id, msg_ts, msg fields
It takes forever to retrieve logs from this table.
Explain plan suggests it does a full table scan on mylog table which contain log entries since Jul-2012, having 500,000 entries.
There are no partitions or index available on the table.
It is not possible to add partitions now as all programs in my project will have to be modified so that the partition key is also sent by mypkg.write_log(v_msg,v_date) everwhere.
Index on msg filed does not seem to be a proper way since there is no key field in it just text.
How can i pull the logs faster, what are my alternatives.