First Table is 550MB, Second Table is 26GB – Nested Loops or Full Table Scan? 2

2 10 2010

October 2, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Let’s try to test a couple of ideas that were suggested either at the end of my previous blog article, or in the comments section of that blog article.  What if we change…

Let’s try changing the Oracle Database release version, the PGA_AGGREGATE_TARGET, and the server.  That is likely too many changes to allow identifying what changed and why it changed, but we will use this server as the baseline for today’s article.  Let’s check a couple of the settings for the database instance:

SQL> COLUMN PNAME FORMAT A15
SQL>
SQL> SELECT
  2    PNAME,
  3    PVAL1
  4  FROM
  5    SYS.AUX_STATS$;

PNAME                PVAL1
--------------- ----------
STATUS
DSTART
DSTOP
FLAGS                    1
CPUSPEEDNW      2128.85496
IOSEEKTIM               10
IOTFRSPEED            4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

SQL> SHOW PARAMETER OPTIMIZER

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.2.0.1
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

All of the above are at the default values for Oracle Database 11.2.0.1.

SQL> SHOW PARAMETER PGA

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 2000M

The PGA_AGGREGATE_TARGET is at 2000MB (1.953GB), which is four times as high as the previous test with Oracle Database 11.1.0.7.  If we run through the test case, what in the test case results changes?

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2
  7  FROM
  8    T1,
  9    T2
 10  WHERE
 11    T1.COL1=T2.COL1
 12    AND T1.COL2 > (10000000-10000);

Execution Plan
----------------------------------------------------------
Plan hash value: 512758489

---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |  9999 |  2177K|       |  1238   (1)| 00:00:15 |
|*  1 |  HASH JOIN                   |                  |  9999 |  2177K|  2192K|  1238   (1)| 00:00:15 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1               |  9999 |  2070K|       |   330   (0)| 00:00:04 |
|*  3 |    INDEX RANGE SCAN          | IND_T1_COL2      |  9999 |       |       |    26   (0)| 00:00:01 |
|   4 |   VIEW                       | index$_join$_002 |   100K|  1074K|       |   691   (1)| 00:00:09 |
|*  5 |    HASH JOIN                 |                  |       |       |       |            |          |
|   6 |     INDEX FAST FULL SCAN     | SYS_C0018300     |   100K|  1074K|       |   236   (1)| 00:00:03 |
|   7 |     INDEX FAST FULL SCAN     | IND_T2_COL2      |   100K|  1074K|       |   299   (1)| 00:00:04 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."COL1"="T2"."COL1")
   3 - access("T1"."COL2">9990000)
   5 - access(ROWID=ROWID)

The above plan shows two hash joins, with the second of the hash joins (the first hash join executed) performed on the ROWIDs returned by two index fast full scans.  Under 11.1.0.7 the plan looked like this:

Execution Plan
----------------------------------------------------------
Plan hash value: 570481587

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  9999 |  2167K|       |   804   (2)| 00:00:04 |
|*  1 |  HASH JOIN                   |             |  9999 |  2167K|  2184K|   804   (2)| 00:00:04 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1          |  9999 |  2060K|       |   331   (1)| 00:00:02 |
|*  3 |    INDEX RANGE SCAN          | IND_T1_COL2 |  9999 |       |       |    26   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | T2          |   100K|  1074K|       |   379   (2)| 00:00:02 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."COL1"="T2"."COL1")
   3 - access("T1"."COL2">9990000)

So, just one execution plan changed.  Let’s try a couple of more tests, comparing the results with the new baseline.

PGA_AGGREGATE_TARGET = 1000M, no changes from the new baseline (11.2.0.1).

PGA_AGGREGATE_TARGET = 100M, no changes from the new baseline (11.2.0.1).

PGA_AGGREGATE_TARGET = 2000M, OPTIMIZER_MODE=FIRST_ROWS_1000 – there are enough changes to support posting the entire output.

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2,
  7    T2.COL3
  8  FROM
  9    T1,
 10    T2
 11  WHERE
 12    T1.COL1=T2.COL1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2077217387

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |  1000 |   414K|  1085   (1)| 00:00:14 |
|   1 |  NESTED LOOPS                |              |       |       |            |          |
|   2 |   NESTED LOOPS               |              |  1000 |   414K|  1085   (1)| 00:00:14 |
|   3 |    TABLE ACCESS FULL         | T1           | 10000 |  2070K|    84   (0)| 00:00:02 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C0018300 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2           |     1 |   212 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL1"="T2"."COL1")

