有一篇文章讲解了水平(列)分区的情况下,如何进行优化?
原文链接如下:http://everythingoracle.com/obieehp.htm
总结:
如果如果查询对应到两个表,两个表中存在同样的维度,只是相关的指标被分割到两张表中,比如A(product_id,sales_quantity),B(product_id,unit_price),如果简单的分别引入两张表,做好跟product维度表的关联,会发出两条sql。如果能够尽量的合并,只发出一条sql,这样会对性能有显著地提升。当然了,我们是在两张表位于同一个库的情况下了,如果不同的库,则比较难办了。可以在BIEE admin tool的物理层,采用“select”视图的方式,预先将表进行关联,从而指定了发出的sql,该文章中就用到了with语句的写法。具体如下图:
|
|
Oracle9i
significantly enhances both
the functionality and performance of SQL to address the requirements of business
intelligence queries. The SELECT statement's WITH clause, introduced in
Oracle9i
, provides
powerful new syntax for enhancing query performance. It optimizes query speed by
eliminating redundant processing in complex queries.
Consider a lengthy query which has multiple references to a
single subquery block. Processing subquery blocks can be costly, so recomputing
a block every time it is referenced in the SELECT statement is highly
inefficient. The WITH clause enables a SELECT statement to define the subquery
block at the start of the query, process the block just once, label the results,
and then refer to the results multiple times.
The WITH clause, formally known as the subquery factoring
clause, is part of the SQL-99 standard. The clause precedes the SELECT statement
of a query and starts with the keyword "WITH." The WITH is followed by the
subquery definition and a label for the result set. The query below shows a
basic example of the clause:
WITH channel_summary AS
( SELECT channels.channel_desc,
SUM(amount_sold) AS channel_total
FROM sales, channels
WHERE sales.channel_id = channels.channel_id
GROUP BY channels.channel_desc )
SELECT channel_desc, channel_total
FROM channel_summary
WHERE channel_total >
( SELECT SUM(channel_total) * 1/3
FROM channel_summary );
This query uses the WITH clause to calculate the sum of sales
for each sales channel and label the results as channel_summary. Then it checks
each channel's sales total to see if any channel's sales are greater than one
third of the total sales. By using the new clause, the channel_summary data is
calculated just once, avoiding an extra scan through the large sales table.
Although the primary purpose of the WITH clause is performance
improvement, it also makes queries easier to read, write and maintain. Rather
than duplicating a large block repeatedly through a SELECT statement, the block
is localized at the very start of the query. Note that the clause can define
multiple subquery blocks at the start of a SELECT statement: when several blocks
are defined at the start, the query text is greatly simplified and its speed
vastly improved.
The SQL WITH clause in Oracle9i
significantly improves
performance for complex business intelligence queries. Together with the many
other SQL enhancements in Oracle9i
, the WITH clause extends
Oracle's leadership in business intelligence.
|
- 大小: 67.7 KB
分享到:
相关推荐
IEEE Guide for Improving the Lightning Performance of Electric Power Overhead Distribution Lines
Specifically, mobile users with high bit rates TCP services are considered, and the impacts of the intra LTE handover over their perceived throughput are studied. Due to the mobility of the users ...
The investigation produced detailed measurements in order to profile the application and help on improving the performance. Code analysis shows that the current IPOP version suffers from ...
Improving TCP Performance over Wireless Networksat the Link Lay 英文原版 We present the transport unaware link improvement protocol (TULIP), which dramatically improves the performance of TCP over ...
Improving routing performance when several routing protocols are used sequentially in a WSN
Improving the performance of a Gigabit Ethernet driver:改进网卡性能的一些资料。
Sharing Spark RDD states between different Spark applications for improving performance. Processing events & streaming data, integrate Apache Ignite with other frameworks like Storm, Camel, etc. ...
Improving forecasts of GARCH family models with the artificial neural networks: An application to the daily returns in Istanbul Stock Exchange. Expert Systems with Applications, 36 (4), 7355–7362 .
用基于模板的方法改善Web服务安全性能 Improving WS-Security Performance with a Template-Based Approach
Indexes are the most useful tool for improving query performance. Without a useful index, Microsoft® SQL Server™ must search every row on every page in table to find the rows to return. With a ...
Designing with Data: Improving the User Experience with A/B Testing by Rochelle King English | 29 Mar. 2017 | ASIN: B06XY9TTN8 | 370 Pages | AZW3 | 4.61 MB On the surface, design practices and data ...
Optimizing Java_Practical Techniques for Improving JVM Application Performance-O’Reilly(2018) How do you define performance? Most developers, when asked about the performance of their application, ...
And with the growth of server virtualization and consolidation, high-volume data transactions, real-time technologies such as VoIP and video on demand, and the increase to 10GbE bandwidth, the ...
异常检测 异常检测 异常检测 Improving iForest with Relative Mass.
Provides you with tips and real-life case studies for improving application performance What you’ll learn Measure your code’s performance with profilers and other tools. Understand where the ...
From data abstraction, the course then turns to transaction management, with some additional material on improving query performance. Finally, there is an introduction of up-to-date trends in ...
With this comprehensive guide, developers and performance engineers alike will learn a variety of features, tools, and processes for improving the way Java 7 and 8 applications perform.
Improving the quality of end-of-life care for hospitalized patients is a priority for healthcare organizations. Studies have shown that physicians tend to over-estimate prog- noses, which in ...
Improving the Start-up Performance of the TFRC Protocol