No question at this time
DBA Top 10
1 A. Kavsek 8100
2 M. Cadot 7800
3 B. Vroman 6600
4 T. Boles 5750
5 J. Schnackenberg 4100
6 P. Wisse 2900
7 T. P 1200
8 G. Lambregts 1100
8 . Lauri 1100
8 M. Hidayathullah ... 1100
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48365
Total active users1548
Act. users last 24h4
Act. users last hour0
Registered user hits last week329
Registered user hits last month1228
Go up

About rebuilding houses
Next thread: Oracle Fusion Middleware
Prev thread: how to query last 5 partitions of some tables

Message Score Author Date
Hi, I have found in few houses policies to rebu...... Lauri Jan 22, 2019, 16:30
Hi Lauri, For DWH environments the rules might ...... Philip Wisse Jan 24, 2019, 10:07
I rarely rebuild indexes, but frequently update st...... Rob Pattyn Jan 24, 2019, 10:24
Hi, Thanks for the answers. @Philip: Yes, I a...... Lauri Jan 24, 2019, 11:23
Hello, you touched a very huge topic without a ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Matej Pagac Jan 25, 2019, 15:02
Hi, Thanks for the answers. @Matej: I agree wi...... Lauri Feb 07, 2019, 13:22

Follow up by mail Click here


Subject: About rebuilding houses
Author: Lauri, Netherlands
Date: Jan 22, 2019, 16:30, 93 days ago
Os info: all
Oracle info: 12c
Error info: NA
Message: Hi,

I have found in few houses policies to rebuild regularly indexes in datawarehouse environments.
This is usually resource and time consumming.
Until now, this is something I had never to consider, relying on the Oracle engine to do that job.
Does someone has experience in rebuilding indexes?

Kind Regards
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: About rebuilding houses
Author: Philip Wisse, Netherlands
Date: Jan 24, 2019, 10:07, 91 days ago
Message: Hi Lauri,

For DWH environments the rules might be different.
1. The ETL process has to be as quick as possible
2. During the Reporting Window no other activity allowed but reporting

If there's any time left in a day you can schedule database maintence, whatever.

If you have a near realtime system you have to limit maintenance to a minimum of course.

HTH, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: About rebuilding houses
Author: Rob Pattyn, Belgium
Date: Jan 24, 2019, 10:24, 91 days ago
Message: I rarely rebuild indexes, but frequently update statistics.
Fragmentation isn't usually the problem as the data is on a smart storage device (black box, ssd, ...)

https://www.brentozar.com/archive/2017/11/good-reasons-rebuild-reorganize-indexes/
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re-About rebuilding houses
Author: Lauri, Netherlands
Date: Jan 24, 2019, 11:23, 91 days ago
Message: Hi,

Thanks for the answers.
@Philip:
Yes, I agree that in DWH environments the rules might be different.
Indexes are usually dropepd or set as unusable before the load.
@Rob:
Yes, this is something that I rarely do.
But what about DWH environments types?

Any other experience to share?

Kind Regards
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: About rebuilding houses
Author: Matej Pagac, Slovakia
Date: Jan 25, 2019, 15:02, 90 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello,

you touched a very huge topic without a definite answer.

I would suggest you to read several forum threads on asktom about this, as "it all depends" :)
So for every specific scenario there's specific answer. It;s not straight forward that this type of DWH required this type of index maintenance. Just a general guidance on what to focus the most.

https://asktom.oracle.com/pls/apex/asktom.search?tag=rebuilding-indexes-200202
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6601312252730

and so on..

mTj
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re-About rebuilding houses
Author: Lauri, Netherlands
Date: Feb 07, 2019, 13:22, 77 days ago
Message: Hi,

Thanks for the answers.
@Matej: I agree with you... huge topic without a definite answer.
In the meantime I found my way:
1) I do not rebuild indexes for low-DML, especially when it concerns bitmap indexes.
2) I do rebuild the indexes when the table is truncated, expecially if this table has bitmap indexes; even if it is a small table.

Kind Regards
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here