 |
|
Oracle Tips by Burleson |
Oracle 10g Ranking Functions
A ranking function typically compares the ranks
of records in the data set, based on the values of a set of
measures.
The default sorting of the ORDER BY in rank()
is ASC (ascending), so the keyword 'asc' can be omitted in either
the query or the materialized view. Oracle 10g will do a general
rewrite if the materialized view has the keyword and the query does
not (and vice versa).
There are other situations where the
materialized view is grouped on more columns than the query. Then,
it may be possible to do a rollup and rewrite the query.
Let us look at an example where we have the
materialized view rank_on_sum_mv, which groups data by prod_id and
cust_id.
CREATE
MATERIALIZED VIEW rank_on_sum_mv
ENABLE QUERY REWRITE AS
SELECT prod_id, cust_id, SUM(quantity_sold) AS
Total FROM sales GROUP BY prod_id, cust_id;
Here is a possible query rewrite statement:
SELECT
prod_id, SUM(total),
RANK() OVER (ORDER BY SUM(total)) AS n_rank
FROM rank_on_sum_mv GROUP BY prod_id;
Get the complete Oracle10g story:
To get the code instantly, click here:
Need an Oracle Mentor?
BEI is now offering personal mentors for Oracle DBAs where you can have an
Oracle expert right at your fingertips, anytime day or night. We work with
hundreds of Oracle databases every year, so we know exactly how to quickly
assist you with any Oracle question.
Why risk an unplanned outage? You can now get telephone access to Don
Burleson or any of his Oracle Certified DBAs with more than 20 years of
full-time IT experience. Click here for details:
http://www.dba-oracle.com/service_oracle_backup.htm
|