The above was a simple hash join before – telling the optimizer that we only want the first 1,000 rows was sufficient to switch from a hash join to a nested loops join.  Now let’s try again, this time joining on the column that has an index with a high clustering factor caused by the descending number sequence in that column:

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2,
  7    T2.COL3
  8  FROM
  9    T1,
 10    T2
 11  WHERE
 12    T1.COL2=T2.COL2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000 |   621K|       |  2075   (1)| 00:00:25 |
|*  1 |  HASH JOIN         |      |  1000 |   621K|    21M|  2075   (1)| 00:00:25 |
|   2 |   TABLE ACCESS FULL| T2   |   100K|    20M|       |   825   (1)| 00:00:10 |
|   3 |   TABLE ACCESS FULL| T1   | 10000 |  2070K|       |    84   (0)| 00:00:02 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."COL2"="T2"."COL2")

The index clustering factor, caused by the descending number sequence in column COL2, this time did not cause a change in the execution plan, unlike when we joined on the column with the ascending sequence of numbers.  Continuing:

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2,
  7    T2.COL3
  8  FROM
  9    T1,
 10    T2
 11  WHERE
 12    T1.COL1=T2.COL1
 13    AND T1.COL1 BETWEEN 1 AND 1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1188283739

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |   999 |   413K|    70   (2)| 00:00:01 |
|*  1 |  HASH JOIN                   |              |   999 |   413K|    70   (2)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |  1000 |   207K|    35   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0018298 |  1000 |       |     4   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2           |  1000 |   207K|    34   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | SYS_C0018300 |  1000 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."COL1"="T2"."COL1")
   3 - access("T1"."COL1">=1 AND "T1"."COL1"<=1000)
   5 - access("T2"."COL1">=1 AND "T2"."COL1"<=1000)

No change in the above execution plan.  Continuing:

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2,
  7    T2.COL3
  8  FROM
  9    T1,
 10    T2
 11  WHERE
 12    T1.COL1=T2.COL1
 13    AND T1.COL2 BETWEEN 1 AND 1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2243940848

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |   424 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |     1 |   424 |     5   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1           |     1 |   212 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IND_T1_COL2  |     1 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C0018300 |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2           |     1 |   212 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL2">=1 AND "T1"."COL2"<=1000)
   5 - access("T1"."COL1"="T2"."COL1")

No change in the above execution plan, the optimizer is correctly predicting that one or less rows will be returned.  Continuing:

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2
  7  FROM
  8    T1,
  9    T2
 10  WHERE
 11    T1.COL1=T2.COL1
 12    AND T1.COL1 < 10000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1945774515

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |  1002 |   218K|   135   (0)| 00:00:02 |
|   1 |  NESTED LOOPS                 |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |  1002 |   218K|   135   (0)| 00:00:02 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1           |  9999 |  2070K|    35   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | SYS_C0018298 |  9999 |       |     4   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C0018300 |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2           |     1 |    11 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL1"<10000)
   5 - access("T1"."COL1"="T2"."COL1")
       filter("T2"."COL1"<10000)

There is a definite change in the above execution plan, which had appeared like this in the baseline:

Execution Plan
----------------------------------------------------------
Plan hash value: 2371011080

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |  9998 |  2177K|   586   (1)| 00:00:08 |
|*  1 |  HASH JOIN                   |                  |  9998 |  2177K|   586   (1)| 00:00:08 |
|*  2 |   VIEW                       | index$_join$_002 |  9999 |   107K|   260   (1)| 00:00:04 |
|*  3 |    HASH JOIN                 |                  |       |       |            |          |
|*  4 |     INDEX RANGE SCAN         | SYS_C0018300     |  9999 |   107K|    22  (10)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN     | IND_T2_COL2      |  9999 |   107K|   299   (1)| 00:00:04 |
|   6 |   TABLE ACCESS BY INDEX ROWID| T1               |  9999 |  2070K|   325   (0)| 00:00:04 |
|*  7 |    INDEX RANGE SCAN          | SYS_C0018298     |  9999 |       |    21   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."COL1"="T2"."COL1")
   2 - filter("T2"."COL1"<10000)
   3 - access(ROWID=ROWID)
   4 - access("T2"."COL1"<10000)
   7 - access("T1"."COL1"<10000)

