Re: suggestion to improve a query

On Mon, 20 Mar 2000 12:05:33 +0100, "Tisan Gabriel" <de_at_dorner.at> wrote:

>SELECT /*+ first_rows */ * FROM kundeWdatum A
>WHERE A.kunde_datum =
>(SELECT MIN(B.kunde_datum) AS DATE_MINIMUM FROM kundeWdatum B
>WHERE B.kunde_datum > '15-Aug-1999' AND B.kunde_nr = A.kunde_nr GROUP BY
>B.kunde_nr)
>
>200000 Zeilen ausgewõhlt.
>
> real: 808001
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=26019 Card
> =34698 Bytes=3469800)
>
> 1 0 FILTER
> 2 1 TABLE ACCESS (FULL) OF 'KUNDEWDATUM' (Cost=26019 Card=34
> 698 Bytes=3469800)
>
> 3 1 SORT (GROUP BY NOSORT) (Cost=2 Card=1735 Bytes=52050)
> 4 3 INDEX (RANGE SCAN) OF 'PK_KUNDE' (UNIQUE) (Cost=2 Card
> =1735 Bytes=52050)
>
>But the time is not good(7 minutes). Do you have any suggestions to improve
>the response time of this query ?
>

 ?real: 808001 => 808 seconds ~= 12 minutes <> 7 minutes

Analyze the table. Modify the query to make it uncorrelated. Set sort_area_size, sort_area_retained_size and hash_area_size to 10485760 reduces the time to nearer 1 minute (although I'm using count rather than actually fetching the rows, but I'm counting a non-indexed column to ensure a row fetch occurs):
SQL> alter session set sort_area_size = 10485760;

Session altered.

 real: 0
SQL> alter session set sort_area_retained_size = 10485760;

Session altered.

 real: 60
SQL> alter session set hash_area_size = 10485760;

Session altered.

 real: 50
SQL> select count(kunde_adr_1) from kundeWdatum a   2 where ( a.kunde_nr, a.kunde_datum ) in   3 ( select b.kunde_nr, min(b.kunde_datum)

  4      from  kundeWdatum b
  5      where b.kunde_datum > '15-aug-99'
  6      group by b.kunde_nr 
  7      );

COUNT(KUNDE_ADR_1)

            200000

 real: 63000

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=17762 Card=1 Bytes=4
          7)

   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=17762 Card=198158 Bytes=9313426)
   3    2       VIEW (Cost=10074 Card=198158 Bytes=2972370)
   4    3         SORT (GROUP BY) (Cost=10074 Card=198158 Bytes=594474
          0)

   5    4           INDEX (FAST FULL SCAN) OF 'PK_KUNDE' (UNIQUE) (Cos
          t=1242 Card=776048 Bytes=23281440)

   6    2       TABLE ACCESS (FULL) OF 'KUNDEWDATUM' (Cost=6443 Card=2
          400000 Bytes=76800000)