SEO

Current State:
Currently we have a system that inserts records directly into a
staging table directly after any of several tables are inserted,
updated or deleted to/from. This is done by invoking the update
functions in every trigger attached to these tables that force changes
to the staging table. This staging table is part of a larger search.

Research:
My first response when seeing the code that creates the staging table
was to as if there was a more elegant way to accomplish the same
result. The existing code works but lacks documentation and the person
who built it left the company without imparting knowledge of this to
some else.

I have explored several different ways to deal with this issue. Some
are unworkable due to how the application layer works. Currently I
have two possibilities, leave it as it is or design a materialized view
that will replace the staging table in question.

I've gone through the process of using the DBMS packages to determine
if the Materialized view is possible and what type of refresh it could
have.

Because I am on an OLTP database the materialized view must be
refreshed prior to the user has accessing the search to account to any
recent changes they have made. I know that I can do an ON_DEMAND
refresh by calling DBMS_MV.REFRESH for the materialized view. I also
know that I have to allow for 2 users going doing a search at the same
time.

Question:
Does the DBMS Materialized view functionality has any issues with being
called at the same time, by 2 different users?

References to the articles/documentation I have read available upon
request.
Thanks for your time!
A.E. Harris

AEH wrote:
> Current State:
> Currently we have a system that inserts records directly into a
> staging table directly after any of several tables are inserted,
> updated or deleted to/from. This is done by invoking the update
> functions in every trigger attached to these tables that force changes
> to the staging table. This staging table is part of a larger search.
>
> Research:
> My first response when seeing the code that creates the staging table
> was to as if there was a more elegant way to accomplish the same
> result. The existing code works but lacks documentation and the person
> who built it left the company without imparting knowledge of this to
> some else.
>
> I have explored several different ways to deal with this issue. Some
> are unworkable due to how the application layer works. Currently I
> have two possibilities, leave it as it is or design a materialized view
> that will replace the staging table in question.
>
> I've gone through the process of using the DBMS packages to determine
> if the Materialized view is possible and what type of refresh it could
> have.
>
> Because I am on an OLTP database the materialized view must be
> refreshed prior to the user has accessing the search to account to any
> recent changes they have made. I know that I can do an ON_DEMAND
> refresh by calling DBMS_MV.REFRESH for the materialized view. I also
> know that I have to allow for 2 users going doing a search at the same
> time.
>
> Question:
> Does the DBMS Materialized view functionality has any issues with being
> called at the same time, by 2 different users?
>
> References to the articles/documentation I have read available upon
> request.
> Thanks for your time!
> A.E. Harris
>

Why not use a view? A materialized view is a table, ultimately. You
can query it, certainly, but if you haven't refreshed it, you will get
old data.
And as readers don't block writers, writers don't block readers, and
readers most certainly do not block readers, you can query anything
you like. Unless you meant something different, of course.
--
Regards,
Frank van Bortel

And in addition the my post: an oracle version might be nice. The latest
versions have the ability to do a refresh on commit.
No calls to DBMS_REFRESH needed.

--
Regards,
Frank van Bortel

The version is 9.2.0.3.0.

The goal for using a materialized view is provide a smaller group of
data for the search to run against so that it doesn't have to do all of
the ugly joins within the search itself.

Because this is a user facing search it needs to be as quick as
possible so creating a subset of records to join against is useful.

If the session had been persistant I would have loaded all the data for
that user into a Global temp table when they logged in and then
updated that table as they created transactions in the system, and then
joined the search criteria against that. However the session isn't
persistant so a Global Temp isn't an option. So I'm left with something
that works but I don't like or a MV which I haven't worked with before.

>And as readers don't block writers, writers don't block readers, and
>readers most certainly do not block readers, you can query anything
>you like. Unless you meant something different, of course.

Hmmm. I guess I'm cautious about what happens if 2 different
procedures, try to insert into the MV at the same time. Will one throw
an error? Will one or both of them succeed etc?

Does that sucessfully clarify things?
Thanks for your time.
A.E. Harris

AEH wrote:
> The goal for using a materialized view is provide a smaller group of
> data for the search to run against so that it doesn't have to do all of
> the ugly joins within the search itself.
>
> Because this is a user facing search it needs to be as quick as
> possible so creating a subset of records to join against is useful.
>
> If the session had been persistant I would have loaded all the data for
> that user into a Global temp table when they logged in and then
> updated that table as they created transactions in the system, and then
> joined the search criteria against that. However the session isn't
> persistant so a Global Temp isn't an option. So I'm left with something
> that works but I don't like or a MV which I haven't worked with before.
>
>
>>And as readers don't block writers, writers don't block readers, and
>>readers most certainly do not block readers, you can query anything
>>you like. Unless you meant something different, of course.
>
>
> Hmmm. I guess I'm cautious about what happens if 2 different
> procedures, try to insert into the MV at the same time. Will one throw
> an error? Will one or both of them succeed etc?
>
> Does that sucessfully clarify things?
> Thanks for your time.
> A.E. Harris
>
I see you did mean something else
yes - two calls to DBMS_REFRESH.REFRESH would be
a (performance) problem. If you even managed to
do the at the same time, one would block the other,
causing a delay.

Non-persistent connections, searches... sounds like
a web application with a search screen.
If so, I'd urge you to look into InterMedia Text.

--
Regards,
Frank van Bortel