Re: Bitmap indexes cause slow load

On Tue, 15 May 2001 00:30:02 +0200, "Mario" <mtechera_at_wpmc.com> wrote:

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

I may stand corrected, but I know SORT_AREA_SIZE applies to ORDER BY, GROUP BY or CREATE INDEX, none of which have squat to do with INSERT.

And BITMAP_MERGE_AREA_SIZE would apply to merging bitmap indexes in a query, which again got nuthn' to do with INSERT. So at a guess I'll take a punt and say none of those two will help you much.

Any other opinions? Did I forget something?

>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

That would be because instead of doing row-at-a-time insert you'll be doing a set operation. That is much faster. And your row-at-a-time insert coming from SAS will go into the temp table, with no major overhead of bitmap indexes and others. Overall a much better way of doing it than letting SAS dump bits and pieces into your main table.

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

This you can control by selecting ranges off the temporary table rather than a bulk load of all rows?

>
>If you are inetrested I will keep you informed.
>

Yes, please. It's always interesting to see what others are doing to solve large volume problems, regardless of the path used to get there.

Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Tue May 15 2001 - 06:38:22 CDT