Cube oracle описание
Обновлено: 04.05.2024
Результат инструкции SELECT в данном примере CUBE может интерпретироваться следующим образом:
Пример оператора CUBE
суммарный оклад для каждой должности в подразделении (для подразделений, идентификатор которых меньше 60);
суммарный оклад для каждого подразделения, идентификатор которого меньше 60;
суммарный оклад для каждой должности независимо от подразделения;
суммарный оклад для тех подразделений, идентификатор которых меньше 60, независимо от названий должностей.
В этом примере 1 показывает общий итог, 2 показывает строки, подытоживаемые только по JOB_ID , 3 показывает несколько строк, подытоживаемых по DEPARTMENT_ID и JOB_ID , а 4 показывает несколько строк, подытоживаемых только по DEPARTMENT_ID .
Оператор CUBE также выполнил операцию ROLLUP , чтобы отобразить промежуточные итоги для подразделений, идентификатор которых меньше 60, и суммарный оклад для подразделений, идентификатор которых меньше 60, независимо от названий должностей. Далее оператор CUBE выводит суммарный оклад для каждой должности независимо от подразделения.
Примечание . Аналогично оператору ROLLUP , создание промежуточных итогов для n измерений (т.е. n столбцов в предложении GROUP BY ) без оператора CUBE требует, чтобы 2n инструкций SELECT должны быть связаны с помощью UNION ALL . Следовательно, отчет с тремя измерениями потребует связать с помощью UNION ALL 23 = 8 инструкций SELECT .
20
Analyzing Data with ROLLUP, CUBE, AND TOP-N QUERIES
The last decade has seen a tremendous increase in the use of query, reporting, and on-line analytical processing (OLAP) tools, often in conjunction with data warehouses and data marts. Enterprises exploring new markets and facing greater competition expect these tools to provide the maximum possible decision-making value from their data resources.
Oracle expands its long-standing support for analytical applications in Oracle8i release 8.1.5 with the CUBE and ROLLUP extensions to SQL. Oracle also provides optimized performance and simplified syntax for Top-N queries. These enhancements make important calculations significantly easier and more efficient, enhancing database performance, scalability and simplicity.
ROLLUP and CUBE are simple extensions to the SELECT statement's GROUP BY clause. ROLLUP creates subtotals at any level of aggregation needed, from the most detailed up to a grand total. CUBE is an extension similar to ROLLUP , enabling a single statement to calculate all possible combinations of subtotals. CUBE can generate the information needed in cross-tab reports with a single query. To enhance performance, both CUBE and ROLLUP are parallelized: multiple processes can simultaneously execute both types of statements.
For information on parallel execution, see Oracle8i Concepts.
Enhanced Top-N queries enable more efficient retrieval of the largest and smallest values of a data set. This chapter presents concepts, syntax, and examples of CUBE , ROLLUP and Top-N analysis.
Analyzing across Multiple Dimensions
One of the key concepts in decision support systems is "multi-dimensional analysis": examining the enterprise from all necessary combinations of dimensions. We use the term "dimension" to mean any category used in specifying questions. Among the most commonly specified dimensions are time, geography, product, department, and distribution channel, but the potential dimensions are as endless as the varieties of enterprise activity. The events or entities associated with a particular set of dimension values are usually referred to as "facts." The facts may be sales in units or local currency, profits, customer counts, production volumes, or anything else worth tracking.
Here are some examples of multi-dimensional requests:
-
Show total sales across all products at increasing aggregation levels: from state to country to region for 1996 and 1997.
All the requests above constrain multiple dimensions. Many multi-dimensional questions require aggregated data and comparisons of data sets, often across time, geography or budgets.
Answering multi-dimensional questions often involves huge quantities of data, sometimes millions of rows. Because the flood of detailed data generated by large organizations cannot be interpreted at the lowest level, aggregated views of the information are essential. Subtotals across many dimensions are vital to multi-dimensional analyses. Therefore, analytical tasks require convenient and efficient data aggregation.
Optimized Performance
Not only multi-dimensional issues, but all types of processing can benefit from enhanced aggregation facilities. Transaction processing, financial and manufacturing systems--all of these generate large numbers of production reports needing substantial system resources. Improved efficiency when creating these reports will reduce system load. In fact, any computer process that aggregates data from details to higher levels needs optimized performance.
To leverage the power of the database server, powerful aggregation commands should be available inside the SQL engine. New extensions in Oracle provide these features and bring many benefits, including:
-
Simplified programming requiring less SQL code for many tasks
Oracle8i provides all these benefits with the new CUBE and ROLLUP extensions to the GROUP BY clause. These extensions adhere to the ANSI and ISO proposals for SQL3, a draft standard for enhancements to SQL.
A Scenario
To illustrate CUBE , ROLLUP , and Top-N queries, this chapter uses a hypothetical videotape sales and rental company. All the examples given refer to data from this scenario. The hypothetical company has stores in several regions and tracks sales and profit information. The data is categorized by three dimensions: Time, Department, and Region. The time dimensions are 1996 and 1997, the departments are Video Sales and Video Rentals, and the regions are East, West, and Central.
1997
Department
167,000
238,000
193,000
ROLLUP
ROLLUP enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. The ROLLUP extension is highly efficient, adding minimal overhead to a query.
Syntax
ROLLUP appears in the GROUP BY clause in a SELECT statement. Its form is:
Details
ROLLUP 's action is straightforward: it creates subtotals which "roll up" from the most detailed level to a grand total, following a grouping list specified in the ROLLUP clause. ROLLUP takes as its argument an ordered list of grouping columns. First, it calculates the standard aggregate values specified in the GROUP BY clause. Then, it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns. Finally, it creates a grand total.
ROLLUP will create subtotals at n+1 levels, where n is the number of grouping columns. For instance, if a query specifies ROLLUP on grouping columns of Time, Region, and Department ( n=3), the result set will include rows at four aggregation levels.
Example
This example of ROLLUP uses the data in the video store database.
-
Regular aggregation rows that would be produced by GROUP BY without using ROLLUP
Interpreting "[NULL]" Values in Results
See the section "GROUPING Function" for details on how the NULLs representing subtotals are distinguished from NULLs stored in the data.
The NULLs shown in the figures of this paper are displayed only for clarity: in standard Oracle output these cells would be blank.
Calculating Subtotals without ROLLUP
The approach shown in the SQL above has two shortcomings compared to using the ROLLUP operator. First, the syntax is complex, requiring more effort to generate and understand. Second, and more importantly, query execution is inefficient because the optimizer receives no guidance about the user's overall goal. Each of the four SELECT statements above causes table access even though all the needed subtotals could be gathered with a single pass. The ROLLUP extension makes the desired result explicit and gathers its results with just one table access.
The more columns used in a ROLLUP clause, the greater the savings versus the UNION approach. For instance, if a four-column ROLLUP replaces a UNION of 5 SELECT statements, the reduction in table access is four-fifths or 80%.
Some data access tools calculate subtotals on the client side and thereby avoid the multiple SELECT statements described above. While this approach can work, it places significant loads on the computing environment. For large reports, the client must have substantial memory and processing power to handle the subtotaling tasks. Even if the client has the necessary resources, a heavy processing burden for subtotal calculations may slow down the client in its performance of other activities.
When to Use ROLLUP
Use the ROLLUP extension in tasks involving subtotals.
-
It is very helpful for subtotaling along a hierarchical dimension such as time or geography. For instance, a query could specify a ROLLUP of year/month/day or country/state/city.
For information on parallel execution, see Oracle8i Concepts.
CUBE enables a SELECT statement to calculate subtotals for all possible combinations of a group of dimensions. It also calculates a grand total. This is the set of information typically needed for all cross-tabular reports, so CUBE can calculate a cross-tabular report with a single SELECT statement. Like ROLLUP , CUBE is a simple extension to the GROUP BY clause, and its syntax is also easy to learn.
Syntax
CUBE appears in the GROUP BY clause in a SELECT statement. Its form is:
Details
Оператор CUBE – это дополнительный параметр в предложении GROUP BY инструкции SELECT .
CUBE – это расширение предложения GROUP BY .
Оператор CUBE можно использовать, чтобы получать значения перекрестной таблицы с помощью одной инструкции SELECT .
Оператор CUBE может быть применен ко всем функциям агрегирования, включая AVG , SUM , MAX , MIN и COUNT . Он используется для создания наборов результатов, обычно используемых для отчетов в виде перекрестных таблиц. ROLLUP создает только часть возможных комбинаций промежуточных итогов, а CUBE создает промежуточные итоги для всех возможных комбинаций группировок, заданных в предложении GROUP BY , и общий итог. Оператор CUBE используется с функцией агрегирования для создания дополнительных строк в наборе результатов. Столбцы, включенные в предложение GROUP BY , содержат перекрестные ссылки для создания расширенного набора групп. Функция агрегирования, заданная в списке выбора, применяется к этим группам, чтобы получить итоговые значения для дополнительных строк суперагрегата. Число дополнительных групп в наборе результатов определяется числом столбцов, включенным в предложение GROUP BY .
По сути для создания суперагрегатов используются все возможные комбинации столбцов или выражений в предложении GROUP BY . При наличии n столбцов или выражений в предложении GROUP BY возможно 2n комбинаций суперагрегатов. Математически эти комбинации образуют n-мерный куб, от которого оператор и получил свое название.
С помощью внешнего приложения или средств программирования эти значения суперагрегатов могут быть отображены на диаграммах и графиках, которые визуально и наглядно представляют результаты и отношения.
Операторы ROLLUP и CUBE задаются в предложении GROUP BY запроса.
Группирование ROLLUP создает набор результатов, содержащий обычные группированные строки и строки промежуточных итогов. Оператор ROLLUP также вычисляет общий итог. Операция CUBE в предложении GROUP BY группирует выбранные строки в зависимости от значений всех возможных комбинаций выражений в спецификации и возвращает одну строку сводных данных для каждой группы. Оператор CUBE можно использовать для создания значений строк перекрестной таблицы.
Используйте операторы ROLLUP или CUBE с предложением GROUP BY , чтобы создать супер-агрегированные строки с помощью столбцов с перекрестными ссылками.
Группирование ROLLUP создает набор результатов, содержащий обычные группированные строки и значения промежуточных итогов.
Группирование CUBE создает набор результатов, содержащий строки из ROLLUP и строки перекрестной таблицы.
Примечание . При работе с операторами ROLLUP и CUBE убедитесь, что столбцы, следующие за предложением GROUP BY , связаны друг с другом осмысленными и реалистичными отношениями. В противном случае операторы вернут бесполезную информацию.
Читайте также: