Re: Fixing Performance issue with less selective columns

Two major problems with single table hash clusters

1. (Show-stopper) You shouldn't use them for transaction tables because you're supposed to know a fixed limiting size for the data before you create the cluster
2. Unless you are very lucky with your "fixed size" table you will end up wasting a lot of space if there's even a fairly small amount of skew in the cluster key values, and if you don't have a fairly precise cluster key then you could easily end up with visiting many chained blocks to find the data for a single cluster key.

Added to which they will only give you a performance benefit for one (possibly multi-column) predicate, and increase the cost of inserting the data "randomly" to get that benefit.
Single table hash clusters are good for (relatively) small static lookup tables - where "relative" means that *e.g." a 50MB table is small compared to a 54GB table where you make the primary key the cluster key

Regards
Jonathan Lewis

On Thu, 26 Aug 2021 at 04:04, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> Well, if the database is an OLTP database, I would also consider
> creating a single table hash cluster. True, that would probably require
> quite a large reorganization of the table but only the OP can decide
> whether it's worth the effort or not. After all, such decisions are
> eventually made on the cost/benefit basis.
>
> On 8/25/21 2:14 PM, Lothar Flatz wrote:
> > This scenario cries for bitmap indexes.
> > Bitmap Indexes can deal with "not equal" as well as "is null".
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
> https://dbwhisperer.wordpress.com
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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