FAQ

Frequently asked questions about the Oracle database, SQL and PL/SQL.

When should we use dynamic SQL

When Should We Use Dynamic SQL?

We know that, if there exists a choice between static SQL vs dynamic SQL, static SQL is the way to go. In that case, why and when should we use dynamic SQL?

Read the Full Article →
Static SQL vs Dynamic SQL

Static SQL vs Dynamic SQL: Which to use?

Static SQL vs dynamic SQL: a comparison on metrics such as security, maintainability and performance, and a rule of thumb for choosing between the two.

Read the Full Article →
Thumbnail image for INNER JOIN and OUTER JOIN Explained

INNER JOIN and OUTER JOIN Explained

Overview of INNER JOIN and OUTER JOIN in Oracle, with examples, memory aids and a further drilldown of INNER JOIN and OUTER JOIN types.

Read the Full Article →
Thumbnail image for How to Find out Your Oracle Database Version

How to Find out Your Oracle Database Version

Three ways to find out your Oracle database version in SQL and PL/SQL: using V$VERSION, V$INSTANCE, or DBMS_DB_VERSION constants.

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 →
15 Things You Should Know about the ORDER BY Clause

15 Things You Should Know about the ORDER BY Clause

When multiple columns/expressions are specified in the ORDER BY clause, the precedence of sorting is left to right. The ORDER BY clause can order in ascending (ASC) or descending (DESC) sequence, or a mix of both. If ASC or DESC is not explicitly stated, then ASC is the default. ORDER BY ASC places NULL values […]

Read the Full Article →
Thumbnail image for The Difference Between Views and Materialized Views

The Difference Between Views and Materialized Views

Other than the word "view" in their names and the fact that both are defined by an underlying SQL, there is little else common between Oracle views and materialized views – yet they can be a source of great confusion to beginners. Here’s a summary of the key differences between views and materialized views, to […]

Read the Full Article →
UNION and UNION ALL

The Difference Between UNION and UNION ALL

Both UNION and UNION ALL concatenate the result sets of two separate SQLs. They differ in the way they handle duplicates. UNION performs a DISTINCT on the result set, eliminating any duplicate rows. UNION ALL does not remove duplicates, and is therefore faster than UNION.

Read the Full Article →
Duplicate Records in Oracle

How to Find Duplicate Records in a Table

A usual situation we come across in development: a table missed a unique key and allowed duplicate rows to be entered unchecked.  Now we want to find and delete those duplicates. In this article, we’ll see how to achieve this.

Read the Full Article →
Create Table as SELECT (CTAS)

What is CTAS?

You have probably heard other DBAs/developers and websites talk about creating a table through "CTAS". Does that leave you befuddled? There’s a very simple explanation to that acronym – read on!

Read the Full Article →