Re: SELECT, UPDATE and ressource usage
Date: Tue, 27 Oct 1998 05:30:53 GMT
Message-ID: <713lqe$9pu$1_at_nnrp1.dejanews.com>
In article <36344958.44B0_at_hospvd.ch>,
ebaechle_at_hospvd.ch wrote:
> Hi,
>
> I did recently observe two divergent behaviors of one
of our ORACLE 7.3
> databases that I have difficulties understanding.
>
> This instance contains two tables, CLIENT with around
15000 entries and
> TRANSACTION_HEADER with around 2'000'000 entries. Both
table have a
> CARD_NUMBER field (VARCHAR2(20)). CLIENT has an index
on CARD_NUMBER
> and TRANSACTION_HEADER has an index on two (combined
)fields:
> CARD_NUMBER
> and DATE (the date of the transaction).
>
> I had to do a two tests on this database, which were
performed during
> daily operations:
> 1) look at all the clients who did not have any
transaction:
> select CARD_NUMBER from CLIENT
> minus
> select CARD_NUMBER from TRANSACTION_HEADER
> Although this request requires a full table scan of
the
> TRANSACTION_HEADER
> table, the normal use of the database was not
perturbated at all.
>
> 2) Evalualte the time required to extend the witdh of
the card numbers
> from
> 5 to 6 positions. To do this, I copied a bit less
that 5% of the
> 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
> more than 20 smaller than the full
TRANSACTION_HEADER table.
>
> Alhough it was done on a separate table, made
exclusively for this
> test,
> it did however significantly perturbate the daily
operations, and
> the
> test had to be killed.
>
> Now, my question is: how does it come that a SELECT
implying a full
> table
> scan on a table of more than two million records does
not cause
> significant
> trouble while an update requiring a full table scan of
less than 100'000
> ones
> causes so much trouble?
>
> Any help will be appreciated
>
> Emmanuel Baechler
> Lausanne
> Switzerland
>
Hi,
I always avoid using PL/SQL whenever I can achieve my
goal using SQL statements. So, in your case I would do
the following:
CREATE TABLE transaction_header_temp AS
SELECT col1, col2, ....
DECODE(length(card_number), 5, '0'||card_nmber,
card_number)
card_number, col4, col5...
FROM transaction_header
WHERE rownum <= 100000;
The select statement will perform a full table scan of transaction_header because the index is not used in the where clause.
So, after executing this DDL statetment, you'll end up with a table (transaction_header_temp) of 100,000 records and with a card_number = '0XXXXX' for all card_numbers of length 5.
Now, if this is what you want, you can make the above DDL statement run even faster under the following two conditions:
1- if you do not care about restoring
transaction_header_temp after a media failure you can change the DDL to look like this:
CREATE TABLE transaction_header_temp URECOVERABLE AS .....
the addition of unrecoverable will bypass the generation of redo/rollback data, thus increasing the speed of execution, but you will not be able to restore the table after any database failure.
2- If you have a multiprocessor server then maybe you may consider increasing the parallel degree on transaction_header, resulting in a faster full table scan of that table.
[Quoted] I hope that I presented a good solution to your problem or question. Looking forward to hearing from you again.
Good Luck.
-- Edward Awad Oracle Developer -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your OwnReceived on Tue Oct 27 1998 - 06:30:53 CET