Oracle between включает границы или нет

Обновлено: 06.07.2024

Читает индекс целиком (все строки) в порядке, представленном индексом. В зависимости от различной системной статистики СУБД может выполнять эту операцию, если нужны все строки в порядке индекса, например, из-за соответствующего предложения ORDER BY. Вместо этого оптимизатор может также использовать операцию Index Fast Full Scan и выполнить дополнительную операцию сортировки.

Index Fast Full Scan

Читает индекс целиком (все строки) в порядке, хранящемся на диске. Эта операция обычно выполняется вместо полного сканирования таблицы, если в индексе доступны все необходимые столбцы. Подобно операции TABLE ACCESS FULL, INDEX FAST FULL SCAN может извлечь выгоду из многоблочных операций чтения.

Table Access By Index ROWID

Извлекает строку из таблицы, используя ROWID, полученный из предыдущего поиска по индексу.

Table Access Full

Полное сканирование таблицы. Читает всю таблицу (все строки и столбцы), в порядке, хранящемся на диске. Хотя многоблочные операции чтения значительно повышают скорость сканирования полной таблицы, это все еще одна из самых дорогих операций. Помимо высоких затрат времени ввода-вывода, полное сканирование таблицы должно проверять все строки таблицы, что также занимает значительное количество процессорного времени.

Merge Join

Соединение слиянием объединяет два отсортированных списка. Обе стороны объединения должны быть предварительно отсортированы.

Nested Loops

Соединение вложенными циклами объединяет две таблицы, выбирая результат из одной таблицы и запрашивая другую таблицу для каждой строки из первой. Встречается очень часто. Выполняет довольно эффективное соединение относительно небольших наборов данных. Соединение вложенными циклами не требует сортировки входных данных.

Hash Join

Хеш-соединение загружает записи-кандидаты с одной стороны соединения в хеш-таблицу, которая затем проверяется для каждой строки с другой стороны соединения. Операция используется всегда, когда невозможно применить другие виды соединения: если соединяемые наборы данных достаточно велики и/или наборы данных не упорядочены по столбцам соединения.

Sort Unique

Сортирует строки и устраняет дупликаты.

Hash Unique

Более эффективная реализация алгоритма сортировки и устранения дупликатов с использованием хэш-таблицы. Заменяет операцию Sort Unique в определенных обстоятельствах.

Sort Aggregate

Вычисляет суммарные итоги с использованием агрегатных функций SUM, COUNT, MIN, MAX, AVG и пр.

Sort Order By

Сортирует результат в соответствии с предложением ORDER BY. Эта операция требует больших объемов памяти для материализации промежуточного результата.

Sort Group By

Сортирует набор записей по столбцам GROUP BY и агрегирует отсортированный результат на втором этапе. Эта операция требует больших объемов памяти для материализации промежуточного результата.

Sort Group By Nosort

Агрегирует предварительно отсортированный набор записей в соответствии с предложением GROUP BY. Эта операция не буферизует промежуточный результат.

Hash Group By

Группирует результат, используя хеш-таблицу. Эта операция требует больших объемов памяти для материализации промежуточного набора записей. Вывод не упорядочен каким-либо значимым образом.

Filter

Применяет фильтр к набору строк.

Создает промежуточное представление данных.

Count Stopkey

Прерывает выполение операций, когда было выбрано нужное количество строк.

Sort Join

Сортирует набор записей в столбце соединения. Используется в сочетании с операцией Merge Join для выполнения сортировки соединением слияния.

Intersection

Выполняет операцию пересечения между двумя источниками.

Union-All

Выполняет операцию объединения всех записей между двумя таблицами. Дублирующиеся строки не удаляются.

Load As Select

Прямая загрузка с использованием оператора SELECT в качестве источника.

Temp Table Generation/Transformation

Создает/преобразует временную таблицу. Используется в специфичных для Oracle преобразованиях типа Star.

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause . For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE , GROUP BY , and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

The semantics of this syntax are discussed in the sections that follow.

Specify the name of an analytic function (see the listing of analytic functions following this discussion of semantics).

Analytic functions take 0 to 3 arguments. The arguments can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. Oracle determines the argument with the highest numeric precedence and implicitly converts the remaining arguments to that data type. The return type is also that data type, unless otherwise noted for an individual function.

"Numeric Precedence" for information on numeric precedence and Table 3-10, "Implicit Type Conversion Matrix" for more information on implicit conversion

