optimizer OR transformation

From: "LS Cheng" <exriscer@xxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 1 Aug 2006 16:01:53 +0200

Hi

I have some queries with ORs in 8i which ahd this plan

  0      SELECT STATEMENT Optimizer=RULE
  1    0   SORT (ORDER BY)
  2    1     TABLE ACCESS (BY INDEX ROWID) OF 'S_LST_OF_VAL'
  3    2       INDEX (RANGE SCAN) OF 'S_LST_OF_VAL_U2' (UNIQUE)


in 9i this changed to

  0      SELECT STATEMENT Optimizer=RULE
  1    0   SORT (ORDER BY)
  2    1     CONCATENATION
  3    2       TABLE ACCESS (BY INDEX ROWID) OF 'S_LST_OF_VAL'
  4    3         INDEX (RANGE SCAN) OF 'S_LST_OF_VAL_U1' (UNIQUE)
  5    2       TABLE ACCESS (BY INDEX ROWID) OF 'S_LST_OF_VAL'
  6    5         INDEX (RANGE SCAN) OF 'S_LST_OF_VAL_U1' (UNIQUE)
  7    2       TABLE ACCESS (BY INDEX ROWID) OF 'S_LST_OF_VAL'
  8    7         INDEX (RANGE SCAN) OF 'S_LST_OF_VAL_U1' (UNIQUE)
  9    2       TABLE ACCESS (BY INDEX ROWID) OF 'S_LST_OF_VAL'
 10    9         INDEX (RANGE SCAN) OF 'S_LST_OF_VAL_U1' (UNIQUE)

I was wondering which parameter (hidden of course) affects this change of
plans?

I have checked _or_expand_nvl_predicate and _no_or_expansion but doesnt seem
they are affceting the behaviour.

Using RBO by the way.

Thanks

optimizer OR transformation

Other related posts: