When all attempts to optimize a query failed to improve the performance, I would resort to a somewhat less elegant but almost always practical and workable trick: use a static table to hold query result, and query that static table, instead of directly querying underlying tables. Combined with scheduled database job, this solution can have a dramatic impact on system responsiveness.

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).

SELECT
some_crap
FROM
very_slow_and_smelly_stuff;


First make a view out of the SQL:

CREATE OR REPLACE VIEW vq_slow_query AS
SELECT
some_crap
FROM
very_slow_and_smelly_stuff;


Then create table in the exactly same structure as the view, e.g.

CREATE TABLE tq_slow_query
(
some_crap number(10)
);


Now, prepare a stored procedure to be called by database job, e.g. (Oracle PL/SQL):

CREATE OR REPLACE PROCEDURE pq_slow_query AS
BEGIN
-- delete previously queried stuff before inserting
DELETE FROM tq_slow_query;
INSERT INTO tq_slow_query SELECT * FROM vq_slow_query;
commit;
END;
/


And finally, run a database job to update the query table periodically (Oracle PL/SQL):

DECLARE
l_job number;
BEGIN
-- run every 1 hour, starting at the next hour
dbms_job.submit(l_job, 'pq_slow_query;', sysdate+1/24, 'sysdate+1/24');
end;
/


Here's how to check and stop the job in Oracle:

-- checking active jobs status

SELECT
job,
next_date,
next_sec,
failures,
broken,
SUBSTR(what,1,40) description
FROM user_jobs;

-- removing a job by id

exec dbms_job.remove(21);


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.