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

sorry, of course i meant: search criteria are *not *historized. Anybody (like myself) who think this is silly should vote for Franck Pachot's idea on OTN.

Am 26.01.2019 um 11:30 schrieb Lothar Flatz:
> 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