How does a WHERE CLAUSE work?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How does a WHERE CLAUSE work?

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    How does a WHERE CLAUSE work?

    i need a good explanation
    thnx,


    Cheers!
    OraKid.

  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    And have you read the documentation? :-)

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    "And have you read the documentation? :-)"

    yes sir
    i wanna a good explanation how the where clause work?
    wheather the WHERE CLAUSE move from downwards or upwards.

    eg,
    select * from emp
    where ename = 'SMITH'
    and empno = 12;

    in the above example how the oracle access the TBALE


    Cheers!
    OraKid.

  4. #4
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    The best way to understand that you want is doing some examples:

    1. Run the query without indexes and look itīs explain plan
    2. Run the query with an index on column empno and look the explain plan.
    ...

    Then you could see the access mode will be different. Normally when you use the where clause is better for the query performance to have indexes on.

    Hope that helps

    Best regards

    Angel

  5. #5
    Join Date
    Dec 2001
    Posts
    10
    binary search?

  6. #6
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi,
    i want to know how does oracle read a record from a table.
    for example,
    select * from emp
    where ename = 'SMITH'
    and empno = 1090;

    in the above case i want to know wheather the "empno" will execute first or "ename".

    thnx,

    Cheers!
    OraKid.

  7. #7
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    If you have a complex index (ename,empno) the optimizer will go for it, if you havenīt, probably it will do a full scan table.


  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    the optimizer determines the rule, it depends on so many factors

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    You are venturing into a very large world - the world of the Oracle query optimizer.

    The most simple answer is that the order of the WHERE clause is completely irrelevant, but even this is not completely true.

    First, there are actually 2 Oracle Optimizers:
    - A Rule-Based Optimizer (RBO), which uses pre-defined rules for deciding how to join tables. These rules are based on variables such as simple existence of indexes. One of the variables, albeit somewhat low on the list of priorities, is the order of items in the WHERE clause. Now, it is important to realize that the RBO is all but dead. If you are just getting into Oracle, I would say that it is not even worth worrying about.
    -The Cost-Based Optimizer. This optimizer uses statistics as well to determine a plan. Such things as table size, number of distinct values in a column, data distribution (if histograms exist) and *many* other variables to determine the most efficient execution plan for any given statement at any given time. *VERY* far down the list of variables is the order of items in the WHERE clause; SOOO far down that in 99.9% of the cases, the order is *completely* irrelevant.

    So basically:

    1 - Your question is un-aswerable without more information
    2 - You need to do a lot of reading

    HTH,

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width