Important Notice: Our web hosting provider recently started charging us for additional visits, which was unexpected. In response, we're seeking donations. Depending on the situation, we may explore different monetization options for our Community and Expert Contributors. It's crucial to provide more returns for their expertise and offer more Expert Validated Answers or AI Validated Answers. Learn more about our hosting issue here.

What are steps required tuning this query to improve its performance?

April 26, 2017PERFORMANCE query steps tuning

0

Posted

What are steps required tuning this query to improve its performance?

1 Answer

0

Posted

Have an index on TER_MASTER.REPNO and one on ERMAST.REPNO Be sure to get familiar with EXPLAIN PLAN. This can help you determine the execution path that Oracle takes. If you are using Cost Based Optimizer mode, then be sure that your statistics on TER_MASTER are up-to-date. Also, you can change your SQL to: SELECT a.* FROM ter.ter_master a WHERE NOT EXISTS (SELECT b.repno FROM ermast b WHERE a.repno=b.repno) AND (a.brepno = ‘ALL’ or a.repno > a.brepno) ORDER BY a.