After adding hint /*+ parallel(table1) */, instead runs 150 seconds, now it runs 120 seconds.
Here is the result of DBMS_XPLAN.DISPLAY:
Code:------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 39 | 87767 (3)| 00:17:34 | | 1 | SORT AGGREGATE | | 1 | 39 | | | |* 2 | HASH JOIN | | 17262 | 657K| 87767 (3)| 00:17:34 | |* 3 | TABLE ACCESS FULL| table2 | 91 | 1274 | 25 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL| table1 | 726K| 17M| 87734 (3)| 00:17:33 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("table1"."col1"="table2"."cola" AND "table1"."col2"="table2"."colb") 3 - filter("table2"."colc"='xy' AND "table2"."cold"='z') 4 - filter("table1"."col3"<=SYSDATE@! AND ("table1"."col4"='a' AND "table1"."col5"<100 OR "table1"."col4"='b' AND ("table1"."col5"=10 OR "table1"."col5"=20)) AND "table1"."col6"<>'abc' AND "table1"."col6"<>'def' AND "table1"."col6"<>'ghi')




Reply With Quote