Re: oracle can ignore hints

From: Barbara Baker <barbarabbaker_at_yahoo.com>
Date: Fri, 5 Mar 2004 10:05:12 -0800 (PST)
Message-ID: <20040305180512.55178.qmail@web14423.mail.yahoo.com>

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_at_freelists.org 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
-----------------------------------------------------------------

Received on Fri Mar 05 2004 - 12:49:44 CST