The predicted number of rows decreased from 9998 to 1002 due to the OPTIMIZER_MODE=FIRST_ROWS_1000 setting, and the optimizer switched from two hash joins to two nested loops joins.  Continuing:

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2
  7  FROM
  8    T1,
  9    T2
 10  WHERE
 11    T1.COL1=T2.COL1
 12    AND T1.COL2 < 10000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2243940848

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |   223 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |     1 |   223 |     5   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1           |     1 |   212 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IND_T1_COL2  |     1 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C0018300 |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2           |     1 |    11 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL2"<10000)
   5 - access("T1"."COL1"="T2"."COL1")

No change from the baseline because the optimizer previously predicted that one or less rows from table T2 would be returned.  Continuing:

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2
  7  FROM
  8    T1,
  9    T2
 10  WHERE
 11    T1.COL1=T2.COL1
 12    AND T1.COL2 > (10000000-10000);

Execution Plan
----------------------------------------------------------
Plan hash value: 2243940848

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |  1001 |   217K|   136   (0)| 00:00:02 |
|   1 |  NESTED LOOPS                 |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |  1001 |   217K|   136   (0)| 00:00:02 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1           |  9999 |  2070K|    36   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IND_T1_COL2  |  9999 |       |     5   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C0018300 |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2           |     1 |    11 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL2">9990000)
   5 - access("T1"."COL1"="T2"."COL1")

The above execution plan changed from what appeared in the baseline:

Execution Plan
----------------------------------------------------------
Plan hash value: 512758489

---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |  9999 |  2177K|       |  1238   (1)| 00:00:15 |
|*  1 |  HASH JOIN                   |                  |  9999 |  2177K|  2192K|  1238   (1)| 00:00:15 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1               |  9999 |  2070K|       |   330   (0)| 00:00:04 |
|*  3 |    INDEX RANGE SCAN          | IND_T1_COL2      |  9999 |       |       |    26   (0)| 00:00:01 |
|   4 |   VIEW                       | index$_join$_002 |   100K|  1074K|       |   691   (1)| 00:00:09 |
|*  5 |    HASH JOIN                 |                  |       |       |       |            |          |
|   6 |     INDEX FAST FULL SCAN     | SYS_C0018300     |   100K|  1074K|       |   236   (1)| 00:00:03 |
|   7 |     INDEX FAST FULL SCAN     | IND_T2_COL2      |   100K|  1074K|       |   299   (1)| 00:00:04 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."COL1"="T2"."COL1")
   3 - access("T1"."COL2">9990000)
   5 - access(ROWID=ROWID)

Convincing the optimizer that we only wanted the first 1,000 rows was sufficient to switch from two hash joins to two nested loops joins.

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2,
  7    T2.COL3
  8  FROM
  9    T1,
 10    T2
 11  WHERE
 12    T1.COL1=T2.COL1
 13    AND T1.COL1 BETWEEN 1 AND 100;

Execution Plan
----------------------------------------------------------
Plan hash value: 1188283739

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |    99 | 41976 |    14   (8)| 00:00:01 |
|*  1 |  HASH JOIN                   |              |    99 | 41976 |    14   (8)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |   100 | 21200 |     7   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0018298 |   100 |       |     3   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2           |   100 | 21200 |     6   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | SYS_C0018300 |   100 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."COL1"="T2"."COL1")
   3 - access("T1"."COL1">=1 AND "T1"."COL1"<=100)
   5 - access("T2"."COL1">=1 AND "T2"."COL1"<=100)

The above output is the same as the baseline – we are already expected to return less than 1,000 rows, so we are still performing the hash join.  Continuing:

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2,
  7    T2.COL3
  8  FROM
  9    T1,
 10    T2
 11  WHERE
 12    T1.COL1=T2.COL1
 13    AND T1.COL1 BETWEEN 1 AND 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 1945774515

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     9 |  3816 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |     9 |  3816 |     5   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1           |    10 |  2120 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | SYS_C0018298 |    10 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C0018300 |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2           |     1 |   212 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL1">=1 AND "T1"."COL1"<=10)
   5 - access("T1"."COL1"="T2"."COL1")
       filter("T2"."COL1"<=10 AND "T2"."COL1">=1)

