SELECT, UPDATE and ressource usage

From: Emmanuel Baechler <ebaechle_at_hospvd.ch>
Date: Mon, 26 Oct 1998 11:05:12 +0100
Message-ID: <36344958.44B0_at_hospvd.ch>



Hi,

[Quoted] I did recently observe two divergent behaviors of one of our ORACLE 7.3 [Quoted] databases that I have difficulties understanding.

This instance contains two tables, CLIENT with around 15000 entries and [Quoted] TRANSACTION_HEADER with around 2'000'000 entries. Both table have a [Quoted] CARD_NUMBER field (VARCHAR2(20)). CLIENT has an index on CARD_NUMBER [Quoted] and TRANSACTION_HEADER has an index on two (combined )fields: CARD_NUMBER
[Quoted] and DATE (the date of the transaction).

I had to do a two tests on this database, which were performed during daily operations:
[Quoted] 1) look at all the clients who did not have any transaction:

   select CARD_NUMBER from CLIENT
   minus
[Quoted]    select CARD_NUMBER from TRANSACTION_HEADER    Although this request requires a full table scan of the TRANSACTION_HEADER
[Quoted]    table, the normal use of the database was not perturbated at all.

[Quoted] 2) Evalualte the time required to extend the witdh of the card numbers from

[Quoted]    5 to 6 positions. To do this, I copied a bit less that 5% of the [Quoted]    TRANSACTION_HEADER table into a temporary table TRANSACTION_HEADER_TEMP:
    WHILE DEL >0 LOOP

      update TRANSACTION_HEADER_TEMP:
      set CARD_NUMBER  = '0' || CARD_NUMBER 
      where length(CARD_NUMBER)=5 and rownum <1000;
      DEL := SQL%ROWCOUNT;
      commit;

    END LOOP;     This operation too, requires a full table scan, but on a table that is

[Quoted]     more than 20 smaller than the full TRANSACTION_HEADER table.

[Quoted]     Alhough it was done on a separate table, made exclusively for this test,

[Quoted]     it did however significantly perturbate the daily operations, and the

[Quoted]     test had to be killed.

Now, my question is: how does it come that a SELECT implying a full table
[Quoted] scan on a table of more than two million records does not cause significant
[Quoted] trouble while an update requiring a full table scan of less than 100'000 ones
[Quoted] causes so much trouble?

Any help will be appreciated

Emmanuel Baechler
Lausanne
Switzerland Received on Mon Oct 26 1998 - 11:05:12 CET

Original text of this message