Re: Bitmap indexes cause slow load

Part of the 'correct' solution to your problem should still be to drop and rebuild all the bitmap indexes. However, your comments
about testing to scale suggest that perhaps you have an underlying hardware shortfall.

How many separate discs do you have on
the system, and how are you spreading
the table, the indexes, and the temporary tablespace across them ? Possibly by
re-arranging your disk layouts you can
reduce your 20 minute build time to
something closer my 3 minutes.

Another approach (still focusing on drop and rebuild) - since you have 2GB (compared to the 512M I have on my machine) you could try rebuilding your bitmaps with

    create_bitmap_area_size = 100MB
    sort_area_size = 40M
    sort_area_retained_size = 40M

(These are just guestimates of the sort of size you will need to keep the jobs completely in memory - you may have to go larger, but if the disc is the problem and memory is available ...).

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

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Mario wrote in message <9dpm59$u68$1_at_news.online.de>...


>Hi Folks,

>

>thanks for all the replies!

>

>Some more about the problem:

>Hardware: Sun, 2CPUs, 2 GB mem.

>

>The problem table: over 70 columns with 2 B*Tree indexes

>(including the PK) and 10 bitmap indexes. Over 20 mil records

>and counting. Almost 3 GB in the tablespace is used.

>

>Loading is taking place as some of you suggested:

>one INSERT at a time from a SAS client connecting through Net8

>(terrible, but that's life when dealing with a legacy system

>and with different departments playing politics to protect their

>turf).

>

>One of my question still holds:

>What range of values should be used

>for SORT_AREA_SIZE or BITMAP_MERGE_AREA_SIZE?

>Do they help at all when performing INSERTS into the table

>with bitmap indexes?

>

>Testing with such volumes is very difficult. I have played

>with a scaled down version (only 2 mil records) where I

>have used an INSERT....SELECT from a temporary table

>(i.e. SAS first loads to an empty table and then the

>INSERT...SELECT from the temporary table to the final

>table). In the tests this is about a factor 10 faster than

>the single inserts but I don't have commit control and this

>will be a problem with the production volumes and the rollback segments.

>I am experimenting with bulk loads using FORALL but I have been told that

>I will need plenty of memory.

>