Re: Shrinking an index??

Hi,
partioned views might be what you are looking for regarding perfomance. But not regarding saving space! All tables in a partioned view must have the exact same definition. This also includes indexes. So each table in the view must have the same indexes thus you wan't save space. But Oracle will only access the table needed (and the index needed) to satisfy a query so the amount of data to access is far less.

Rgds
Steinar Heggelund

Steve Phelan (stevep_at_pmcgettigan.demon.co.uk) wrote:
: Z. Martinez wrote:
: >
: > Here is my situation...
: >
: > I have a large table, a very large table, about one million rows.
: > The table definition goes something like this.
: >
: > id number(10)
: > type number(3)
: > desc char(40)
: > .
: > .
: > .
: >
: > There are several select statements that are going query on this
: > table, but only needs types 5 and 9, i.e type = 5 or type = 9.
: > This is probably just 10% of the entire table or 100,000 rows.
: >
: > There is currently an index that keys on the "type" column.
: > It certainly looks like a waste of storage and processing, cause the
: > remaining 900,000 entries are not really needed.
: >
: > Is there a way to create an index such that only a subset of the table
: > is indexed? I would like to shrink the index to save space and
: > hopefully increase the performance of the queries.
: >
: > Please respond to zlm101_at_psu.edu
: >
: > Thanks in advance.
 

: Well, you can't selectively index a subset of a tables rows, but what
: you might want to do is look at 'partion views', which will effectively
: split your table into multiple smaller tables. You could then just index
: the 'types' you are interested in. Note you would have to look very
: carefully at this before jumping in, also I'm not sure which release of
: Oracle you are using (look for partition views in 7.3).
 

: Steve Phelan.
Received on Wed Mar 19 1997 - 00:00:00 CST