Improving Query Performance with the SQL WITH Clause

有一篇文章讲解了水平(列)分区的情况下，如何进行优化？

原文链接如下：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.