Database Reference
In-Depth Information
WHERE s.cust_id = c.cust_id
AND s.time_id = t.time_id
GROUP BY c.cust_state_province, t.fiscal_month_name
ORDER BY c.cust_state_province, sum(s.amount_sold) DESC
--------------------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | HASH GROUP BY | |
|* 3 | HASH JOIN | |
| 4 | TABLE ACCESS FULL | TIMES |
|* 5 | HASH JOIN | |
| 6 | TABLE ACCESS FULL | CUSTOMERS |
| 7 | VIEW | |
| 8 | NESTED LOOPS | |
| 9 | VIEW | VW_NSO_1 |
| 10 | HASH UNIQUE | |
|* 11 | HASH JOIN | |
| 12 | VIEW | |
| 13 | NESTED LOOPS | |
| 14 | TABLE ACCESS BY INDEX ROWID| PRODUCTS |
|* 15 | INDEX RANGE SCAN | PRODUCTS_PROD_SUBCAT_IX |
|* 16 | INDEX RANGE SCAN | SALES_PROD_BIX |
| 17 | VIEW | |
| 18 | NESTED LOOPS | |
|* 19 | TABLE ACCESS FULL | CUSTOMERS |
|* 20 | INDEX RANGE SCAN | SALES_CUST_BIX |
| 21 | TABLE ACCESS BY USER ROWID | SALES |
--------------------------------------------------------------------------
3 - access("S"."TIME_ID"="T"."TIME_ID")
5 - access("S"."CUST_ID"="C"."CUST_ID")
11 - access("C"."RID"="P"."RID")
15 - access("P"."PROD_SUBCATEGORY"='Cameras')
16 - access("P"."PROD_ID"="S"."PROD_ID")
19 - filter("C"."CUST_YEAR_OF_BIRTH">=1970 AND
"C"."CUST_YEAR_OF_BIRTH"<=1979)
20 - access("C"."CUST_ID"="S"."CUST_ID")
On to Chapter 15
This chapter describes two main subjects related to joins. First, it covers the methods used by the database engine to
perform joins (nested loops joins, merge joins, and hash joins) and talks about when it makes sense to use each of
them. Second, it covers some optimization techniques that the query optimizer applies to improve the performance.
Now that I've discussed the basic access path and join methods, it's time to look at advanced optimization
techniques. In the next chapter, I discuss materialized views, result caching, parallel processing, and direct-path
inserts. All of these features aren't used that often, but when correctly applied, they can greatly improve performance.
