Re: suggestion to improve a query

This is a typical case of way too many indexes, which will hurt insert and/or update performance.
For your select:
as you don't have any conditions on the main query, Oracle is forced to use a full table scan.
I would try to remove the first rows hint, chances are you will be getting a merge join or a hash join.
This imply two full table scans but these should be faster than 200000 index lookups (what you have now).
Upon second thought, as you are not interested in the date (in the subquery) the query should read

SELECT /*+ first_rows */ * FROM kundeWdatum A WHERE exists
(SELECT 'x' FROM kundeWdatum B
 WHERE B.kunde_datum > '15-Aug-1999' AND B.kunde_nr = A.kunde_nr )
Standard tip: always try to use exists as much as possible.

Hth,

Sybrand Bakker, Oracle DBA

Tisan Gabriel <de_at_dorner.at> wrote in message news:8b518i$j82$1_at_pollux.ip-plus.net...
> 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