Re: Avoiding generating redo logs

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 16 Sep 2006 23:06:58 -0700
Message-ID: <1158473216.453601@bubbleator.drizzle.com>

Sybrand Bakker wrote:
> On 16 Sep 2006 15:11:41 -0700, "Martin T."
> <bilbothebagginsbab5_at_freenet.de> wrote:
>

>> DA Morgan wrote:
>>> Frank van Bortel wrote:
>>>> DA Morgan schreef:
>>>>
>>>>> My recommendation would be that you use global temporary tables to cache
>>>>> the data as in the following:
>>>>>
>>>>> CREATE GLOBAL TEMPORARY TABLE test (
>>>>> zip_code     VARCHAR2(5),
>>>>> by_user      VARCHAR2(30),
>>>>> entry_date   DATE)
>>>>> ON COMMIT PRESERVE ROWS;
>>>> GTT in combination with mod_plsql is not working.
>>>> Also see:
>>>> http://asktom.oracle.com/pls/ask/f?p=4950:8:18361359189545016282::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:446620083639
>>>>
>>>> http://vanbortel.blogspot.com/2006/07/authentication-in-modplsql.html
>>>>
>>>>> This is likely the most efficient structure for your purposes.
>>>>>
>>>>> But I am left wondering why you think it desirable to let an end user
>>>>> sort and filter anything. Wouldn't it be far easier to just teach them
>>>>> how to use the WHERE and ORDER BY clauses correctly?
>>>> It is not uncommon in a web application to have those nice little
>>>> triangles on top of a column, indicating sort order, and the
>>>> possibility to change the sort order
>>> Didn't see that requirement before.
>>>
>>> My feeling, as already stated, is that OP is wasting time as filter and
>>> sorting should be done by the SELECT statement.
>>>
>> As far as I interpret it, that's exactly what the OP is trying to to -
>> namely sorting a temporary table of some kind because the original
>> query takes too long to execute it again just to sort.
>>
>> cheers,
>> Martin

>
> That may be true, but IMO that is not fixing the problem, but fighting
> symptoms. If people start worrying about redo log there usually is
> something fundamentally wrong. Usually with their design and with
> their knowledge of Oracle. Probably the OP is from a sqlserver
> background, where a temporary table is a panacea for real query
> writing, and he is introducing all of his bad habits in his Oracle
> application.
>
> --
> Sybrand Bakker, Senior Oracle DBA

Sadly I must agree. It is one thing to worry about redo when tuning to enhance performance and scalability. Another thing to be trying to eliminate it completely before the first line of code is written.

Generally this is, as Sybrand states, a sign of bringing habits from another RDBMS into Oracle and not having spent time reading the fine books Tom Kyte, Jonathan Lewis, and others have provided.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

Received on Sun Sep 17 2006 - 01:06:58 CDT