1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

!= vs In

Discussion in 'SQL PL/SQL' started by jagadekara, Jun 27, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Dear Friends,

    One more doubt regarding performance tuning in oracle.

    select * from xx_categories;

    'Depreciation'
    'Addition'
    'Retirement'
    'Adjustment'
    'Reclass'
    'Transfer'

    Q1) select * from xx_categories where je_category != 'Depreciation' ;

    Q2) select * from xx_categories where je_category in ('Addition','Retirement','Adjustment','Reclass','Transfer');

    So among the two Queries which is best one? Other than these is there any Query better than this? suggest me....
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    There won't be any difference in performance between the two queries - with the table content you have listed (as both would do a FULL TABLE scan - assuming no indexes have been created on je_category - and would return the same number of rows).

    Of course, the output (and performance) will differ if additional rows are added to the table whereby Q1 will display more rows than Q2.
    I would say, it's more a question of functional requirement which would drive the choice of the query.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi,Jagadekara .

    I agree that told to Rajenb.

    I will add the following :

    1)
    I will repeat that is generally simple in the text of requests which we read the equivalent, it is impossible to tell: one will be more optimum, and another isn't present.

    2) The operators ! = and IN are different ,also for CBO.

    3) Query of Q1 and Q2 can't be read completely the equivalent generally.
    In your special case shown here: for query of Q1 and Q2 will be used FTS (FULL TABLE SCAN),
    NOTE: In certain cases, it is necessary to consider a distortion(SKEW) in data