RE: HISTOGRAMS , CBO and OLTP

How do randomly inmserted keys result in an unbalanced B-TREE index? Please define your use of the term unbalanced. Perhaps I understand the term differently.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford,edu

-----Original Message-----
Sent: Sunday, February 09, 2003 5:44 PM
To: Multiple recipients of list ORACLE-L

A unique index only says that all of the values therein are unique. There is no need to insert the values in a particular order. That being the case the index can become unbalanced requiring a rebuild.

Dick Goulet

Murali Menon <mjgnmenon_at_yahoo.com> wrote on 2/9/03 12:43 pm:

Uniqueness indicates that there are less skewed values correct! Under what circumstances would a skewed column value for a unique column exist.

Another question, what about histograms on indexes itself? If histograms are generated on columns with skewed values, does it matter when the indexes are generated, before or after? Do indexes on these columns need to be rebuilt.

Thanks    

 Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

It's worth mentioning a frequently overlooked point that even if a column has a unique constraint (whether or not declared and/or supported by an index) then if it has a skewed pattern of values the optimiser can use a histogram to optimise a queries that target a range.

(NB The grammar suggests that the word
"not" was intended as the last word of the first line of (b) below).

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L Date: 08 February 2003 10:44

>
>b) The number of distinct/duplicate values should >really be relevant -
>its

whether you will regularly >need to probe a table using column values that are
>skewed in such a way as to have the optimizer make >poor assumptions
>about

their distribution.
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
--------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line
containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).



---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now <P>Uniqueness indicates that there are less skewed values correct! Under what circumstances would a skewed column value for a unique column exist.</P> <P>Another question, what about histograms on indexes itself?&nbsp; If histograms are generated on columns with skewed values, does it matter when the indexes are generated, before or after?&nbsp; Do indexes on these columns need to be rebuilt.</P> <P>Thanks</P> <P>&nbsp;</P> <P>&nbsp;</P> <P>&nbsp;<B><I>Jonathan Lewis &lt;jonathan_at_jlcomp.demon.co.uk&gt;</I></B>
wrote:</P>
<BLOCKQUOTE style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px;
PADDING-LEFT: 5px"><BR>It's worth mentioning a frequently overlooked<BR>point that even if a column has a unique <BR>constraint (whether or not declared and/or<BR>supported by an index) then if it has a<BR>skewed pattern of values the optimiser<BR>can use a histogram to optimise a queries<BR>that target a range.<BR><BR>(NB The grammar suggests that the word <BR>"not" was intended as the last word of the <BR>first line of (b) below).<BR><BR><BR>Regards<BR><BR>Jonathan
Lewis<BR>http://www.jlcomp.demon.co.uk<BR><BR>Coming soon one-day tutorials:<BR>Cost Based Optimisation<BR>Trouble-shooting and Tuning<BR>Indexing Strategies<BR>(see http://www.jlcomp.demon.co.uk/tutorial.html
)<BR><BR>____UK_______March 19th<BR>____USA_(FL)_May 2nd<BR><BR><BR>Next Seminar
dates: <BR>(see http://www.jlcomp.demon.co.uk/seminar.html )<BR><BR>____USA_(CA, TX)_August<BR><BR><BR>The Co-operative Oracle Users' FAQ<BR>http://www.jlcom! p.! demon.co.uk/

*************************
This message has been truncated. The entire message is available on your desktop e-mail client.
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: dgoulet_at_vicr.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from).  You may also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).