Learning is a journey and that is something I love to keep in mind. Even after being so many years in the industry, I don’t stop learning from peers and people around me. This is the best way I can get better at something. I have been writing about the comparisons working with Oracle and SQL Server in the past and I personally thought this was a great way to learn something new by understanding the concepts of something that we always know. The learning is easy when we are able to make an analogy or metaphor as part of our learning. Do you guys ever use a similar approach while learning something that is completely new in your day-today life? Let us learn about Logical Architecture today.
Last time when I wrote about – Comparison – Understanding Tables Between Oracle and SQL Server, it was a great experience for me and a wonderful learning too. In search of understanding the basics, I wanted to know how the Physical and Logical architectures between these databases are different.
Below is a table that illustrates how the Physical Architecture of Data Files get mapped to the logical architecture between these two databases.
To see how the nomenclatures, differ a little, but the fundamentals never change. How Blocks are logically the same as we call as the pages inside SQL Server. The concept of tablespaces is similar to how we call as filegroups inside SQL Server.
I thought I needed to double click on the block concept working with these databases. You can see both have some similarities and at the same time are quite different.
As you can see though at a high level, both look same, when the data gets persisted, there are differences to understand. The most striking difference here is around how SQL Server has had 8kb fixed Block (page) size which compared to Oracle is variable. This has been the talking point when I met so many Oracle customers in the past. This is by design working with SQL Server and I glad it has been that way since its inception. Do let me know why you have ever wanted to have larger or smaller block sizes? What are some of the block sizes you have been using in Oracle environments in your place? I would surely like to understand the behavior pattern in the same.
Please let me know via comments below on your experiences here.
Reference: Pinal Dave (https://blog.sqlauthority.com)
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.