Why query plan changes by number of rows
Why query plan changes by number of rows?

If you are going to select 80% rows of the table then full table scan is most efficient and If you are going to select less than 10% rows from the table then reading an index followed by a table access by rowid may be more efficient than a full table scan. Optimizer makes decision according to the statistics of the table on whether to do full table scan or index scan.
SQL&gt; explain plan for select * from hr.employees where employee_id=100;

Explained.

SQL&gt; select * from table(dbms_xplan.display());                  

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1833546154

--------------------------------------------------------------------------------
-------------

| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU
)| Time     |

--------------------------------------------------------------------------------
-------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |     1 |    69 |     1   (0
)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0
)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN     | EMP_EMP_ID_PK |     1 |       |     0   (0
)| 00:00:01 |

--------------------------------------------------------------------------------
-------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(&quot;EMPLOYEE_ID&quot;=100)

14 rows selected.


SQL&gt; explain plan for select * from hr.employees where employee_id&lt;=206;

Explained.

SQL&gt; select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   107 |  7383 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter(&quot;EMPLOYEE_ID&quot;&lt;=206)

13 rows selected.

There are all together 107 rows in EMPLOYEES table of HR schema. When I tried to select all employees which means all rows from employees table optimizer chose full table scan and when I tried to select a specific record it chose table scan by index rowid.
Obviously table scan by index rowid is faster than full table scan.
In your first query the optimizer chose table scan by index rowid which is faster than full table scan which has chose by optimizer in your second query.
Another thing that makes these queries different than each other is physical joining algorithm used by the optimizer, namely Nested Loop Join and Hash Join in your case.

In a nested loops join, we have two tables a driving table and a secondary table.  The rows are usually accessed from a driving table index range scan, and the driving table result set is then nested within a probe of the second table, normally using an index range scan method.
In a hash join, the Oracle database does a full-scan of the driving table, builds a RAM hash table, and then probes for matching rows in the other table.

When we dont have optimal schema statistics(column histogram) the optimizer makes an incorrect guess about the cardinality(number of rows returned) of a result set so sometimes optimizer may use nested loop join instead hash join and vice-versa.
For details Database SQL Tuning Guide