《Pro Oracle SQL》Chapter10.2.3 Testing the Effects of Query Changes

Testing the Effects of Query Changes    测试查询改变的效果  (page 315)
    Even as data does not remain static, SQL is not always static. Sometimes requirements change, so code must be modified. What if the requirements changed for the examples in Listings 10-3 and 10-4? Would minor changes invalidate the use of the hints embedded in the SQL? This is probably something worth investigating, so let’s do so.
    正如数据不会保持静态，SQL也不总是静态。 有时候需求改变，因此代码需要修改。如果需求改变对于列表10-3和10-4中例子的会怎样？是否最小的改变使得嵌入SQL中的提示失效？这可能是值得调查的，因此让我们研究一下。
      Previously, you were reporting on income bracket s when the count of them for any country was
greater than or equal to 25% of the total global count for that bracket. Now you are asked to include an
income bracket if it is among those income brackets the number of which is greater than the median,
based on the number of customers per bracket. This SQL is seen in Listing 10-6. Notice that the INLINE
hint has been left in. So now there’s an additional full table scan and index scan as compared to the
execution plan in Listing 10-4. While the elapsed time has increased, it still seems reasonable.
    之前，你报告了任意国家的 收入阶层， 他们的数量大于或等于全球那个阶层数量的25%。现在你要求包含一个收入阶层，如果它位列那些数量大于中位数的收入阶层，基于每桶的顾客数。 SQL如列表 10-6所示。注意INLINE提示留下了。因此，相比于列表10-4的执行计划，现在有一个额外的全表扫描和索引扫描，虽然消耗时间增加，它似乎还是合理的。
    Now that there’s an additional table scan and index scan, how do you think the performance of this
query will fare if temporary table transformations are allowed to take place? The results can be seen in
Listing 10-7.
    既然这里有了额外的表扫描和索引扫描，你如何认为这个查询的性能是公平的，即使临时表变换运行发生？结果可从列表10-7中看出。
    Because there’s that additional scan taking place in the modified version of the query, the overhead
of logical IO becomes more apparent. It is significantly more efficient with this query to allow Oracle to
perform table transformations, writing the results of the hash join to a temporary table on disk where
they can be reused throughout the query.
    因为在修改版的查询中有额外的扫描发生，逻辑IO的开销变得更明显。这个查询显然较为有效率，因为允许Oracle执行表变换，把哈希连接的结果写入磁盘的临时表，可贯穿于整个查询重用。

Listing 10-6. Modified Income Search - INLINE
1     with cust as (
2     select /*+ inline gather_plan_statistics */
3         b.cust_income_level,
4         a.country_name
5     from sh.customers b
6     join sh.countries a on a.country_id = b.country_id
7     ),
8     median_income_set as (
9     select /*+ inline */ cust_income_level, count(*) income_level_count
10      from cust
11     group by cust_income_level
12     having count(cust_income_level) > (
13          select median(income_level_count) income_level_count
14            from (
15                    select cust_income_level, count(*) income_level_count
16                      from cust
17                     group by cust_income_level
18                 )
19          )
20     )
21     select country_name, cust_income_level, count(country_name) country_cust_count
22       from cust c
23     having count(country_name) >
24     (
25         select count(*) * .01
26           from cust c2
27      )
28     or cust_income_level in ( select mis.cust_income_level from median_income_set mis)
29     group by country_name, cust_income_level;
COUNTRY                       INCOME LEVEL        COUNT
------------------------------   --------------------          --------
Argentina                        D: 70,000 - 89,999      25
Argentina                        E: 90,000 - 109,999    39
...
United States of America K: 250,000 - 299,999  1062
United States of America L: 300,000 and above  982
123 rows selected.
Elapsed: 00:00:01.26
Statistics
----------------------------------------------------------
    1524 recursive calls
         0 db block gets
  23362 consistent gets
    1486 physical reads
         0 redo size
 15570 bytes sent via SQL*Net to client
   1195 bytes received via SQL*Net from client
       63 SQL*Net roundtrips to/from client
         3 sorts (memory)
         0 sorts (disk)
     123 rows processed
