I am dealing with a performance issue for a very simple structure involving simple queries.

Consider two tables called TBL01 and TBL02. TBL01 has a master-detail relationship with TBL02. The primary key for TBL01 is called CONFIGURATION_ID. TBL02 has CONFIGURATION_ID as a foreign key to TBL01 which is also indexed. TBL01 has 2.8 million rows and TBL02 has maybe 10 million rows.

A simple INNER JOIN on TBL01.CONFIGURATION_ID = TBL02.CONFIGURATION_ID is kinda slow. By slow I mean it takes a second to return 10 rows with 15 columns in total from both TBL01 and TBL02. If I start using sorting etc, things get worse. I have dealt with very big tables in the past but never had an issue. Could there be something missing?

What should my next steps be in order to improve the performance? BITMAP index with JOIN on CONFIGURATION_ID? Would that help? Any other tips?

Note 1: I am using ORACLE 12.

Note 2: This might be obvious, but if I add a filter after the join things are better.

  • 10 rows from a 10 million result set? This does not add up. You did not mention any filtering condition. So what is the actual SQL executed? Note: if you did not specify any filter and you just fetch the first 10 rows from a 10 million result set, then the databases does exactly what you asked from it. My answer depends on the further information that is missing. Commented Oct 7, 2019 at 8:33
  • @BalazsPapp: You are absolutely right. I added in the note that a filter immediately speeds up things.
    – Midas
    Commented Oct 7, 2019 at 8:34
  • Why you decide that JOIN is slow? You get "10 rows" from millions - so there exists some another conditions (in WHERE for example) - I think they cause long execution, because no suitable index exists. Post full tables' DDL and real query text.
    – Akina
    Commented Oct 7, 2019 at 8:35
  • but if I add a filter after the join In general the execution order do not match formal query text.
    – Akina
    Commented Oct 7, 2019 at 8:36
  • @Akina: The case is SELECT [COLUMNS] FROM TBL01 INNER JOIN TBL02 ON TBL02.CONFIGURATION_ID = TBL01.CONFIGURATION_ID. If I run that without any paging, sort or WHERE on SQL Developer for instance, it takes about a second to execute. SQL Developer limits the results to 200. The only INDEX is on CONFIGURATION_ID, but I am not sure if that is enough. I added the comment about filtering just in case someone would wonder how it performs in that case. However, my main case is the most simple SELECT with a single JOIN. Nothing more.
    – Midas
    Commented Oct 7, 2019 at 9:01

1 Answer 1


What you described is normal behaviour.

create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
delete from t1 where object_id is null;
delete from t2 where object_name = 'T1';
alter table t1 add primary key (object_id);
alter table t2 add constraint t1_fk foreign key (object_id) references t1(object_id);
create index t2_object_id on t2(object_id);
exec dbms_stats.gather_table_stats(user, 'T1');
exec dbms_stats.gather_table_stats(user, 'T2');

explain plan for select * from t1 join t2 on (t1.object_id = t2.object_id);
select * from table(dbms_xplan.display);

Plan hash value: 1838229974

| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT   |      | 23763 |  5337K|       |   516   (1)| 00:00:01 |
|*  1 |  HASH JOIN         |      | 23763 |  5337K|  2952K|   516   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   | 23764 |  2668K|       |   115   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 23763 |  2668K|       |   115   (1)| 00:00:01 |

Predicate Information (identified by operation id):

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

   - this is an adaptive plan

Here the database performs the join for the whole resultset with a HASH JOIN. By default, the optimizer generates a plan to return all rows from a query, which is configured with the optimizer_mode parameter.

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

Your query says: return all rows from TBL1 and TBL2 that match on CONFIGURATION_ID. You never told the database you wanted only the first any 10 rows. If you want the optimizer to generate a plan that returns the first rows quicker, tell that to the optimizer: change the optimizer mode, example:

alter session set optimizer_mode=first_rows_10;

explain plan for select * from t1 join t2 on (t1.object_id = t2.object_id);
select * from table(dbms_xplan.display);

Plan hash value: 209411957

| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT             |             |    10 |  2300 |    12   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |             |    10 |  2300 |    12   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |             |    10 |  2300 |    12   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | T2          | 21604 |  2426K|     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C006383 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T1          |     1 |   115 |     1   (0)| 00:00:01 |

Predicate Information (identified by operation id):

   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

This time, the database performed a NESTED LOOPS JOIN.

The significant difference between HASH JOIN and NESTED LOOPS JOIN in this case: HASH JOIN has to read one of the tables completely to build the hash table for the join. NESTED LOOPS JOIN can work row by row and do index lookups by ID, then stop reading the tables after finding the 10 rows. So even if you fetch only 10 rows, with a HASH JOIN, the database will read the whole 2.8 or 10 million rows from TBL1 or TBL2, depending on the order the optimizer chose.

To make things even worse, if you want to get the first 10 rows by a speficied order, with a HASH JOIN like above, the database has to read both tables completely, perform the join for all rows, sort the ~10 million rows, and return only the first 10 of them.

With a NESTED LOOPS JOIN, if you have an index on the sort column, the database can read the rows even in the sorting order using that index, can do row by row processing, and stop after finding the first 10 rows.

The optimizer mode can be set with a parameter as above, or it can be defined at statement level as well, with a hint, like:

select /*+ first_rows(10) */ ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.