Database Reference
In-Depth Information
Elapsed: 00:00:
01.25
----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 |
| 1 |
RESULT CACHE
|
089x05gkvfuxq7wqg06u9z0zkb
| 1 | 81 |
| 2 | SORT GROUP BY | | 1 | 81 |
|* 3 | HASH JOIN | | 1 | 956 |
| 4 | TABLE ACCESS FULL | PRODUCTS | 1 | 72 |
| 5 | VIEW | VW_GBC_9 | 1 | 956 |
| 6 | HASH GROUP BY | | 1 | 956 |
|* 7 | HASH JOIN | | 1 | 918K|
| 8 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 |
| 9 | PARTITION RANGE ALL| | 1 | 918K|
| 10 | TABLE ACCESS FULL | SALES | 28 | 918K|
----------------------------------------------------------------------------------
3 - access("ITEM_1"="P"."PROD_ID")
7 - access("S"."CUST_ID"="C"."CUST_ID")
The first execution took 1.25 seconds. Notice that in the execution plan, the
RESULT CACHE
operation confirms
that the result cache was enabled for the query. However, the
Starts
column in the execution plan clearly shows that
all operations were executed at least once. The execution of all operations was necessary because this was the first
execution of the query, and consequently, the result cache didn't contain the result set yet.
The second execution is faster (0.16 seconds):
SQL> SELECT /*+
result_cache
*/
2 p.prod_category, c.country_id,
3 sum(s.quantity_sold) AS quantity_sold,
4 sum(s.amount_sold) AS amount_sold
5 FROM sales s, customers c, products p
6 WHERE s.cust_id = c.cust_id
7 AND s.prod_id = p.prod_id
8 GROUP BY p.prod_category, c.country_id
9 ORDER BY p.prod_category, c.country_id;
Elapsed: 00:00:
00.16
----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 |
| 1 |
RESULT CACHE
|
089x05gkvfuxq7wqg06u9z0zkb
| 1 | 81 |
| 2 | SORT GROUP BY | |
0
| 0 |
|* 3 | HASH JOIN | |
0
| 0 |
| 4 | TABLE ACCESS FULL | PRODUCTS |
0
| 0 |
| 5 | VIEW | VW_GBC_9 |
0
| 0 |
| 6 | HASH GROUP BY | |
0
| 0 |
|* 7 | HASH JOIN | |
0
| 0 |
