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

From: Powell, Mark <mark.powell2_at_dxc.com>
Date: Fri, 25 Jan 2019 17:01:10 +0000
Message-ID: <DM6PR01MB391481CE9B27802F6D41F392CE9B0_at_DM6PR01MB3914.prod.exchangelabs.com>

Chris, if there is almost no DML including INSERT of new rows then I would consider Mr Bobak's bitmap index suggestion. Otherwise the multicolumn indexing would seem your best bet.

Mark Powell
Database Administration
(313) 592-5148

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Mark J. Bobak <mark_at_bobak.net> Sent: Friday, January 25, 2019 11:59:08 AM To: cstephens16_at_gmail.com
Cc: oracle-l
Subject: Re: optimizing SQL's w/ multiple (and variable) range based predicates against very wide table

I would consider taking the groups of columns you come up with, but build (partitioned?) multi-column indexes on those groups. Then the queries would be able to (hopefully) select the appropriate index for optimal tuning. I'd stay away from "skinny tables", as even with infrequent DML, I think maintenance would quickly become a nightmare.

If the table really does have no DML (or extremely infrequent DML), then I'd consider bitmap indexes, one per column eligible for predicates, and let the optimizer do it's bitmap operations to come up with an optimal plan.

Just my thoughts...

-Mark

On Fri, Jan 25, 2019 at 11:12 AM Chris Stephens <cstephens16_at_gmail.com<mailto: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

DXC Technology Company - Headquarters: 1775 Tysons Boulevard, Tysons, Virginia 22102, USA. DXC Technology Company -- This message is transmitted to you by or on behalf of DXC Technology Company or one of its affiliates. It is intended exclusively for the addressee. The substance of this message, along with any attachments, may contain proprietary, confidential or privileged information or information that is otherwise legally exempt from disclosure. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient of this message, you are not authorized to read, print, retain, copy or disseminate any part of this message. If you have received this message in error, please destroy and delete all copies and notify the sender by return e-mail. Regardless of content, this e-mail shall not operate to bind DXC Technology Company or any of its affiliates to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the use of e-mail for such purpose. --.

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

Received on Fri Jan 25 2019 - 18:01:10 CET