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

A copy of this was sent to netcomradeNOSPAM_at_earthlink.net (NetComrade ) (if that email address didn't require changing) On Fri, 23 Jul 1999 00:47:42 GMT, you 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).
>

In addition to the things Jonathan mentioned (yes you do need Parallel query installed to get bitmaps else you get an "invalid create" statement error) you might consider:

• using Parallel Query.
• reducing the number of queries you are running, if they are all "select count(*) from big_table where <some conditions>", you could cut the number of queries in half by doing something like:

select
sum(decode(player_id,12345,decode(drop_v_add,0,decode(sign(date-certain_date),-1,1, 0),0),0))
from big_table
/

which is the same as your select count(*) -- as long as there is at least 1 row in the table anyway...

Once you've converted your queries to using decode, combine them so that you select many SUM's in one query -- take one pass on the table instead of many passes. Instead of running 1,000 queries, run 500, or 100, or 1 query that has the same data...

combine options 1 and 2 above even -- use PQO plus decode()

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

an index might help but only if it is selective.

>Thank you.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jul 23 1999 - 09:34:19 CDT