When All Else Failed07 Dec 2006
Let's say we have an unacceptably slow SQL that cannot be optimized further (usually when we have a crappy database design, but updating the design is not an option).
First make a view out of the SQL:
CREATE OR REPLACE VIEW vq_slow_query AS
Then create table in the exactly same structure as the view, e.g.
CREATE TABLE tq_slow_query
Now, prepare a stored procedure to be called by database job, e.g. (Oracle PL/SQL):
CREATE OR REPLACE PROCEDURE pq_slow_query AS
-- delete previously queried stuff before inserting
DELETE FROM tq_slow_query;
INSERT INTO tq_slow_query SELECT * FROM vq_slow_query;
And finally, run a database job to update the query table periodically (Oracle PL/SQL):
-- run every 1 hour, starting at the next hour
dbms_job.submit(l_job, 'pq_slow_query;', sysdate+1/24, 'sysdate+1/24');
Here's how to check and stop the job in Oracle:
-- checking active jobs status
-- removing a job by id
Of course the data queried would not be up-to-date, up-to-the-hour, or up-to-the-five-minutes, or whatever the period is, but if whoever ask me to optimize an unoptimizable query, I would ask them to go and optimass it themselves. Oh, I'm such an over-confident SQL optimizer. Whatever.