Home » RDBMS Server » Performance Tuning » data load-index rebuild or index drop
data load-index rebuild or index drop [message #130220] Thu, 28 July 2005 19:06 Go to next message
rkl1
Messages: 97
Registered: June 2005
Member
Dear Orafaq readers:

As we know we can either drop indexes before loading and then recreate them with nologging and parallel option or we can do same by making indexes unusable and then rebuild. what exactly happens during rebuild process.does oracle uses the old index to build the new.We got some heavy partition based tables where we need to use local bitmap indexes.since these tables undergo loading periodically, we are following the drop and recreate indexes.it is not feasable to know to which partition data would go so we could make those indexes partitions unusable first and then make them usable after loading.Now it takes too much time to build those indexes and even some time to to drop them. Is there any other game plan available for these indexes maintenance. I would appreciate any suggestions.

Here I got another worry.if we recreating 8 bitmapindexes on same table, should we build them one after another with parallel option or run multiple index create script with parallel options so at a time say 4 indexes are built in parallel.with 8 cpus, what the max number of parallel servers could be employed.

thank you again.

thanks.
Re: data load-index rebuild or index drop [message #130339 is a reply to message #130220] Fri, 29 July 2005 07:53 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Just to make sure I've understood you, are you saying that you are going to rebuild your bitmap indexes after the load, but you are questioning whether you should

a) drop them first, do the load, then recreate
or
b) mark them unusable first, do the load, then rebuild

If so, I'd vote for b, because then you'll at least get the possibility of oracle using existing index data in the rebuild of the new index. But just as (if not more) important in my mind, if you drop them, something may happen that prevents you from recreating them, and they may get "lost", leading to other issues that may be difficult to track down.

If you mark them as unusable, then the indexes never go anywhere, they are always there on your system, so there is no way for them to get lost or forgotten about.

As far as which ones to rebuild, are you sure there is no way to know which partition data is changing, or at least changing the most or least? Perhaps of your 8 partitions only 6 are likely to change a lot with the other 2 hardly experiencing any dml, so that you could let those 2 maintain themselves without being rebuilt, but only rebuild the other 6?

Even if you just postponed the rebuild of 2 or 3 until your next window of opportunity, maybe that would speed the load process up enough. Try not to look at it in an all or nothing sort of way (if you can, you may have no choice).

Or perhaps you could even change your partitioning scheme to make the loading and maintenence easier so that you are only operating on a few partitions at a time rather than the whole table all at once. That is the great thing about partitions and local indexes, is that they are not large, global objects.

As far as parallelism and which way to go there, you are going to have to test that for your specific system. I'd start by rebuilding them each in parallel (default) one partition at a time, one table at a time. Then try other variations and see.
Re: data load-index rebuild or index drop [message #130381 is a reply to message #130339] Fri, 29 July 2005 12:08 Go to previous messageGo to next message
srinivas4u2
Messages: 66
Registered: June 2005
Member
rkl1,

I agree with what smartin says. It's always a better idea to rebuild an index (if it's possible) than to recreate as you already have the data sorted.

Your question as to whether the index would be used during rebuild - well, it would be used if you rebuild it online. Make sure that you have sufficient space to hold both your existing index and the new index that's getting built.

smartin, It would be good to me too if you could explian the working of parrallelism. Im not sure if cpu count is the only factor on which degree of parrallelism is dependent.

On a 8cpu server what is the maximum degree of parrallelism I can specify for a single job and what would be if am runninng mutiple jobs at the same. ( would the cpu be used in time-sharing fashion among the jobs?).