The above is the same as the baseline.  So, we did see some changes when the OPTIMIZER_MODE was set to FIRST_ROWS_1000.  Any predictions what will happen when the OPTIMIZER_MODE is set to FIRST_ROWS_100, FIRST_ROWS_10, or FIRST_ROWS_1?

PGA_AGGREGATE_TARGET = 2000M, OPTIMIZER_MODE=FIRST_ROWS_100, just listing the changes from when OPTIMIZER_MODE=FIRST_ROWS_1000:

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2,
  7    T2.COL3
  8  FROM
  9    T1,
 10    T2
 11  WHERE
 12    T1.COL2=T2.COL2;

Execution Plan
----------------------------------------------------------
Plan hash value: 617224274

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   100 | 42400 |   303   (0)| 00:00:04 |
|   1 |  NESTED LOOPS                |             |       |       |            |          |
|   2 |   NESTED LOOPS               |             |   100 | 42400 |   303   (0)| 00:00:04 |
|   3 |    TABLE ACCESS FULL         | T2          |   100K|    20M|     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IND_T1_COL2 |     1 |       |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T1          |     1 |   212 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL2"="T2"."COL2")

The clustering factor for the index on COL 2 was not sufficient this time to keep the above execution plan from changing from a single hash join to two nested loops joins.

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2,
  7    T2.COL3
  8  FROM
  9    T1,
 10    T2
 11  WHERE
 12    T1.COL1=T2.COL1
 13    AND T1.COL1 BETWEEN 1 AND 1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1945774515

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |   102 | 43248 |    17   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |   102 | 43248 |    17   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1           |  1000 |   207K|     7   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | SYS_C0018298 |  1000 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C0018300 |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2           |     1 |   212 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL1">=1 AND "T1"."COL1"<=1000)
   5 - access("T1"."COL1"="T2"."COL1")
       filter("T2"."COL1"<=1000 AND "T2"."COL1">=1)

Telling the optimizer that we only wanted the first 1,000 rows was sufficient to change from a hash join to two nested loops joins.

PGA_AGGREGATE_TARGET = 2000M, OPTIMIZER_MODE=FIRST_ROWS_10, just listing the changes from when OPTIMIZER_MODE=FIRST_ROWS_100:

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2,
  7    T2.COL3
  8  FROM
  9    T1,
 10    T2
 11  WHERE
 12    T1.COL1=T2.COL1
 13    AND T1.COL1 BETWEEN 1 AND 100;

Execution Plan
----------------------------------------------------------
Plan hash value: 1945774515

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |    11 |  4664 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |    11 |  4664 |     5   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1           |    98 | 20776 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | SYS_C0018298 |   100 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C0018300 |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2           |     1 |   212 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL1">=1 AND "T1"."COL1"<=100)
   5 - access("T1"."COL1"="T2"."COL1")
       filter("T2"."COL1"<=100 AND "T2"."COL1">=1)

The above execution plan, which was originally predicted to return 100 rows is now predicted to return 11 rows, so the one hash join became two nested loops joins.  At this point all of the excution plans for the test queries are showing nested loops joins, when previously they showed hash joins – so there is little point in looking at the output from the test case that shows what happens when the OPTIMIZER_MODE is set to FIRST_ROWS_1.

Changing the OPTIMIZER_INDEX_CACHING parameter had no effect on the execution plans when those execution plans were compared with the baseline output.

Changing the OPTIMIZER_INDEX_COST_ADJ to a value of 50 had no effect on the execution plans, except for the last execution plan.  The execution plan now shows a hash join while the baseline shows two nested loops joins.  OPTIMIZER_INDEX_COST_ADJ=50:

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2,
  7    T2.COL3
  8  FROM
  9    T1,
 10    T2
 11  WHERE
 12    T1.COL1=T2.COL1
 13    AND T1.COL1 BETWEEN 1 AND 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 1188283739

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     9 |  3816 |     4   (0)| 00:00:01 |
|*  1 |  HASH JOIN                   |              |     9 |  3816 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |    10 |  2120 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0018298 |    10 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2           |    10 |  2120 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | SYS_C0018300 |    10 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."COL1"="T2"."COL1")
   3 - access("T1"."COL1">=1 AND "T1"."COL1"<=10)
   5 - access("T2"."COL1">=1 AND "T2"."COL1"<=10)

