Hello Chris,
pretty hard to say without knowing the exact queries, the object definitions
and the covered ranges by the predicates but you might want to consider index
joins or just plain btree/bitmap conversion incl. OR/AND BITMAP if the queries
are very volatile and got different predicates all the time.
You can still support DMLs with a lot of btree indexes but have the advantage
of bitmaps (of course with the overhead of more CPU usage due to on-the-fly
conversion). However please be aware that the DMLs get slower with more btree
indexes but based on your description that shouldn't be a big deal here.
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: @OracleSK
Chris Stephens <cstephens16@xxxxxxxxx> hat am 25. Januar 2019 um 17:10--
geschrieben:
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