Problem solve Get help with specific problems with your technologies, process and projects.

Clarification on explain plan statistics and used COST

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?


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 | ----------------------------------------------------------------------------
Hash joins are often faster than "index" (actually "nested loops") joins, because the number of consistent gets, and thus the CPU usage, is much lower. If you would like to compare the performance of the hash join to a nested loops join, use the optimizer hint USE_NL in the query. Also, remember that the optimizer uses statistics on data volume in determining a query plan. The plan you get on your production server, with production volumes, might be different (although I doubt it in this case). You can check that by using DBMS_STATS to export the statistics from production and import them into your development/test server; the optimizer plan produced will be the one you can expect to see in production. Finally, don't worry about the COST values in the plan output--just compare performance, i.e., elapsed time of the query.

Dig Deeper on Oracle database performance problems and tuning

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.