Could I benefit from a materialized view?

Hi,

I have Oracle 11g r1 and applications running on remote machines using
InstantClient. These applications read the same table repeatedly as
part of a function which performs a calculation. The table in question
is small with a primary key and is unlikely to get any more than 175
rows (it is 8 columns 3 numbers, 3 timestamps and 2 varchar(128)). I
would want a refresh on commit but believe if I create the log table
properly I'll get that.

Could I benefit from a materialized view?

Thanks

Martin writes:

> Hi,
>
> I have Oracle 11g r1 and applications running on remote machines using
> InstantClient. These applications read the same table repeatedly as
> part of a function which performs a calculation. The table in question
> is small with a primary key and is unlikely to get any more than 175
> rows (it is 8 columns 3 numbers, 3 timestamps and 2 varchar(128)). I
> would want a refresh on commit but believe if I create the log table
> properly I'll get that.
>
> Could I benefit from a materialized view?
>

Given the table is small with few columns and only a couple of hundred
records, I doubt a materialised view would give you any real performance
gain. I tend to use such techniques where you are doing a similar query
involving multiple tables or queries from a table with many columns, but
most of your queries only involve a smaller subset of the full list of
columns.

Assuming your queries are using bind variables and are getting cached
efficiently and if your queries are reasonably similar and tnings are
ocnfigured correctly, I would not be surprised if most of the data is
coming out of cached memory already (the table and number of rows is
very small). If you have a performance problem, more than likely it is
something like network overhead rather than db query processing. If this
is the case, optimisations at the db engine level will not buy you
anything.

Tim

--
tcross (at) rapttech dot com dot au