Re: Could I benefit from a materialized view?
From: Martin <martin.j.evans_at_gmail.com>
Date: Tue, 1 Mar 2011 01:01:39 -0800 (PST)
Message-ID: <89f6fcc2-feaa-43b4-84c2-1a83ab48a2b1_at_d12g2000vbz.googlegroups.com>
On Feb 26, 1:22�am, Tim X <t..._at_nospam.dev.null> wrote:
> Martin <martin.j.ev..._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
Date: Tue, 1 Mar 2011 01:01:39 -0800 (PST)
Message-ID: <89f6fcc2-feaa-43b4-84c2-1a83ab48a2b1_at_d12g2000vbz.googlegroups.com>
On Feb 26, 1:22�am, Tim X <t..._at_nospam.dev.null> wrote:
> Martin <martin.j.ev..._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
Thanks Tim. I've not used materialized views before and read an article which suggested they might be worth looking at. I am still experimenting and appreciate your response.
Martin Received on Tue Mar 01 2011 - 03:01:39 CST