Thumbnail image for The Special DUAL Table

The Special DUAL Table

DUAL is a special one-row, one-column table in Oracle’s data dictionary. Of what use is DUAL? DUAL comes in handy when you want to select just one row through a query. Oracle SQL structure requires you to have a FROM <table> clause, but some queries don’t need a table – if you want to know […]

Read the Full Article →
Thumbnail image for The Magic Of ROWNUM

The Magic Of ROWNUM

The “ROWNUM greater than” query never fails to have an eye-popping effect  the first time anyone sees it. If you haven’t worked with ROWNUM much before, be prepared! First things first. What is ROWNUM? ROWNUM is a pseudocolumn, assigning a number to every row returned by a query. The numbers follow the sequence 1, 2, […]

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 20 Things You Should Know About Oracle Views

20 Things You Should Know About Oracle Views

A view is like a virtual table. It takes the output of a query and treats it like a table. A view can be based on one or more tables or other views. These tables/views are called base tables. A view takes up no storage space other than for the definition of the view in […]

Read the Full Article →
Thumbnail image for How to Reset a Sequence in Oracle

How to Reset a Sequence in Oracle

Oracle does not a have ready-made command to restart a sequence. There is no direct way to do this: But I have a simple workaround to share with you. This article gives you a script to restart a sequence, explains how it works and shows you a test run with a sample sequence.

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 An Easy Guide to PL/SQL Collections

An Easy Guide to PL/SQL Collections

  A collection is a list of elements of the same type. Each element in the list has a unique position number or label, called the "subscript". To give a logical analogy, consider a list of colors = {red, blue, green, yellow}. This list has four elements, all names of colors. There is a unique […]

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 Rename Column in an Oracle Table

Rename Column in an Oracle Table

Can you change the name of a table column in Oracle? Oracle 9i and above, you can very easily – there is a direct RENAME column command. Pre-Oracle 9i, you have to do a little more. In this article, we’ll look at both the current and older methods of renaming a table column in Oracle.

Read the Full Article →