## Message Posted: Fri, 19 Dec 2003 @ 10:47:45 GMT

 < Last>>

 Subj: Re: can anyone help me out here From: Narayan Murthy

Using NUSI or not is a question which the optimizer would try resolving based on how many datablocks would get accessed. Since it is a nusi we aren't considered about how many amps gets accessed because it is an all-amp operation. So though we have a NUSI defined, if the optimizer finds out that accessing rows for a particular NUSI value would more or less cover all the base table datablocks(a full table scan) then it would be better of for it to go for a full table scan, because a NUSI access always involves first accessing the NUSI subtable and then fetching the base table rows.

So if a column(s) is to be considered for a NUSI one should considered to what extent the column(s) tends towards containing less number of rows per NUSI value.

An Example :

10000 rows each of size 20 bytes.

Size of a datablock = 2000 bytes

# of rows/datablock = 2000/20 = 100 rows/datablock

# of datablocks = 10000/100 = 100 datablocks.

Suppose the NUSI had 10 distinct values and there is an equal distribution of each of these values. Then :

# of rows per NUSI value = 10000/10 = 1000 rows - (A)

So considering 1000 rows/NUSI value and 100 datablocks - the likehood of scanning all the base table datablocks for a given NUSI value is more - A FULL TABLE SCAN. Here not only would you land up doing a full table scan of the base subtable but you would also be accessing the NUSI subtable datablocks. So in such case a FULL TABLE SCAN would be better than a NUSI access.

Thereby according to Equation (A) if the number of rows per NUSI value where to decrease then it is less likely we would land up scanning all the base table datablocks and thereby a NUSI access would work out better than a full table scan.

Narayan

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2003 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback