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.
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.
You do not need a parachute to skydive. You only need a parachute to skydive twice.
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...
"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.
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).
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?