Description UNION ALL (RECURSIVE WITH) DEPTH FIRST operation returns the child rows before any siblings rows are returned. This operation was first introduced in Oracle 11g Release 2 (11.2). Version This sample script developed and executed on SQL> SELECT banner FROM v$version WHERE rownum = 1; BANNER ————————————————————————— Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – [...]
Archive of posts tagged Oracle11g
Row Source Operation: UNION ALL (RECURSIVE WITH) BREADTH FIRST
Description UNION ALL (RECURSIVE WITH) BREADTH FIRST operation returns the sibling rows before any child rows are returned. This operation was first introduced in Oracle 11g Release 2 (11.2). Version This sample script developed and executed on SQL> SELECT banner FROM v$version WHERE rownum = 1; BANNER ————————————————————————— Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 [...]
Row Source Operation: PARTITION SYSTEM SINGLE
Description PARTITION SYSTEM SINGLE performs child operations for a single partition in the table. This operation was first introduced in Oracle 11g Release 1. Version This sample script developed and executed on SQL> SELECT banner FROM v$version WHERE rownum = 1; BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production Script SQL> CREATE TABLE [...]
Row Source Operation: PARTITION SYSTEM ALL
Description PARTITION SYSTEM ALL performs child operations for each partition in the table. This operation was first introduced in Oracle 11g Release 1. Version This sample script developed and executed on SQL> SELECT banner FROM v$version WHERE rownum = 1; BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production Script SQL> CREATE TABLE [...]
Row Source Operation: The Reference List
This post covers over 200 commonly observed row source operations (, which is a unique combination of operation and options) with an example to reproduce the operation. A list of operations and options as of 11g R2 is available here, you should note that not all options are available for each operation. ALTER INDEX STATEMENT [...]
Execution Plan Operations and Options
The row source tree is the core of the execution plan. It contains the sequence of operations that the database performs to run the statement; each operation may have options associated with it. The operation in execution plan is also known as row source operator. If you look at the below example, TABLE ACCESS operation [...]
OVERLAPS predicate
OVERLAPS predicate is a useful feature, which is not available in most SQL implementations that can help to determine whether the two periods overlap each other. If two periods overlap then the predicate evaluates to TRUE otherwise FALSE. Let us create a test table with three rows – one with overlapping dates, second with non-overlapping [...]
Removing table rows using an inline view
The SQL standard allows you to use an inline view in a DELETE statement, see the pseudo form below – If a condition such as key-preserved table is not met then it will result in error, “ORA-01752: cannot delete from view without exactly one key-preserved table.” DELETE (SELECT some_column FROM table_one JOIN table_two USING (join_column)); [...]
Joins, Join Conditions, Filters and Join Types
Joins A join is a query that combines rows from two or more tables. Oracle performs a join whenever multiple tables appear in the FROM clause of the query. Below is a SQL join statement, legacy syntax and ANSI/ISO syntax (also referred as new join syntax, was first introduced in Oracle 9i release) in a [...]
ORION: Oracle I/O Numbers Calibration Tool in Oracle 11g R2
ORION (Oracle I/O Calibration Tool) is a standalone tool for calibrating the I/O performance for storage systems that are intended to be used for Oracle databases. The calibration results are useful for understanding the performance capabilities of a storage system, either to uncover issues that would impact the performance of an Oracle database or to [...]