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

Hi Chris ,

I think for a start you would need a good statistic on which queries are done and how often.
It might be useful to sample typical queries against the table for quite a while. As for the time being, what does col_usage$ tell you? As you already mentioned besides the search conditions the columns selected (projection) are also important. With either index we will always have to retrieve a complete row, which slows you down considerably with lang rows.
If usually only a small subset of columns are retrieved, nothing will beat the columnar format. You can use either in-memory option or exadata hcc format. Both are a no worries concept, where you trade money for brain.;-)
The downside of either would be the cost. A cleverly defined materialized view could help as well for no extra cost. In addition the number of rows retrieved is also important. To support an index based concept, we typically expect less than 1% of the table size. Otherwise I would rather go for a multi row retrieval as partitioned materialized view.
A compromise would be a covering index, which can  be used both ways, but has other downsides.
To research on projection you could simply query historical plans (DBA_HIST_SQL_PLAN). You can also use historical plan as a source to find search criteria. You have to write a small program for reestablishing the plan, since search criteria are historized. You did not mention compression. How about that?

Regards

Lothar

Am 26.01.2019 um 10:55 schrieb Stefan Koehler:
> 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: _at_OracleSK
>
>> Chris Stephens <cstephens16_at_gmail.com> 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
>>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

-- 




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