Re: suggestion to improve a query

From: Tisan Gabriel <de_at_dorner.at>
Date: 2000/03/23
Message-ID: <8bd4fi$t0k$1@pollux.ip-plus.net>#1/1

Hi !

I analyzed the table and I set the params with the coresponding value as you indicate me.
Then I run the following queries :

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-1999'
  6     group by b.kunde_nr

  7 );

COUNT(KUNDE_ADR_1)

---

            200000

 real: 45936

Execution Plan

---

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

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

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

   6    2       TABLE ACCESS (FULL) OF 'KUNDEWDATUM' (Cost=26019 Card=
          2400000 Bytes=76800000)

Your idea :

SQL> select * 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-1999'
  6     group by b.kunde_nr

  7 );

200000 Zeilen ausgewõhlt.

 real: 551063 (9 minutes) (access from VB -> 5 minutes) Yes it's OK. I think that "real" include the time for display also. The time was decreased, but it is not enaugh.
But I think that is maximum who can get from Oracle. What is your opinion ?

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=85517 Card=198158 By
          tes=27147646)

   1    0   HASH JOIN (Cost=85517 Card=198158 Bytes=27147646)
   2    1     VIEW (Cost=44686 Card=198158 Bytes=2972370)
   3    2       SORT (GROUP BY) (Cost=44686 Card=198158 Bytes=5944740)
   4    3         INDEX (FAST FULL SCAN) OF 'PK_KUNDE' (UNIQUE) (Cost=
          9110 Card=776048 Bytes=23281440)

   5    1     TABLE ACCESS (FULL) OF 'KUNDEWDATUM' (Cost=26019 Card=24
          00000 Bytes=292800000)

With my query the time is long.

SQL> select /*+ first_rows */ * from kundeWdatum a   2 where a.kunde_datum =
  3 (select min(b.kunde_datum) as date_minimum from kundeWdatum b   4 where b.kunde_datum > '15-Aug-1999' and b.kunde_nr=a.kunde_nr   5 group by b.kunde_nr);

200000 Zeilen ausgewõhlt.

 real: 689642 (11 minutes) (access from VB -> 6 minutes)

Execution Plan

---

   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=26019 Card
          =200000 Bytes=24400000)

   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'KUNDEWDATUM' (Cost=26019 Card=20
          0000 Bytes=24400000)

   3    1     SORT (GROUP BY NOSORT) (Cost=4 Card=1 Bytes=30)
   4    3       INDEX (RANGE SCAN) OF 'PK_KUNDE' (UNIQUE) (Cost=4 Card
          =4 Bytes=120)

Received on Thu Mar 23 2000 - 00:00:00 CST