Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1579
  • Last Modified:

Set primary extent size?

Hi Experts,

I'm working on an ooooold INFORMIX 5.10 database, and want to change the "primary" extent (if that's what it's called) to say 1.5 times the current size of the loaded data, to maximise performance and allow for 50% growth without going into "secondary" extents (or is it "next"? extents).  The current data is badly fragmented, and the extent sizes are too small, so I plan to export, delete, and import the database.

Questions:

1. What is the correct term for this "primary" extent?

2. Does trying to avoid using next extents sound like a good way to maximise performance?  (Yes, I know there are many other ways - I'm just talking about this one).

3. I know how to set the next extent sizes ("ALTER TABLE...MODIFY NEXT SIZE..."), but how can I go about making the "primary"? extent 50% larger than the current data requirement?

4. Could I achieve item 3 by increasing the row counts in the dbexport file (after the export) by 50%, and then import?

5. Do those row counts in the dbexport file have to match the numbers of rows in the table unload files, for the dbimport to work?

6. Can the primary extent span chunks?  (I understand that next extents can't).

Thanks.
0
tel2
Asked:
tel2
  • 4
  • 4
1 Solution
 
gheistCommented:
It is set when initializing server based on config file
General recommendation is 128MB
0
 
tel2Author Commented:
Hi gheist,

Sorry for the delay in responding.

What variable are you referring to?
0
 
gheistCommented:
confuded with root dbspace.
it is specified when one does create table.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
tel2Author Commented:
Hi gheist,

What do you mean by "confuded" (is it "confused"?).

Are you saying I should do this:
  CREATE TABLE abc ... EXTENT SIZE ...
?

TRS
0
 
tel2Author Commented:
Awesome!

I'll take that as a double "yes".

Thanks gheist!
0
 
gheistCommented:
Informix Unleashed is best book on subject - it deals with some internals covered by Informix courses, not everything, but reasonalbly well.
First extent is allocated when table is created, next are allocated later many times and new ones can be set different size by ALTER TABLE
First extent should be data size when you import data, next is expected growth between UPDATE STATISTICS
They should be rounded up to page size (as taken from onstat) - 2k or 4k (8k page systems like old SunOS are long gone)
0
 
tel2Author Commented:
OK - thanks again, gheist.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now