Re: Query optimization for a table with half million rows

If the number of possible values from column ID is small compared to the number of rows in the table, then it's best to use bitmap indexes, which have a very good performance in such circumstances.

The syntax is:

CREATE BITMAP INDEX index_name ON table_name (column1, column2, ...);

Typical use of this type of index:

Column SEX (2 possible values)
Column COLORS
Column MONTHS
etc.

Hope this helps,

Nuno Guerreiro

On Wed, 16 Sep 1998 07:59:33 -0400, Kevin Kirkpatrick <kjk_at_hrb.com> wrote:

>I have a table right now that has slightly over half a million rows, and
>will soon have about 1.2 million rows. The rows consist of and id
>number (not unique) followed by data that cooresponds to that id
>number. If I query based on an id number it is possible for 100 to
>potentially 50,000 rows to pop up. The problem is that it takes a good
>amount of time for the query to execute when it returns a large amount
>of rows. It seems to be fairly quick when the result is small. Is
>there anyways that I can speed this up for the large results? I am
>using just the standard SELECT * FROM TABLENAME WHERE ID=VARIABLENAME.
>Any suggestions would be highly appreciated. Thanks
>
>Kevin
>
Received on Wed Sep 16 1998 - 07:56:14 CDT