From the baseline:

Execution Plan
----------------------------------------------------------
Plan hash value: 1945774515

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     9 |  3816 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |     9 |  3816 |     5   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1           |    10 |  2120 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | SYS_C0018298 |    10 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C0018300 |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2           |     1 |   212 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL1">=1 AND "T1"."COL1"<=10)
   5 - access("T1"."COL1"="T2"."COL1")
       filter("T2"."COL1"<=10 AND "T2"."COL1">=1)

So, we saw a small change when experimenting with OPTIMIZER_INDEX_COST_ADJ, setting it to half of its normal value.  What if we try a couple of more values?  Don’t say that someone didn’t warn you not to adjust that parameter.  With  OPTIMIZER_INDEX_COST_ADJ=20:

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2,
  7    T2.COL3
  8  FROM
  9    T1,
 10    T2
 11  WHERE
 12    T1.COL1=T2.COL1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3187704125

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |   100K|    40M|       | 11880   (1)| 00:02:23 |
|   1 |  MERGE JOIN                  |              |   100K|    40M|       | 11880   (1)| 00:02:23 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |  1000K|   202M|       |  6441   (1)| 00:01:18 |
|   3 |    INDEX FULL SCAN           | SYS_C0018298 |  1000K|       |       |   377   (1)| 00:00:05 |
|*  4 |   SORT JOIN                  |              |   100K|    20M|    43M|  5439   (1)| 00:01:06 |
|   5 |    TABLE ACCESS FULL         | T2           |   100K|    20M|       |   825   (1)| 00:00:10 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL1"="T2"."COL1")
       filter("T1"."COL1"="T2"."COL1")

Notice in the above that we now see a sort merge join, when before we saw a nice hash join, as seen in the baseline output:

Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100K|    40M|       | 20707   (1)| 00:04:09 |
|*  1 |  HASH JOIN         |      |   100K|    40M|    21M| 20707   (1)| 00:04:09 |
|   2 |   TABLE ACCESS FULL| T2   |   100K|    20M|       |   825   (1)| 00:00:10 |
|   3 |   TABLE ACCESS FULL| T1   |  1000K|   202M|       |  8225   (1)| 00:01:39 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
    1 - access("T1"."COL1"="T2"."COL1")

What about when we join T1.COL2 to T2.COL2 – want to take a guess?

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2,
  7    T2.COL3
  8  FROM
  9    T1,
 10    T2
 11  WHERE
 12    T1.COL2=T2.COL2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1173042340

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   100K|    40M|       | 11979   (1)| 00:02:24 |
|   1 |  MERGE JOIN                  |             |   100K|    40M|       | 11979   (1)| 00:02:24 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1          |  1000K|   202M|       |  6540   (1)| 00:01:19 |
|   3 |    INDEX FULL SCAN           | IND_T1_COL2 |  1000K|       |       |   476   (1)| 00:00:06 |
|*  4 |   SORT JOIN                  |             |   100K|    20M|    43M|  5439   (1)| 00:01:06 |
|   5 |    TABLE ACCESS FULL         | T2          |   100K|    20M|       |   825   (1)| 00:00:10 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL2"="T2"."COL2")
       filter("T1"."COL2"="T2"."COL2")

I think that this could be a problem.  What else do we see in the output?  Just like when OPTIMIZER_INDEX_COST_ADJ was set to 50, the final execution plan now shows a hash join rather than two nested loops joins as seen in the baseline output.  What happened when OPTIMIZER_INDEX_COST_ADJ was decreased to 10?  With the exception of the first two sort merge joins, the excution plans were identical to those found in the baseline output – the last execution plan switched back to two nested loops joins:

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2,
  7    T2.COL3
  8  FROM
  9    T1,
 10    T2
 11  WHERE
 12    T1.COL1=T2.COL1
 13    AND T1.COL1 BETWEEN 1 AND 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 1945774515

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     9 |  3816 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |     9 |  3816 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1           |    10 |  2120 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | SYS_C0018298 |    10 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C0018300 |     1 |       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2           |     1 |   212 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL1">=1 AND "T1"."COL1"<=10)
   5 - access("T1"."COL1"="T2"."COL1")
       filter("T2"."COL1"<=10 AND "T2"."COL1">=1)

