suggestion to improve a query

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

I have a table with customers and I want to enter a new facility to my application : the history of changes in customer's records. I have the following script file :

CREATE TABLE kundeWdatum (

    CONSTRAINT PK_kunde PRIMARY KEY (kunde_nr, kunde_datum) ,

    kunde_nr_s number NOT NULL ,
    kunde_nr varchar (8) NOT NULL ,
    kunde_datum date NOT NULL ,
    kunde_suchbeg varchar (10) NULL ,
    kunde_typ varchar (1) NULL ,
    kunde_tel varchar (15) NULL ,
    kunde_adr_1 varchar (30) NULL ,
    kunde_adr_2 varchar (30) NULL ,
    kunde_adr_3 varchar (30) NULL ,
    kunde_adr_4 varchar (30) NULL ,
    kunde_plz varchar (8) NULL ,
    kunde_bon__text_nr varchar (4) NULL ,
    kunde_ansprpart varchar (15) NULL ,
    kunde__haend_nr varchar (8) NULL ,
    kunde_nr_prov varchar (8) NULL ,
    kunde_verschprio smallint NULL ,
    kunde__firma_nr varchar (2) NULL ,
    kunde_blz varchar (8) NULL ,
    kunde_kto varchar (12) NULL ,
    kunde_bankbez1 varchar (40) NULL ,
    kunde_bankbez2 varchar (40) NULL ,
    kunde_change_cnt int NULL ,
    kunde__preisliste_nr varchar (4) NULL ,
    kunde_mwst_nr varchar (10) NULL ,
    kunde_von_werk varchar (1) NULL ,
    kunde_fax varchar (15) NULL ,

    upsize_ts date NULL
);

CREATE INDEX ansprepartW ON kundeWdatum(kunde_ansprpart);

CREATE INDEX bonitaetW ON kundeWdatum(kunde_bon__text_nr);

CREATE INDEX fuhrpark_reihenfolge_chg_cnW ON kundeWdatum(kunde_change_cnt);

CREATE INDEX kunde_nrW ON kundeWdatum(kunde_nr);

CREATE INDEX kunde_nr_sW ON kundeWdatum(kunde_nr_s);

CREATE INDEX kunde_typW ON kundeWdatum(kunde_nr, kunde_typ);

CREATE INDEX suchbegW ON kundeWdatum(kunde_suchbeg);

CREATE INDEX kunde_datumW ON kundeWdatum(kunde_datum);

CREATE SEQUENCE Wkunde_nr_s_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE CACHE 10; CREATE OR REPLACE PROCEDURE insertXkundeWdatum (insnum in number)   is
    currdatum DATE;
  begin
    <<test>>
    for x in 1..insnum loop
 <<internal>>
 for y in 1..12 loop

       currdatum := ADD_MONTHS('01-Dez-1998',y);
       INSERT INTO kundeWdatum (
        kunde_nr_s ,

  kunde_nr ,
  kunde_datum ,

      kunde_suchbeg ,
         kunde_tel ,
      kunde_adr_1 ,
      kunde_adr_2 ,
      kunde_adr_3 ,
      kunde_adr_4 ,
      kunde_plz
       )
       VALUES (Wkunde_nr_s_SEQ.NEXTVAL, x, currdatum, 'test',

'124284', 'Pater 11 Lindau', 'stehle 34

Munich', 'Maximstr 1 Augsburg',
'Grimmstrasse 23 Feldheim', 'RO-2875');

       COMMIT;
 end loop internal;

    end loop test;
  end;

To simulate the history I creates 200.000 customers(call procs insertXkundeWdatum(200000)) and for each customer I will have 12 records with different timestamp.
I made the query : all 200.000 customers with the first timestamp greather than 15 August 1999. So that's means that I want just the row of every customer with timestamp 1 September 1999 :

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 ? Received on Mon Mar 20 2000 - 00:00:00 CST