Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: November 27, 2018 - 4:18 pm UTC

Last updated: November 29, 2018 - 12:30 am UTC

Version: 12

Viewed 1000+ times

You Asked

I've always created tables using the defaults for storage parameters.

create table blah (
  a number;
  b varchar2(100)
);


however I recently worked with an organization where the following storage parameters were always specified:

* Initial
* Next
* MinExtents
* MaxExtents

which got me thinking - how much overhead is there (typically) these days in using the defaults? Should developers always be specifying storage parameters (and calculating them) or is it reasonably safe to take the defaults without having a big hit?

Are there any compelling reasons to specify the parameters explicitly?

and Connor said...

Are there any compelling reasons to specify the parameters explicitly?


Nope.

For me, I'll do some thought on pctfree, and maybe initrans in conjunction with that, but the rest I simple don't worry about any more. I consider the other parameters legacy of a bygone era.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

John Keymer, November 28, 2018 - 8:39 am UTC

Perfect, thank-you very much. I presume you give thought to pctfree only when you expect updates to the row to be significantly larger than when it was created (so as to avoid row migration in the future)?

Just for my own understanding, why did the other parameters matter "in the past"? I.e. what was it that was so slow that would make you want to allocate a load of space up front? I also could never understand maxextents - why would I want to limit the size of my table??
Connor McDonald
November 29, 2018 - 12:30 am UTC

pctfree is important for me because most tables are insert only, or insert/delete, and yet we blow 10% of the space for updates that might never occur.

We used to bludgeon some internal dictionary tables (uet$/fet$) to manage everyone's extents, so choosing how many to grab initially and how often to grab them was important. Locally managed tspaces pretty much solved that.

maxextents was a means for stopping an unexpected/undesired explosion in growth of a particular table.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.