SELECT DISTINCT ID1, ID2, TRUNC(AC_DATE) FROM (SELECT TAB1A.TABLE1_C3 AS ID1, TAB1C.TABLE1_C3 AS IDall, TAB2A.TABLE2_C3 AS ID2, TAB1A.TABLE1_C6 AS AC_DATE, MAX (TAB1C.TABLE1_C4) over (partition BY TAB1C.TABLE1_C3) AS MAXALL FROM TABLE1 TAB1A, TABLE2 TAB2A, TABLE3 TAB3A, TABLE1 TAB1B, TABLE2 TAB2B, TABLE3 TAB3B, TABLE1 TAB1C WHERE TAB2A.TABLE2_C1 =to_date('12/31/9999', 'MM/DD/YYYY') AND TAB1A.TABLE1_C1 =TAB2A.TABLE2_C2 AND TAB1A.TABLE1_C2 =TAB3A.TABLE3_C2 AND TAB3A.TABLE3_C1 IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P') AND TAB2B.TABLE2_C1 =to_date('12/31/9999', 'MM/DD/YYYY') AND TAB1B.TABLE1_C5 =TAB2B.TABLE2_C2 AND TAB1B.TABLE1_C2 =TAB3B.TABLE3_C2 AND TAB3B.TABLE3_C1 ='A' AND LENGTH(TAB1A.TABLE1_C3(+)) =5 AND TAB1A.TABLE1_C3 =TAB1B.TABLE1_C3 AND TAB1A.TABLE1_C3 =TAB1C.TABLE1_C3 AND to_number(TAB1A.TABLE1_C3(+))>=10000 ) WHERE AC_DATE= MAXALL AND ID1 = IDALL / Execution Plan ---------------------------------------------------------- Plan hash value: 195783687 ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 48 | 8851 (2)| 00:02:04 | | | | 1 | HASH UNIQUE | | 1 | 48 | 8851 (2)| 00:02:04 | | | |* 2 | VIEW | | 1 | 48 | 8850 (2)| 00:02:04 | | | | 3 | WINDOW SORT | | 1 | 241 | 8850 (2)| 00:02:04 | | | | 4 | NESTED LOOPS | | 1 | 241 | 8849 (2)| 00:02:04 | | | | 5 | NESTED LOOPS | | 1 | 224 | 8847 (2)| 00:02:04 | | | | 6 | NESTED LOOPS | | 1 | 201 | 8844 (2)| 00:02:04 | | | | 7 | NESTED LOOPS | | 2 | 352 | 8842 (2)| 00:02:04 | | | | 8 | NESTED LOOPS | | 1 | 156 | 8840 (2)| 00:02:04 | | | |* 9 | HASH JOIN | | 49 | 6125 | 8693 (2)| 00:02:02 | | | |* 10 | TABLE ACCESS FULL | TABLE3 | 7 | 700 | 804 (1)| 00:00:12 | | | | 11 | INDEX FAST FULL SCAN | IDX_TABLE1_UNIQUE_UK | 6979K| 166M| 7846 (1)| 00:01:50 | | | | 12 | TABLE ACCESS BY INDEX ROWID | TABLE1 | 1 | 31 | 3 (0)| 00:00:01 | | | |* 13 | INDEX RANGE SCAN | IDX_TABLE1_C3 | 1 | | 2 (0)| 00:00:01 | | | | 14 | TABLE ACCESS BY INDEX ROWID | TABLE1 | 9 | 180 | 2 (0)| 00:00:01 | | | |* 15 | INDEX RANGE SCAN | IDX_TABLE1_C3 | 9 | | 2 (0)| 00:00:01 | | | |* 16 | TABLE ACCESS BY INDEX ROWID | TABLE3 | 1 | 25 | 1 (0)| 00:00:01 | | | |* 17 | INDEX UNIQUE SCAN | TABLE3_PK | 1 | | 0 (0)| 00:00:01 | | | | 18 | PARTITION RANGE SINGLE | | 1 | 23 | 3 (0)| 00:00:01 | 2 | 2 | | 19 | TABLE ACCESS BY LOCAL INDEX ROWID| TABLE2 | 1 | 23 | 3 (0)| 00:00:01 | 2 | 2 | |* 20 | INDEX RANGE SCAN | TABLE2_C2_EFF_UK | 1 | | 2 (0)| 00:00:01 | 2 | 2 | | 21 | PARTITION RANGE SINGLE | | 1 | 17 | 2 (0)| 00:00:01 | 2 | 2 | |* 22 | INDEX RANGE SCAN | TABLE2_C2_EFF_UK | 1 | 17 | 2 (0)| 00:00:01 | 2 | 2 | ---------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("AC_DATE"="MAXALL" AND "ID1"="IDall") 9 - access("TAB1B"."TABLE1_C2"="TAB3B"."TABLE3_C2") 10 - filter("TAB3B"."TABLE3_C1"='A') 13 - access("TAB1A"."TABLE1_C3"="TAB1B"."TABLE1_C3") filter(LENGTH("TAB1A"."TABLE1_C3")=7 AND TO_NUMBER("TAB1A"."TABLE1_C3")>=10000) 15 - access("TAB1C"."TABLE1_C3"="TAB1A"."TABLE1_C3") 16 - filter("TAB3A"."TABLE3_C1"='A' OR "TABLE3A"."TABLE3_C1"='B' OR "TABLE3A"."TABLE3_C1"='C' OR "TABLE3A"."TABLE3_C1"='D' OR "TABLE3A"."TABLE3_C1"='E' OR "TABLE3A"."TABLE3_C1"='F' OR "TABLE3A"."TABLE3_C1"='G' OR "TABLE3A"."TABLE3_C1"='H' OR "TABLE3A"."TABLE3_C1"='J' OR "TABLE3A"."TABLE3_C1"='NK' OR "TABLE3A"."TABLE3_C1"='L' OR "TABLE3A"."TABLE3_C1"='M' OR "TABLE3A"."TABLE3_C1"='N' OR "TABLE3A"."TABLE3_C1"='O' OR "TABLE3A"."TABLE3_C1"='P' OR "TABLE3A"."TABLE3_C1"='Q' OR "TABLE3A"."TABLE3_C1"='R' OR "TABLE3A"."TABLE3_C1"='S' OR "TABLE3A"."TABLE3_C1"='T' OR "TABLE3A"."TABLE3_C1"='U' OR "TABLE3A"."TABLE3_C1"='V' OR "TABLE3A"."TABLE3_C1"='W' OR "TABLE3A"."TABLE3_C1"='X' OR "TABLE3A"."TABLE3_C1"='Y') 17 - access("TAB1A"."TABLE1_C2"="TAB3A"."TABLE3_C2") 20 - access("TAB1A"."TABLE1_C1"="TAB2A"."TABLE2_C2" AND "TAB2A"."TABLE2_C1"=TIMESTAMP'9999-12-31 00:00:00') 22 - access("TAB1B"."TABLE1_C5"="TAB2B"."TABLE2_C2" AND "TAB2B"."TABLE2_C1"=TIMESTAMP' 9999-12-31 00:00:00') CREATE TABLE TABLE1 ( TABLE1_C6 NUMBER NOT NULL, TABLE1_C7 TIMESTAMP(6) NOT NULL, TABLE1_C5 INTEGER NOT NULL, TABLE1_C8 TIMESTAMP(6) NOT NULL, TABLE1_C1 INTEGER NOT NULL, TABLE1_C4 TIMESTAMP(6) NOT NULL, TABLE1_C9 INTEGER NOT NULL, TABLE1_C2 INTEGER NOT NULL, TABLE1_C10 INTEGER NOT NULL, TABLE1_C3 VARCHAR2(20 CHAR) NOT NULL, TABLE1_C11 INTEGER NOT NULL ) TABLESPACE TBS_DATA PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 80K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOLOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; CREATE UNIQUE INDEX TABLE1_UNIQUE_UK ON TABLE1 (TABLE1_C5, TABLE1_C8, TABLE1_C9, TABLE1_C2, TABLE1_C10,TABLE1_C3, TABLE1_C11) NOLOGGING TABLESPACE tbs_datax PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 80K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; CREATE INDEX IDX_TABLE1_C3 ON TABLE1 (TABLE1_C3) NOLOGGING TABLESPACE TBS_DATAX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; ALTER TABLE TABLE1 ADD ( CONSTRAINT TABLE1_PK PRIMARY KEY (TABLE1_C6, TABLE1_C7) USING INDEX TABLESPACE TBS_DATAX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 80K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 ), CONSTRAINT IDX_TABLE1_UNIQUE_UK UNIQUE (TABLE1_C5, TABLE1_C8, TABLE1_C9, TABLE1_C2, TABLE1_C10,TABLE1_C3, TABLE1_C11) USING INDEX TABLESPACE TBS_DATAX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 80K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 )); *************************************************8 CREATE TABLE2 ( TABLE2_C2 INTEGER NOT NULL, TABLE2_C4 TIMESTAMP(6) NOT NULL, TABLE2_C1 TIMESTAMP(6) NOT NULL, TABLE2_C3 INTEGER NOT NULL ) TABLESPACE TBS_DATA PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 PARTITION BY RANGE (TABLE2_C1) ( PARTITION PART_HIST VALUES LESS THAN (TIMESTAMP' 9999-12-31 00:00:00') NOLOGGING NOCOMPRESS TABLESPACE TBS_DATA_HIST PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 80K MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT ), PARTITION PART_CURR VALUES LESS THAN (MAXVALUE) NOLOGGING NOCOMPRESS TABLESPACE TBS_DATA_CURR PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 80K MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT ) ) NOCOMPRESS NOCACHE NOPARALLEL MONITORING ENABLE ROW MOVEMENT; CREATE UNIQUE INDEX TABLE2_C2_EFF_UK ON TABLE2 (TABLE2_C2, TABLE2_C1, TABLE2_C4) INITRANS 2 MAXTRANS 255 NOLOGGING LOCAL ( PARTITION PART_HIST NOLOGGING NOCOMPRESS TABLESPACE TBS_DATA_HIST PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 80K MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT ), PARTITION PART_CURR NOLOGGING NOCOMPRESS TABLESPACE TBS_DATA_CURR PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 80K MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT ) ) NOPARALLEL; CREATE UNIQUE INDEX TABLE2_PK ON TABLE2 (TABLE2_C2, TABLE2_C4) NOLOGGING TABLESPACE TBS_DATAX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 80K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; ALTER TABLE TABLE2 ADD ( CONSTRAINT TABLE2_PK PRIMARY KEY (TABLE2_C2, TABLE2_C4) USING INDEX TABLESPACE TBS_DATAX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 80K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 ), CONSTRAINT TABLE2_C2_EFF_UK UNIQUE (TABLE2_C2, TABLE2_C1, TABLE2_C4) USING INDEX LOCAL); ************************************************* CREATE TABLE TABLE3 ( TABLE3_C2 INTEGER NOT NULL, TABLE3_C1 VARCHAR2(255 CHAR) ) TABLESPACE TBS_DATA PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 80K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL KEEP ) NOLOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; CREATE UNIQUE INDEX TABLE3_PK ON TABLE3 (TABLE3_C2) NOLOGGING TABLESPACE TBS_DATAX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 80K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL KEEP ) NOPARALLEL; ALTER TABLE TABLE3 ADD ( CONSTRAINT TABLE3_PK PRIMARY KEY (TABLE3_C2) USING INDEX TABLESPACE TBS_DATAX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 80K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 ));