Thanks for the question, Gaurang.

Asked: August 18, 2017 - 12:21 pm UTC

Last updated: August 19, 2017 - 1:54 am UTC

Version: Oracle DB 11G

Viewed 10K+ times! This question is

You Asked

> I am running following query :

select hh.cash_receipt_id,
hh.gl_date,
hh.amount,
hh.status,
hh.account_code_combination_id,
hh.cash_receipt_history_id
from cflow_temp_1 h, ar_cash_receipt_history_all hh
where hh.cash_receipt_id = h.cash_receipt_id
and hh.cash_receipt_history_id = h.cash_receipt_history_id

> Explain Plan is giving following output :

Plan
SELECT STATEMENT ALL_ROWSCost: 3,329 Bytes: 860,730 Cardinality: 13,242
3 HASH JOIN Cost: 3,329 Bytes: 860,730 Cardinality: 13,242
1 TABLE ACCESS FULL TABLE APPS.CFLOW_TEMP_1 Cost: 11 Bytes: 344,292 Cardinality: 13,242
2 TABLE ACCESS FULL TABLE AR.AR_CASH_RECEIPT_HISTORY_ALL Cost: 3,312 Bytes: 18,766,800 Cardinality: 481,200

> How to eliminate TABLE ACCESS FULL ?

and Connor said...

Why would you want to eliminate it ? It might be the best option.

Have you *proven* another option is better ?

There is nothing *inherently* wrong with TABLE ACCESS FULL

Maybe start here

http://docs.oracle.com/database/122/TGSQL/toc.htm

and you'll be able to move onto the following

http://www.centrexcc.com/Tuning%20by%20Cardinality%20Feedback.pdf

which will equip you with the knowledge to tune *any* SQL

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Table has around five lac records

Gaurang Pathak, August 18, 2017 - 4:53 pm UTC

This table is used in one of our procedure in 10 individual queries joined by UNION and has around five lac records. We are tuning this procedure|queries. Our team members along with Application Head have decided to remove FULL TABLE ACCESS.
So provide solution to remove FULL TABLE ACCESS.

August 19, 2017 - 1:54 am UTC

Sigh.... that's not the way to tune SQL. But anyway, here you go:

select /*+ index(h) index(hh) */ hh.cash_receipt_id,
hh.gl_date,
hh.amount,
hh.status,
hh.account_code_combination_id,
hh.cash_receipt_history_id
from cflow_temp_1 h, ar_cash_receipt_history_all hh
where hh.cash_receipt_id = h.cash_receipt_id
and hh.cash_receipt_history_id = h.cash_receipt_history_id

Dont say I didnt warn you....

hum

A reader, August 19, 2017 - 6:26 am UTC

Yes Sigh.. is light.
Yes need lot of patience.

Well I begin by leading the *smallest * table eg cflow_temp_1 h.

select h.cash_receipt_id, ....
Eg begin by selecting h cols and try using of course the index hint but try also the leading (h hh) and why not a parallel hint. .
It's a matter of tuning.

More to Explore

Performance

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

Breadcrumb

Question and Answer