Oracle 多个模式聚合实时视图
所有，
寻找有关 Oracle 设计决策的一些指导，我目前正在尝试评估：
问题
我在同一 Oracle 数据库服务器上的三个不同模式中拥有数据。 我希望构建一个应用程序来显示来自所有三个模式的数据，但是显示的数据将基于应用于全局数据的实时排序和优先级规则（即：基于应用的优先级权重，我可能会从三个模式中的任何一个拉回数据）。
暂定解决方案
在数据库中创建一个视图，维护三个模式中相关列的逻辑链接，编写一个接受参数化优先级权重的存储过程。 应用程序随后调用存储过程从视图中选择“优先”行，然后根据返回的行直接查询关联的架构以获取其他数据。
我担心在执行每个查询时对数据进行排序/优先级的性能，但无法找到解决此问题的方法，因为优先级规则会经常更改。 我们讨论的是每个模式 2-3 百万行的数据集。
对于如何提供数据的聚合和排序视图，有人有其他建议吗？
All,
Looking for some guidance on an Oracle design decision I am currently trying to evaluate:
The problem
I have data in three separate schemas on the same oracle db server. I am looking to build an application that will show data from all three schemas, however the data that is shown will be based on real time sorting and prioritisation rules that is applied to the data globally (i.e.: based on the priority weightings applied I may pull back data from any one of the three schemas).
Tentative Solution
Create a VIEW in the DB which maintains logical links to the relevant columns in the three schemas, write a stored procedure which accepts parameterised priority weightings. The application subsequently calls the stored procedure to select the ‘prioritised’ row from the view and then queries the associated schema directly for additional data based on the row returned.
I have concerns over performance where the data is being sorted/ prioritised upon each query being performed but cannot see a way around this as the prioritisation rules will change often. We are talking of data sets in the region of 2-3 million rows per schema.
Does anyone have alternative suggestions on how to provide an aggregated and sorted view over the data?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
从多个模式查询（甚至 多个数据库）并不是什么大问题，即使在同一个查询中也是如此。 只需在表名前面加上您感兴趣的模式，就像
如果性能成为问题，那么这是一个大话题...最佳查询计划、索引和数据库连接方法都可以发挥作用。 我想到的一件事是，如果它不必是实时的，那么您可以使用 物化视图（又名“快照”） 将数据缓存在一个位置。 然后你就可以以合理的性能查询它。
只需将快照设置为适合您需要的间隔刷新即可。
Querying from multiple schemas (or even multiple databases) is not really a big deal, even inside the same query. Just prepend the table name with the schema you are interested in, as in
If performance becomes a problem, then that is a big topic... optimal query plans, indexes, and your method of database connection can all come into play. One thing that comes to mind is that if it does not have to be realtime, then you could use materialized views (aka "snapshots") to cache the data in a single place. Then you could query that with reasonable performance.
Just set the snapshots to refresh at an interval appropriate to your needs.
事实上，数据来自 3 个模式并不重要。 重要的是要了解数据更改的频率、条件更改的频率以及查询的频率。
如果有一组有限的标准（即，将以有限数量的方式查看数据），并且每隔几天才更改一次，并且会像疯狂一样查询，那么您可能应该查看物化视图。
如果标准几乎是无限的，那么创建物化视图就没有意义，因为它们不太可能被重用。 如果标准本身非常频繁地更改，情况也是如此，物化视图中的数据在这种情况下也无济于事。
另一个尚未解答的问题是源数据更新的频率以及掌握最新信息有多重要。 频繁更新源日可能意味着物化视图在一段时间内会变得“陈旧”，或者您可能会花费大量时间不必要地刷新物化视图以保持数据“新鲜”。
老实说，如果有足够的硬件，2-300 万条记录对于 Oracle 来说已经不算什么了。 在尝试花哨的（物化）视图之前，我可能会首先对简单的动态查询进行基准测试。
It doesn't matter that the data is from 3 schemas, really. What's important to know is how frequently the data will change, how often the criteria will change, and how frequently it will be queried.
If there is a finite set of criteria (that is, the data will be viewed in a limited number of ways) which only change every few days and it will be queried like crazy, you should probably look at materialized views.
If the criteria is nearly infinite, then there's no point making materialized views since they won't likely be reused. The same holds true if the criteria itself changes extremely frequently, the data in a materialized view wouldn't help in this case either.
The other question that's unanswered is how often the source data is updated, and how important is it to have the newest information. Frequently updated source day can either mean a materialized view will get "stale" for some duration or you may be spending a lot of time refreshing the materialized views unnecessarily to keep the data "fresh".
Honestly, 2-3 million records isn't a lot for Oracle anymore, given sufficient hardware. I would probably benchmark simple dynamic queries first before attempting fancy (materialized) view.
正如其他人所说，在 Oracle 中查询几百万行并不是真正的问题，但这取决于您执行此操作的频率 - 每十分之一秒可能会导致数据库服务器产生一些负载！
如果没有更多的业务需求细节和良好的数据模型，总是很难提供良好的性能想法。 它通常归结为提出一个理论，然后针对您的数据库进行尝试并访问它是否“足够快”。
您可能还值得退后一步，问问自己结果需要有多准确。 企业是否真的需要此查询的精确值，或者良好的估计是否可以接受
Tom Kyte（以 Ask Tom 闻名）在这些领域总是有一些有趣的想法（和实际事实）。 本文介绍了生成正确的动态搜索查询 - 但 Tom 指出，当您查询 Google 时，它永远不会尝试获取查询的准确命中数 - 它会给您一个猜测。 如果您可以应用良好的估计，那么您确实可以提高查询性能时间
As others have said, querying a couple of million rows in Oracle is not really a problem, but then that depends on how often you are doing it - every tenth of a second may cause some load on the db server!
Without more details of your business requirements and a good model of your data its always difficult to provide good performance ideas. It usually comes down to coming up with a theory, then trying it against your database and accessing if it is "fast enough".
It may also be worth you taking a step back and asking yourself how accurate the results need to be. Does the business really need exact values for this query or are good estimates acceptable
Tom Kyte (of Ask Tom fame) always has some interesting ideas (and actual facts) in these areas. This article describes generating a proper dynamic search query - but Tom points out that when you query Google it never tries to get the exact number of hits for a query - it gives you a guess. If you can apply a good estimate then you can really improve query performance times