| Optimizer [message #59053] |
Sun, 26 October 2003 01:00  |
Sujit Sarkar
Messages: 40
Registered: September 2003
|
Member |
|
|
Hi friends,
can any one help me in the folllowing,
If we use ALL_ROWS as an optimizer hint and the statistics are not gathered.Then will the system use cost base optimization.
|
|
|
|
| Re: Optimizer [message #59057 is a reply to message #59053] |
Sun, 26 October 2003 05:26  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
YES. Pls see below :
thiru@9.2.0:SQL>show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string CHOOSE
-- My optimizer_mode is CHOOSE, which means Oracle will try to use COST based optimization with the goal of best throughput when atleast one of the tables involved in the sql statement is analyzed. In the absence of statistics,it will use RULE optimizer.
-- Lets delete the statistics
thiru@9.2.0:SQL>analyze table t delete statistics;
Table analyzed.
-- we have these indexes..
thiru@9.2.0:SQL>select * from user_ind_columns where table_name='T';
INDEX_NAME TABLE_NAME
-------------------- ------------------------------
COLUMN_NAME
---------------------------------------------------------------------------------------------------------
COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
--------------- ------------- ----------- ----
T_IDX T
EMPNO
1 22 0 ASC
T_ENAME_IDX T
ENAME
1 10 10 ASC
T_CONCAT_IDX T
EMPNO
1 22 0 ASC
T_CONCAT_IDX T
ENAME
2 10 10 ASC
thiru@9.2.0:SQL>set autotrace on explain
thiru@9.2.0:SQL>select ename from t where empno=1000;
ENAME
----------
Victor
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)
-- Oracle uses Rule based optimizer and goes for the Index range scan of T_IDX index.
-- Now, lets hint the optimizer with ALL_ROWS and remember we have no statistics
thiru@9.2.0:SQL>select /*+ ALL_ROWS */ ename from t where empno=1000;
ENAME
----------
Victor
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=1 Card=2 Bytes=40)
1 0 INDEX (RANGE SCAN) OF 'T_CONCAT_IDX' (NON-UNIQUE) (Cost=2 Card=2 Bytes=40)
-- Oracle uses the COST based optimizer ( see the Cost ) ..
-Thiru
thiru@9.2.0:SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
