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

Hi Chris,

Full disclosure, I work for Oracle as a PM for Database In-Memory. Yes, I think Database In-Memory could provide a very good solution for your situation. By populating the table in the column store you effectively “index” all of the columns and with In-Memory Storage Indexes you will probably get good IMCU pruning. If you want to email me directly I would be happy to talk further about this.

Regards,

Andy Rivenes
Product Manager Database In-Memory
email: andy.rivenes_at_oracle.com
twitter: _at_TheInMemoryGuy

> On Jan 25, 2019, at 8:10 AM, Chris Stephens <cstephens16_at_gmail.com> 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
>

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