调整行到行查询 [英] Tuning Rows-to-Cols Query

问题描述

以下查询(V_TITRATION_RESULTS)是一个使用行到列数据透视图的视图，该视图返回约20,000行:

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)

我想搜索自9月初以来仅返回测试的视图:

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    

如何重写我的视图，以使WHERE条件将在行到列枢轴之前过滤测试?

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

推荐答案

史蒂文，我想知道为什么您不能使用:

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

摆脱子选择将提高查询性能.假设使用Oracle 9i +，请注意，您可以使用子查询分解(WITH子句)在视图中.

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

然后您可以使用:

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

这篇关于调整行到行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！