What is a staging table, and how is the staging concept used in Oracle?

Expert Brian Peasland gives an example of what the staging concept is and how to use a staging table in Oracle.

What is the staging concept and why is it used? Also, what is a staging table?

In its simplest form, "staging" involves putting something in a specific location to be accessed at a later time. All like items will be staged in the same location. For databases, the most common staging location would be a database table. Maybe an example can help illustrate why we would use staging.

More on Oracle business intelligence

Understand the subtle differences between right-time and real-time analytics

Learn how the city of Las Vegas increased government transparency with Oracle BI

Check out this tip on accessing Hyperion financial information from mobile devices

Let's assume we are working on a website to obtain orders from our customers. Our customer shops for items on our website and then places their order. At the end of the order, we confirm their credit charges and then display webpage indicating the successful status of their order. The next thing that normally happens is that we email the customer a copy of their order. But waiting for an email server can cause an unacceptable delay in displaying the order confirmation. So we stage the email confirmation in the table. Instead of actually sending an email to the user during their order process, all we do is INSERT one row containing the order_id into the email staging table. If we have many simultaneous users, the email staging table can contain many rows, one for each order. Every five minutes, a scheduled job looks in the staging table to see if it needs to send out order confirmation emails. If the table contains data, it formulates the email and sends it out. The job then removes the entry from the staging table.

With the staging table, we do not need to perform the action right now. We will get to the action later. So the table contains entries for that action.

Dig Deeper on Oracle business intelligence and analytics