Skip to Main Content


Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Geraldo.

Asked: May 27, 2020 - 1:29 pm UTC

Answered by: Chris Saxon - Last updated: May 27, 2020 - 3:25 pm UTC

Category: SQL - Version: 18.6

Viewed 100+ times

You Asked

Hello, Ask TOM Team.

I have two tables: TABLE1 (parent) and TABLE2 (child). TABLE1_ID (FK) on TABLE2 table has an index. When I run a join query, I got a table access full. There are ~4M rows in the result set.

select * from user.table1 t join user.table2 t2 on;

SQL_ID  82j4w4wq0u9xc, child number 0
select * from user.table1 t join user.table2 t2 on
Plan hash value: 3338755292
| Id  | Operation          | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT   |                 |       |       |       |   143K(100)|          |
|*  1 |  HASH JOIN         |                 |  4163K|  1508M|   414M|   143K  (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TABLE1     |  4303K|   365M|       | 15294   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TABLE2 |  4163K|  1155M|       | 47577   (1)| 00:00:01 |
Predicate Information (identified by operation id):
   1 - access("T1"."ID"="T2"."TABLE1_ID")

1. Why the database is not using the PK index on TABLE1 and the FK index on TABLE2?

Thanks in advanced.


and we said...

There is no WHERE clause in this query.

So if you have an FK from TABLE2 -> TABLE1 then the join will return every row in TABLE2. And all the rows from TABLE1 with a child row in TABLE2. Which presumably is most/all of them.

So if the optimizer uses the index on T2.TABLE1_ID, then it has to:

- Read every entry in the index
- Read every row in the table

Whereas with a full table scan it has to

- Read every row in the table

Notice that both operations read every row. But going via the index also has to read the index too! Clearly this is more work than just reading all the rows. So full scan is the way!

Assuming you're getting (close to) all the rows from the parent table, similar logic applies re using the PK on this table vs. a full table scan.

Remember: indexes are only useful when fetching a small fraction of the rows from a table. For more on this, read:

and you rated our response

  (1 rating)



May 27, 2020 - 3:32 pm UTC

Reviewer: Geraldo

Thanks for the answer. It really helped.

More to Explore


Get all the information about database performance in the Database Performance guide.