JL Computer Consultancy

Optimisation and Sorting (7.3, 8.0 and 8.1).

Auguest 2000


With the passage of time Oracle is coninually adding new features and options to the Optimizer. Most of the time such features improve the performance of existing code by allowing Oracle to discover different execution strategies, but sometimes you can find that the new features are just clever enough to spoil some of your cunning performance tricks.

This note is just a brief example of how SORTING and INLINE views have interfered with each other over the last three major versions, 7.3, 8.0, and 8.1 to introduce, eliminate, and then re-introduce a useful perfomance benefit. Consider the following SQL statement:

select
        product_id,
        sum(sales)                     sum_sales, 
        count(distinct store_id)       ct
from    sales_history
group by 
        product_id
;

This is a fairly simple model of a SALES query - for each product, calculate the total sales value, and number of stored that actually involved in selling that product. Assume that you created the SALES_HISTORY table with the following SQL so that there were exactly 2,000 rows in the table:

create table sales_history(
        store_id,
        sales,
        product_id
)
nologging
as
select 
        mod(rownum,57),rownum,mod(rownum,73) 
from 
        all_objects 
where 
        rownum <= 2000
;

The first quesion is, how many rows would Oracle sort to produce the result ? The answer may be larger than you would expect. Although there are 2,000 rows in the table, Oracle has to sort 4,000 rows because of the presence of the DISTINCT in the count(distinct store_id).

What happens, then, if you decide to enhance the report by including a column that gives the sales per product averaged across the number of stores that actually sold that product - in other words dividing the sales total by the store count. In the sample code for this, we include a 'decode' to avoid the risk of a 'divide by zero error', even though this appears to be redundant in this simple example:

select
        product_id,
        sum(sales), 
        count(distinct store_id), 
        sum(sales)/
               decode(
                       count(distinct store_id),
                               0,null, 
                               count(distinct store_id)
        )
from    sales_history
group by 
        product_id
;

If you are running Oracle 8.1 you will find that Oracle has still sorted only 4,000 rows to address this query - the optimizer has recognised that the three occurences of count(distinct store_id) are equivalent, and has therefore processed the query in a way that avoids sorting and counting three times.

If you are running Oracle 8.0 or Oracle 7.3 you will find that Oracle has sorted 8,000 rows because it has performed a sub-sort on the store_id three times, once for each DISTINCT. (Interestingly, the sorts(memory) will only show one sort which is due to the overall sort by product id).

Under 7.3, the total work-load can be reduced to the original 4,000 row sort by introducing an in-line view:

select  /*+ hint position 1 */
        product_id,
        sum_sales, 
        ct
        sum_sales/decode(ct,0,null,ct)
from
        (
        select  /*+ hint position 2 */
               product_id,
               sum(sales)                     sum_sales,
               count(distinct store_id)       ct
        from    sales_history
        group by 
               product_id
        )       sum_view
;

Unfortunately, Oracle 8.0 is just clever enough to be able to open up and fold out in-line views, so our work-around for 7.3 is spoilt by Oracle re-writing the query back into it's original form. Fortunately we can call on a HINT at this point, and make Oracle do things the way we want - the NO_MERGE hint. This hint tells Oracle that it should NOT attempt to merge a view (in-line or otherwise) with the rest of the SQL before working out a full access path; in this way we can insist that Oracle pre-generates the sorted summary information once before using intermediate results in the decode().

The NO_MERGE hint can be used in two locations - indicated above by the /*+ hint positions */. If you include the hint in /*+ hint position 1 */ it should reference the alias you have given to the in-line view. - /*+ no_merge ( sum_view) */ If you include the index in /*+ hint position 2 */ the hint is actually inside the view so the view alias is invisible, and the hint should simply read /*+ no_merge */.

Back to Main Index of Topics