---------------------------------------------------------------------------------
| Id  | Operation                               | Name                     |Starts    |E-Rows    |A-Rows     | A-Time            |
---------------------------------------------------------------------------------
| 0  | SELECT STATEMENT               |                               | 1          |                | 123          |00:00:00.37     |
|* 1 |   FILTER                                |                                | 1          |                | 123          |00:00:00.37    |
| 2  |     SORT GROUP BY                |                                | 1          | 20           | 236          |00:00:00.08    |
|* 3 |       HASH JOIN                      |                                | 1          | 55500      | 55500      |00:00:00.38    |
| 4  |         TABLE ACCESS FULL    | COUNTRIES            | 1          | 23           | 23            |00:00:00.01    |
| 5  |         TABLE ACCESS FULL    | CUSTOMERS          | 1          | 55500      | 55500      |00:00:00.08    |
| 6  |    SORT AGGREGATE           |                                | 1          | 1              | 1             |00:00:00.04    |
|* 7 |      HASH JOIN                       |                                | 1           | 55500      | 55500      |00:00:00.43    |
| 8  |         INDEX FULL SCAN         | COUNTRIES_PK      | 1         | 23            | 23            |00:00:00.01    |
| 9  |         TABLE ACCESS FULL    | CUSTOMERS          | 1         | 55500       | 55500       |00:00:00.10    |
|* 10 |    FILTER                             |                                | 13       |                 | 6              |00:00:00.65    |
| 11  |      HASH GROUP BY           |                                 | 13       | 1               | 133          |00:00:00.59    |
|* 12 |        HASH JOIN                   |                                | 13        | 55500       | 721K        |00:00:05.18    |
| 13 |            INDEX FULL SCAN      | COUNTRIES_PK    | 13        | 23           | 299            |00:00:00.01|
| 14 |            TABLE ACCESS FULL | CUSTOMERS         | 13       | 55500       | 721K          |00:00:01.10|
| 15 |       SORT GROUP BY            |                               | 1          | 1              |  1               |00:00:00.06|
| 16 |         VIEW                             |                                | 1         | 12            | 13              |00:00:00.06|
| 17 |          SORT GROUP BY         |                               | 1          | 12            | 13              |00:00:00.06|
|* 18 |           HASH JOIN                  |                               | 1           | 55500      | 55500        |00:00:00.42|
| 19  |              INDEX FULL SCAN    | COUNTRIES_PK     | 1           | 23           | 23              |00:00:00.01|
| 20  |             TABLE ACCESS FULL | CUSTOMERS       | 1          | 55500      | 55500         |00:00:00.08|
---------------------------------------------------------------------------------

Listing 10-7. Modified Income Search - MATERIALIZE
1     with cust as (
2             select /*+ materialize gather_plan_statistics */
3                       b.cust_income_level,
4                       a.country_name
5              from sh.customers b
6               join sh.countries a on a.country_id = b.country_id
7          ),
...
CUSTOMER
COUNTRY                       INCOME LEVEL          COUNT
------------------------------    -------------------------     --------
Argentina                         D: 70,000 - 89,999       25
Argentina                         E: 90,000 - 109,999     39
...
United States of America  K: 250,000 - 299,999   1062
United States of America  L: 300,000 and above   982
123 rows selected.
Elapsed: 00:00:00.87
Statistics
----------------------------------------------------------
              2001 recursive calls
               324 db block gets
              3221 consistent gets
              1822 physical reads
1244 redo size
15570 bytes sent via SQL*Net to client
1195 bytes received via SQL*Net from client
63 SQL*Net roundtrips to/from client
38 sorts (memory)
0 sorts (disk)
123 rows processed
-------------------------------------------------------------------------------
| Id  |Operation                                         |Name                |Starts        |E-Rows       |A-Rows       | A-Time             |
-------------------------------------------------------------------------------
| 0  |SELECT STATEMENT                        |                        | 1            |                   | 123            |00:00:00.54      |
| 1  |   TEMP TABLE TRANSFORMATION  |                        | 1            |                   | 123            |00:00:00.54      |
| 2  |      LOAD AS SELECT                       |                        | 1            |                   | 0                |00:00:00.37      |
|* 3 |        HASH JOIN                                |                       | 1            | 55500        | 55500         |00:00:03.21     |
| 4   |          TABLE ACCESS FULL             | COUNTRIES     | 1           | 23             | 23             |00:00:00.01   |
| 5   |          TABLE ACCESS FULL             | CUSTOMERS   | 1           | 55500        | 55500       |00:00:02.91   |
| 6   |        LOAD AS SELECT                     |                        | 1           |                   | 0              |00:00:00.10   |
|* 7  |          FILTER                                   |                         | 1           |                  | 6               |00:00:00.09    |
| 8   |             HASH GROUP BY                |                         | 1           | 1                | 13              |00:00:00.06    |
| 9   |               VIEW                                |                          | 1           |   55500      | 55500        |00:00:00.24     |
| 10  |                TABLE ACCESS FULL     | SYS_TEMP_0F  | 1           | 55500         | 55500       |00:00:00.11     |
| 11  |          SORT GROUP BY                 |                          | 1            | 1               | 1                |00:00:00.03    |
| 12  |             VIEW                                 |                          | 1            |12              |   13            |00:00:00.03|
| 13 |              SORT GROUP BY              |                          | 1            | 12             | 13              |00:00:00.03     |
| 14|                  VIEW                              |                          | 1            | 55500        | 55500        |00:00:00.21    |
| 15 |                 TABLE ACCESS FULL     |SYS_TEMP_0F   | 1            | 55500        | 55500        |00:00:00.07    |
|* 16|     FILTER                                       |                          | 1            |                   | 123           |00:00:00.06    |
| 17 |       SORT GROUP BY                     |                          | 1            | 20              | 236            |00:00:00.05|
| 18 |          VIEW                                     |                          | 1            | 55500         | 55500        |00:00:00.21|
| 19 |            TABLE ACCESS FULL          |SYS_TEMP_0F   | 1            | 55500         | 55500        |00:00:00.07|
| 20 |       SORT AGGREGATE                  |                         | 1             | 1                | 1                |00:00:00.01|
| 21 |         VIEW                                       |                         | 1             | 55500        | 55500         |00:00:00.21|
| 22 |           TABLE ACCESS FULL            |SYS_TEMP_0F  | 1             | 55500        | 55500         |00:00:00.07|
|* 23|       VIEW                                        |                          | 13           | 1               | 6                 |00:00:00.01|
| 24 |         TABLE ACCESS FULL             |SYS_TEMP_0F   | 13           | 1               | 63                |00:00:00.01|
-------------------------------------------------------------------------------