We have CBO Cost based Optimizer in oracle which will optimize the query. Do we need to do any settings in oracle in order to enable this?
BI DWH BALA
According to this, if you're on 10g, the cost-based optimizer is the only optimizer:
so it's "enabled" by default.
I also found this, referring to the 8i release:
Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favoured by Oracle and will be desupported in future releases. and Cost Based Optimizer (CBO) - This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources.
So, it seems that you will use CBO in earlier releases than 10g, if you gather stats.
alter system set optimizer_mode=first_rows_10; alter session set optimizer_goal = all_rows; select /*+ first_rows(100) */ from table_nme;
No one has followed this question yet.