Tuning Rows-to-Cols Query

Question

The following query (V_TITRATION_RESULTS) is a view uses a row-to-column pivot which returns about 20,000 rows:

SELECT test.created_on as "Created On", 
       r_titr as "Titrator", 
       r_fact as "Factor" 
  FROM (SELECT test_id, 
               MAX(CASE WHEN result_tmpl_id = 2484 THEN result END) r_titr, 
               MAX(CASE WHEN result_tmpl_id = 2483 THEN result END) r_fact 
          FROM (SELECT lims.test.* 
                  FROM lims.test 
                 WHERE test_tmpl_id = 867) 
          JOIN lims.result USING (test_id) 
      GROUP BY test_id) 
  JOIN lims.test test USING (test_id)

I would like to search on the view returning only the tests since the beginning of September:

SELECT * FROM V_TITRATION_RESULTS WHERE "Created On" > DATE '2009-09-01'

The 'GET PLANs' for both the view and filtered query are identical, and the trace statistics (below) for both queries are similar indicating the rows aren't filtered until they all have been processed.

                   VIEW     Filtered      Diff
Physical Reads    81730        83946      2216
Logical Reads    364488       344063    -20425
Sort Rows        632194       632193        -1
ROWID Gets       580778       580778         0
Chained Gets     101823       101823         0
Memory (kB)         307          324        17
Scan Rows             3            3         0
Scan Gets             3            3         0
Sorts In Mem          4            4         0
Temp Segments         1            1         0
Scan Short            3            3         0
CPU Total (sec)    8.13          7.3     -0.83
First Row        2m 12s       2m 40s    
Last Row            18s           0s

How can I rewrite my view so that a WHERE condition will filter the tests before the row-to-column pivot?

Answer 1

This is a bit out of my territory, but please look into using bind variables instead of literals within your SELECT statement as a means to improve performance.

See http://www.akadia.com/services/ora%5Fbind%5Fvariables.html and http://www.dba-oracle.com/concepts/views.htm. Specifically, the sections of predicate pushing might be of interest to you.

Answer 2

Steven, I'm wondering why you can't use:

CREATE OR REPLACE VIEW V_TITRATION_RESULTS AS 
  SELECT lt.test_id, 
         lt.created_on,
         MAX(CASE WHEN result_tmpl_id = 2484 THEN result END) 'titrator', 
         MAX(CASE WHEN result_tmpl_id = 2483 THEN result END) 'factor'
    FROM lims.test lt 
    JOIN lims.result USING (test_id) 
   WHERE lt.test_tmpl_id = 867 
GROUP BY lt.test_id, lt.created_on

Getting rid of the subselects will improve query performance. Assuming Oracle 9i+ - mind that you can use Subquery Factoring (WITH clause) in views.

Then you could use:

SELECT vtr.* 
  FROM V_TITRATION_RESULTS vtr
 WHERE vtr.created_on > TO_DATE('2009-09-01', 'YYYY-MM-DD')