Reindex Tables

We are considering reindexing the F0911 which has over 8,000,000 records and
are wondering how long this might take.

If anyone has experience with this please let me know.

B7332
Oracle 8
Sun Hardware for Enterprise server.

Madonna Smeltzer
Technical Consultant
Data Systems International
7801 W. 110th St.
Overland Park, Kansas 66210
Phone: (913) 696-2512

I reindex our F0911 (approximately 3 million rows) in around 40
minutes. We currently have 36 indexes on it, so depending on the
number/type of indexes, the time required for your particular instance
could vary wildly.

John

----------------------------------------------------------
OneWorld Xe (B733.3)
Update 6, SP 19.1_B1
Running on: WIN2K/SP2, SQL2k/SP2
Metaframe 1.8a
----------------------------------------------------------





Xe, Update2, SP16
SQL2k, Win2k
Metaframe 1.8a

This depends on the Server performance and Oracle configuration.

Do you know how long a full table scan query takes on this table? Then each index rebuild would take comparable time, so just multiply that by the number of indexes (about 30)...

Or try one (a smaller one) - this would give you a very good idea how long it would take.

It could be from ~1/2h per index for a fast Server up to 6h for a slow one, hence you really need to take one of the approaches above to base your estimate on on some numbers taken from your server.

Regards,
Alex.

We also have over 8 million rows with 20 indexes. We indexed last
weekend and the F0911 took about 40 minutes each. DB in noarchivelog
mode.
Regards,
Gopal OWXe U1 SP19.1, Oracle 8.1.6.3.0 Solaris 7

We are considering reindexing the F0911 which has over 8,000,000
records and
are wondering how long this might take.
If anyone has experience with this please let me know.
B7332
Oracle 8
Sun Hardware for Enterprise server.
Madonna Smeltzer
Technical Consultant
Data Systems International
7801 W. 110th St.
Overland Park, Kansas 66210
Phone: (913) 696-2512
--------------------------

Really this depends on your server performance. I would suggest switching archiving off in Oracle as well while you reindex to speed up the process.

However, remember that most of the indexes only go over a few columns - so I would not expect it to take more than an hour or so.

Lastly, DBA's often create additional indexes on tables to improve performance - custom indexes - you might want to evaluate if there are any custom indexes that you'll be dropping.

Instead of a re-index - why not Analyze the table ? You should re-analyze all the tables in your database on a regular basis - usually most of my larger customers do this weekly.

Jon Steel
OneWorld CNC Specialist
erpSOURCING LLC

hi,altquark,

i have never heard of re-analyze tables, can u explain it a little more ?
we have about 13 million record in F0911,and the perform is very bad,if re-analyze can do any help ,that will be great !


bee

Regularly analyzing schemas can have a massive effect on performance.
If you do not analyze, a full table scan may occur even if using an index would be quicker .
To analyze a schema log in using sqlplus and type:
exec dbms_utility.analyze_schema('SCHEMA_NAME', 'ESTIMATE');
DO NOT analyze SYS or SYSTEM schemas.
Performance will be degraded while this is running so suggest you do this at a quiet time.
We do this via cron on all non system schemas weekly.
COMPUTE rather then ESTIMATE gives better results but takes MUCH longer.

Can you tell me where I can get some more information on re-analyzing =
our XE
tables? We have had some problems with performance that re-indexing =
hasn't
been able to take care of and this might be just what we need.

hi,patrickjolliffe,

do you mean that re-analyze is a utility of oracle ,not oneworld?

we are using as400/db2,maybe we can't re-analyze it??

bee

Correct - only relevent for Oracle - not sure about the db2 case

Re: RE: Reindex Tables

All,
A little off topic, but a few thoughts on this thread:
a) you can do it on the AS400 - look at DB Performance Monitor.
b) Remember that removing indexes can actually improve write times too. This is particularly true for indexes using frequently updated fields.
c) Couple archiving wih indexing - some applications, like credit hold release, beg for archiving - archiving that can makea dramatic difference for your end users.

Cheers,
--Malcolm.

Re: RE: Reindex Tables

Would you be so good as to tell me the method that you used to reindex this table? DBREINDEX? INDEXDEFRAG? drop and recreate through queries? or something else? Also, do you know of any problems with reindexing or defragging indexes on jde's tables through the use of sql server tools?