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

A couple of points, -

    each query is a simple count(*) so it can return only ONE row,     do you mean that the answer is about 1,000 in each case ?

    the syntax error may be because you don't have the compatible     parameter in init.ora set to 7.3 or above. (I think you may also     need the parallel query option installed at 7.3.4 anyway to allow     bitmap indices to be built).

Answers to your questions:

    reducing the number of extents is almost certain to have no effect.

    However, if the table has suffered huge numbers of inserts and     deletes so that lots of blocks are half empty (or worse), and lots     of blocks are on the free list, then a rebuild may pack the data     much more efficiently and leave you with a much smaller table.

    Adding a suitable index would almost certainly help, but we need     some statistics on the three different columns to be able to     determine what that index would be.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

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.
>
>Thank you.
>
Received on Fri Jul 23 1999 - 03:54:52 CDT