So, what happens when OPTIMIZER_INDEX_COST_ADJ is set to 1, a value that I have seen recommended a couple of times in various documents and a couple of forum posts?  The first two execution plans that included sort merge joins with the other tested values of OPTIMIZER_INDEX_COST_ADJ now show an execution plan similar to the following:

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2,
  7    T2.COL3
  8  FROM
  9    T1,
 10    T2
 11  WHERE
 12    T1.COL1=T2.COL1;

Execution Plan
----------------------------------------------------------
Plan hash value: 285382392

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |   100K|    40M|  2825   (1)| 00:00:34 |
|   1 |  NESTED LOOPS                |              |       |       |            |          |
|   2 |   NESTED LOOPS               |              |   100K|    40M|  2825   (1)| 00:00:34 |
|   3 |    TABLE ACCESS FULL         | T2           |   100K|    20M|   825   (1)| 00:00:10 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C0018298 |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T1           |     1 |   212 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL1"="T2"."COL1")

No more sort merge join, and no more hash join either, even when we join on the column COL2 (with the descending values).  Any other changes when compared to the baseline output?

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2
  7  FROM
  8    T1,
  9    T2
 10  WHERE
 11    T1.COL1=T2.COL1
 12    AND T1.COL1 < 10000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3560619641

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |  9998 |  2177K|     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN                   |              |  9998 |  2177K|     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T2           |  9999 |   107K|     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0018300 |  9999 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T1           |  9999 |  2070K|     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | SYS_C0018298 |  9999 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."COL1"="T2"."COL1")
   3 - access("T2"."COL1"<10000)
   5 - access("T1"."COL1"<10000)

The above shows a hash join, what did the baseline show?

Execution Plan
----------------------------------------------------------
Plan hash value: 2371011080

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |  9998 |  2177K|   586   (1)| 00:00:08 |
|*  1 |  HASH JOIN                   |                  |  9998 |  2177K|   586   (1)| 00:00:08 |
|*  2 |   VIEW                       | index$_join$_002 |  9999 |   107K|   260   (1)| 00:00:04 |
|*  3 |    HASH JOIN                 |                  |       |       |            |          |
|*  4 |     INDEX RANGE SCAN         | SYS_C0018300     |  9999 |   107K|    22  (10)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN     | IND_T2_COL2      |  9999 |   107K|   299   (1)| 00:00:04 |
|   6 |   TABLE ACCESS BY INDEX ROWID| T1               |  9999 |  2070K|   325   (0)| 00:00:04 |
|*  7 |    INDEX RANGE SCAN          | SYS_C0018298     |  9999 |       |    21   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."COL1"="T2"."COL1")
   2 - filter("T2"."COL1"<10000)
   3 - access(ROWID=ROWID)
   4 - access("T2"."COL1"<10000)
   7 - access("T1"."COL1"<10000)

Any other changes?

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2
  7  FROM
  8    T1,
  9    T2
 10  WHERE
 11    T1.COL1=T2.COL1
 12    AND T1.COL2 > (10000000-10000);

Execution Plan
----------------------------------------------------------
Plan hash value: 2243940848

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |  9999 |  2177K|   103   (0)| 00:00:02 |
|   1 |  NESTED LOOPS                 |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |  9999 |  2177K|   103   (0)| 00:00:02 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1           |  9999 |  2070K|     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IND_T1_COL2  |  9999 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C0018300 |     1 |       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2           |     1 |    11 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL2">9990000)
   5 - access("T1"."COL1"="T2"."COL1")

In the baseline output the execution plan appeared as follows:

Execution Plan
----------------------------------------------------------
Plan hash value: 512758489

---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |  9999 |  2177K|       |  1238   (1)| 00:00:15 |
|*  1 |  HASH JOIN                   |                  |  9999 |  2177K|  2192K|  1238   (1)| 00:00:15 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1               |  9999 |  2070K|       |   330   (0)| 00:00:04 |
|*  3 |    INDEX RANGE SCAN          | IND_T1_COL2      |  9999 |       |       |    26   (0)| 00:00:01 |
|   4 |   VIEW                       | index$_join$_002 |   100K|  1074K|       |   691   (1)| 00:00:09 |
|*  5 |    HASH JOIN                 |                  |       |       |       |            |          |
|   6 |     INDEX FAST FULL SCAN     | SYS_C0018300     |   100K|  1074K|       |   236   (1)| 00:00:03 |
|   7 |     INDEX FAST FULL SCAN     | IND_T2_COL2      |   100K|  1074K|       |   299   (1)| 00:00:04 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."COL1"="T2"."COL1")
   3 - access("T1"."COL2">9990000)
   5 - access(ROWID=ROWID)

