performance

Thumbnail image for What are CBO and RBO?

What are CBO and RBO?

In large applications that juggle tons of data, it isn’t enough to get a query to run without errors. The query must also perform well – get the results in fastest possible time, using the least possible resources. This is where Oracle’s inbuilt optimizers come into play. Till Oracle 9i, Oracle server provided the options […]

Read the Full Article →
Check if an Element Exists in a Collection

The Smart Way to Check if an Element Exists in a Collection

When you need to find out if a specific element exists in a collection or not, how do you go about it? Do you loop through the list till you find the value? If that’s what you do, this post is for you.

Read the Full Article →
Thumbnail image for NOCOPY Parameter Hint in PL/SQL

NOCOPY Parameter Hint in PL/SQL

Before we get to understanding the NOCOPY parameter hint in PL/SQL, it would help to first look at PL/SQL parameter modes and the ways in which they pass values in and out of a subprogram. PL/SQL subprograms have three parameter modes:

Read the Full Article →
Thumbnail image for DISTINCT and How NOT To Use It

DISTINCT and How NOT To Use It

The DISTICT keyword placed next to SELECT restricts the result to unique rows from a query. DISTINCT is also a much abused keyword, often used as a quick fix to bad queries. Take this example from Oracle Applications:

Read the Full Article →
Thumbnail image for Which is faster – IN or EXISTS?

Which is faster – IN or EXISTS?

A question asked multiple times over on Oracle forums: Which is faster – IN or EXISTS? The short answer, post-Oracle 9i is: Both are pretty much the same! Huh? What about that thumb rule – IN for small inner query, EXISTS for big inner query?

Read the Full Article →
Thumbnail image for Write Smarter Queries with the SQL WITH Clause

Write Smarter Queries with the SQL WITH Clause

The WITH clause, also known as the subquery factoring clause, was introduced in Oracle 9i as a way to make queries more efficient and readable. Let’s see how WITH works with a basic example.

Read the Full Article →
plsql-package

6 Reasons Why You Should Use PL/SQL Packages

A package is: …a database object that groups together logically related procedures/functions, and other constructs like variables, constants, PL/SQL types, cursors, and exceptions. One may well ask: when it’s possible to write standalone procedures/functions and define related variables within them, why have packages at all?

Read the Full Article →