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,
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