Skip to Main Content
  • Questions
  • Index rebuild is taking long time before partition exchange

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Prakash.

Asked: February 11, 2020 - 10:15 pm UTC

Answered by: Chris Saxon - Last updated: February 18, 2020 - 10:43 am UTC

Category: SQL - Version: 12c

Viewed 100+ times

You Asked

Hi Tom,

I have a work table where I will do all the calculations and do partition exchange to main table. This work table is truncate & load.

This process has couple of steps.

1. I will copy few records from main table to work table and calculate the derivation fields and copy will be done based on current month data set (some aggregations).
2. Remaining data copy from main table to work table
3. Rebuild index in work table
4. Do partition exchange including indexes from work table to main table.

In this way Im avoiding huge updates in main table and main table will have yearly 300 M.

Im facing an issue while rebuilding the index in work table which is running so long.

Script - dbms_index_utl.build_table_indexes (<table>, FALSE, 'ALL', FALSE, FALSE, 8);

Can you please suggest me some solution to this problem.

Thanks.

and we said...

Easy:

STOP REBUILDING THE INDEX!

Seriously.

Oracle Database maintains indexes for you during DML. I see no reason to rebuild based on what you've said.

PS - dbms_index_utl is undocumented... Using undocumented features is a bad idea...

PPS - why bother switching to another table to update? What's the problem with just doing it in the real table?

and you rated our response

  (1 rating)

Reviews

February 14, 2020 - 7:21 pm UTC

Reviewer: prk

Thanks Chrish for quick response.

Below are the details why I'm doing index rebuild...

I have 4 main source tables which are big tables and every day I will have millions updates from any of the table. It will update historically for 30 derivation columns in main table. These updates are aggregate updates.

For example product -111 has effective from 201001 to 202001 and if 202002 day, if 111 product effective dates are changing/split then I have to update the min and max dates in main table. Like this I have 10 columns which will change.

So, to avoid update Im picking the changed records from 4 source tables and calculating aggregations and inserting into into temp table and copying the rest of the data again back to temp table from main table. After that doing rebuild index and partition exchange.

Please suggest is there any way I can avoid updates or improve performance?


Appreciate your help.
Chris Saxon

Followup  

February 18, 2020 - 10:43 am UTC

I still see no reason to rebuild indexes based on what you've said. Skipping this step will save you however long that takes.

So, to avoid update ... any way I can avoid updates

I'm confused - are you running any updates or not?

To help, we need a worked example:

- create tables
- insert into
- the process you're running
- a description of why you're doing this

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.