SQL> explain plan for select object_name, object_type 2 from ( select o.*, max(last_ddl_time) over ( partition by owner ) last_mo dified_time 3 from all_my_objects o 4 ) 5 where last_ddl_time = last_modified_time; Explained. SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'all')); Plan hash value: 2149268367 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 339K| 14M| | 6142 (1)| 00:01:14 | |* 1 | VIEW | | 339K| 14M| | 6142 (1)| 00:01:14 | | 2 | WINDOW SORT | | 339K| 17M| 44M| 6142 (1)| 00:01:14 | | 3 | TABLE ACCESS FULL| ALL_MY_OBJECTS | 339K| 17M| | 1643 (1)| 00:00:20 | ---------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$2 / from$_subquery$_001@SEL$1 2 - SEL$2 3 - SEL$2 / O@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("LAST_DDL_TIME"="LAST_MODIFIED_TIME") Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "OBJECT_NAME"[VARCHAR2,30], "OBJECT_TYPE"[VARCHAR2,19], "LAST_DDL_TIME"[DATE,7], "LAST_MODIFIED_TIME"[DATE,7] 2 - (#keys=1) "OWNER"[VARCHAR2,30], "LAST_DDL_TIME"[DATE,7], "O"."OBJECT_NAME"[VARCHAR2,30], "O"."OBJECT_TYPE"[VARCHAR2,19], MAX("LAST_DDL_TIME") OVER ( PARTITION BY "OWNER")[7] 3 - "OWNER"[VARCHAR2,30], "O"."OBJECT_NAME"[VARCHAR2,30], "O"."OBJECT_TYPE"[VARCHAR2,19], "LAST_DDL_TIME"[DATE,7] Note ----- - dynamic sampling used for this statement