Re: optimizing SQL's w/ multiple (and variable) range based predicates against very wide table

thanks for all of the suggestions everyone! I will work through them on Monday and report back if/when i discover something that provides adequate performance.

On Sat, Jan 26, 2019 at 11:39 AM Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> Hi Chris!
>
> Before you start testing effective but expensive solutions, like the one
> suggested by Andy Rivenes, you can try compressing the table. You don't
> need "compress for all operations", which requires an advanced
> compression license, a normal compression would do the trick because, as
> you yourself have said, there is practically no DML on the table.
>
> However, at some point, the normal DBA tricks like compressing the table
> will not do it any more and you will have to use in-memory.
>
> Regards
>
> On 1/25/19 11:10 AM, Chris Stephens wrote:
> > Oracle 12.2.0.1 3 node RAC on Centos 7.
> >
> > We have a very wide table (182 columns) with ~400 million rows and a
> > single column surrogate PK. There is very little DML against the table
> > if any.
> >
> > Most of those columns are subject to range based predicates. It is
> > sort of a discovery table open to ad hoc SQL which makes targeted
> > optimization a -challenge-. As you can imagine response time isn't
> > awesome for the vast majority of SQL.
> >
> > The table is currently range partitioned and sub-partitioned on 2
> > columns frequently used in range based filters. There are tons of
> > local indexes.
> >
> > I'm not sure how frequently this partitioning/indexing strategy helps
> > vs hurts (new database to me) but after looking at a few SQL's, i
> > don't think this helps in vast majority of cases.
> >
> > I'm wondering if there are any general approaches to something like this.
> >
> > I've only just begun to look at this but my first thought is to try
> > and figure out groups of columns that often get filtered on together
> > and create some partitioned "skinny" tables with those columns along
> > with the surrogate key. I think that would allow us to optimize for up
> > to 3 range based predicates (partition, subpartition, local index)
> > against each table and join on key. Is that sane?
> >
> > What other options are there?
> >
> > Would inMemory help w/ something like this (we aren't currently
> licensed)?
> >
> > Any feedback is greatly appreciated!
> >
> > Thanks,
> > Chris
> >
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l