Re: Shrinking an index??

Christina Villmer 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.
>
> Hi!
> I'm not sure if this is the better way, but try this (if you like):
> I wouldn't use the index over the whole table. I would create a view on
> the table with only the types you need. And then just query on the
> view..!
> --
> Christina Villmer

Hi!
Sorry, what I had written yesterday was nonsense! It will make a full table scan, because you can't build an index over a view....

But the ideas from Allen Kirbey and Doug Anderson are good.

I had a similar one, but with more work for you to do. My second idea was:
- Create a new table with the columns "row_id" and "type". - Create a trigger on the first table, which inserts a   sentence into the new table, whenever the type is 5 or 9. - Create an index on the second table (now you can drop the index of   table1 if you don't need it for other queries) - Create a view in which you join the tables and only select   the sentences from table1, which have an corespondent sentence   in table2.
- When you want to select, than select just sentences from the   view.
But this will take a lot more space (so it is not shrinking anymore...) But it hopefully will improve the performance of the select-statements you have written about...

Yours
Christina Villmer Received on Wed Mar 19 1997 - 00:00:00 CST