Analytics updates - analyzing time to reload tables

We have an Analytics data warehouse running on Oracle 8.1.7.4, Solaris 5.9. We are updating from our production database every hour. Updates are taking longer than management thinks they should, so I am trying to understand where the time is being spent. I know that the update process truncates approximately 20% of the tables and reloads from scratch. We have an option set to drop and recreate indexes which, I assume means that the reload drops the indexes, reloads the table, and recreates the indexes.

I have written a query to show approximately how long the full reloads are taking so that I can see where to look for the performance bottleneck:

select do1.object_name,do1.object_type,do1.owner,do1.created, do2.OBJECT_NAME, do2.last_ddl_time,(do1.created - d2.last_ddl_time)*3600*24 reload_time
from dba_objects do1,
dba_objects do2,
dba_indexes di
where do1.owner='SIEBEL'
and do1.created > trunc(sysdate)
and di.table_name=do2.OBJECT_NAME
and di.index_name=do1.object_name
order by reload_time desc;

I assume that the dba_objectslast_ddl_time for the table should be the time that the table was truncated, having verified that truncating a test table does, in fact, update this column. However, the LAST_DDL_TIMEs for the tables are always later than the CREATED times for the indexes. This would seem to say that the update process is deleting and recreating the indexes before it updates the tables.

What am I missing here?

Thanks,
Peter Schauss

--
http://www.freelists.org/webpage/oracle-l