Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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