] cc:
Subject:RE: re Rebuilding Indexes in
Oracle Apps -- an updateRichard et al,{for those who've been
following the thread on Rebuilding Indexes ...}I've just been reading
the AskTom thread on rebuilding indexesat
http://asktom.oracle.com/pls/ask/f
:
Subject:Re: re Rebuilding Indexes in Oracle Apps -- an update
Jared,
I don't see how index skip scans could benefit more from a rebuild than from coalesce (providing the index height remains the same). Skip scan doesn't scan the whole index like FFS does, it just does several scans
/2003 12:34 AM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: re Rebuilding Indexes in Oracle Apps -- an update
Richard et al,
{for those who've been following the thread on Rebuilding Indexes ...}
I've just been
Richard et al,
{for those who've been following the thread on Rebuilding Indexes ...}
I've just been reading the AskTom thread on rebuilding indexes
at
http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:6601312252730
and picked on the important line
Coalesce... reclaim the
Hi Hemant,
How I dislike being immortalised ;)
The note basically quoted me word for word on my feedback and that's fine,
it's certainly an improvement on what was previously suggested (and yes,
Oracle asked for my permission).
A point I would add though is that the whole subject of how Oracle
Yes. However, every time he has replied to me, he has been confident that he
IS right.
Mind you, Richard, you are immortalised now !
Hemant
At 05:04 PM 22-10-03 -0800, you wrote:
So now the blame rests solely on Richard for any material in the note that's
wrong. :)
Check the latest update:
11:42 AM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Re: re Rebuilding Indexes in Oracle Apps -- was
RE: RE:
I wonder if it is not necessary to rebuild indexes is also a myth.
It IS in some cases
So now the blame rests solely on Richard for any material in the note that's
wrong. :)
Check the latest update:
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab
ase_id=NOTp_id=182699.1
Pete
Controlling developers is like herding cats.
Kevin Loney, Oracle DBA Handbook
Hi Hemant,
If you purge 80% of rows from a table without intending to reinsert them
anytime soon, then yes, a table reorg would be a recommended step. No
arguments from me there ;)
I had lunch with Pete Sharman today and he mentioned that he sent an email
to the support person responsible for
Unfortunately, the lines
Unoccupied space on indexes occurs when a key value changes, and the
index
row is deleted from one place (Leaf Block) and inserted into another.
Deleted Leaf Rows are not reused. Therefore, indexes whose columns are
subject to intensive value change
Oops, I didn't see that part. Thanks for the catch, Hemant.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
- SQL Optimization 101: 12/8-12 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit
Fyi, Oracle updated note 182699.1 last Friday. The inaccurate statements
about index fragmentation have been removed.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
- SQL Optimization 101: 12/8-12 Dallas
-
Thanks for the info Cary.
Jared
On Mon, 2003-10-20 at 09:29, Cary Millsap wrote:
Fyi, Oracle updated note 182699.1 last Friday. The inaccurate statements
about index fragmentation have been removed.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
-
Hi Hemant,
One word perfectly describes the Metalink article you highlighted:
Crap ;)
A nice example of how Oracle Corp is the greatest myth generator of them
all !! It's all rather sad and embarressing isn't.
Thanks for the headsup. Anyone in a position to get the note removed ?
Cheers
ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: re Rebuilding Indexes in Oracle Apps -- Quoting an Apps
Richard,
Quoting Metalink Note 182699.1 on bde_rebuild.sql - Validates and
Rebuilds Fragmentated Indexes (8.0-9.0)
Index fragmentation occurs when a key value changes
[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/17/2003 04:29 AM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Hi Hemant,
One word perfectly describes
PROTECTED]
cc:
Subject:Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
I wonder if it is not necessary to rebuild indexes is also a myth.
It IS in some cases necessary
1. Indexes on monotonically increasing values [eg Conrurrent_Request_ID
based on a Sequence
or even on date
-LSubject: Re:
re Rebuilding Indexes in Oracle Apps -- was RE:
RE:The article states
that leaf blocks are not reused, which is indeed incorrect, and has been for a very long
time.**This e-mail message
I wonder if it is not necessary to rebuild indexes is also a myth.
It IS in some cases necessary
1. Indexes on monotonically increasing values [eg Conrurrent_Request_ID
based on a Sequence
or even on date columns which signify when the record is created] if the
table is also
purged by the same
ist ORACLE-LSubject: Re:
re Rebuilding Indexes in Oracle Apps -- was RE:
RE:The article states
that leaf blocks are not reused, which is indeed incorrect, and has been for a very long
time.
Hemant K Chitale
[EMAIL PROTECTED] Sent by: [EMAIL PROTECTED
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Saturday, October 18, 2003 4:42 AM
Hi Hermant,
I wonder if it is not necessary to rebuild indexes is also a myth.
It might be but I've yet to hear it. It's certainly not something I've ever
claimed,
On Wed, 2003-10-15 at 18:04, M Rafiq
wrote: Jared,Those tables are transit
type of tables and depending on your volume of data, there are lot
of deletes and inserts all the time resuling index
fragmentation(holes due to deletes) and space usage.
The rebuilding not only release
All of that is fair enough but the number of rows and the values you've
chosen fit the point you wished to prove. The value 5 conveniently fits
the range for an existing leaf block with empty space.
The facts as I understand them are this :
Index space freed by deleted entries can be reused (
At 01:34 PM 14-10-03 -0800, you wrote:
Hemant,
John,
My apologies for the delay. I hadn't logged on to my email last night.
Here's the output from my site
11.0.3
Purge Obsolete Workflow Runtime Data set to AGE=90 days [ITEM_TYPE and
ITEM_KEY null in parameters]
SQL set time on
14:57:42 SQL
Correction. Paragraph 4 should begin, I agree though that index rebuilds
are often unnecessary.
Mike
-Original Message-
Sent: 16 October 2003 15:20
To: '[EMAIL PROTECTED]'
All of that is fair enough but the number of rows and the values you've
chosen fit the point you wished to prove.
: re Rebuilding Indexes in Oracle Apps -- was
RE:
RE: Separate
John
What about gl_interface table indexes? I think indexes on all *interface(
tables must be rebuild on a regular interval...I was building indexes on
gl_interfaces and fnd_request* tables on monthly basis.
Regards
Rafiq
]
Sent by: [EMAIL PROTECTED]
10/16/2003 10:34 AM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Jared,
Unfortunately at this stage I cannot quantify
:
Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE:
RE: Separate
Jared,
Unfortunately at this stage I cannot quantify in numbers as I have left
that
job 5 months back. But dealing with Oracle Financials 10.7 with version
7.3.4, I observed it practically that this table
Richard,
Quoting Metalink Note 182699.1 on
bde_rebuild.sql -
Validates and
Rebuilds Fragmentated Indexes
(8.0-9.0)
Index fragmentation occurs when a key value changes, and the index row is
deleted from one place (Leaf Block) and inserted into
another.
Deleted Leaf Rows are not reused.
Hemant,
It is absolutely true with Oracle Financials Databases and I have seen
performance degradation when indexes on such databses are not rebuilt at a
regular interval meaning indexes on certain tables on mothly basis.
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of
]
cc:
Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
John
What about gl_interface table indexes? I think indexes on all *interface(
tables must be rebuild on a regular interval...I was building indexes on
gl_interfaces and fnd_request* tables on monthly basis
, October 15, 2003
5:19 PMTo: Multiple recipients of list ORACLE-LSubject:
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
SeparatePlease explain
why these indexes must be built. What benefits do you see from it? Are they quantifiable? Jared
"M
by: [EMAIL PROTECTED]
10/14/2003 03:49 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE:
RE: Separate
John
What about gl_interface
PROTECTED]
Sent by: [EMAIL PROTECTED]
10/14/2003 03:49 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE:
RE: Separate
John
What about gl_interface table
]
cc:
Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE:
RE: Separate
John
What about gl_interface table indexes? I think indexes on all *interface(
tables must be rebuild on a regular interval...I was building indexes on
gl_interfaces and fnd_request* tables
it?
Are they quantifiable?
Jared
M Rafiq [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/14/2003 03:49 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE
John,
I rebuild the FND_CONCURRENT_REQUESTS indexes every four months [and the
table itself, occassionally].
This Saturday I will also be rebuilding some ALR indexes.
Which WorkFlow Indexes do you rebuild ?
Hemant
At 11:44 AM 13-10-03 -0800, you wrote:
Jared,
Any indexes supporting a
In-Today;
Hemant,
This applies on 11i only. I would rebuild all indexes supporting the
WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been
working on some AOL table(space) problems in the background and noticed that
in 11i by default, we are not be purging _all_ the WF data that we should
John
What about gl_interface table indexes? I think indexes on all *interface(
tables must be rebuild on a regular interval...I was building indexes on
gl_interfaces and fnd_request* tables on monthly basis.
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
Rafiq,
John
What about gl_interface table indexes? I think indexes on all
*interface(
tables must be rebuild on a regular interval...I was building
indexes on
gl_interfaces and fnd_request* tables on monthly basis.
Indeed the interface tables suffer as well. I would suggest a TRUNCate of
John
At my location I was not finding those tables without rows so simple
truncate was not the easy option. However, from time to time I was
truncating them by removing rows into temp type of tables and placing those
rows back. However, index rebuilding was more practical under that
situation.
Hi, I do rebuild index for table AP_INVOICES_ALL
but it seems that no effect on extents.
select owner, segment_name, tablespace_name, count(*), sum(bytes)
2 from sys.dba_extents
3 where segment_name like 'AP_INVOICES_N3' and tablespace_name='APX'
4 group by owner, segment_name,
42 matches
Mail list logo