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

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