Frits Hoogland Weblog

Yes, I know. We should all be using the CBO. Since oracle version 7.3, or at least since version 8.1.7.

But sometimes it isn’t used. It’s sometimes even troublesome to see the ignorance of the oracle DBA documentation and tuning tips. Sometimes we just have to work with what we get…
(It helps us in our daily whining about bad applications

But now the case:
-Oracle database version 9.2.0.5 (EE)
-RBO (optimizer mode CHOOSE, no statistics)

Two schema’s:
-’A’
-’B’

We got a table in both schema’s: MD_REQUESTS:

MD_REQUEST_ID number(10) not null
CONNECT_EAN varchar2(18) null
REQ_PARTY_EAN varchar2(13) not null
MD_BATCH_ID number(10) null
REASON_ID varchar2(3) null
REQUEST_DATE date(7) null
STATUS_GROUP_ID number(2) null
REQUEST_REJECTED number(1) null
FILTER_DATE date(7) null
REQ_PARTY_REF varchar2(35) null
PROVIDER_EAN varchar2(13) null
PARENT_REQUEST_ID number(10) null


Indexes in schema ‘A’:

PK_MDR md_request unique
MDR_IDX_1 provider_ean,status_group_id,md_request_id unique
MDR_IDX_2 req_party_ean,status_group_id,md_request_id unique
MDR_IDX_3 req_party_ean,md_batch_id,md_request_id unique
MDR_IDX_4 parent_request_id,md_request_id unique
MDR_IDX_5 md_batch_id nonunique
MDR_IDX_6 connect_ean,provider_ean nonunique

Indexes in schema ‘B’:

PK_MDR md_request unique
MDR_IDX_1 provider_ean,status_group_id,md_request_id unique
MDR_IDX_2 req_party_ean,status_group_id,md_request_id unique
MDR_IDX_3 req_party_ean,md_batch_id,md_request_id unique
MDR_IDX_4 parent_request_id,md_request_id unique
MDR_IDX_5 md_batch_id nonunique
MDR_IDX_6 connect_ean,provider_ean nonunique


Now we execute the following query:

SELECT ''
FROM A.md_requests mr
WHERE mr.parent_request_id = 90109886
AND mr.req_party_ean = '8716665189995'
AND mr.md_batch_id IS NOT NULL


Which results in the following execution plan:

Execution Plan
-----------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MD_REQUESTS'
2 1 INDEX (RANGE SCAN) OF 'MDR_IDX_3' (UNIQUE)

(1269512 consistent gets/141903 physical reads)


And we execute:

SELECT ''
FROM B.md_requests mr
WHERE mr.parent_request_id = 90109886
AND mr.req_party_ean = '8716665189995'
AND mr.md_batch_id IS NOT NULL


Which results in the following execution plan:

Execution Plan
-----------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MD_REQUESTS'
2 1 INDEX (RANGE SCAN) OF 'MDR_IDX_4' (UNIQUE)

(4 consistent gets/0 physical reads)


Obviously, the second one (schema B, using index MDR_IDX_4) is more efficient.
If I put a hint ( SELECT /*+ INDEX( mr,mdr_idx_4 ) */ ‘’) in the statement in schema A, it also runs efficient (5 consistent gets/1 physical read)

So, why does the RBO choose a different execution plan?

The RBO uses rules to make the execution plan, rather than the CBO, which calculates cost.

So, following that argument, there must be something that makes the RBO fullfill its need. This is what came up:


select o.obj#, u.name, o.name
from sys.obj$ o, sys.user$ u
where o.owner# = u.user#
and o.type = 1
and o.name like 'MDR_IDX_%'
order by 2,1


Which gave the following output:


OBJ# NAME NAME
---------------------- -------------------------------- ----------------------
17424 A MDR_IDX_4
17489 A MDR_IDX_5
17490 A MDR_IDX_6
17491 A MDR_IDX_1
17493 A MDR_IDX_2
17495 A MDR_IDX_3

17424 B MDR_IDX_1
17425 B MDR_IDX_2
17426 B MDR_IDX_3
17737 B MDR_IDX_4
17738 B MDR_IDX_5
17740 B MDR_IDX_6


The RBO scans possible indexes using the object id (obj#) from the highest number to the lowest number, and uses the first one in that order that fullfills its need!

This means that the order in which indexes are created (strongly) influence the RBO execution path!