SQL Server 和 Oracle 哪个在可扩展性方面更好？

SQL Server and Oracle, which one is better in terms of scalability?

提问by user21572

MS SQL Server and Oracle, which one is better in terms of scalability?
For example, if the data size reach 500 TB etc.

MS SQL Server 和 Oracle，在可扩展性方面哪个更好？
例如，如果数据大小达到 500 TB 等。

回答by ConcernedOfTunbridgeWells

Both Oracle and SQL Server are shared-disk databases so they are constrained by disk bandwidth for queries that table scan over large volumes of data. Products such as Teradata, Netezzaor DB/2 Parallel Editionare 'shared nothing'architectures where the database stores horizontal partitions on the individual nodes. This type of architecture gives the best parallel query performance as the local disks on each node are not constrained through a central bottleneck on a SAN.

Oracle 和 SQL Server 都是共享磁盘数据库，因此它们受到磁盘带宽的限制，用于对大量数据进行表扫描的查询。产品如Teradata的，Netezza公司或DB / 2并行版是“不共享”体系结构，其中各个节点上的数据库中存储的水平分区。这种类型的架构提供了最佳的并行查询性能，因为每个节点上的本地磁盘不受 SAN 上中央瓶颈的限制。

Shared disk systems (such as Oracle Real Application Clustersor Clustered SQL Serverinstallations still require a shared SAN, which has constrained bandwidth for streaming. On a VLDB this can seriously restrict the table-scanning performance that is possible to achieve. Most data warehouse queries run table or range scans across large blocks of data. If the query will hit more than a few percent of rows a single table scan is often the optimal query plan.

共享磁盘系统（例如 Oracle Real Application Clusters或Clustered SQL Server安装仍然需要共享SAN，它限制了流式传输的带宽。在 VLDB 上，这会严重限制可能实现的表扫描性能。大多数数据仓库查询跨大块数据运行表或范围扫描。如果查询将命中超过百分之几的行，单表扫描通常是最佳查询计划。

Multiple local direct-attach disk arrays on nodes gives more disk bandwidth.

节点上的多个本地直连磁盘阵列提供了更多的磁盘带宽。

Having said that I am aware of an Oracle DW shop (a major european telco) that has an oracle based data warehouse that loads 600 GB per day, so the shared disk architecture does not appear to impose unsurmountable limitations.

话虽如此，我知道一家 Oracle DW 商店（一家主要的欧洲电信公司）拥有一个基于 Oracle 的数据仓库，每天加载 600 GB，因此共享磁盘架构似乎没有强加不可逾越的限制。

Between MS-SQL and Oracle there are some differences. IMHO Oracle has better VLDB support than SQL server for the following reasons:

MS-SQL 和 Oracle 之间存在一些差异。恕我直言，Oracle 对 VLDB 的支持比 SQL 服务器好，原因如下：

• Oracle has native support for bitmap indexes, which are an index structure suitable for high speed data warehouse queries. They essentially do a CPU for I/O tradeoff as they are run-length encoded and use relatively little space. On the other hand, Microsoft claim that Index Intersectionis not appreciably slower.

• Oracle has better table partitioning facilities than SQL Server. IIRC The table partitioning in SQL Server 2005 can only be done on a single column.

• Oracle can be run on somewhatlargerhardwarethan SQL Server, although one can run SQL server on some quiterespectablylargesystems.

• Oracle has more mature support for Materialized viewsand Query rewriteto optimise relational queries. SQL2005 does have some query rewrite capability but it is poorly documented and I haven't seen it used in a production system. However, Microsoft will suggest that you use Analysis Services, which does actually support shared nothing configurations.

• Oracle 原生支持位图索引，位图索引是一种适合高速数据仓库查询的索引结构。它们本质上是为 I/O 权衡做一个 CPU，因为它们是游程编码的并且使用相对较少的空间。另一方面，Microsoft 声称Index Intersection并没有明显变慢。

• Oracle 具有比 SQL Server 更好的表分区功能。IIRC SQL Server 2005 中的表分区只能在单个列上完成。

• 甲骨文可以运行在一定程度上较大的硬件比SQL Server中，虽然可以在某些运行SQL服务器相当体面的大系统。

• Oracle 对物化视图和查询重写有更成熟的支持，以优化关系查询。SQL2005 确实有一些查询重写功能，但它的文档很差，我还没有看到它在生产系统中使用过。但是，Microsoft 会建议您使用Analysis Services，它实际上支持无共享配置。

Unless you have truly biblical data volumes and are choosing between Oracle and a shared nothing architecture such as Teradata you will probably see little practical difference between Oracle and SQL Server. Particularly since the introduction of SQL2005 the partitioning facilities in SQL Server are viewed as good enough and there are plentyof examplesof multi-terabytesystems that have been successfully implemented on it.

除非您拥有真正符合圣经的数据量并在 Oracle 和无共享架构（如 Teradata）之间进行选择，否则您可能会发现 Oracle 和 SQL Server 之间几乎没有实际区别。特别是因为引进SQL2005的SQL Server中的分区设施被视为足够好并且有大量的例子的多TB已在其上成功实施系统。

回答by Gary Myers

When you are talking 500TB, that is (a) big and (b) specialized. I'd be going to a consultancy firm with appropriate specialists to look at the existing skill sets, integration with existing technology stacks, expected usage, backup/recovery/DR requirements....

当您谈论 500TB 时，即 (a) 大 (b) 专业。我会去一家拥有合适专家的咨询公司，看看现有的技能组合、与现有技术堆栈的集成、预期使用情况、备份/恢复/DR 要求......

In short, it's not the sort of project I'd be heading into based on opinions from stackoverflow. No offence intended, but there's simply too many factors to take into account, a lot of which would be business confidential.

简而言之，根据来自 stackoverflow 的意见，这不是我将要进行的那种项目。无意冒犯，但要考虑的因素太多了，其中很多都是商业机密。

回答by KristoferA

Whether Oracle or MSSQL will scale / perform better is question #15. The data model is the first make-it or break-it item regardless of if you're running Oracle, MSSQL, Informix or anything else. Data model structure, what kind of applicaiton, how it accesses the db etc, which platform your developers know well enough to target for a large system etc are the first questions you should ask yourself.

Oracle 或 MSSQL 是否会扩展/执行得更好是问题 #15。无论您运行的是 Oracle、MSSQL、Informix 还是其他任何东西，数据模型都是第一个成功或失败的项目。数据模型结构、什么样的应用程序、它如何访问数据库等，您的开发人员对哪个平台足够了解以针对大型系统等是您应该问自己的第一个问题。

回答by HLGEM

I have to agree with those who said deisgn was more important.

我不得不同意那些说设计更重要的人。

I've worked with superfast and super slow databases of many different flavors (the absolute worst being an Oracle database, but it wasn't Oracle's fault). Design of the database and how you decide to index it and partition it and query it have far more to do with the scalability than whether the product is from MSSQL Server or Oracle.

我使用过许多不同风格的超快和超慢数据库（绝对最糟糕的是 Oracle 数据库，但这不是 Oracle 的错）。数据库的设计以及您决定如何对其进行索引、分区和查询，与产品是来自 MSSQL Server 还是 Oracle 相比，与可伸缩性的关系更大。

I think you may more easily find more Oracle dbas with terrabyte database experience (running a large database is a specialty just like knowing a particular flavor of SQL) but that could depend on your local area.

我认为您可能更容易找到更多具有 terrabyte 数据库经验的 Oracle dbas（运行大型数据库是一种专长，就像了解 SQL 的特定风格一样），但这可能取决于您所在的地区。

回答by Cruachan

I've worked as a DBA on Oracle (although some years back) and I use MSSQL extensively now, although not as a formal DBA. My advice would be that in the vast majority of cases both will meet everything you can throw at them and your performance issues will be much more dependent upon database design and deployment than the underlying characteristics of the products, which in both cases are absolutely and utterly solid (MSSQL is the best product that MS makes in many peoples opinion so don't let the usual perception of MS blind you on that).

