SQL> explain plan for WITH c1 as (SELECT owner, table_name, column_name, histogram, num_nulls, avg_col_len FROM all_my_tab_col_stats WHERE owner not in ('SYS','SYSMAN','SYSTEM','OUTLN','TSMSYS','DBSNMP','WMSYS','EXFSYS','CTXSYS','DMSYS','XDB','MDSYS','ORDSYS','OLAPSYS')) 2 SELECT distinct owner, table_name FROM ( 3 SELECT owner, table_name FROM c1 WHERE HISTOGRAM = 'FREQUENCY' 4 INTERSECT 5 SELECT owner, table_name FROM c1 WHERE NUM_NULLS > 1 6 INTERSECT 7 SELECT owner, table_name FROM c1 WHERE avg_col_len > 25 8 ) 9 ; Explained. SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'all')); Plan hash value: 3916227590 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 33415 | 1109K| | 1920 (1)| 00:00:24 | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | | 2 | LOAD AS SELECT | | | | | | | |* 3 | TABLE ACCESS FULL | ALL_MY_TAB_COL_STATS | 33415 | 2806K| | 434 (1)| 00:00:06 | | 4 | VIEW | | 33415 | 1109K| | 1485 (1)| 00:00:18 | | 5 | INTERSECTION | | | | | | | | 6 | INTERSECTION | | | | | | | | 7 | SORT UNIQUE | | 33415 | 1403K| 3704K| 478 (1)| 00:00:06 | |* 8 | VIEW | | 33415 | 1403K| | 108 (1)| 00:00:02 | | 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6611_13A684 | 33415 | 2806K| | 108 (1)| 00:00:02 | | 10 | SORT UNIQUE | | 33415 | 1533K| 3944K| 504 (1)| 00:00:07 | |* 11 | VIEW | | 33415 | 1533K| | 108 (1)| 00:00:02 | | 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6611_13A684 | 33415 | 2806K| | 108 (1)| 00:00:02 | | 13 | SORT UNIQUE | | 33415 | 1533K| 3944K| 504 (1)| 00:00:07 | |* 14 | VIEW | | 33415 | 1533K| | 108 (1)| 00:00:02 | | 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6611_13A684 | 33415 | 2806K| | 108 (1)| 00:00:02 | ---------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$2 2 - SEL$1 3 - SEL$1 / ALL_MY_TAB_COL_STATS@SEL$1 4 - SET$1 / from$_subquery$_002@SEL$2 5 - SET$1 8 - SEL$D67CB2D4 / C1@SEL$3 9 - SEL$D67CB2D4 / T1@SEL$D67CB2D4 11 - SEL$D67CB2D3 / C1@SEL$4 12 - SEL$D67CB2D3 / T1@SEL$D67CB2D3 14 - SEL$D67CB2D2 / C1@SEL$5 15 - SEL$D67CB2D2 / T1@SEL$D67CB2D2 Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("OWNER"<>'SYS' AND "OWNER"<>'SYSMAN' AND "OWNER"<>'SYSTEM' AND "OWNER"<>'OUTLN' AND "OWNER"<>'TSMSYS' AND "OWNER"<>'DBSNMP' AND "OWNER"<>'WMSYS' AND "OWNER"<>'EXFSYS' AND "OWNER"<>'CTXSYS' AND "OWNER"<>'DMSYS' AND "OWNER"<>'XDB' AND "OWNER"<>'MDSYS' AND "OWNER"<>'ORDSYS' AND "OWNER"<>'OLAPSYS') 8 - filter("HISTOGRAM"='FREQUENCY') 11 - filter("NUM_NULLS">1) 14 - filter("AVG_COL_LEN">25) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "OWNER"[VARCHAR2,30], "TABLE_NAME"[VARCHAR2,30] 2 - SYSDEF[4], SYSDEF[0], SYSDEF[1], SYSDEF[84], SYSDEF[0] 3 - "OWNER"[VARCHAR2,30], "TABLE_NAME"[VARCHAR2,30], "COLUMN_NAME"[VARCHAR2,30], "NUM_NULLS"[NUMBER,22], "AVG_COL_LEN"[NUMBER,22], "HISTOGRAM"[VARCHAR2,15] 4 - "OWNER"[VARCHAR2,30], "TABLE_NAME"[VARCHAR2,30] 5 - STRDEF[30], STRDEF[30] 6 - STRDEF[30], STRDEF[30] 7 - (#keys=2) "OWNER"[VARCHAR2,30], "TABLE_NAME"[VARCHAR2,30] 8 - "OWNER"[VARCHAR2,30], "TABLE_NAME"[VARCHAR2,30], "HISTOGRAM"[VARCHAR2,15] 9 - "C0"[VARCHAR2,30], "C1"[VARCHAR2,30], "C2"[VARCHAR2,30], "C3"[VARCHAR2,15], "C4"[NUMBER,22], "C5"[NUMBER,22] 10 - (#keys=2) "OWNER"[VARCHAR2,30], "TABLE_NAME"[VARCHAR2,30] 11 - "OWNER"[VARCHAR2,30], "TABLE_NAME"[VARCHAR2,30], "NUM_NULLS"[NUMBER,22] 12 - "C0"[VARCHAR2,30], "C1"[VARCHAR2,30], "C2"[VARCHAR2,30], "C3"[VARCHAR2,15], "C4"[NUMBER,22], "C5"[NUMBER,22] 13 - (#keys=2) "OWNER"[VARCHAR2,30], "TABLE_NAME"[VARCHAR2,30] 14 - "OWNER"[VARCHAR2,30], "TABLE_NAME"[VARCHAR2,30], "AVG_COL_LEN"[NUMBER,22] 15 - "C0"[VARCHAR2,30], "C1"[VARCHAR2,30], "C2"[VARCHAR2,30], "C3"[VARCHAR2,15], "C4"[NUMBER,22], "C5"[NUMBER,22] Note ----- - dynamic sampling used for this statement