Use OVER analytic_clause to indicate that the function operates on a query result set. This clause is computed after the FROM , WHERE , GROUP BY , and HAVING clauses. You can specify analytic functions with this clause in the select list or ORDER BY clause. To filter the results of a query based on an analytic function, nest these functions within the parent query, and then filter the results of the nested subquery.

Notes on the analytic_clause : The following notes apply to the analytic_clause :

You cannot nest analytic functions by specifying any analytic function in any part of the analytic_clause . However, you can specify an analytic function in a subquery and compute another analytic function over it.

You can specify OVER analytic_clause with user-defined analytic functions as well as built-in analytic functions. See CREATE FUNCTION.

Use the PARTITION BY clause to partition the query result set into groups based on one or more value_expr . If you omit this clause, then the function treats all rows of the query result set as a single group.

To use the query_partition_clause in an analytic function, use the upper branch of the syntax (without parentheses). To use this clause in a model query (in the model_column_clauses ) or a partitioned outer join (in the outer_join_clause ), use the lower branch of the syntax (with parentheses).

You can specify multiple analytic functions in the same query, each with the same or different PARTITION BY keys.

If the objects being queried have the parallel attribute, and if you specify an analytic function with the query_partition_clause , then the function computations are parallelized as well.

Valid values of value_expr are constants, columns, nonanalytic functions, function expressions, or expressions involving any of these.

Use the order_by_clause to specify how data is ordered within a partition. For all analytic functions you can order the values in a partition on multiple keys, each defined by a value_expr and each qualified by an ordering sequence.

Within each function, you can specify multiple ordering expressions. Doing so is especially useful when using functions that rank values, because the second expression can resolve ties between identical values for the first expression.

Whenever the order_by_clause results in identical values for multiple rows, the function behaves as follows:

CUME_DIST , DENSE_RANK , NTILE , PERCENT_RANK , and RANK return the same result for each of the rows.

ROW_NUMBER assigns each row a distinct value even if there is a tie based on the order_by_clause . The value is based on the order in which the row is processed, which may be nondeterministic if the ORDER BY does not guarantee a total ordering.

For all other analytic functions, the result depends on the window specification. If you specify a logical window with the RANGE keyword, then the function returns the same result for each of the rows. If you specify a physical window with the ROWS keyword, then the result is nondeterministic.

Restrictions on the ORDER BY Clause The following restrictions apply to the ORDER BY clause:

When used in an analytic function, the order_by_clause must take an expression ( expr ). The SIBLINGS keyword is not valid (it is relevant only in hierarchical queries). Position ( position ) and column aliases ( c_alias ) are also invalid. Otherwise this order_by_clause is the same as that used to order the overall query or subquery.

An analytic function that uses the RANGE keyword can use multiple sort keys in its ORDER BY clause if it specifies any of the following windows:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW . The short form of this is RANGE UNBOUNDED PRECEDING .

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

RANGE BETWEEN CURRENT ROW AND CURRENT ROW

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Window boundaries other than these four can have only one sort key in the ORDER BY clause of the analytic function. This restriction does not apply to window boundaries specified by the ROW keyword.

ASC | DESC Specify the ordering sequence (ascending or descending). ASC is the default.

NULLS FIRST | NULLS LAST Specify whether returned rows containing nulls should appear first or last in the ordering sequence.

NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.

Analytic functions always operate on rows in the order specified in the order_by_clause of the function. However, the order_by_clause of the function does not guarantee the order of the result. Use the order_by_clause of the query to guarantee the final result ordering.

order_by_clause of SELECT for more information on this clause

Some analytic functions allow the windowing_clause . In the listing of analytic functions at the end of this section, the functions that allow the windowing_clause are followed by an asterisk (*).

ROWS | RANGE These keywords define for each row a window (a physical or logical set of rows) used for calculating the function result. The function is then applied to all the rows in the window. The window moves through the query result set or partition from top to bottom.

ROWS specifies the window in physical units (rows).

RANGE specifies the window as a logical offset.

You cannot specify this clause unless you have specified the order_by_clause . Some window boundaries defined by the RANGE clause let you specify only one expression in the order_by_clause . Refer to "Restrictions on the ORDER BY Clause".

The value returned by an analytic function with a logical offset is always deterministic. However, the value returned by an analytic function with a physical offset may produce nondeterministic results unless the ordering expression results in a unique ordering. You may have to specify multiple columns in the order_by_clause to achieve this unique ordering.

