Mysql's Innodb has concepts of page, extent, segment, tablespace but I don't know how they interact with each other. How are they related? What does it mean a tablespace has 16K of pages? How does this relate to the physical rows and is 'pages' something in the memory?
I would assume that Oracle might have the same concept but I'm not sure where to look for it. Is there a good reference / book that talks about this in detail?
There is a pretty good discussion of it at:
http://www.markleith.co.uk/?p=25
File system -> InnoDB ---------------------------------------------- disk partition -> tablespace file -> segment inode -> fsp0fsp.c 'inode' fs space allocation unit -> extent disk block -> page (16 kB)
If you really want deep down details, there is also:
Oracle has blocks, segments, and extents for describing the physical layout, and tables and tablespaces for describing the logical layout. Unfortunately, I don't remember the details, and in any case I learned them two versions ago.
In general, there will be two different ways of describing databases. From the users's point of view, a database can be divided into tablespaces, and the tablespaces contain tables, which contain rows, and so on. These do have to live somewhere on the disk, though, and so the DBA will typically have to allocate files, and inside the files there has to be a layout that will allow rows and fields to be stored in the bytes of the file.
These are implementation details, and are normally of interest only to the DBA. You'll find them in the documentation for individual database systems, and books intended for DBAs, and typically you'll find each individual database system in its own books.
Bear in mind that database systems usually come with documentation, and systems like Oracle and DB2 generally have very extensive documentation, often arranged in books that you can read online or download in some form or other. Of course, trying to actually find something on www.oracle.com or www.ibm.com can be a frustrating experience, but Google is probably still your friend.
There is a distinction between Oracle Physical Database Structure and Logical Database Structure. Oracle data files and control files are part of the physical structure, whereas blocks and extents are parts of logical.
Google search on "Oracle architecture" yields a bunch of results. This and this one may be useful, or you may choose to buy a good book such as this one. But one of the best sources of information about Oracle database is Oracle documentation itself.
Documentation for your database version of choice is freely available both online and for local download from the Oracle web site.
for innodb specifically:
a tablespace is both a physical and a logical concept. it used to be that all innodb tables had their data mixed together in the ibdata files. within those files, a tablespace is a not-necessarily-contiguous collection of segments that make up the table. in newer versions of mysql, a single table can be put in to its own file, which is also called a tablespace.
in either case, a tablespace contains:
segments, which contain 1 or more extents for that table.
extents contain 64 pages. i don't know why 64.
pages are 16k, which is theoretically optimized for efficient storage and retrieval at the disk level. pages are allocated in extent quantities when more pages are needed.
see page 11 of this pdf.
The storage structure of oracle is well described in the documentation: http://download.oracle.com/docs/cd/E11882%5F01/server.112/e10713/logical.htm#CNCPT304
A tablespace is a logical storage container made up of one or more data files. a tablespace is defined with a particular block size and in general all tablespaces in an Oracle database will have a common blocksize. 8kb is the most commonly chosen one.
Storage of indexes, tables, clusters etc, is represented as segments. A single segment will be assigned to a single tablespace but can be spread over multiple data files. An unpartitioned table or index will have a single segment. For a partitioned table or index each partition or subpartition will be a single segment.
Segments are built of extents, which are logically contiguous sets of blocks. New extents are allocated to segments either manually or automatically as required by growth patterns.