Problem with nightly batch job against a 1.5Gig table. Also BITMAP prob

Hi,

We have Oracle 7.3.4 running on Solaris 2.6. We have a query which is supposed to run a 1000 times against a table of a size 1.5GIG. Table is heavily fragmented (>100 fragments), the query is using no indexes. The query has the following structure:

select count(*) from BIG_TABLE where player_id=12345 and drop_v_add=0 and date <= certain_date;

each of this queries returns more then a thousand rows

Each of this queries takes forever to run (and we need 1000 of them). We don't have enough RAM to put the whole table into SGA, for some reason I am also not able to create a BITMAP INDEX on drop_V_Add column (even though I think I should be able to, Oracle gives a syntax error).

I was wondering how much defragmentation of the table will have speed up the query, and if creating an INDEX on these 3 field will help, or if anybody has any better solutions.

Thank you. Received on Thu Jul 22 1999 - 19:47:42 CDT