Performance Archives

DETERMINISTIC Functions in Oracle

A PL/SQL function in Oracle can be tagged with a DETERMINISTIC clause, to indicate that the function will always produce the same output for a given input and will have no side effects.

A little elaboration is in order here.

1. …will always produces the same output for a given input

Let’s see this with an example: a function get_primary_phone takes as input customer_id and returns the customer’s primary phone. Internally, the function executes SQL on a customer contact table, ranks and filters the result to get the customer’s primary phone number.

Running Procedures Asynchronously with Oracle Job Scheduler

Consider a PL/SQL stored procedure that handles a heavy transaction. The procedure is extremely slow – when executed from a UI, the application hangs for minutes. On analysis it is found that the procedure is performing a complex series of steps, a portion of which are non-critical and need not hold up the entire transaction. In other words, it would be acceptable if:

some of the steps are run asynchronously while a slimmer main transaction completes
failures (if any) in the asynchronous steps do not cause a failure in the main transaction

Oracle PL/SQL helps us achieve these objectives with asynchronous processing using Oracle job scheduler DBMS_SCHEDULER. Here’s a demo to show you how.

When you have an enormous load of data to copy/merge from one table to another, you are probably concerned about:

Performance: How fast can the program copy/merge all the data?
Exception handling: How well does the program deal with errors in the data? Can it highlight bad data if present in the lot, at the same time allow the good data to pass successfully?

Oracle 12.2 has introduced a number of new features that ease partitioning, the most-awaited perhaps is the ALTER TABLE MODIFY syntax to convert a non-partitioned table to partitioned.

What do you do if your database version is pre-12.2? Partitioning a non-partitioned table in pre-12.2 databases is trickier, not impossible. Try DBMS_REDEFINITION: here’s how.

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 of CBO (cost-based optimizer) and RBO (rule-based optimizer). RBO has been desupported by Oracle 10G onwards, but it is still worth knowing about to understand the evolution of Oracle’s optimization techniques.

This post explores the key differences between CBO and RBO.

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.

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:

The DISTINCT 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:

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?

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.

DETERMINISTIC Functions in Oracle

1. …will always produces the same output for a given input

Running Procedures Asynchronously with Oracle Job Scheduler

DML Error Logging: Supersized DML Operations Made Easy

DBMS_REDEFINITION: Convert Non-Partitioned Table to Partitioned [pre 12.2]

What are CBO and RBO?

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

NOCOPY Parameter Hint in PL/SQL

DISTINCT and How NOT To Use It

Which is faster – IN or EXISTS?

Huh? What about that thumb rule – IN for small inner query, EXISTS for big inner query?

Write Smarter Queries with the SQL WITH Clause