The parameter OPTIMIZER_INDEX_COST_ADJ sets the cost consumption relationship between the index scan and full meant scan, its default value of 100, indicating that Oracle is calculated when calculating the query cost, and the index lookup and full table scan cost is equal. The smaller the parameter value, the smaller the price of the index lookup, the greater it. The use of this parameter is demonstrated by the following test. 1) View the current value of parameter Optimizer_index_cost_adj
SQL> conn / as sysdba
Connected.
SQL> Show parameter Optimizer_index_cost_adj
Name Type Value
----------------------------------- --- --------
Optimizer_index_cost_adj integer 100
- The current default value of the parameter is 100
SQL>
SQL> SELECT ISSES_MODIFIABLE, ISSYS_MODIFIABLE
2 from V $ Parameter
3 where name = 'Optimizer_index_cost_adj';
ISSESSYS_MOD
----- ---------
True False
- Description This parameter can be dynamically changed at the session level,
But can't change dynamic changes in the System level
2) Create a table and index, and analyze the table
SQL> CONN Scott / Tiger
Connected.
SQL> CREATE TABLE TEST (A Number, B CHAR (10));
Table created.
SQL>
SQL> Begin
2 for i in 1..10000 loop
3 Insert Into Test Values (I, To_Char (i));
4 end loop;
5 commit;
6 End;
7 /
PL / SQL Procedure SuccessFully Completed.
SQL>
SQL> CREATE INDEX IDX_TEST_A On Test (a);
Index created.
SQL>
SQL> Analyze Table Test Compute Statistics
2 for Table
3 for all indexes
4 for all indexed columns;
Table analyzed.
SQL>
3)
Put parameters
Optimizer_index_cost_adj
Set to
100
, Check the corresponding execution plan
SQL> Set Autotrace Traceonly Explain
SQL> ALTER session set Optimizer_index_cost_adj = 100;
Session altered.
SQL> SELECT * from test where a = 1;
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 2 Card = 1 Bytes = 15)
1 0 Table Access (By Index Rowid) of 'Test' (COST = 2 Card = 1 BYT
ES = 15)
2 1 Index (Range Scan) of 'IDX_TEST_A' (Non-Unique) (COST = 1CARD = 1)
In the above part, Oracle uses the correct lookup method that uses the index to find data.
How is the following demonstrate?
Oracle
Use full mete-scan to find data, although we know that this method is not efficient.
4)
Change parameters
Optimizer_index_cost_adj
for
1000
,change
Oracle
Decision process
SQL> ALTER session set optimizer_index_cost_adj = 1000;
Session altered.
SQL> SELECT * from test where a = 1;
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 4 Card = 1 Bytes = 15)
1 0 Table Access (Full) of 'Test' (COST = 4 Card = 1 Bytes = 15)
In the above demo, by changing
Optimizer_index_cost_adj
parameter,
Oracle
The same query is performed using a full mete scan.
5) Summary
in
OLTP
In the system, you can consider
Optimizer_index_cost_adj
The parameter value is small, making the system tend to use the index;
DSS
In the system, it is possible to consider that the parameter is appropriate, affecting
Oracle
Decision process.
