SQL> set linesize 300 SQL> set pagesize 300 SQL> set echo on SQL> set feedback on SQL> SQL> /* (1) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ SQL> EXPLAIN PLAN FOR 2 SELECT O_ORDERKEY, O_ORDERDATE, C_CUSTKEY 3 FROM ORDERS LEFT OUTER JOIN CUSTOMER 4 ON ORDERS.O_CUSTKEY = CUSTOMER.C_CUSTKEY; Explained. SQL> SQL> @showplan SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 247941471 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 450K| 10M| 7203 (1)| 00:00:01 | | 1 | NESTED LOOPS OUTER| | 450K| 10M| 7203 (1)| 00:00:01 | | 2 | TABLE ACCESS FULL| ORDERS | 450K| 8349K| 7180 (1)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN| CUSTOMER_PKEY | 1 | 5 | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ORDERS"."O_CUSTKEY"="CUSTOMER"."C_CUSTKEY"(+)) 15 rows selected. SQL> SQL> EXPLAIN PLAN FOR 2 SELECT O_ORDERKEY, O_ORDERDATE, O_CUSTKEY 3 FROM ORDERS LEFT OUTER JOIN CUSTOMER 4 ON ORDERS.O_CUSTKEY = CUSTOMER.C_CUSTKEY; Explained. SQL> SQL> @showplan SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1275100350 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 450K| 8349K| 7180 (1)| 00:00:01 | | 1 | TABLE ACCESS FULL| ORDERS | 450K| 8349K| 7180 (1)| 00:00:01 | ---------------------------------------------------------------------------- 8 rows selected. SQL> SQL> /* (2) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ SQL> EXPLAIN PLAN FOR 2 SELECT * 3 FROM PART 4 WHERE P_PARTKEY = 101 5 INTERSECT 6 SELECT * 7 FROM PART 8 WHERE P_NAME = 'bolt'; Explained. SQL> SQL> @showplan SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 507123922 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 242 | 1070 (1)| 00:00:01 | | 1 | INTERSECTION | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| PART | 1 | 121 | 2 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | PART_PEKEY | 1 | | 1 (0)| 00:00:01 | | 4 | SORT UNIQUE | | 1 | 121 | 1067 (1)| 00:00:01 | |* 5 | TABLE ACCESS FULL | PART | 1 | 121 | 1066 (1)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("P_PARTKEY"=101) 5 - filter("P_NAME"='bolt') 18 rows selected. SQL> SQL> EXPLAIN PLAN FOR 2 SELECT * 3 FROM PART 4 WHERE P_PARTKEY = 101 AND P_NAME ='bolt'; Explained. SQL> SQL> @showplan SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1532124002 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 121 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| PART | 1 | 121 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PART_PEKEY | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("P_NAME"='bolt') 2 - access("P_PARTKEY"=101) 15 rows selected. SQL> SQL> /* (3) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ SQL> EXPLAIN PLAN FOR 2 SELECT P_NAME, COUNT(*) 3 FROM PART LEFT OUTER JOIN LINEITEM 4 ON PART.P_PARTKEY = LINEITEM.L_PARTKEY 5 GROUP BY P_NAME 6 HAVING COUNT(L_PARTKEY) >= 0; Explained. SQL> SQL> @showplan; SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2934951400 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3000 | 131K| | 35077 (1)| 00:00:02 | |* 1 | FILTER | | | | | | | | 2 | HASH GROUP BY | | 3000 | 131K| | 35077 (1)| 00:00:02 | |* 3 | HASH JOIN OUTER | | 1786K| 76M| 3048K| 35032 (1)| 00:00:02 | | 4 | TABLE ACCESS FULL| PART | 60000 | 2343K| | 1065 (0)| 00:00:01 | |* 5 | TABLE ACCESS FULL| LINEITEM | 1800K| 8789K| | 32362 (1)| 00:00:02 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(COUNT("LINEITEM"."L_PARTKEY")>=0) 3 - access("PART"."P_PARTKEY"="LINEITEM"."L_PARTKEY"(+)) 5 - filter("LINEITEM"."L_PARTKEY"(+)>=0) 19 rows selected. SQL> SQL> EXPLAIN PLAN FOR 2 SELECT P_NAME, COUNT(*) 3 FROM PART 4 GROUP BY P_NAME; Explained. SQL> SQL> @showplan; SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3898611309 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 60000 | 2050K| | 1624 (1)| 00:00:01 | | 1 | HASH GROUP BY | | 60000 | 2050K| 2608K| 1624 (1)| 00:00:01 | | 2 | TABLE ACCESS FULL| PART | 60000 | 2050K| | 1065 (0)| 00:00:01 | ----------------------------------------------------------------------------------- 9 rows selected. SQL> SQL> /* (4) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ SQL> SQL> EXPLAIN PLAN FOR 2 SELECT P_NAME 3 FROM PART P 4 WHERE ( SELECT COUNT(*) 5 FROM PART 6 WHERE P.P_PARTKEY = PART.P_PARTKEY ) > 5; Explained. SQL> SQL> @showplan SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2310518650 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 60000 | 2343K| 50154 (1)| 00:00:02 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | PART | 60000 | 2343K| 1066 (1)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | 5 | | | |* 4 | INDEX UNIQUE SCAN| PART_PEKEY | 1 | 5 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( (SELECT COUNT(*) FROM "PART" "PART" WHERE "PART"."P_PARTKEY"=:B1)>5) 4 - access("PART"."P_PARTKEY"=:B1) 18 rows selected. SQL> SQL> EXPLAIN PLAN FOR 2 SELECT P_NAME, COUNT(*) 3 FROM PART 4 GROUP BY P_NAME 5 HAVING COUNT(*) > 5; Explained. SQL> SQL> @showplan SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3898611309 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3000 | 102K| 1068 (1)| 00:00:01 | |* 1 | HASH GROUP BY | | 3000 | 102K| 1068 (1)| 00:00:01 | | 2 | TABLE ACCESS FULL| PART | 60000 | 2050K| 1065 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(COUNT(*)>5) 14 rows selected. SQL> SQL> /* (5) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ SQL> SQL> EXPLAIN PLAN FOR 2 SELECT C_NAME, C_ADDRESS 3 FROM CUSTOMER 4 WHERE C_NAME = (SELECT DISTINCT C_NAME 5 FROM CUSTOMER 6 WHERE C_NAME = 'James'); Explained. SQL> SQL> @showplan SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2542011642 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 46 | 2070 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL | CUSTOMER | 1 | 46 | 1034 (0)| 00:00:01 | | 2 | SORT UNIQUE NOSORT| | 1 | 19 | 1035 (1)| 00:00:01 | |* 3 | TABLE ACCESS FULL| CUSTOMER | 1 | 19 | 1034 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C_NAME"= (SELECT DISTINCT "C_NAME" FROM "CUSTOMER" "CUSTOMER" WHERE "C_NAME"='James')) 3 - filter("C_NAME"='James') 17 rows selected. SQL> SQL> EXPLAIN PLAN FOR 2 SELECT C_NAME, C_ADDRESS 3 FROM CUSTOMER 4 WHERE C_NAME = 'James'; Explained. SQL> SQL> @showplan SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2844954298 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 46 | 1034 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| CUSTOMER | 1 | 46 | 1034 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C_NAME"='James') 13 rows selected. SQL> SQL> /* (6) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ SQL> EXPLAIN PLAN FOR 2 SELECT DISTINCT C_CUSTKEY, C_NAME 3 FROM CUSTOMER CROSS JOIN ORDERS; Explained. SQL> SQL> @showplan SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1434941425 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1431M| 32G| | 145M (1)| 01:35:01 | | 1 | HASH UNIQUE | | 1431M| 32G| 530G| 145M (1)| 01:35:01 | | 2 | MERGE JOIN CARTESIAN | | 20G| 452G| | 39M (1)| 00:25:31 | | 3 | TABLE ACCESS FULL | CUSTOMER | 45000 | 1054K| | 1034 (0)| 00:00:01 | | 4 | BUFFER SORT | | 450K| | | 39M (1)| 00:25:31 | | 5 | INDEX FAST FULL SCAN| ORDERS_PKEY | 450K| | | 871 (1)| 00:00:01 | ----------------------------------------------------------------------------------------------- 12 rows selected. SQL> SQL> EXPLAIN PLAN FOR 2 SELECT C_CUSTKEY, C_NAME 3 FROM CUSTOMER; Explained. SQL> SQL> @showplan SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2844954298 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 45000 | 1054K| 1034 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| CUSTOMER | 45000 | 1054K| 1034 (0)| 00:00:01 | ------------------------------------------------------------------------------ 8 rows selected. SQL> SQL> SQL> spool OFF