BETWEEN . AND Use the BETWEEN . AND clause to specify a start point and end point for the window. The first expression (before AND ) defines the start point and the second expression (after AND ) defines the end point.

If you omit BETWEEN and specify only one end point, then Oracle considers it the start point, and the end point defaults to the current row.

UNBOUNDED PRECEDING Specify UNBOUNDED PRECEDING to indicate that the window starts at the first row of the partition. This is the start point specification and cannot be used as an end point specification.

UNBOUNDED FOLLOWING Specify UNBOUNDED FOLLOWING to indicate that the window ends at the last row of the partition. This is the end point specification and cannot be used as a start point specification.

CURRENT ROW As a start point, CURRENT ROW specifies that the window begins at the current row or value (depending on whether you have specified ROW or RANGE , respectively). In this case the end point cannot be value_expr PRECEDING .

As an end point, CURRENT ROW specifies that the window ends at the current row or value (depending on whether you have specified ROW or RANGE , respectively). In this case the start point cannot be value_expr FOLLOWING .

value_expr PRECEDING or value_expr FOLLOWING For RANGE or ROW :

If value_expr FOLLOWING is the start point, then the end point must be value_expr FOLLOWING .

If value_expr PRECEDING is the end point, then the start point must be value_expr PRECEDING .

If you are defining a logical window defined by an interval of time in numeric format, then you may need to use conversion functions.

NUMTOYMINTERVAL and NUMTODSINTERVAL for information on converting numeric times into intervals

If you specified ROWS :

value_expr is a physical offset. It must be a constant or expression and must evaluate to a positive numeric value.

If value_expr is part of the start point, then it must evaluate to a row before the end point.

If you specified RANGE :

value_expr is a logical offset. It must be a constant or expression that evaluates to a positive numeric value or an interval literal. Refer to "Literals" for information on interval literals.

You can specify only one expression in the order_by_clause .

If value_expr evaluates to a numeric value, then the ORDER BY expr must be a numeric or DATE data type.

If value_expr evaluates to an interval value, then the ORDER BY expr must be a DATE data type.

If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW .

Analytic functions are commonly used in data warehousing environments. In the list of analytic functions that follows, functions followed by an asterisk (*) allow the full syntax, including the windowing_clause .

Oracle Database Data Warehousing Guide for more information on these functions and for scenarios illustrating their use

SQL functions are built into Oracle Database and are available for use in various appropriate SQL statements. Do not confuse SQL functions with user-defined functions written in PL/SQL.

If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, then Oracle attempts to convert the argument to the expected datatype before performing the SQL function. If you call a SQL function with a null argument, then the SQL function automatically returns null. The only SQL functions that do not necessarily follow this behavior are CONCAT , NVL , REPLACE , and REGEXP_REPLACE .

The combined values of the NLS_COMP and NLS_SORT settings determine the rules by which characters are sorted and compared. If NLS_COMP is set to LINGUISTIC for your database, then all entities in this chapter will be interpreted according to the rules specified by the NLS_SORT parameter. If NLS_COMP is not set to LINGUISTIC , then the functions are interpreted without regard to the NLS_SORT setting. NLS_SORT can be explicitly set. If it is not set explicitly, it is derived from NLS_LANGUAGE . Please refer to Oracle Database Globalization Support Guide for more information on these settings.

In the syntax diagrams for SQL functions, arguments are indicated by their datatypes. When the parameter function appears in SQL syntax, replace it with one of the functions described in this section. Functions are grouped by the datatypes of their arguments and their return values.

When you apply SQL functions to LOB columns, Oracle Database creates temporary LOBs during SQL and PL/SQL processing. You should ensure that temporary tablespace quota is sufficient for storing these temporary LOBs for your application.

"User-Defined Functions" for information on user functions and "Data Conversion" for implicit conversion of datatypes

Oracle Text Reference for information on functions used with Oracle Text

Oracle Data Mining Application Developer's Guide for information on frequent itemset functions used with Oracle Data Mining

The syntax showing the categories of functions follows:

The sections that follow list the built-in SQL functions in each of the groups illustrated in the preceding diagrams except user-defined functions. All of the built-in SQL functions are then described in alphabetical order.

Single-Row Functions

Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, START WITH and CONNECT BY clauses, and HAVING clauses.

Numeric Functions

Character Functions Returning Character Values

Character functions that return character values return values of the following datatypes unless otherwise documented:

If the input argument is CHAR or VARCHAR2 , then the value returned is VARCHAR2 .

If the input argument is NCHAR or NVARCHAR2 , then the value returned is NVARCHAR2 .

The length of the value returned by the function is limited by the maximum length of the datatype returned.

For functions that return CHAR or VARCHAR2 , if the length of the return value exceeds the limit, then Oracle Database truncates it and returns the result without an error message.

For functions that return CLOB values, if the length of the return values exceeds the limit, then Oracle raises an error and returns no data.

NLS Character Functions

Character Functions Returning Number Values

Character functions that return number values can take as their argument any character datatype.

Datetime Functions

Datetime functions operate on date ( DATE ), timestamp ( TIMESTAMP , TIMESTAMP WITH TIME ZONE , and TIMESTAMP WITH LOCAL TIME ZONE ), and interval ( INTERVAL DAY TO SECOND , INTERVAL YEAR TO MONTH ) values.

Some of the datetime functions were designed for the Oracle DATE datatype ( ADD_MONTHS , CURRENT_DATE , LAST_DAY , NEW_TIME , and NEXT_DAY ). If you provide a timestamp value as their argument, Oracle Database internally converts the input type to a DATE value and returns a DATE value. The exceptions are the MONTHS_BETWEEN function, which returns a number, and the ROUND and TRUNC functions, which do not accept timestamp or interval values at all.

The remaining datetime functions were designed to accept any of the three types of data (date, timestamp, and interval) and to return a value of one of these types.

General Comparison Functions

Conversion Functions

Large Object Functions

Collection Functions

Hierarchical Function

The hierarchical function applies hierarchical path information to a result set.

Data Mining Functions

XML Functions

Encoding and Decoding Functions

The encoding and decoding functions let you inspect and decode data in the database.

NULL-Related Functions

Environment and Identifier Functions

Aggregate Functions

Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.

If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.

"Using the GROUP BY Clause: Examples" and the "HAVING Clause" for more information on the GROUP BY clause and HAVING clauses in queries and subqueries

Many (but not all) aggregate functions that take a single argument accept these clauses:

DISTINCT causes an aggregate function to consider only distinct values of the argument expression.

ALL causes an aggregate function to consider all values, including all duplicates.

For example, the DISTINCT average of 1, 1, 1, and 3 is 2. The ALL average is 1.5. If you specify neither, then the default is ALL .

All aggregate functions except COUNT (*) and GROUPING ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT never returns null, but returns either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.

The aggregate functions MIN , MAX , SUM , AVG , COUNT , VARIANCE , and STDDEV , when followed by the KEEP keyword, can be used in conjunction with the FIRST or LAST function to operate on a set of values from a set of rows that rank as the FIRST or LAST with respect to a given sorting specification. Please refer to FIRST for more information.

You can nest aggregate functions. For example, the following example calculates the average of the maximum salaries of all the departments in the sample schema hr :

This calculation evaluates the inner aggregate ( MAX ( salary )) for each group defined by the GROUP BY clause ( department_id ), and aggregates the results again.

Analytic Functions

Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

The semantics of this syntax are discussed in the sections that follow.

Specify the name of an analytic function (see the listing of analytic functions following this discussion of semantics).

Analytic functions take 0 to 3 arguments. The arguments can be any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence and implicitly converts the remaining arguments to that datatype. The return type is also that datatype, unless otherwise noted for an individual function.

"Numeric Precedence" for information on numeric precedence and Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion

Use OVER analytic_clause to indicate that the function operates on a query result set. That is, it is computed after the FROM , WHERE , GROUP BY , and HAVING clauses. You can specify analytic functions with this clause in the select list or ORDER BY clause. To filter the results of a query based on an analytic function, nest these functions within the parent query, and then filter the results of the nested subquery.

Notes on the analytic_clause : The following notes apply to the analytic_clause :

You cannot specify any analytic function in any part of the analytic_clause . That is, you cannot nest analytic functions. However, you can specify an analytic function in a subquery and compute another analytic function over it.

You can specify OVER analytic_clause with user-defined analytic functions as well as built-in analytic functions. See CREATE FUNCTION.

You can specify multiple analytic functions in the same query, each with the same or different PARTITION BY keys.

If the objects being queried have the parallel attribute, and if you specify an analytic function with the query_partition_clause , then the function computations are parallelized as well.

Valid values of value_expr are constants, columns, nonanalytic functions, function expressions, or expressions involving any of these.

