《Pro Oracle SQL》Chapter10.2.3 Testing the Effects of Query ChangesITeye - 亚美娱乐

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

2019-01-12 10:52:04 | 作者: 灵珊 | 标签: 查询,扫描,列表 | 浏览: 1226

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

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表亚美娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章