Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question.

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

Answered by: Connor McDonald - Last updated: July 07, 2020 - 6:24 am UTC

Category: SQL - Version: Oracle 11g

Viewed 100+ 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 we 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.

More to Explore

Performance

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