Use the order_by_clause to specify how data is ordered within a partition. For all analytic functions except PERCENTILE_CONT and PERCENTILE_DISC (which take only a single key), you can order the values in a partition on multiple keys, each defined by a value_expr and each qualified by an ordering sequence.

Whenever the order_by_clause results in identical values for multiple rows, the function returns the same result for each of those rows. Please refer to the analytic example for SUM for an illustration of this behavior.

Restrictions on the ORDER BY Clause The following restrictions apply to the ORDER BY clause:

An analytic function that uses the RANGE keyword can use multiple sort keys in its ORDER BY clause if it specifies either of these two windows:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW . The short form of this is RANGE UNBOUNDED PRECEDING .

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING . The short form of this is RANGE UNBOUNDED FOLLOWING .

Window boundaries other than these two can have only one sort key in the ORDER BY clause of the analytic function. This restriction does not apply to window boundaries specified by the ROW keyword.

ASC | DESC Specify the ordering sequence (ascending or descending). ASC is the default.

NULLS FIRST | NULLS LAST Specify whether returned rows containing nulls should appear first or last in the ordering sequence.

NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.

order_by_clause of SELECT for more information on this clause

Some analytic functions allow the windowing_clause . In the listing of analytic functions at the end of this section, the functions that allow the windowing_clause are followed by an asterisk (*).

ROWS specifies the window in physical units (rows).

RANGE specifies the window as a logical offset.

If you omit BETWEEN and specify only one end point, then Oracle considers it the start point, and the end point defaults to the current row.

value_expr PRECEDING or value_expr FOLLOWING For RANGE or ROW :

If value_expr FOLLOWING is the start point, then the end point must be value_expr FOLLOWING .

If value_expr PRECEDING is the end point, then the start point must be value_expr PRECEDING .

If you are defining a logical window defined by an interval of time in numeric format, then you may need to use conversion functions.

NUMTOYMINTERVAL and NUMTODSINTERVAL for information on converting numeric times into intervals

If you specified ROWS :

value_expr is a physical offset. It must be a constant or expression and must evaluate to a positive numeric value.

If value_expr is part of the start point, then it must evaluate to a row before the end point.

If you specified RANGE :

value_expr is a logical offset. It must be a constant or expression that evaluates to a positive numeric value or an interval literal. Please refer to "Literals" for information on interval literals.

You can specify only one expression in the order_by_clause

If value_expr evaluates to a numeric value, then the ORDER BY expr must be a numeric or DATE datatype.

If value_expr evaluates to an interval value, then the ORDER BY expr must be a DATE datatype.

If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW .

Oracle Data Warehousing Guide for more information on these functions and for scenarios illustrating their use

Object Reference Functions

Oracle Database Concepts for more information about REF datatypes

Model Functions

В SQL поддерживается ряд операций для решения различных задач, наподобие сравнения значений столбцов и проведения логических вычислений. В этом статье блога будут кратко описаны наиболее важные из этих операций — операции сравнения, логические операции и операции над

Операции сравнения

Операции сравнения позволяют сравнивать одно определенное значение столбца с несколькими другими значениями. Ниже перечислены основные операции сравнения.

BETWEEN . Позволяет проверять, находится ли значение между двумя другими значениями.
IN . Позволяет проверять, присутствует ли значение в списке значений.
LIKE . Позволяет проверять, соответствует ли значение определенному образцу, как показано ниже:

Логические операции

Логические операции, также называемые булевскими, позволяют логическим образом сравнивать два или более значений. Главные логические операции выглядят так: AND (И), OR (ИЛИ), NOT (НЕ), GE (БОЛЬШЕ ИЛИ РАВНО) и LE (МЕНЬШЕ ИЛИ РАВНО). Ниже приведен пример, иллюстрирующий применение некоторых из этих операций:

В случае использования сразу нескольких операций внутри одного оператора необходимо помнить об их старшинстве. Oracle всегда вычисляет арифметические операции вроде умножения, деления, сложения и вычитания перед вычислением условий. Ниже показан порядок старшинства операций в Oracle, причем самая важная операция идет первой:

Операции над множествами

Иногда бывает необходимо, чтобы запрос предусматривал объединение результатов из нескольких SQL-операторов. Такой запрос называется составным. Операции над множествами облегчают написание и выполнение составных запросов. Наиболее важные из этих операций перечислены ниже.

Читайте также: