パフォーマンスチューニングの勘所 のSQLと結果のサンプル

join_sample.sql

SELECT p.prod_category,p.prod_id,p.prod_name,
COUNT(s.quantity_sold),SUM(s.amount_sold)
FROM products p
JOIN sales s
ON p.prod_id = s.prod_id --products テーブル と sales テーブル を prod_id でJOIN
group by p.prod_category,p.prod_id,p.prod_name
ORDER BY p.prod_category,p.prod_id;

join_sample2.sql

SELECT pr.promo_category,pr.promo_subcategory,pr.promo_name, -- pr.promo
p.prod_category,p.prod_id,p.prod_name,
COUNT(s.quantity_sold),SUM(s.amount_sold)
FROM products p
JOIN sales s
ON p.prod_id = s.prod_id
JOIN promotions pr
ON pr.promo_id = s.promo_id
group by pr.promo_category,pr.promo_subcategory,pr.promo_name,p.prod_category,p.prod_id,p.prod_name
ORDER BY pr.promo_category,pr.promo_subcategory,pr.promo_name,p.prod_category,p.prod_id;

prod_table.md

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |    72 |  5544 |   537   (6)| 00:00:01 |       |       |
|   1 |  SORT GROUP BY                |             |    72 |  5544 |   537   (6)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                |             |       |       |            |          |       |       |
|   3 |    NESTED LOOPS               |             |    72 |  5544 |   537   (6)| 00:00:01 |       |       |
|   4 |     VIEW                      | VW_GBC_5    |    72 |  2160 |   537   (6)| 00:00:01 |       |       |
|   5 |      HASH GROUP BY            |             |    72 |   648 |   537   (6)| 00:00:01 |       |       |
|   6 |       PARTITION RANGE ALL     |             |   918K|  8075K|   516   (2)| 00:00:01 |      1|    28 |
|   7 |        TABLE ACCESS FULL      | SALES       |   918K|  8075K|   516   (2)| 00:00:01 |       |    28 |
|*  8 |     INDEX UNIQUE SCAN         | PRODUCTS_PK |     1 |       |     0   (0)| 00:00:01 |       |       |
|   9 |    TABLE ACCESS BY INDEX ROWID| PRODUCTS    |     1 |    47 |     0   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------

promo_table.md

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            | 36148 |  4589K|       |  6562   (1)| 00:00:01 |       |       |
|   1 |  SORT GROUP BY                |            | 36148 |  4589K|  5176K|  6562   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                |            |       |       |       |            |          |       |       |
|   3 |    NESTED LOOPS               |            | 36148 |  4589K|       |  5515   (1)| 00:00:01 |       |       |
|   4 |     VIEW                      | VW_GBC_9   | 36148 |  2718K|       |  5514   (1)| 00:00:01 |       |       |
|   5 |      HASH GROUP BY            |            | 36148 |  2118K|    63M|  5514   (1)| 00:00:01 |       |       |
|*  6 |       HASH JOIN               |            |   918K|    52M|       |   522   (2)| 00:00:01 |       |       |
|   7 |        TABLE ACCESS FULL      | PRODUCTS   |    72 |  3384 |       |     3   (0)| 00:00:01 |       |       |
|   8 |        PARTITION RANGE ALL    |            |   918K|    11M|       |   516   (2)| 00:00:01 |     1 |    28 |
|   9 |         TABLE ACCESS FULL     | SALES      |   918K|    11M|       |   516   (2)| 00:00:01 |     1 |    28 |
|* 10 |     INDEX UNIQUE SCAN         | PROMO_PK   |     1 |       |       |     0   (0)| 00:00:01 |       |       |
|  11 |    TABLE ACCESS BY INDEX ROWID| PROMOTIONS |     1 |    53 |       |     0   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------