Skip to Main Content

Breadcrumb

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 t1.id=t2.table1_id;

SQL_ID  82j4w4wq0u9xc, child number 0
-------------------------------------
select * from user.table1 t join user.table2 t2 on t1.id=t2.table1_id
 
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.

Regards,

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:

https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4433887271030
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9422487749968

and you rated our response

  (1 rating)

Reviews

Review

May 27, 2020 - 3:32 pm UTC

Reviewer: Geraldo

Thanks for the answer. It really helped.

More to Explore

Performance

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