Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: July 06, 2020 - 2:28 pm UTC

Last updated: July 07, 2020 - 6:24 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi Tom,

I need to know how the where clause is effecting my query.

I have a table ABC where it has batch_id. It has 1L rows. Its daily truncated table. so it will have only one batch_id.

table1 = ABC
table2 = XYZ

select * from ABC t1, XYZ t2 where batch_id = 111
and t1.col1 = t2.col2

the above query is taking 20+ minutes to retrieve data.

the below query is taking hardly 6s.
select * from ABC t1, XYZ t2 where t1.col1 = t2.col2;

Just want it understand why its taking time with just batch_id in where clause.






and Connor said...

Take a look here

https://connor-mcdonald.com/2017/07/03/asktom-tv-episode-8/

which shows an example of how to use the "gather_plan_statistics" hint.

Paste the output back here as a review and we can take a look.

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

More to Explore

Performance

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