okay I'm a ding-bat, whats an extent?

[snippy]

Can someone give me a good, clear explanation of what an extent is? Our dba tried to explain it to us programmers or as he refers to us 'typists,' but its not sinking in.

What are they, what do they do?
Is there a simple rule of thumb as to how I should create them when I setup a new table?
Is there a rule of thumb for evaluating existing tables that might need to be changed?
How or do they affect indexes?

Our dba makes this seem like secret black magic!

 

[cdlvj]

Many many moons ago on mainframe computers when you wrote out a disk file, you had to define how much disk space you thought that your program would use.

So in the jcl you said 10 cylinders. So for the case where you made a mistake, and your program wrote 11 cylinders, you had the capability of add a parameter called and EXTENT. So the system ignored your estimation and kept on trucking adding space until it completed ok or ran out of extents.

Essentially an extent is a saftey valve for space allocation, in the event that you use up the main space, then the system allocates extents.

This also is an indicator that you need to re-org and allocate more main space.

 

[snippy]

Thanks cdlvj,
I was begging to think the question was to basic for anyone to respond to. I hope you dont mind me belaboring, but I am not sure what you mean by allocate more "main space" and "reorg." Informix has a reorg function? Or did you mean unload my table and then reload it to some new and better organized space? If so, what would you pay attention to is say you were looking at an existing table and wanted to reorg/allocate to improve performance?

 

[cdlvj]

If you have a lot of deletes and adds, the index's can get fragmented, and you can but not necessarily get better performance. A simple unload dumps the active records, and rebuilds the index and data. On a high maintenance table, yes this is something to look for. There is a parameter called fill which leaves expansion space in each page of data.

For optimal performance, you want all your data to be in the primary area, because the system does have to go out and allocate more space for inserts.

The Database engine does a excellent job in managing all this, but by doing some of this other tweaking you can squeeze some more cycles out. If you have lots of user access and heavily maintained tables, then this needs to be watched.

There are tools that help you watch for bottlenecks. Another useful item is fragments for large Tables. Other options are to break up tables into static and non static data. Like for instance a State Code Table. It has 50 records, does not change, and it is better not to have an index as the system can load it into memory and get extreme performance for seaching.