SELECT, UPDATE and ressource usage
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