The parameter Optimizer_index_cost_adj sets the cost-consumption relationship between the index scan and the full table scan, with a default value of 100, which means that Oracle calculates the query cost at the same cost as the index lookup and full table scan. The smaller the parameter value, the smaller the cost of searching through the index, and the greater the converse.
Use the following test to demonstrate the usage of this parameter.
1 View the current value of the parameter Optimizer_index_cost_adj
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=1
card=1)
In the above section, Oracle uses the correct lookup method, using indexes to find data.
The following shows how to get Oracle to look up data in a full table scan, although we know this is not a very efficient approach.
4 Change the parameter Optimizer_index_cost_adj to 1000, change Oracle's decision process
Sql> alter session set optimizer_index_cost_adj=1000;
In the above demo, by changing the OPTIMIZER_INDEX_COST_ADJ parameter, Oracle uses a full table scan to perform the same query.
5) Summary
In an OLTP system, it is possible to consider setting the Optimizer_index_cost_adj parameter values small, so that the system tends to use the index, in the DSS system, it is possible to consider adjusting the parameters appropriately to influence the Oracle decision process.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.