RE: Creating unique index on huge table.

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 24 Sep 2016 09:30:22 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9015033FD1C_at_exmbx05.thus.corp>

Single column unique.

Not all queries are for single row by primary key. Queries for ranges of primary key need to estimate the number of rows in the range; histograms can describe gaps in ranges, not just frequency of values. I'll post a blog note on Monday.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: Patrick Jolliffe [jolliffe_at_gmail.com] Sent: 24 September 2016 08:21
To: Jonathan Lewis
Cc: oracle-l_at_freelists.org
Subject: Re: Creating unique index on huge table.

Jonathan,
Any chance you could clarify your statement? My initial assumption would be that as column is unique a histogram WOULD be unnecessary. Thinking about it I guess that a histogram on unique column might be useful for CBO to know whether a particular value exists for the column rather than how many values (because that will always be one). Although I imagine in this case, as the number of unique values is likely to be huge, it would be height-balanced histogram and hence the information available would likely be not particularly useful. Or are you saying that if it is a multi-column index, that although the combination of columns in the index is unique, the individual columns will not be? Regards
Patrick

On 24 September 2016 at 14:16, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> wrote:

Just for the record - uniqueness doesn't automatically mean that a histogram will be unnecessary; moreover if a histogram on a unique column is necessary Oracle will detect it build it during a default "gather_table_stats".

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Mladen Gogala [gogala.mladen_at_gmail.com<mailto:gogala.mladen_at_gmail.com>] Sent: 24 September 2016 02:55
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: Re: Creating unique index on huge table.

And the data distribution for the unique index is trivial and does not need statistics. Hopefully, you didn't create histograms on the unique index?

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

Received on Sat Sep 24 2016 - 11:30:22 CEST