| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How hints affect the CBO?
"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
 |
 |