On 16 Sep 2006 15:11:41 -0700, "Martin T."
<bilbothebagginsbab5@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...A:446620083639
>> >
>> > http://vanbortel.blogspot.com/2006/0...-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

Sybrand Bakker wrote:
> On 16 Sep 2006 15:11:41 -0700, "Martin T."
> <bilbothebagginsbab5@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...A:446620083639
>>>>
>>>> http://vanbortel.blogspot.com/2006/0...-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

On Sat, 16 Sep 2006, jeremy0505@gmail.com wrote:
> Environment as in sig.
>
> I am a little puzzled about the logging_clause when creating a
> table. We are designing a web application which will have use , at
> times, a table to hold a "cache" for a user. For example, a query is
> executed which takes a few seconds. We store the results in the
> "cache" and then the user can sort and filter on that "cache". This
> cache I envisage as a table structure with its PK being the user_id of
> the user logged in. The data in this table is of no lasting value as
> it is just a redult of a query.
>
> I wanted to design this, if possible, so that there is no redo
> generated for DML performed on this table.

Why do you need to worry about redo?

--
Galen Boyer

In article <eegn2k$io8$1@news6.zwoll1.ov.home.nl>, Frank van Bortel
says...
> As I understand the thread until now, you are worried
> about logging - why?

It was just a thought - if the option existed to reduce server activity
then it was perhaps something to take advantage of. It is not a
prerequisite by any means.

> Also, you want to retain data over web sessions. Beats me
> as to why, but it's *your* requirement. So, you
> need a table.

Well think of it like this - a query takes 5 seconds to execute (for
example - i.e. it's a a wait). Results are say 200 rows. You display
this in a browser window, perhaps 20 at a time. You want the user to be
able to click a 'next' button to see the next 20 rows. Instead of re-
executing the query, you simply select rows 21-40 from the table based
on the current 'order by' - results returned almost instantly.

> And you need some identifier over the
> web sessions in order to distinguish one returning
> user from the other.

Yes sure - the DAD is defined so the user has to login using a username
nad password - after that we always know the user who is accessing the
application.

>
> Again: what's wrong with logging?

Absolutely nothing, just exploring/wishing to understand.

> Why not create a table with the nologging option? Be
> advised, it will not be recoverable in case of mishap.
>
> It seems to me, you worry about potential overhead, where
> you should actually be worried about the efficiency of
> your code.
> Did you put up a test, with your code accessing a session
> table, created with nologging vs. one created with logging?
>
No - not at that stage - just exploring all possibilities.

> Do (because you did not!), and come back with the differences,
> if you are able to measure these consistently. I bet the
> differences are not noticeable (though measurable)
>

Thanks.


--

jeremy

In article <1158408561.269247.293930@k70g2000cwa.googlegroups .com>,
Martin T. says...
> Jeremy wrote:
> > Environment as in sig.
> >
> > (...) (as ours is a web app over http a new session is
> > started for every new screen displayed).
> >
> Really. Really. Really bad idea. All kind of complications will arrise.
> Is it really not possible with your environment to have a HTTP user
> session and an associated DB session?

The environment was as specified in the sig - i.e. apache / mod_plsql -
so this is quite beyond my control

>
> I'd recommend you take a few hours and try to find out how you could
> change the design so that you have a session per user session.
>

It would need to be a very different techynology foundation to support
that.

--

jeremy

In article <9sqng2lqh30vumre927tafg5bs2fkfh0p2@4ax.com>, Sybrand Bakker
says...
> On 16 Sep 2006 05:09:21 -0700, "Martin T."
> <bilbothebagginsbab5@freenet.de> wrote:
>
> >Jeremy wrote:
> >> Environment as in sig.
> >>
> >> (...) (as ours is a web app over http a new session is
> >> started for every new screen displayed).
> >>
> >Really. Really. Really bad idea. All kind of complications will arrise.
> >Is it really not possible with your environment to have a HTTP user
> >session and an associated DB session?
> >
> >I'd recommend you take a few hours and try to find out how you could
> >change the design so that you have a session per user session.
> >
> >cheers,
> >Martin
>
> Good recommendation.

You think so?

> The OP sounds like someone who just starts
> 'somewhere' and continues to hack code until it 'works'.

Based on what - I merely asked a question about whether it was
possible/desirable to suppress generation of redo under certain
circumstances.

> After some time disaster is apparent, but then he has already left the
> company, or the application has been outsourced, and people like
> myself can clean out the mess.
>

What a lot of old rubbish.

--

jeremy

In article <1158411086.860994@bubbleator.drizzle.com>, DA Morgan says...
>
> I am in full agreement with Sybrand and others here. Your business case
> around not generating redo starts with "I want" and that is not a
> business case.

That is not the business case at all - the business case is to provide a
flexible user interface to the users of a system via a web browser. The
question of redo is simply something that occurred to me that perhaps,
in the case where the need to recover data from a particular table in
the event of some kind of failure did not exist, might be
able/recommended to suppress.
>
> 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;
>
> This is likely the most efficient structure for your purposes.

Not when using apache/mod_plsql - a new session is started on every
interaction.

>
> 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?
>

Have you ever used a browser-based system where you the user clicks a
column heading to re-order the resukts into price or date order? You're
right, of course, let's teach the end users about WHERE and ORDER BY and
give them a little field into which they can type their own SQL
statements which we execute dynamically. Brilliant.


--

jeremy

In article <uejua5rr0.fsf@rcn.com>, Galen Boyer says...

> Why do you need to worry about redo?
>
>
Perhaps I don't - see other responses.

--

jeremy

In article <edlpg2loldb21qs39ro694pktc03l81pd0@4ax.com>, Sybrand Bakker
says...
> On 16 Sep 2006 15:11:41 -0700, "Martin T."
> <bilbothebagginsbab5@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...A:446620083639
> >> >
> >> > http://vanbortel.blogspot.com/2006/0...-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.

I am aware that excuting DML generates redo. The system I am working on
has 100s of users and I was contemplating that, if we used this "cache"
table approach, it would generate a lot of deletions and insertions.


> 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.
>

Speculation - 100% wrong. I know about Oracle temporary tables and have
used them to good effect in the past.

--

jeremy

Jeremy wrote:
> In article <1158411086.860994@bubbleator.drizzle.com>, DA Morgan says...
> >
> > I am in full agreement with Sybrand and others here. Your business case
> > around not generating redo starts with "I want" and that is not a
> > business case.
>
> That is not the business case at all - the business case is to provide a
> flexible user interface to the users of a system via a web browser. The
> question of redo is simply something that occurred to me that perhaps,
> in the case where the need to recover data from a particular table in
> the event of some kind of failure did not exist, might be
> able/recommended to suppress.
> >
> > 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;
> >
> > This is likely the most efficient structure for your purposes.
>
> Not when using apache/mod_plsql - a new session is started on every
> interaction.
>

I had the impression (may be wrong here) that you are in an early-ish
phase of the project/implementation.
Let me just repeat that it may well be worthwhile to look at expanding
the technologies used if that means you're application could be
implemented better. (And if you tell "them" that if you are allowed to
use X we can do THIS(!) ... "they" are often more flexible as one had
initially thought.)


> >
> > 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?
> >
>
> Have you ever used a browser-based system where you the user clicks a
> column heading to re-order the resukts into price or date order? You're
> right, of course, let's teach the end users about WHERE and ORDER BY and
> give them a little field into which they can type their own SQL
> statements which we execute dynamically. Brilliant.
>

Yeah - I also had a good laugh on this ;-)


cheers,
Martin