Re: oracle can ignore hints

Hmmmm..
kinda interesting:

========================================
7.3.4
========================================

10:56:58 DISCUS:DCTEST>select t1.small_vc, t2.small_vc
10:56:58   2  from  . . .

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3
Card=1 Bytes=66)
   1    0   NESTED LOOPS (Cost=3 Card=1 Bytes=66)
   2    1     TABLE ACCESS (BY ROWID) OF 'T2' (Cost=2
Card=1 Bytes=33)
   3    2       INDEX (UNIQUE SCAN) OF 'T2_PK'
(UNIQUE)
   4    1     TABLE ACCESS (BY ROWID) OF 'T1'
   5    4       INDEX (UNIQUE SCAN) OF 'T1_PK'
(UNIQUE)

10:56:58 DISCUS:DCTEST>select
10:56:58   2   /*+ ordered index(t1 t1_junk) */
10:56:58   3   t1.small_vc, t2.small_vc
10:56:58   4  from . . .

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=72
Card=1 Bytes=66)
   1    0   NESTED LOOPS (Cost=72 Card=1 Bytes=66)
   2    1     TABLE ACCESS (BY ROWID) OF 'T1' (Cost=71
Card=1 Bytes=33)
   3    2       INDEX (RANGE SCAN) OF 'T1_JUNK'
(NON-UNIQUE)
   4    1     TABLE ACCESS (BY ROWID) OF 'T2'
   5    4       INDEX (UNIQUE SCAN) OF 'T2_PK'
(UNIQUE)

===============================
8.1.7.4
===============================

11:00:47 ADMARC:AMTEST>select t1.small_vc, t2.small_vc
11:00:47   2  from . . .

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T2'
   3    2       INDEX (UNIQUE SCAN) OF 'T2_PK'
(UNIQUE)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'T1'
   5    4       INDEX (UNIQUE SCAN) OF 'T1_PK'
(UNIQUE)



11:00:47 ADMARC:AMTEST>select
11:00:47   2   /*+ ordered index(t1 t1_junk) */
11:00:47   3   t1.small_vc, t2.small_vc
11:00:47   4  from . . .

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE (Cost=73
Card=1 Bytes=28)
   1    0   MERGE JOIN (CARTESIAN) (Cost=73 Card=1
Bytes=28)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T1'
(Cost=72 Card=1 Bytes=15)
   3    2       INDEX (RANGE SCAN) OF 'T1_JUNK'
(NON-UNIQUE) (Cost=15 Card=1)
   4    1     SORT (JOIN) (Cost=1 Card=1 Bytes=13)
   5    4       TABLE ACCESS (BY INDEX ROWID) OF 'T2'
(Cost=1 Card=1 Bytes=13)
   6    5         INDEX (UNIQUE SCAN) OF 'T2_PK'
(UNIQUE)






__________________________________
Do you Yahoo!?
Yahoo! Search - Find what you?re looking for faster
http://search.yahoo.com
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: