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

You don't state how many rows are in the table, it helps to know this. The table size is a little less important. Assuming that there 1000's of rows then indexes will help but you should really try to reduce the 1000 queries down to one. Another note, your query should only return one row, its a count so are you sure that it returns more then a thousand rows ?

If the date is consent for all of the 1000 queries then you could use the group by key word to collect the count by the player_id. This of course depends on how you use the data.
select count(*) from BIG_TABLE where drop_v_add=0 and date <= certain_date group by player_id
Cheers
Alex

NetComrade wrote:

> 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.

--
FICS Group.
Phone +32 2 714.43.21 GSM +32 (0) 495 20.11.72 Received on Fri Jul 23 1999 - 03:10:36 CDT