QUESTION POSED ON: 14 March 2005
In the following SQL queries I have used four tables, all parent tables have primary key constraints and the same column in the child table is a foreign key column. I have created an index on the foreign key column. Before running these queries, I have analyzed the tables and indexes.
My questions are:
1. Why is it not using index join?
2. Are any changes required within the SQL query to use the index joins?
3. If the optimizer uses index join will it affect the COST?
This is sample data; in the production server these four tables have data in the millions. The optimizer is using so much COST
for this query. Is there any way to reduce the COST?
Example:
-------
Explain plan set statement_id = 'test1' for select sup.supplier_name,
rem.supplier_remitter_name, acc.account_num, inv.invoice_number from supplier_hdr sup,
supplier_remit_hdr rem, acnt_info_hdr acc, inv_info_sum inv where
SUP.SUPPLIER_HDR_SYS_ID(Primary Key) = REM.SUPPLIER_HDR_SYS_ID (Foreign Key) and
REM.SUPPLIER_REMIT_SYS_HDR_ID (Primary Key) = ACC.SUPPLIER_REMIT_SYS_HDR_ID (Foreign Key)AND
ACC.ACNT_INFO_HDR_SYS_ID (Primary Key) = INV.ACNT_INFO_HDR_SYS_ID (Foreign Key)
/
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 353 | 33182 | 12 |
| 1 | HASH JOIN | | 353 | 33182 | 12 |
| 2 | TABLE ACCESS FULL | INV_INFO_SUM | 353 | 10943 | 3 |
| 3 | HASH JOIN | | 265 | 16695 | 8 |
| 4 | TABLE ACCESS FULL | SUPPLIER_HDR | 4 | 60 | 2 |
| 5 | HASH JOIN | | 265 | 12720 | 5 |
| 6 | TABLE ACCESS FULL| SUPPLIER_REMIT_HDR | 4 | 64 | 2 |
| 7 | TABLE ACCESS FULL| ACNT_INFO_HDR | 265 | 8480 | 2 |
----------------------------------------------------------------------------
|
