SEO

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.

The NOLOGGING clause used when creating a table appears from the doc I
read to state that its creation won't be recorded in the redo logs and
that subsequent "direct loader (sql loader) and direct path INSERT
operations against the object" will not be logged. Does this mean that a
regular

insert into mycache(id,val) values (1,'fred');

would be logged?

I cannot use a GLOBAL TEMPORARY table as the data will not be retained
outside of the session (as ours is a web app over http a new session is
started for every new screen displayed).

Also wondering if there are differences between 9i and 10g (this will go
out on 9i first).

Thanks for any pointers.

--
jeremy

================================================== ==========
ENVIRONMENT:
Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8
================================================== ==========

On Sat, 16 Sep 2006 09:19:58 +0100, Jeremy <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.
>

Sounds like a recipe for disaster.

>I wanted to design this, if possible, so that there is no redo generated
>for DML performed on this table.
>

You can't. There is an underdocumented option to turn of logging
*completely*, but this will corrupt your database.

>The NOLOGGING clause used when creating a table appears from the doc I
>read to state that its creation won't be recorded in the redo logs and
>that subsequent "direct loader (sql loader) and direct path INSERT
>operations against the object" will not be logged. Does this mean that a
>regular
>
> insert into mycache(id,val) values (1,'fred');
>
>would be logged?

It sure does just mean that. And an INSERT /*+ APPEND */ would turn
all indexes invalid
>
>I cannot use a GLOBAL TEMPORARY table as the data will not be retained
>outside of the session (as ours is a web app over http a new session is
>started for every new screen displayed).

Sounds like you *can* use a global temporary table. In a temporary
table you can destroy your data over a commit.

>
>Also wondering if there are differences between 9i and 10g (this will go
>out on 9i first).


9i is dead after July 2007. I think you should reconsider.
Apart from that, any new major release of Oracle comes with a manual
called 'New Features Manual' and several upgrade courses.
>
>Thanks for any pointers.
--
Sybrand Bakker, Senior Oracle DBA

In article <3reng2dh8jd5lanpp527b7lja403vem5lt@4ax.com>, Sybrand Bakker
says...
> On Sat, 16 Sep 2006 09:19:58 +0100, Jeremy <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.
> >
>
> Sounds like a recipe for disaster.
>
Why? We basicaly want to preserve a result set and allow the user to do
things with it via the browser (e.g. column sort) without havin to re-
execute the original query.

> >I wanted to design this, if possible, so that there is no redo generated
> >for DML performed on this table.
> >
>
> You can't. There is an underdocumented option to turn of logging
> *completely*, but this will corrupt your database.

OK, no problem.
>
> >The NOLOGGING clause used when creating a table appears from the doc I
> >read to state that its creation won't be recorded in the redo logs and
> >that subsequent "direct loader (sql loader) and direct path INSERT
> >operations against the object" will not be logged. Does this mean that a
> >regular
> >
> > insert into mycache(id,val) values (1,'fred');
> >
> >would be logged?
>
> It sure does just mean that. And an INSERT /*+ APPEND */ would turn
> all indexes invalid


OK thanks

> >
> >I cannot use a GLOBAL TEMPORARY table as the data will not be retained
> >outside of the session (as ours is a web app over http a new session is
> >started for every new screen displayed).
>
> Sounds like you *can* use a global temporary table. In a temporary
> table you can destroy your data over a commit.

I don't think so - there is a new session for each interaction with the
user (over http) and consequently the data will be lost. Or have I
misunderstood? I want the data to persist across sessions.


>
> >Also wondering if there are differences between 9i and 10g (this will go
> >out on 9i first).
>
>
> 9i is dead after July 2007. I think you should reconsider.
> Apart from that, any new major release of Oracle comes with a manual
> called 'New Features Manual' and several upgrade courses.

Thanks, I should have said my question re the new features was only with
regard to this specific question regqarding NOLOGGING

--
jeremy

As I understand the thread until now, you are worried
about logging - why?
Also, you want to retain data over web sessions. Beats me
as to why, but it's *your* requirement. So, you
need a table. And you need some identifier over the
web sessions in order to distinguish one returning
user from the other.

Again: what's wrong with logging?
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?

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)
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

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

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. The OP sounds like someone who just starts
'somewhere' and continues to hack code until it 'works'.
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.

--
Sybrand Bakker, Senior Oracle DBA

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

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.

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.

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?
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

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
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

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.
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

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