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

Hello,
IMHO, defragmenting the table may help, but only marginally. I can't imagine why you can't create a bitmap index - Pursue this first. Depending on the selectivity and distribution of values of the column 'drop_add_v', query performance should increase significantly. As far as an index on all three columns, this may help, but depends somewhat on what percentage of the rows in the table meet the select criterion. Perhaps if you post the CREATE BITMAP INDEX syntax and associated error message...

james
j.scharpf_at_worldnet.att.net

NetComrade wrote in message <3797b9ab.105477329_at_news.earthlink.net>...
>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.
Received on Thu Jul 22 1999 - 23:54:58 CDT