RE: Optimizer

Hi Robert

For hash joins the optimizer creates the hash table on the smaller row =
source. In this case it's obviously DEPT.

Now, the difference with LIO depends on the number of fetches that are =
performed.=20
Just an example (notice the "set arraysize" statements)...

SQL> set autotrace trace exp stat
SQL> set arraysize 15
SQL> select /*+ ordered */ * from emp, dept where emp.deptno =3D =
dept.deptno;

14336 rows selected.

Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D18 Card=3D14336 =
Bytes=3D817152)
  HASH JOIN (Cost=3D18 Card=3D14336 Bytes=3D817152)
    TABLE ACCESS (FULL) OF 'EMP' (Cost=3D10 Card=3D14336 Bytes=3D530432)
    TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D4 Bytes=3D80)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         98  consistent gets
          0  physical reads
          0  redo size
     607686  bytes sent via SQL*Net to client
      11000  bytes received via SQL*Net from client
        957  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      14336  rows processed

SQL> select /*+ ordered */ * from dept, emp where emp.deptno =3D =
dept.deptno;

14336 rows selected.

Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D13 Card=3D14336 =
Bytes=3D817152)
  HASH JOIN (Cost=3D13 Card=3D14336 Bytes=3D817152)
    TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D4 Bytes=3D80)
    TABLE ACCESS (FULL) OF 'EMP' (Cost=3D10 Card=3D14336 Bytes=3D530432)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1051  consistent gets
          0  physical reads
          0  redo size
     794713  bytes sent via SQL*Net to client
      11000  bytes received via SQL*Net from client
        957  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      14336  rows processed

SQL> set arraysize 5000
SQL> select /*+ ordered */ * from dept, emp where emp.deptno =3D =
dept.deptno;

14336 rows selected.

Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D13 Card=3D14336 =
Bytes=3D817152)
  HASH JOIN (Cost=3D13 Card=3D14336 Bytes=3D817152)
    TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D4 Bytes=3D80)
    TABLE ACCESS (FULL) OF 'EMP' (Cost=3D10 Card=3D14336 Bytes=3D530432)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         98  consistent gets
          0  physical reads
          0  redo size
     718473  bytes sent via SQL*Net to client
        517  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      14336  rows processed



Chris


>-----Original Message-----
>From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Freeman
>Robert - IL
>Sent: 30 September 2004 23:17
>To: 'oracle-l@xxxxxxxxxxxxx '
>Subject: Optimizer
>
>I can do backup and recovery in my sleep.... I can create databases, =
and I
>am not a bad SQL tuning fellow I must say.. But, if there were to be an
>Oracle inqusition, I would have to confess that the optimizer still
>befuddles me sometimes. I have two tables: EMP and DEPT. EMP has 15,000 =
rows
>and DEPT has 1 row. No indexes. Real simple.
>
>I have a simple SQL statement joining these tables:
>
>select a.empid, a.ename, b.dname
>from emp a, dept b
>where a.deptno=3Db.deptno
>and a.empid < 1000;
>
>In playing with this statement, this is the execution path the =
optimizer
>takes:
>
>Execution Plan
>----------------------------------------------------------
>   0      SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D40 Card=3D1000 =
Bytes=3D22000)
>   1    0   HASH JOIN (Cost=3D40 Card=3D1000 Bytes=3D22000)
>   2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D1 =
Bytes=3D10)
>   3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=3D37 Card=3D1000 =
Bytes=3D12000)
>
>Statistics
>----------------------------------------------------------
>          0  recursive calls
>          0  db block gets
>        444  consistent gets
>          0  physical reads
>          0  redo size
>      21517  bytes sent via SQL*Net to client
>       1378  bytes received via SQL*Net from client
>         68  SQL*Net roundtrips to/from client
>          0  sorts (memory)
>          0  sorts (disk)
>        999  rows processed
>
>If I do an ORDERED hint and reverse the join order, I get these =
results:
>
>Execution Plan
>----------------------------------------------------------
>   0      SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D40 Card=3D1000 =
Bytes=3D22000)
>   1    0   HASH JOIN (Cost=3D40 Card=3D1000 Bytes=3D22000)
>   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=3D37 Card=3D1000 =
Bytes=3D12000)
>   3    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D1 =
Bytes=3D10)
>
>
>Statistics
>----------------------------------------------------------
>          0  recursive calls
>          0  db block gets
>        377  consistent gets
>          0  physical reads
>          0  redo size
>      21517  bytes sent via SQL*Net to client
>       1378  bytes received via SQL*Net from client
>         68  SQL*Net roundtrips to/from client
>          0  sorts (memory)
>          0  sorts (disk)
>        999  rows processed
>
>Note that the plan the optimizer chooses results in more consistent =
gets,
>than the plan using the ordered hint does. I would expect that for =
something
>this basic, the optimizer would "get it right" and come up with the =
better
>plan, which the later plan seems to be. Any thoughts on this? Did I =
miss
>something basic in my statistics gathering? I gathered stats for all
>columns, and did 100 buckets for the histograms.
>
>I note that the cost for both plans is the same, so is there some tie
>breaking going on and if so, what are the rules for this tie breaking?
>Or...Is this just a "law of diminishing returns" thing, and the =
difference
>is so slight that Oracle could just go either way? I'm going to add =
more
>rows to both tables and see if that impacts the results....
>
>Thoughts anyone?
>
>RF
>
>
>--
>http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Other related posts: