SQL> explain plan for select object_name, object_type 2 from all_my_objects o 3 where last_ddl_time = ( select max(last_ddl_time) from all_my_objects o2 w here o2.owner = o.owner) 4 ; Explained. SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'all')); Plan hash value: 2978210586 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1468 | 114K| | 4969(1)| 00:01:00 | |* 1 | HASH JOIN | | 1468 | 114K| 12M| 4969(1)| 00:01:00 | | 2 | VIEW | VW_SQ_1 | 339K| 8610K| | 1654(2)| 00:00:20 | | 3 | HASH GROUP BY | | 339K| 13M| | 1654(2)| 00:00:20 | | 4 | TABLE ACCESS FULL| ALL_MY_OBJECTS | 339K| 13M| | 1640(1)| 00:00:20 | | 5 | TABLE ACCESS FULL | ALL_MY_OBJECTS | 339K| 17M| | 1640(1)| 00:00:20 | ----------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$C772B8D1 2 - SEL$683B0107 / VW_SQ_1@SEL$7511BFD2 3 - SEL$683B0107 4 - SEL$683B0107 / O2@SEL$2 5 - SEL$C772B8D1 / O@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("LAST_DDL_TIME"="MAX(LAST_DDL_TIME)" AND "ITEM_1"="O"."OWNER") Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=2) "OBJECT_TYPE"[VARCHAR2,19], "OBJECT_NAME"[VARCHAR2,30] 2 - "MAX(LAST_DDL_TIME)"[DATE,7], "ITEM_1"[VARCHAR2,30] 3 - (#keys=1) "O2"."OWNER"[VARCHAR2,30], MAX("LAST_DDL_TIME")[7] 4 - "O2"."OWNER"[VARCHAR2,30], "LAST_DDL_TIME"[DATE,7] 5 - "O"."OWNER"[VARCHAR2,30], "OBJECT_NAME"[VARCHAR2,30], "OBJECT_TYPE"[VARCHAR2,19], "LAST_DDL_TIME"[DATE,7] Note ----- - dynamic sampling used for this statement