Re: Could I benefit from a materialized view?

From: Tim X <timx_at_nospam.dev.null>
Date: Sat, 26 Feb 2011 12:22:54 +1100
Message-ID: <878vx3pp1t.fsf_at_rapttech.com.au>



Martin <martin.j.evans_at_gmail.com> 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
Received on Fri Feb 25 2011 - 19:22:54 CST

Original text of this message