db2_practice8.txt

Execution plans, hints
----------------------

The owner of the following tables is NIKOVITS.

PRODUCT(prod_id, name, color, weight)
SUPPLIER(supl_id, name, status, address)
PROJECT(proj_id, name, address)
SUPPLY(supl_id, prod_id, proj_id, amount, sDate)

The tables have indexes too.


------------------------------------------------------------
Exercise 1.
Query:
Give the sum amount of products where prod_id=2 and supl_id=2.

Give hints in order to use the following execution plans:
a) No index 
b) Two indexes and the intersection of ROWID-s (AND-EQUAL in plan).

------------------------------------------------------------
Exercise 2.
Query:
Give the sum amount of products where the color of product is 'piros' and address of supplier is 'Pecs'.

Give hints in order to use the following execution plans:
a) Join order should be: first supply and product tables then supplier table.
b) Join order should be: first supply and supplier tables then product table.

------------------------------------------------------------
Exercise 3.
Give a SELECT statement which has the following execution plan.


PLAN (OPERATION + OPTIONS + OBJECT_NAME)                                               
---------------------------------------- 
SELECT STATEMENT +  + 
  SORT + AGGREGATE + 
    TABLE ACCESS + FULL + PRODUCT


SELECT STATEMENT +  + 
  SORT + AGGREGATE + 
    TABLE ACCESS + BY INDEX ROWID + PRODUCT
      INDEX + UNIQUE SCAN + PROD_ID_IDX


SELECT STATEMENT +  + 
  SORT + AGGREGATE + 
    HASH JOIN +  + 
      TABLE ACCESS + FULL + PROJECT
      TABLE ACCESS + FULL + SUPPLY


SELECT STATEMENT +  + 
  HASH + GROUP BY + 
    HASH JOIN +  + 
      TABLE ACCESS + FULL + PROJECT
      TABLE ACCESS + FULL + SUPPLY


SELECT STATEMENT +  + 
  SORT + AGGREGATE + 
    MERGE JOIN +  + 
      SORT + JOIN + 
        TABLE ACCESS + BY INDEX ROWID BATCHED + PRODUCT
          INDEX + RANGE SCAN + PROD_COLOR_IDX
      SORT + JOIN + 
        TABLE ACCESS + FULL + SUPPLY


SELECT STATEMENT +  + 
  FILTER +  + 
    HASH + GROUP BY + 
      HASH JOIN +  + 
        TABLE ACCESS + FULL + PROJECT
        HASH JOIN +  + 
          TABLE ACCESS + FULL + SUPPLIER
          TABLE ACCESS + FULL + SUPPLY