Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Shrinking an index??

Re: Shrinking an index??

From: Steve Phelan <stevep_at_pmcgettigan.demon.co.uk>
Date: 1997/03/18
Message-ID: <332EB9FC.22B4@pmcgettigan.demon.co.uk>#1/1

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 Tue Mar 18 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US