What the Oracle Database can do for your Data Warehouse

There are many MANY times I write a particularly difficult query or PL/SQL package and I realize that I am re-inventing the wheel. Reinventing the wheel = BAD. (unless there is something nifty in there that hasn't been done before).

I wanted to make a quick list of links to various tutorials, articles, and direct bits of Oracle docs that will get you to understand and learn some of the database provided tools that can make your SQL writing and Data Warehouse tuning and building much easier. Some of these tools are not always easy to use the first time, but they will be easier to learn than forcing yourself to build them (or rebuild the wheel).

I'll post the 10g features for now. I'll try to get back and post the 11g features and how they work as well. Suffice to say, the 10g features are in 11g and some are enhanced. These can really save you some time. Usually the hard concepts and difficult things you seek to do are already here.
For example, how many times have you had to do some pretty tough hierarchical processing (parent-child relationships) but never used the SQL connect-by query clauses?? These parent child relationships are extremely common in a Data Warehouse.

SQL Model Calculations
The MODEL clause enables you to specify complex formulas while avoiding multiple joins and UNION clauses. This clause supports OLAP queries such as share of ancestor and prior period comparisons, as well as calculations typically done in large spreadsheets. The MODEL clause provides building blocks for budgeting, forecasting, and statistical applications.
link

Materialized View Refresh Enhancements
Materialized view fast refresh involving multiple tables, whether partitioned or non-partitioned, no longer requires that a materialized view log be present. The second link I provide here is particularly useful because it has screen shots and walk throughs to help you with MVs. Getting MVs to work well with fast refresh can take a little patience with all of their prerequisites!
link
link2

Query Rewrite Enhancements
Query rewrite performance has been improved because query rewrite is now able to use multiple materialized views to rewrite a query.
link

Partitioning Enhancements
You can now use partitioning with index-organized tables. Also, materialized views in OLAP are able to use partitioning. You can now use hash-partitioned global indexes. I also wrote a nice article in the IOUG BIWA (business intelligence, warehousing, and analytics) SIG's newsletter on the 11g database enhancements to partitioning, if you want to dig up their publications!
link

Change Data Capture
Oracle now supports asynchronous change data capture as well as synchronous change data capture.
link

ETL Enhancements
Oracle's extraction, transformation, and loading capabilities have been improved with several MERGE improvements and better external table capabilities.
link