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.
[click to continue…]
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.
[click to continue…]
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?
[click to continue…]
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.
[click to continue…]
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.
[click to continue…]
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.
[click to continue…]
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:
[click to continue…]
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:
[click to continue…]
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?
[click to continue…]
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.
[click to continue…]