Thanks,
Sri
Re: data load-index rebuild or index drop [message #130382 is a reply to message #130381] Fri, 29 July 2005 12:10 Go to previous messageGo to next message
srinivas4u2
Messages: 66
Registered: June 2005
Member
And want to add -

Does specifying multiple dbwrs too depend on cpu count?

Thanks,
Sri
Re: data load-index rebuild or index drop [message #130391 is a reply to message #130220] Fri, 29 July 2005 13:23 Go to previous messageGo to next message
rkl1
Messages: 97
Registered: June 2005
Member
thanks guys for excellent help. regarding online index rebuild, please correct me. My understanding is that, not possible for the partitioned bitmap indexes:neither we can create them or rebuild them online. thanks for mentioning the cpu count.my understanding was always multiply the cpu_count to the number of cpus and assign that number to the degree of parallel for a single job.However, I have no idea to what value of parallel_max_server could be assigned.I was told by an oracle guru that, max_server parameter could be set to the factor of cpus.so if you have 4 cpus then it would be:4X3X2X1=24 and for 8 cpus it must be very high.In my laptop, which has a single cpu, I could even set max_server parameter to a very high number like 128 and could run a large number of jobs in parallel.

According to Tom (ASK TOM), if your computer can multitask, then it is ready for parallel query option.

thanks.
Re: data load-index rebuild or index drop [message #130605 is a reply to message #130220] Mon, 01 August 2005 07:34 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I would suggest that everyone read this:

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10743/bus_intl.htm#i32362
Re: data load-index rebuild or index drop [message #130620 is a reply to message #130220] Mon, 01 August 2005 09:47 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Here is a test that compares dropping and recreating an index to rebuilding it. This test makes many assumptions, and it is always better to compare performance of different approaches on your own system with your own data. In both cases in this test, rebuilding was faster than recreating. The bitmap indexes are much smaller, so their total times are smaller.

-- rebuild_vs_drop.sql (Scot Martin)
-- 10.1.0.4 01-AUG-2005

-- This test compares dropping and recreating a locally partitioned index
-- with rebuilding the index.  Does both btree and bitmap.  Many factors
-- are ignored in this test, such as the nature of the data modifications
-- that would happen between the drop and recreate, or unusable and rebuild.

MYDBA@ORCL > 
MYDBA@ORCL > create table test(a date, x number, data char(5))
  2  partition by range(a)
  3  (
  4  	     partition p1 values less than (to_date('01-JAN-2006','DD-MON-YYYY')),
  5  	     partition p2 values less than (to_date('01-JAN-2007','DD-MON-YYYY')),
  6  	     partition p3 values less than (to_date('01-JAN-2008','DD-MON-YYYY'))
  7  );

Table created.

MYDBA@ORCL > 
MYDBA@ORCL > insert /*+ append */ into test
  2  select to_date('01-AUG-2005','DD-MON-YYYY') + rownum/3000,
  3  trunc(dbms_random.value(100000,100009)), 'x'
  4  from dual connect by level <= 1800000;

1800000 rows created.

MYDBA@ORCL > commit;

Commit complete.

MYDBA@ORCL > 
MYDBA@ORCL > select count(*) from test;

  COUNT(*)
----------
   1800000

1 row selected.

MYDBA@ORCL > select min(a) from test;

MIN(A)
--------------------
01-AUG-2005 00:00:29

1 row selected.

MYDBA@ORCL > select max(a) from test;

MAX(A)
--------------------
24-MAR-2007 00:00:00

1 row selected.

MYDBA@ORCL > select table_name, partition_name from user_tab_partitions order by 1,2;

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TEST                           P1
TEST                           P2
TEST                           P3

3 rows selected.

MYDBA@ORCL > 
MYDBA@ORCL > set timing on;
MYDBA@ORCL > 
MYDBA@ORCL > create index test_idx on test(x)
  2  local;

Index created.

Elapsed: 00:00:45.96
MYDBA@ORCL > 
MYDBA@ORCL > drop index test_idx;

Index dropped.

Elapsed: 00:00:00.19
MYDBA@ORCL > 
MYDBA@ORCL > create index test_idx on test(x)
  2  local;

Index created.

Elapsed: 00:00:45.87
MYDBA@ORCL > 
MYDBA@ORCL > alter index test_idx rebuild partition p1;

Index altered.

Elapsed: 00:00:08.79
MYDBA@ORCL > alter index test_idx rebuild partition p2;

Index altered.

Elapsed: 00:00:19.02
MYDBA@ORCL > alter index test_idx rebuild partition p3;

Index altered.

Elapsed: 00:00:05.64
MYDBA@ORCL > 
MYDBA@ORCL > alter index test_idx rebuild partition p1;

Index altered.

Elapsed: 00:00:09.56
MYDBA@ORCL > alter index test_idx rebuild partition p2;

Index altered.

Elapsed: 00:00:17.44
MYDBA@ORCL > alter index test_idx rebuild partition p3;

Index altered.

Elapsed: 00:00:05.04
MYDBA@ORCL > 
MYDBA@ORCL > drop index test_idx;

Index dropped.

Elapsed: 00:00:00.45
MYDBA@ORCL > 
MYDBA@ORCL > create bitmap index test_idx on test(x)
  2  local;

Index created.

Elapsed: 00:00:04.80
MYDBA@ORCL > 
MYDBA@ORCL > drop index test_idx;

Index dropped.

Elapsed: 00:00:00.12
MYDBA@ORCL > 
MYDBA@ORCL > create bitmap index test_idx on test(x)
  2  local;

Index created.

Elapsed: 00:00:06.16
MYDBA@ORCL > 
MYDBA@ORCL > alter index test_idx rebuild partition p1;

Index altered.

Elapsed: 00:00:00.32
MYDBA@ORCL > alter index test_idx rebuild partition p2;

Index altered.

Elapsed: 00:00:00.78
MYDBA@ORCL > alter index test_idx rebuild partition p3;

Index altered.

Elapsed: 00:00:00.18
MYDBA@ORCL > 
MYDBA@ORCL > alter index test_idx rebuild partition p1;

Index altered.

Elapsed: 00:00:00.30
MYDBA@ORCL > alter index test_idx rebuild partition p2;

Index altered.

Elapsed: 00:00:00.62
MYDBA@ORCL > alter index test_idx rebuild partition p3;

Index altered.

Elapsed: 00:00:00.21
MYDBA@ORCL > 
MYDBA@ORCL > set timing off;
MYDBA@ORCL > 
MYDBA@ORCL > drop table test;

Table dropped.

MYDBA@ORCL > purge recyclebin;

Recyclebin purged.

MYDBA@ORCL > 
MYDBA@ORCL > set echo off;

Previous Topic: Index range scan vs full table access
Next Topic: delete statement cause application hang
Goto Forum:
  


Current Time: Fri May 27 01:51:24 CDT 2022