Oratable — Oracle stuff worth talking about

DISTINCT and How NOT To Use It

March 15, 2011

distinct-keyword-in-oracle

The DISTICT 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:

{ 3 comments }

How to Add Table Column at Specific Position

February 21, 2011

in DBA,ddl

alter-table-add-column

In a perfect world, the database design is firm and final before development starts. But we do not live in a perfect world – changes can and do happen. You forgot a column in a table, or your client has a change request – for various reasons, you may find that you need to add a column to an existing table.

Oracle allows you to alter a table easily for adding a column, but what if you want the new column added at a specific position in the table? Now you’re in a spot. There is no command to “alter table add column at position 2″; Oracle simply adds the column after all the existing columns.

{ 1 comment }

Unlock User Account in Oracle Express Edition

The default user HR in Oracle 10G Express Edition is locked when you first try to login after database installation. This is apparently due to security reasons, and needs to be unlocked by the administrator. To unlock HR – or any other database user account in Oracle Database XE – follow the simple steps below:

Read the Full Article →

LTRIM, TO_CHAR, And A “Strange” Query

A query using LTRIM and TO_CHAR seemed to be behaving oddly, but soon a simple explanation presented itself. Check out the query below. Can you tell why it gives the answer it does?

Read the Full Article →

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 →

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 →

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 →

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 →

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 →

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 →

Page 1 of 5