Re: identifying candidate columns for histograms

On Wed, Mar 4, 2009 at 6:09 AM, hrishy <hrishys@xxxxxxxxxxx> 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

Other related posts: