NEW QUERY ********* SQL> EXPLAIN PLAN FOR 2 WITH max_price_id as 3 (select max(price_id),UNDERLYING_ID 4 from NXMO.PRICE 5 where CURRENCY_CODE = 'USD' 6 AND IS_DELETED = 'N' 7 group by underlying_id) 8 select COUNT(*) from 9 NXMO.PRICE P, 10 NXMO.UNDERLYING U, 11 NXMO.DOMAINS D, 12 max_price_id M 13 where U.OCC_ELIGIBLE = 'Y' 14 AND U.SECURITY_TYPE = D.DOMAIN_CODE 15 AND D.DOMAIN_VALUE = 'STOCK' 16 AND D.DOMAIN_TYPE = 'SECURITY_TYPES' 17 AND U.IS_DELETED = 'N' 18 AND P.UNDERLYING_ID = U.UNDERLYING_ID 19 AND P.underlying_id=M.underlying_id; Explained. SQL> @?/rdbms/admin/utlxpls.sql PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2770774157 ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 32303 (1)| 00:06:28 | | 1 | SORT AGGREGATE | | 1 | | | | | | 2 | VIEW | | 103K| | | 32303 (1)| 00:06:28 | | 3 | HASH GROUP BY | | 103K| 14M| 253M| 32303 (1)| 00:06:28 | |* 4 | HASH JOIN | | 1586K| 216M| 11M| 12378 (2)| 00:02:29 | |* 5 | HASH JOIN | | 86352 | 10M| | 3615 (2)| 00:00:44 | PLAN_TABLE_OUTPUT -----------------------------------------------------------------------------------------------------------------------------|* 6 | TABLE ACCESS BY INDEX ROWID | UNDERLYING | 5699 | 400K| | 633 (1)| 00:00:08 | | 7 | BITMAP CONVERSION TO ROWIDS| | | | | | | |* 8 | BITMAP INDEX SINGLE VALUE | UNDERLYING_IX2 | | | | | | | 9 | NESTED LOOPS | | 3123K| 178M| | 2961 (2)| 00:00:36 | | 10 | TABLE ACCESS BY INDEX ROWID| DOMAINS | 1 | 33 | | 2 (0)| 00:00:01 | |* 11 | INDEX UNIQUE SCAN | DOMAINS_UK1 | 1 | | | 1 (0)| 00:00:01 | | 12 | INDEX FAST FULL SCAN | IDX_PRICE | 3123K| 80M| | 2959 (2)| 00:00:36 | |* 13 | TABLE ACCESS FULL | PRICE | 1893K| 19M| | 6087 (2)| 00:01:14 | ------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- 4 - access("P"."UNDERLYING_ID"="UNDERLYING_ID") 5 - access("U"."SECURITY_TYPE"="D"."DOMAIN_CODE" AND "P"."UNDERLYING_ID"="U"."UNDERLYING_ID") 6 - filter("U"."IS_DELETED"='N') 8 - access("U"."OCC_ELIGIBLE"='Y') 11 - access("D"."DOMAIN_VALUE"='STOCK' AND "D"."DOMAIN_TYPE"='SECURITY_TYPES') 13 - filter("CURRENCY_CODE"='USD' AND "IS_DELETED"='N') 30 rows selected. SQL> SQL> SQL> OLD QUERY ********* SQL> explain plan for 2 SELECT 3 * 4 FROM 5 NXMO.PRICE P, 6 NXMO.UNDERLYING U, 7 NXMO.DOMAINS D 8 WHERE 9 P.UNDERLYING_ID = U.UNDERLYING_ID AND 10 U.OCC_ELIGIBLE = 'Y' AND P.PRICE_ID = (SELECT 11 12 MAX(PRICE_ID) 13 FROM 14 NXMO.PRICE 15 WHERE 16 UNDERLYING_ID = P.UNDERLYING_ID 17 AND CURRENCY_CODE = 'USD' 18 AND IS_DELETED = 'N' 19 ) AND U.SECURITY_TYPE = D.DOMAIN_CODE 20 AND D.DOMAIN_VALUE = 'STOCK' 21 AND D.DOMAIN_TYPE = 'SECURITY_TYPES' 22 AND U.IS_DELETED = 'N'; Explained. SQL> @?/rdbms/admin/utlxpls.sql PLAN_TABLE_OUTPUT -----------------------------------------------------------------------------------------------------------------------------Plan hash value: 2295546093 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 216 | | 16826 (2)| 00:03:22 | |* 1 | FILTER | | | | | | | | 2 | HASH GROUP BY | | 1 | 216 | | 16826 (2)| 00:03:22 | |* 3 | HASH JOIN | | 1586K| 326M| 16M| 16727 (2)| 00:03:21 | |* 4 | HASH JOIN | | 86352 | 15M| | 6727 (2)| 00:01:21 | |* 5 | TABLE ACCESS BY INDEX ROWID | UNDERLYING | 5699 | 640K| | 633 (1)| 00:00:08 | PLAN_TABLE_OUTPUT -----------------------------------------------------------------------------------------------------------------------------| 6 | BITMAP CONVERSION TO ROWIDS| | | | | | | |* 7 | BITMAP INDEX SINGLE VALUE | UNDERLYING_IX2 | | | | | | | 8 | NESTED LOOPS | | 3123K| 235M| | 6072 (2)| 00:01:13 | | 9 | TABLE ACCESS BY INDEX ROWID| DOMAINS | 1 | 33 | | 2 (0)| 00:00:01 | |* 10 | INDEX UNIQUE SCAN | DOMAINS_UK1 | 1 | | | 1 (0)| 00:00:01 | | 11 | TABLE ACCESS FULL | PRICE | 3123K| 137M| | 6070 (2)| 00:01:13 | |* 12 | TABLE ACCESS FULL | PRICE | 1893K| 39M| | 6087 (2)| 00:01:14 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- 1 - filter("P"."PRICE_ID"=MAX("PRICE_ID")) 3 - access("UNDERLYING_ID"="P"."UNDERLYING_ID") 4 - access("P"."UNDERLYING_ID"="U"."UNDERLYING_ID" AND "U"."SECURITY_TYPE"="D"."DOMAIN_CODE") 5 - filter("U"."IS_DELETED"='N') 7 - access("U"."OCC_ELIGIBLE"='Y') 10 - access("D"."DOMAIN_VALUE"='STOCK' AND "D"."DOMAIN_TYPE"='SECURITY_TYPES') 12 - filter("CURRENCY_CODE"='USD' AND "IS_DELETED"='N') 30 rows selected.