Whatever happens to the data, it has to be read from permanent storage first, and written back after. Things might change in next ten years, but so far the reality is that this permanent storage is a hard disk.
The hard disk performs read/write operations in minimal units, which are sectors. No surprise the databases also read and write the data not in single bytes, but in some reasonably small pieces. In SQL Server this piece is called page, and in Oracle it is called block. The SQL Server page has a fixed size of 8 KB, the Oracle block size may vary, but 8 KB could be safely called "typical".
The optimal performance can be achieved when the data pieces are contiguous. Thus both SQL Server and Oracle group their minimal units, pages and blocks, respectively, into larger chunks of contiguous pieces, called extents.
In SQL Server, extent is 8 logically contiguous pages.
In Oracle, extent is 1 to N logically contiguous blocks, where N = 2 GB / Block Size.
With some exceptions, extents are the units in which the storage is consumed by tables and indexes, both in SQL Server and Oracle.
The difference is how the storage is optimized when objects are yet small enough to effectively occupy the whole extent.
SQL Server will not allocate the whole extent for an object until it grows beyond 64 KB, instead allocating pages from an extent shared between several objects (Mixed extent).
In Oracle this problem is solved by simply using different extent sizes: one for the newly created objects (INITIAL), and another one for the existing objects which grow further (NEXT).
No comments:
Post a Comment