SQL Performance (MOSC)

MOSC Banner

The greater than operator causing performance issue

edited Nov 29, 2019 4:07AM in SQL Performance (MOSC) 1 commentAnswered

The below query execution is taking long time. Is there any way to increase the performance without modifying the logic. We have observed another thing that, when we replace '>' with '='  it's executing very quickly.

Query cost with '>' operator

     SELECT STATEMENT  ALL_ROWSCost: 2,758,228  Bytes: 224,636,855  Cardinality: 722,305

Query cost with '=' operator

     SELECT STATEMENT  ALL_ROWSCost: 2,758,228  Bytes: 311  Cardinality: 1

Oracle Database Ver: 12C

SELECT  *

  FROM  custom_table cust_tab

WHERE  1 = 1

    AND ( SELECT  Count( * )

            FROM  custom_view cust_v

           WHERE  ( cust_tab.Customer_number    = cust_v.Coulm1

                    OR cust_tab.Customer_number = cust_v.Coulm2

                    OR cust_tab.Customer_number = cust_v.Coulm3

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center