Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --############################################################################################################
- --### Bestellungen aus Land x (shopping-cart: delivery_country)
- SELECT *
- FROM SHOPPING_CART WHERE DELIVERY_COUNTRY = 'USA' AND DELIVERY_POSTAL_CODE = 10573
- ORDER BY ORDER_TIME DESC;
- PLAN_TABLE_OUTPUT
- -------------------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TIME | Pstart| Pstop |
- -------------------------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 220 | 17820 | 201 (1)| 00:00:01 | | |
- | 1 | SORT ORDER BY | | 220 | 17820 | 201 (1)| 00:00:01 | | |
- | 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SHOPPING_CART | 220 | 17820 | 200 (0)| 00:00:01 | ROWID | ROWID |
- |* 3 | INDEX RANGE SCAN | DELIVERYCOUNTRY | 220 | | 1 (0)| 00:00:01 | | |
- -------------------------------------------------------------------------------------------------------------------------------
- Der INDEX RANGE Scan findet alle Ergebnisse zu der angegebenen Condition
- über den globalen INDEX (DELIVERY_COUNTRY & DELIVERY_POSTAL_CODE).
- --############################################################################################################
- --### Durchschnitts-Warenkorbgröße im letzten Monat
- SELECT TO_CHAR(AVG(COUNT(c.PRODUCT_ID)), '9999D0') AS AVG_ORDERSIZE
- FROM SHOPPING_CART s, CART_ITEM c
- WHERE s.CART_ID = c.CART_ID AND s.ORDER_TIME > sysdate-30
- GROUP BY s.CART_ID;
- PLAN_TABLE_OUTPUT
- -------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TIME | Pstart| Pstop |
- -------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 21 | 24345 (1)| 00:00:01 | | |
- | 1 | SORT AGGREGATE | | 1 | 21 | 24345 (1)| 00:00:01 | | |
- | 2 | HASH GROUP BY | | 1 | 21 | 24345 (1)| 00:00:01 | | |
- |* 3 | HASH JOIN | | 9476 | 194K| 24344 (1)| 00:00:01 | | |
- | 4 | PARTITION RANGE ITERATOR| | 932 | 14912 | 23690 (1)| 00:00:01 | KEY |1048575|
- |* 5 | TABLE ACCESS FULL | SHOPPING_CART | 932 | 14912 | 23690 (1)| 00:00:01 | KEY |1048575|
- | 6 | TABLE ACCESS FULL | CART_ITEM | 908K| 4436K| 652 (1)| 00:00:01 | | |
- -------------------------------------------------------------------------------------------------------------
- Die ShoppingCart-Tabelle wird nicht vollständig ausgelesen. Nur die relevanten Partitionen müssen aufgrund der
- order_time-Kondition berücksichtigt werden. Es wird ausgegangen, dass ShoppingCarts meist mit Angabe eines Zeitraumes
- ausgelesen werden.
- --############################################################################################################
- --### Verhältnis der gekauften lowfat-Produkten von PayPal-Käufen zu gesamt im letzten Monat
- SELECT TO_CHAR(lf*100/al, '9990D99')||'%' AS RATIO_LF
- FROM (
- SELECT COUNT(*) AS lf
- FROM SHOPPING_CART s, CART_ITEM c, PRODUCT p
- WHERE s.cart_id = c.cart_id AND c.product_id = p.product_id AND s.order_time > sysdate-30
- AND p.low_fat = 1
- ) , (
- SELECT COUNT(*) AS al
- FROM SHOPPING_CART s, CART_ITEM c, PRODUCT p
- WHERE s.cart_id = c.cart_id AND c.product_id = p.product_id AND s.order_time > sysdate-30
- );
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TIME | Pstart| Pstop |
- ------------------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 26 | 58109 (1)| 00:00:03 | | |
- | 1 | NESTED LOOPS | | 1 | 26 | 58109 (1)| 00:00:03 | | |
- | 2 | VIEW | | 1 | 13 | 33765 (1)| 00:00:02 | | |
- | 3 | SORT AGGREGATE | | 1 | 33 | | | | |
- | 4 | NESTED LOOPS | | 4709 | 151K| 33765 (1)| 00:00:02 | | |
- | 5 | NESTED LOOPS | | 9477 | 151K| 33765 (1)| 00:00:02 | | |
- |* 6 | HASH JOIN | | 9477 | 231K| 24344 (1)| 00:00:01 | | |
- | 7 | PARTITION RANGE ITERATOR | | 932 | 14912 | 23690 (1)| 00:00:01 | KEY |1048575|
- |* 8 | TABLE ACCESS FULL | SHOPPING_CART | 932 | 14912 | 23690 (1)| 00:00:01 | KEY |1048575|
- | 9 | TABLE ACCESS FULL | CART_ITEM | 908K| 7985K| 652 (1)| 00:00:01 | | |
- |* 10 | INDEX UNIQUE SCAN | PK_PRODUCT_ID | 1 | | 0 (0)| 00:00:01 | | |
- |* 11 | TABLE ACCESS BY GLOBAL INDEX ROWID| PRODUCT | 1 | 8 | 1 (0)| 00:00:01 | ROWID | ROWID |
- | 12 | VIEW | | 1 | 13 | 24344 (1)| 00:00:01 | | |
- | 13 | SORT AGGREGATE | | 1 | 21 | | | | |
- |* 14 | HASH JOIN | | 9477 | 194K| 24344 (1)| 00:00:01 | | |
- | 15 | PARTITION RANGE ITERATOR | | 932 | 14912 | 23690 (1)| 00:00:01 | KEY |1048575|
- |* 16 | TABLE ACCESS FULL | SHOPPING_CART | 932 | 14912 | 23690 (1)| 00:00:01 | KEY |1048575|
- | 17 | TABLE ACCESS FULL | CART_ITEM | 908K| 4436K| 652 (1)| 00:00:01 | | |
- ------------------------------------------------------------------------------------------------------------------------
- Die beiden "Count-Statements" sind ähnlich behandelt worden. Beide machen die Partitionen teilweise zu nutze.
- Genauer kann durch die order_time-Kondition die interessante PARTITION mit ShoppingCart-Daten ausgewählt werden.
- Um die "p.lowfat = 1"-Kondition zu berücksichtigen, verwendet das erste "Count-Statement" zusätzlich den
- Standard-INDEX (PRODUKT_ID).
- --############################################################################################################
- --### Übersicht Bestellungen mit Produktmenge
- SELECT s.ORDER_TIME, s.CART_ID, COUNT(c.PRODUCT_ID) AS PRODUCTS
- FROM SHOPPING_CART s, CART_ITEM c
- WHERE s.CART_ID = c.CART_ID AND s.ORDER_TIME > sysdate-30
- GROUP BY s.CART_ID, s.ORDER_TIME
- ORDER BY s.ORDER_TIME DESC;
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TIME | Pstart| Pstop |
- ------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 992 | 20832 | 24345 (1)| 00:00:01 | | |
- | 1 | SORT GROUP BY | | 992 | 20832 | 24345 (1)| 00:00:01 | | |
- |* 2 | HASH JOIN | | 9476 | 194K| 24344 (1)| 00:00:01 | | |
- | 3 | PARTITION RANGE ITERATOR| | 932 | 14912 | 23690 (1)| 00:00:01 |1048575| KEY |
- |* 4 | TABLE ACCESS FULL | SHOPPING_CART | 932 | 14912 | 23690 (1)| 00:00:01 |1048575| KEY |
- | 5 | TABLE ACCESS FULL | CART_ITEM | 908K| 4436K| 652 (1)| 00:00:01 | | |
- ------------------------------------------------------------------------------------------------------------
- Die ShoppingCart-Tabelle wird nicht vollständig ausgelesen. Nur die relevanten Partitionen müssen aufgrund der
- order_time-Kondition berücksichtigt werden. Es wird ausgegangen, dass ShoppingCarts meist mit Angabe eines Zeitraumes
- ausgelesen werden.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement