wangbinlxx_at_gmail.com wrote:
> Hi
>
> 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(*)
> ----------
> 9
>
> 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
I have a question too ... why are you using 9.2.0.3? There have been
four patches since then that have substantially improved the optimizer.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Thu Sep 08 2005 - 19:54:18 CDT