我曾在 Oracle 担任 DBA（尽管几年前），现在我广泛使用 MSSQL，尽管不是正式的 DBA。我的建议是，在绝大多数情况下，两者都可以满足您的所有要求，并且您的性能问题将更多地依赖于数据库设计和部署，而不是产品的基本特性，这在两种情况下都是绝对和完全的可靠（MSSQL 是 MS 在许多人看来是最好的产品，所以不要让通常对 MS 的看法蒙蔽了你）。

Myself I would tend towards MSSQL unless your system is going to be very large and truly enterprise level (massive numbers of users, multiple 9's uptime etc.) simply because in my experience Oracle tends to require a higher level of DBA knowledge and maintenance than MSSQL to get the best out of it. Oracle also tends to be more expensive, both for initial deployment and in the cost to hire DBAs for it. OTOH if you are looking at an enterprise system then Oracle would have the edge, not least because if you can afford it their support is second to none.

我自己我会倾向于 MSSQL 除非你的系统将是非常大的和真正的企业级（大量用户，多个 9 的正常运行时间等）仅仅因为根据我的经验 Oracle 往往需要比 MSSQL 更高水平的 DBA 知识和维护充分利用它。Oracle 也往往更昂贵，无论是在初始部署方面还是在为其聘请 DBA 的成本方面。OTOH 如果您正在查看企业系统，那么 Oracle 将具有优势，尤其是因为如果您负担得起，他们的支持是首屈一指的。

回答by Guy

When you get to OBSCENE database sizes (where over 1TB is really big enough, and 500TB is frigging massive), then operational support must come very high up on the list of requirements. With that much data, you don't mess about with penny pinching system specifications.

当您达到 OBSCENE 数据库大小（超过 1 TB 真的足够大，而 500 TB 是巨大的）时，那么运营支持必须在需求列表中占据很高的位置。有了这么多数据，您就不会被一分钱一分货的系统规范弄得一团糟。

How are you going to backup that size of system? Upgrade the OS and patch the database? Scalability and reliability a concern?

你打算如何备份这么大的系统？升级操作系统并修补数据库？可扩展性和可靠性值得关注吗？

I have experience of both Oracle and MS SQL, and for the really really big systems (users, data or importance) then Oracle is better designed for operational support and data management.

我有 Oracle 和 MS SQL 的经验，对于真正的大系统（用户、数据或重要性），Oracle 更适合用于运营支持和数据管理。

Every tried to backup and restore a 1TB+ SQL Server database split over multiple databases on multiple instances with transaction log files being spat out everywhere by each database and trying to keep it all in sync? Good luck with that.

每个人都试图备份和恢复一个 1TB 以上的 SQL Server 数据库，该数据库分布在多个实例上的多个数据库上，每个数据库都在到处吐出事务日志文件，并试图保持同步？祝你好运。

With Oracle, you have ONE database (so I disagree with the "shared nothing" approach is better) with ONE set of REDO logs(1) and one set of archive logs(2) and you can just add extra hardware nodes without changing (i.e. repartitioning) you application and data.

使用 Oracle，您有一个数据库（所以我不同意“无共享”方法更好），其中包含一组 REDO 日志 (1) 和一组存档日志 (2)，并且您只需添加额外的硬件节点而无需更改（即重新分区）您的应用程序和数据。

(1) Redo logs are, of course, mirrored. (2) Archive logs are, of course, stored in multiple locations.

(1) 当然，重做日志是镜像的。(2) 当然，归档日志存储在多个位置。

回答by Mladen

oracle people will tell you oracle is better, sql server peopele will tell you sql server is better. i say they scale pretty much the same. use what you know better. you have databases out there that are that size on oracle as well as sql server

oracle 的人会告诉你 oracle 更好，sql server 的人会告诉你 sql server 更好。我说它们的比例几乎相同。使用你更了解的东西。你在 oracle 和 sql server 上都有这么大的数据库

回答by Dheer

It would also depend on what is your application meant for. If it uses only Inserts with very few updates, then I think MSSQL would be more scalable and better in terms of performance. However if one has lots of updates, then Oracle would scaleup better

这也取决于您的应用程序的用途。如果它只使用很少更新的插入，那么我认为 MSSQL 会在性能方面更具可扩展性和更好。但是，如果有很多更新，那么 Oracle 会更好地扩展

回答by Rad

I very much doubt that you are going to get an objective answer to that particular question, until you come across anyone that has implemented the same database (schema, data, etc.) on both platforms.

我非常怀疑您是否会得到该特定问题的客观答案，直到您遇到在两个平台上都实现了相同数据库（架构、数据等）的任何人。

However given the fact that you can find millions of happy users of both databases, I dare say it's not too much of a stretch to say either will scale just fine (I've seen a snappy Sql 2005 implementation of 300 TB that seemed pretty responsive)

然而，鉴于您可以找到这两个数据库的数百万快乐用户，我敢说这两个数据库都可以很好地扩展并不过分（我已经看到了一个 300 TB 的快速 Sql 2005 实现，它似乎非常敏感)

回答by william chen

Oracle like a high-quality manual film camera, which needs the best photographer to take the best picture while MS SQL like an automatic digital camera. In old days, of course, all professional photographers will use film camera, now think about how many professional photographers use automatic digital camera.

Oracle 就像一台高质量的手动胶片相机，需要最好的摄影师才能拍出最好的照片，而 MS SQL 就像一台自动数码相机。在过去，当然所有专业摄影师都会使用胶片相机，现在想想有多少专业摄影师使用自动数码相机。