Category: ‘Oracle’

ROLLUP, CUBE, and GROUPING SETS

Oct 15 2013

The ROLLUP, CUBE, and GROUPING SETS operators are extensions of the GROUP BY clause. The GROUPING SET is the union of the aggregates based on the columns specified in each set in the grouping set. Multiple columns that are in inner parentheses in the GROUPING SETS list are treated as a single set. The following […]

START WITH CONNECT BY SQL

Oct 15 2013

The START WITH… CONNECT BY clause can be used to select data that has a hierarchical relationship such as manager to employee relationships. The root of the hierarchy is selected with the START WITH condition. In the example below from Oracle, the top level manager has an employee_id of 100. The output is sorted by […]

Oracle Exadata: Ensuring smart scan offload processing with “insert as select”

Apr 06 2012

One of the key performance enhancing features of Oracle Exadata Database is the offloading of query processing down to the storage layer. Only relevant rows and columns are returned to the database server. As a result, much less data travels over the Infiniband interconnect, dramatically improving the performance and concurrency of queries. However, there is […]

Import a subset of table columns using Oracle Data Pump

Mar 06 2012

Importing data is most efficient when you load only the columns you need into the target database. This becomes apparent when importing unnecessary LOB columns, which can slow down an import significantly. One method of limiting which columns to import is to first only export the columns you need. You can create a temporary table […]