Re: identifying candidate columns for histograms

From: Jared Still <jkstill_at_gmail.com>
Date: Wed, 4 Mar 2009 16:09:44 -0800
Message-ID: <bf46380903041609j6b1d0f3al12017284bc51e12_at_mail.gmail.com>

On Wed, Mar 4, 2009 at 6:09 AM, hrishy <hrishys_at_yahoo.co.uk> wrote:

>
>
> Given a table how do i identify programatically if some of the column
> values are skewed and the columns would benefit by generating histograms
> against them.
>

Here's an example of one brute force method, best used against tables you already suspect of skew.

• create a table with skew

drop table skew_detect;

create table skew_detect
as
select * from dba_objects;

• show % of tables by owner

with objcount as (

        select owner, count(*) objcount, round(ratio_to_report(count(*)) over() * 100,2) || '%' skew

        from skew_detect
        group by owner
        order by objcount

)
select owner, o.objcount, skew
from objcount o
order by skew
/

OWNER OBJCOUNT SKEW

---------- ---------- -----------------------------------------
BENCHMARK           3 .01%
TSMSYS              3 .01%
ECO_RPT             3 .01%
...
MY_APP              1 0%
XDB               676 1.37%
ORDSYS           1720 3.48%
PUBLIC          19087 38.58%
SYS             22694 45.87%

46 rows selected.

Jared

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

Received on Wed Mar 04 2009 - 18:09:44 CST