just test optimizer_index_cost_adj

How to use optimizer_index_cost_adj for controlling the Behavior of the Optimizer?
This is something what simples for someone and you can see many articles on internet, in books or etc. However, You should learn, test, and ... by yourself. That is useful for learning.

A optimizer_index_cost_adj(default 100 percent) is parameter what defines the percentage of the cost of an index.

SQL> select product_name
from order_items o, product_information p
where o.unit_price = 15 and quantity = 48
and p.product_id = o.product_id;

PRODUCT_NAME QUANTITY
-------------------------------------------------- ----------
Screws <B.28.S> 48

Execution Plan
----------------------------------------------------------
Plan hash value: 1255158658
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 32 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | ORDER_ITEMS | 1 | 12 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION | 1 | 20 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

The result shows one row, and Execution plan uses INDEX. but After changed SQL (quantity = 48) to (quantity > 1)

SQL> select product_name, quantity
from order_items o, product_information p
where o.unit_price = 15 and quantity > 1
and p.product_id = o.product_id;

PRODUCT_NAME QUANTITY
-------------------------------------------------- ----------
Screws <B.28.S> 48
Screws <B.28.S> 24
Screws <B.28.S> 176
Screws <B.28.S> 39
Screws <B.28.S> 82
Screws <B.28.S> 129
Screws <B.28.S> 17
Screws <B.28.S> 30
Screws <B.28.S> 21
Screws <B.28.S> 12
Screws <B.28.S> 31
Screws <B.28.S> 93
Screws <B.28.S> 97

13 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1553478007
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 128 | 6 (17)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 128 | 6 (17)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| ORDER_ITEMS | 4 | 48 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| PRODUCT_INFORMATION | 288 | 5760 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

A Execution Plan shows "TABLE ACCESS FULL", How to control the behavior of the Optimizer? If, I would like to use INDEX.
optimizer_index_cost_adj = The range of values is 1 to 10000, which means that indexes are evaluated as an access path (index). If you set it much lower than 100 (Execution Plan has a chance to use INDEX), Really?

SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
optimizer_index_cost_adj integer 100

SQL> alter session set optimizer_index_cost_adj=50;

Session altered.

SQL> select product_name, quantity
from order_items o, product_information p
where o.unit_price = 15 and quantity > 1
and p.product_id = o.product_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1255158658
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 128 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 4 | 128 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | ORDER_ITEMS | 4 | 48 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION | 1 | 20 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

*** optimizer_index_cost_adj=50 - that mean index access path look half as expensive as normal ***

This is example for testing. However, this parameter is good idea for someone who spend much time with query query and query.