Why Statistics doesn't match reponse time?

From: <wangbinlxx_at_gmail.com>
Date: 8 Sep 2005 16:54:41 -0700
Message-ID: <1126223681.362757.321590@o13g2000cwo.googlegroups.com>

I test the following two queries in my 9.2.0.3 database. The response time
is consistent.

select /*+ index(cl Total_contributionlog_idx) */ jackpotpool_id,

       max(contributionlog_id) as contributionlog_id,
       max(cl.datecreated) as datecreated,
       max(jackpotPoolCurrentTotal) as jackpotPoolCurrentTotal

from contributionlog cl, pool p
where cl.jackpotpool_id=p.pool_id
group by jackpotpool_id;
8 rows selected.
Elapsed: 00:00:00.50

select jackpotpool_id,

           max(contributionlog_id) as contributionlog_id,
           max(cl.datecreated) as datecreated,
           max(jackpotPoolCurrentTotal) as jackpotPoolCurrentTotal

    from contributionlog cl, pool p
    where cl.jackpotpool_id=p.pool_id
    group by jackpotpool_id;

8 rows selected.

Elapsed: 00:00:02.09

When I turn on the autotrace, this is the result.

Hint:
Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2477 Card=1789 Bytes=25046)

1 0 SORT (GROUP BY) (Cost=2477 Card=1789 Bytes=25046)

   2    1     NESTED LOOPS (Cost=2461 Card=8351 Bytes=116914)
   3    2       INDEX (FULL SCAN) OF 'PK_POOL' (UNIQUE) (Cost=1 Card=8
Bytes=32)
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'CONTRIBUTIONLOG'
(Cost=308 Card=1044 Bytes=10440)
   5    4         INDEX (RANGE SCAN) OF 'TOTAL_CONTRIBUTIONLOG_IDX'

(NON-UNIQUE) (Cost=1 Card=1044)

Statistics

          0  recursive calls
          0  db block gets
     137605  consistent gets
          0  physical reads
          0  redo size
        590  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          8  rows processed

No hint:
Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1505 Card=8 Bytes=112)

1 0 SORT (GROUP BY) (Cost=1505 Card=8 Bytes=112)

   2    1     NESTED LOOPS (Cost=1489 Card=8351 Bytes=116914)
   3    2       TABLE ACCESS (FULL) OF 'CONTRIBUTIONLOG' (Cost=1487
Card=1867580 Bytes=18675800)
   4    2       INDEX (UNIQUE SCAN) OF 'PK_POOL' (UNIQUE)

Statistics

          0  recursive calls
          0  db block gets
      15462  consistent gets
          0  physical reads
          0  redo size
        590  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          8  rows processed

The index Total_contributionlog_idx is on contributionlog(jackpotpool_id,
jackpotPoolCurrentTotal).

>From Execution Plan and response time, I believe the first query is
better.
select count(*)
from (select distinct jackpotpool_id from contributionlog); COUNT(*)

1789

select count(*) from pool;
COUNT(*)

However, I have two questions.
1. Why CBO cannot find the better one, provided that both tables have been analyzed?
2. Why statistics of query 2 looks better than query 1?

query 1 with hint:          137605  consistent gets
query 2 withiout hint:       15462  consistent gets

Kind Regards,
Bin Received on Thu Sep 08 2005 - 18:54:41 CDT