Logical architecture is where Oracle and SQL Server look alike, until you look at how the data is stored. Even after many years in the industry, I keep learning from peers and the people around me. I have been writing comparisons between Oracle and SQL Server because an analogy makes learning easy. Do you use a similar approach when you learn something new?
Last time, I wrote about Comparison: Understanding Tables Between Oracle and SQL Server. It was a great experience and a wonderful lesson. Next, I wanted to know how the physical and logical architectures of these two databases differ.
How the Physical Files Map to the Logical Structures
The picture below shows how the physical data files map to the logical architecture of both databases.
The names differ a little, but the fundamentals stay the same. Blocks in Oracle are logically the same as pages in SQL Server. The concept of a tablespace is similar to a filegroup in SQL Server.
Blocks and Pages in Detail
I wanted to look closer at the block concept. Both databases have similarities, and they are also different. The table below shows the details.
| Structure | Oracle | SQL Server |
|---|---|---|
| Smallest unit of logical storage | Block | Page |
| Block size | Variable | 8 KB fixed |
| Storage allocation | Performed in multiple pages; are ‘extents’ | Performed in multiple pages; are ‘extents’ |
| Extent size | Variable | 64 KB fixed |
| Segment | Any logical structure that is allocated storage | No equivalent structure |
At a high level, both look the same. When the data is persisted, though, there are differences to understand. The most striking one is the block size. SQL Server has a fixed 8 KB page, while the Oracle block size is variable.
This has been a talking point with many Oracle customers I met in the past. It is by design in SQL Server, and I’m glad it has been that way since the start.
Your Turn
Why have you wanted larger or smaller block sizes? Which block sizes do you use in your Oracle environments? I would like to understand the pattern. Please tell me about your experience in the comments.
Logical architecture is not a different idea in each database, it is the same idea with different page sizes.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
5 Comments. Leave new
So, do you think the variable size has a advantage or vice-versa?
If you know your exact requirement and the size required then the variable size can help you in a much better way than fixed size.
Thanks Pinal,
What about the server as a whole? SQL we can have multiple Database in one server,in oracle is it same or Single database per Server?
It’s same. Oracle has a concept call packages where you can have multiple stored procedures in a single package. In SQL Server you don’t have such concept. There a numerous differences between PL-SQL and T-SQL, Data Types etc. But basic concepts are same I suppose.
Yes there are many similar features in oracle and sqlserver. Eg oracle copied the concept of replication which is now called as an goldengate. Internally u can remember oracle internal database concept as BEST bus in mumbai. B BLOCK E EXTENT S SEGMENT T TABLESPACE.