Re: Faster index creation

On May 14, 11:02 am, Tom <tzebli..._at_autooneins.com> wrote:
> I need to create 2 indexes on a 107 GB table. The indexes have no
> columns in common. I have a 8 Gig SGA.
>
> I am not looking forward to 2 tablescans so I was thinking of running
> both create index statements at the same time from different sqlplus
> sessions and hoping that the data for the slower statement is in
> cache for the faster statement.
>
> Or as an alternative, I was thinking of first creating an index which
> was a superset of the columns in the other 2 indexes and then creating
> the other indexes. They will just use the first index instead of
> doing table scans. When they are done, drop the first index.
>
> I'm hoping that someone else has better ideas or that there is just a
> simpler way.
>
> . . . Tom

Tom,

Is the table partitioned? (enterprise edition + partitioning option required)
What degree of parallelism is set for the table? (if any) What version of the Oracle database server software are you referring to?
What is the maximum OS IO size that is supported? (e.g. 1 MB) What is the db_file_multiblock_read_count set to? (e.g. 16) Have you gathered system stats under workload? (check sys.aux_stats$) This would enable cpu costing.
The db_block_size would also be of interest, as would the tablespace block size if it differs from the db_block_size. (assume that it is 8192 bytes, but being a DW it might be 16K)

Perhaps the limiting factor is that the dbfmbrc is only say 8, but could be set to 128 and grab a full 1 MB of data in a single IO call (show parameter db_file)
Perhaps the limiting factor is that the pga_area_size is smallish causing a small sort_area_size to be used during index creation.

If you're looking to make table scan and sorting operations cheaper (and go faster) you might consider the following: alter session set workarea_size_policy='MANUAL'; alter session set db_file_multiblock_read_count=128; alter session set sort_area_size=536870912;

Larger values for sort_area_size would likely be helpful. You might be limited on available physical memory.

If other user sessions are working with blocks from the heap table, the likelihood of obtaining a large number of blocks from an extent in a single read decreases.
Although it seems that accessing the blocks of the table at the same time would reduce the runtime of the 2 index creations due to having more blocks of that segment in memory, its much more likely that it would cause contention and cause the effective dbfmbrc to be reduced and the overall runtime to increase.

Hopefully you have a good test environment in which to test for yourself.

-bdbafh Received on Wed May 14 2008 - 12:53:15 CDT