Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How hints affect the CBO?

Re: How hints affect the CBO?

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Tue, 2 Jul 2002 11:33:26 +0100
Message-ID: <3d218176$0$8506$ed9e5944@reading.news.pipex.net>


"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:afrqnd$l91$1_at_babylon.agtel.net...

> Well, without an index use_nl() doesn't make sense at
> all, so, even though it would be semantically correct,
> it would also be plain dumb thing to do as it results
> in an FTS of T2 for each row from T1. :)

It depends on the data and the stats. The (highly contrived) example below illustrates this. there are no indexes on either table but the nested loops plan is more efficient because of the daft differences in table size.

SQL> desc nl_objects;

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
----
 OWNER                                     NOT NULL VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(18)
 CREATED                                   NOT NULL DATE
 LAST_DDL_TIME                             NOT NULL DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL> desc nl_objects2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
----
 OWNER                                     NOT NULL VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(18)
 CREATED                                   NOT NULL DATE
 LAST_DDL_TIME                             NOT NULL DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL> select count(*) from nl_objects;

  COUNT(*)


    232681

Elapsed: 00:00:04.00

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (AGGREGATE)
   2 1 TABLE ACCESS (FULL) OF 'NL_OBJECTS' SQL> select count(*) from nl_objects2;

  COUNT(*)


         1

Elapsed: 00:00:00.00

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (AGGREGATE)
   2 1 TABLE ACCESS (FULL) OF 'NL_OBJECTS2' SQL> select t1.object_name,t2.object_type   2 from nl_objects t1,nl_objects2 t2
  3 where t1.object_id=t2.object_id;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------

/1005bd30_LnkdConstant JAVA CLASS
/1005bd30_LnkdConstant JAVA CLASS
/1005bd30_LnkdConstant JAVA CLASS
/1005bd30_LnkdConstant JAVA CLASS
/1005bd30_LnkdConstant JAVA CLASS
/1005bd30_LnkdConstant JAVA CLASS
/1005bd30_LnkdConstant JAVA CLASS
/1005bd30_LnkdConstant JAVA CLASS

8 rows selected.

Elapsed: 00:00:10.02

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 MERGE JOIN

   2    1     SORT (JOIN)
   3    2       TABLE ACCESS (FULL) OF 'NL_OBJECTS2'
   4    1     SORT (JOIN)
   5    4       TABLE ACCESS (FULL) OF 'NL_OBJECTS'



SQL> select /*+ use_nl(t1) */ t1.object_name,t2.object_type   2 from nl_objects t1,nl_objects2 t2
  3* where t1.object_id=t2.object_id
SQL> /

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------

/1005bd30_LnkdConstant JAVA CLASS
/1005bd30_LnkdConstant JAVA CLASS
/1005bd30_LnkdConstant JAVA CLASS
/1005bd30_LnkdConstant JAVA CLASS
/1005bd30_LnkdConstant JAVA CLASS
/1005bd30_LnkdConstant JAVA CLASS
/1005bd30_LnkdConstant JAVA CLASS
/1005bd30_LnkdConstant JAVA CLASS

8 rows selected.

Elapsed: 00:00:04.06

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=616 Card=1 Bytes=54)    1 0 NESTED LOOPS (Cost=616 Card=1 Bytes=54)

   2    1     TABLE ACCESS (FULL) OF 'NL_OBJECTS2' (Cost=2 Card=1 Byte
          s=24)

   3    1     TABLE ACCESS (FULL) OF 'NL_OBJECTS' (Cost=614 Card=1 Byt
          es=30)




SQL> spool off. Received on Tue Jul 02 2002 - 05:33:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US