Results 1 to 11 of 11

Thread: SQL Performance

  1. #1
    Join Date
    Feb 2004
    Posts
    86

    Unanswered: SQL Performance

    I have read so much about SQL performance and how to construct it, however I was asked a question recently that I couldn't quite answer.

    Does it make a different the order of tables in the FROM clause or is this important? How should it be constructed?

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    For the RBO (Rule-Based Optimizer) maybe it was important (though I think only the order of predicates and the order of tables within the predicates for joins were relevant).

    Anyway, for the CBO (Cost-Based Optimizer) which is the only one to be used nowadays (at least for 9i and 10g), it has absolutely no importance.

    Regards,

    RBARAER

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by RBARAER
    For the RBO (Rule-Based Optimizer) maybe it was important (though I think only the order of predicates and the order of tables within the predicates for joins were relevant).

    Anyway, for the CBO (Cost-Based Optimizer) which is the only one to be used nowadays (at least for 9i and 10g), it has absolutely no importance.

    Regards,

    RBARAER
    Almost true, 9i still has the RBO option, but it is not suggested. The order of the tables in the from clause on an RBO system was important. It would start at the last table in the from clause and use that table as the driver table.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    I heard that RBO starts at the FIRST table in the from clause?? And one should allways put the most limiting table first (eg the table with the least amount of work involved and/or returning the records in the where).

    Sometimes i just guess and try it in another order, sometimes (Oracle 817) it helps (a lot) sometimes no diff at all...

    *I wish i knew more about tuning and stuff*

  5. #5
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    This is an extract from my (old) text book:

    "When using rule based optimisation in Oracle v7, the order in which tables are placed in the from clause of a SELECT statement can be critical to it's performance. The desired "driving" table must be placed last in the SELECT statement for optimum performance. A table is considered the driving table if all conditions depend on one or more pieces of information from that table. Other tables should be listed according to the sequence of the extractions desired. The WHERE clause conditions must follow the same order as the tables in the from clause."

    You can see the sequence of table selection in the FROM clause by using EXPLAIN_PLAN.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Almost true, 9i still has the RBO option, but it is not suggested.
    What I meant is that from 9i, only the CBO is to be used. You're right, the RBO is still there in 9i, but only for backward compatibility reasons. Oracle strongly recommends to use the CBO in 9i. In 10g the RBO is simply not there anymore. It's much more than a "suggestion" IMO : I think only people having applications using the RBO in older versions or migrating from older versions to 9i may consider still using it today.
    *I wish i knew more about tuning and stuff*
    I see you're using 8i and the RBO. You may consider using the CBO. I'd really think twice about it before spending time learning how to tune SQL with the RBO as it is a deprecated thing now, which will be useless when you migrate to 10g (one day you will).

    Regards,

    RBARAER

  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    I am not sure what i am using and why, that is part of my issue.
    Normaly I just type out a SQL statement, which is quite easy, and if its not running to my satisfaction then i muck around trying to get something that works. Sometimes it helps to put the tables in another order. Sometimes a subselect in the from, like this question i asked. Other times i get stuck with something i feel can be done better, it works, but works slowly.

    I dont much understand the hints, i have seen query's use Hash and stuff. In the other thread i ended up using Index_desc in the subquery, because i only needed the later bit of the table/index. I am mostly using the "default" settings of Oracle in both 817 and 9i which would be CBO wouldnt it?

    Regards

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If you want to check your using CBO check the optimizer_mode parameter in your init.ora (or v$parameter) and also check your tables have been analyzed (see dba_tables).

  9. #9
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    I have no INIT.ORA on my C: drive (where oracle/sqlplus is intalled) and i dont know how to fetch v$parameter from the system a select v$parameter from dual; doesnt work

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    just logon as system (or another user with DBA privs) and do 'select * from v$parameter;'

    To see when your tables where last analysed do 'select * from dba_tables' (or (user_tables) and check the last_analyzed column.


    Alan

  11. #11
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Quote Originally Posted by AlanP
    just logon as system (or another user with DBA privs) and do 'select * from v$parameter;'
    Dont have DBA privs select only with very limited extra's besides the "default tables" and some others like USER_TABLES and alike


    Quote Originally Posted by AlanP
    To see when your tables where last analysed do 'select * from dba_tables' (or (user_tables) and check the last_analyzed column.
    Just under 1 month ago
    Regards

Posting Permissions

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