Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    6

    Unanswered: determine driving table in CBO

    Hi,

    In case of cost based optimization how is the driving table determined? Does the order of the tables matter in CBO. How are the conditions in the WHERE clause executed in CBO. Do we need to specify any order for the conditions in the WHERE clause.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    In most systems the answer is NO. Cost Based Optimization is determined my your DBMS going through some rather complex logic. At a rather basic level it is looking at inputs given in your SQL statements, indexes available on your tables and amount of data in your tables. There are tons of docs out there that explain this process for each of the DBMS's out there and a ton of classes on writing SQL.
    Dave

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    This response is valid for currently supported versions of Oracle (V10.2+)

    >In case of cost based optimization how is the driving table determined?
    Well

    > Does the order of the tables matter in CBO.
    no

    > How are the conditions in the WHERE clause executed in CBO.
    They are not "executed" but evaluated.

    >Do we need to specify any order for the conditions in the WHERE clause.
    no

    Since you really have no direct control over the CBO, just relax & let it do its thing.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anacedent View Post
    Since you really have no direct control over the CBO, just relax & let it do its thing.
    Not much to add to this, except:

    - Make sure your statistics are up to date
    - Make sure your are testing with real world data (in terms of size and value distribution)

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >- Make sure your statistics are up to date
    With V10+ Oracle RDBMS, default installation include SCHEDULER_JOB that collects statistics nightly.
    So statistics stay relatively current without any DBA/manual intervention.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anacedent View Post
    >- Make sure your statistics are up to date
    With V10+ Oracle RDBMS, default installation include SCHEDULER_JOB that collects statistics nightly.
    So statistics stay relatively current without any DBA/manual intervention.
    They should, but Murphy lives

    And it's important to update them after bulk-loading, if tests are run immediately after the load.

Posting Permissions

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