faq

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 →
Thumbnail image for 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 →
Thumbnail image for The Difference Between UNION and UNION ALL

The Difference Between UNION and UNION ALL

Both UNION and UNION ALL concatenate the result sets of two different 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 →
Thumbnail image for How to Find Duplicate Records in a Table

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 →
Thumbnail image for What is 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 →
Thumbnail image for The Difference between CHAR, VARCHAR and VARCHAR2

The Difference between CHAR, VARCHAR and VARCHAR2

Oracle has three datatypes to store strings. – CHAR, VARCHAR and VARCHAR2. How do they differ from each other? When should you use which? Let’s find out the answers in this article.

Read the Full Article →
Thumbnail image for IN = EXISTS, but beware: NOT IN is not the same as NOT EXISTS!

IN = EXISTS, but beware: NOT IN is not the same as NOT EXISTS!

In our last article we said that in SQL queries, IN and EXISTS are interchangeable. Many of us assume therefore, that NOT IN and NOT EXISTS are also interchangeable. A big mistake. See how NOT IN and NOT EXISTS behave differently in this small example.

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 Select Rows With Max Value

Select Rows With Max Value

We often need to select rows based on the maximum value of a specific column, such as date. Let’s say we  have a table that stores customers orders, and we want to list the last order dates and amounts for each customer. Here is a demo data set and query to achieve this. The same […]

Read the Full Article →