So, we saw a bit of a change there, but at least the access cost in the plan dropped from 1238 to 103, so certainly the query must execute faster, right? :-)  Any other changes?

SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2,
  7    T2.COL3
  8  FROM
  9    T1,
 10    T2
 11  WHERE
 12    T1.COL1=T2.COL1
 13    AND T1.COL1 BETWEEN 1 AND 100;

Execution Plan
----------------------------------------------------------
Plan hash value: 1945774515

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |    99 | 41976 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |    99 | 41976 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1           |   100 | 21200 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | SYS_C0018298 |   100 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C0018300 |     1 |       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2           |     1 |   212 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL1">=1 AND "T1"."COL1"<=100)
   5 - access("T1"."COL1"="T2"."COL1")
       filter("T2"."COL1"<=100 AND "T2"."COL1">=1)

The baseline output showed a hash join, with the entire cost of the execution plan at 14.

Pavan Kumar decided to try a couple of modifications to my original test case, allowing the HASH_AREA_SIZE to default to 128KB with an unset PGA_AGGREGATE_TARGET and a 10 character filler value in COL3 rather than a 200 character filler value in that column.  He managed to obtain at least one sort merge join in an execution plan.  You can see his test case here.  I might try a similar change to the original test case to see what happens.

You can download the full test script here: HashOrNestedLoops.sql

So, what else do we need to test?  Changing the row lengths, changing the join columns from NUMBER columns to VARCHAR2 columns, and adjusting the system (CPU) statistics from the default values to something else (hopefully realistic values) as suggested by Jan-Marten Spit.


Actions

Information

2 responses

3 10 2010
Narendra

Charles,

That is a lot of SQL and plans to look at ;)
On a serious note, I am not sure if this is any way related to this topic but this reminded me of a case I saw on forums in the past.

http://forums.oracle.com/forums/thread.jspa?threadID=1015916&start=0&tstart=0

The database version in that case was 10.2.0.4 and I clearly remember that I was able to reproduce OP’s results. Jonathan and Greg have provided some classic answers (as always) there. I am not sure if 11g has “fixed” it (I thought of testing it but a) I did not have enough space on my VM and b) got bored of creating so many daily partitions ;) )

4 10 2010
Charles Hooper

Narendra,

I am not sure that I am in a position to disagree with either Jonathan or Greg regarding that OTN thread. Their answers are probably better than the answers I would have provided had I joined that thread.

I noticed in that thread you stated “I thought when oracle joins two tables, which are accessed using full table scan, it will always (??) choose hash join (unless explcitly hinted to use Nested Loop).” In part 4 of this blog article series (and also in this blog article) I showed an unhinted execution plan that showed nested loops and a full table scan, even when suitable indexes existed:

Plan hash value: 2077217387

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |  1000 |   414K|  1085   (1)| 00:00:14 |
|   1 |  NESTED LOOPS                |              |       |       |            |          |
|   2 |   NESTED LOOPS               |              |  1000 |   414K|  1085   (1)| 00:00:14 |
|   3 |    TABLE ACCESS FULL         | T1           | 10000 |  2070K|    84   (0)| 00:00:02 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C0018300 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2           |     1 |   212 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL1"="T2"."COL1") 

The above shows a nested loops join between the “large” table and the primary key index for table T2.

In this blog article we also see another case:

Plan hash value: 617224274

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   100 | 42400 |   303   (0)| 00:00:04 |
|   1 |  NESTED LOOPS                |             |       |       |            |          |
|   2 |   NESTED LOOPS               |             |   100 | 42400 |   303   (0)| 00:00:04 |
|   3 |    TABLE ACCESS FULL         | T2          |   100K|    20M|     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IND_T1_COL2 |     1 |       |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T1          |     1 |   212 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."COL2"="T2"."COL2")

In the above a full table scan is performed on T2 (the small table) and a nested loops operation is performed on an index for table T2 (the large table).

Was there something else in that thread that you found to be interesting?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 143 other followers

%d bloggers like this: