Performance

Performance tuning concepts and guidelines for the Oracle database.

Thumbnail image for DETERMINISTIC Functions in Oracle

DETERMINISTIC Functions in Oracle

Deterministic functions always produce the same output for a given input. Learn how Oracle uses the DETERMINISTIC clause for optimizing function calls.

Read the Full Article →
Thumbnail image for Running Procedures Asynchronously with Oracle Job Scheduler

Running Procedures Asynchronously with Oracle Job Scheduler

Oracle PL/SQL provides the ability to run procedures asynchronously using Oracle job scheduler DBMS_SCHEDULER. Here’s how to create and run such jobs.

Read the Full Article →
Thumbnail image for DML Error Logging: Supersized DML Operations Made Easy

DML Error Logging: Supersized DML Operations Made Easy

Oracle DML error logging lets you manage very large DML operations with high performance and robust error handling, with the addition of a LOG ERRORS INTO clause with the DML statement. An explanation and working example.

Read the Full Article →
DBMS_REDEFINITION for online table redefinition

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

Partitioning a non-partitioned table in an Oracle pre-12.2 database, using DBMS_REDEFINITION built-in methods. Scripts and demo.

Read the Full Article →
Thumbnail image for What are CBO and RBO?

What are CBO and RBO?

Oracle’s inbuilt optimizers – CBO and RBO: how they work to improve query performance, and key differences between cost-based vs rule-based optimization.

Read the Full Article →
Thumbnail image for The Smart Way to Check if an Element Exists in a Collection

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

Oracle 10G onwards, the set operator MEMBER OF can be applied to test if an element exists in a collection or not. You do not need to loop through all elements in the collection to find a match.

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

NOCOPY Parameter Hint in PL/SQL

NOCOPY parameter hint in PL/SQL suggests to the compiler to pass OUT and IN OUT parameters by reference instead of by value. The use of NOCOPY saves on the processing and memory overhead of copying data from subprogram to calling program.

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

DISTINCT and How NOT To Use It

Oracle keyword DISTINCT restricts the result to unique rows from a query, but DISTINCT is often used as a quick fix to bad queries. Some practices to avoid when using DISTINCT.

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!

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

Write Smarter Queries with the SQL WITH Clause

SQL WITH clause, or the subquery factoring clause, makes queries more efficient and readable. When a subquery is being processed multiple times, WITH lets you factor it out, give it a name and then reference the name wherever needed in the query.

Read the Full Article →
Page 1 of 2
1
2