Re: Data Skew

tq wrote:

> I have the following situation -
>
> Table A has 10 rows and the primary key is an sequence generated id.
> The only other column is a 'description' field.
>
> Table B has 10 million records and has an indexed foreign key pointing
> to the table A pk.
>
> The foreign key values in the table B are massively skewed. 95% of the
> records have a single value. And any queries against these tables can
> only use the 'description' column in Table A to limit the records
> produced in the join to table B.
>
> So histograms on the fk are not effective since the limit is on the
> description column. How can I get the optimizer to know the optimal
> access strategy for table B (based on the skew) if the queries can only
> be limited on table A.description?
>
> Thanks.

Build a function based index such that the single value that constitutes 95% of the records is reduced to NULL: And thus not included in the index.

There is a demo of this technique at:
http://www.psoug.org
click on Morgan's Library
click on Indexes
scroll down to Function Based Indexes and look for Tom Kyte's name (because I plagiarized the demo from his live presentation to ViCOUG). Why copy from the rest when you can copy from the best.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)