Scaling to Infinity Seminar

I went to a seminar on Oracle partitioning and data warehousing earlier this week call “Scaling to Infinity” by Tim Gorman. It gave me some new ideas, and reinforced some others, for where we should take our architecture at the U.

Here are some notes that I will hopefully talk more about as I research them.

1. Functions and query optimization
   One slide he had was a demo where
   TRUNC(date_posted) = '10/12/06'
   would force a table scan where
   date_posted >= '10/12/06'
AND date_posted < '10/13/05'
   would use an index or use partition-pruning to execute. It seems to me that if you have good statistics, then the CBO would be smart enough to partition-prune even with the first method. The instructor recommended using a function-based index. There must be an easier way.
2. Why do we get “corrupt index” errors when people are running queries during partition exchanges? According to the presenter, partition exchanges do not take effect until all the index rebuilds, etc. have completed. PE commits like a regular transaction. I’ll need to replicate this error to figure out what’s going on.
3. “Range of ROWIDs stored in a direct-path log made visible by view ALL_SUMDELTA” Not very useful, but interesting nonetheless.
4. DBMS_MVIEW.REFRESH Fast Refresh does not work if your MVIEW has unions, subqueries, etc.
5. You can use SQL*Loader to generate external table syntax? I’ll need to check this out. Generating external table syntax, especially for wide flat files, is a major PITA.
6. DBMS_XPLAN is a way to look at explain plans.
7. Table compression. Test table compression (and exchange partitioning) for a large transactions table. Possibly run the loads in parallel in development with the regular production table for a while to see if there are any gotchas. Test ETL performance, maintenance “costs”, space usage, and query performance.
8. DBMS_REDEFINITION is a package for doing online table reorgs. It would have been nice to have this during our tablespace move.
9. Does Oracle Warehouse Builder (OWB) do exchange partitioning without a lot of manual coding?
10. SYS.COL_USAGE$ keeps usage stats on columns. It tells you how often columns are used in WHERE clauses and how they are used? Equi-joins, range predicates, etc.
11. Download exchpart.sql to automate partition exchange for ETL.
12. Bitmap join indexes. Very cool technology. You basically create a bitmap index of your dimensions. Then if you are quering multiple dimensions versus the fact table, it can just join the BJI to the bitmap indexes on the fact.
13. Transportable tablespaces for DWPROD. Could we create PCOM3 and then attach that tablespace to DWPROD to simply our ETL and eliminate those pesky database links.
14. ALTER SESSION SET CURRENT_SCHEMA
15. PARALLEL good for lots of processing that return small result sets or CREATE TABLE AS SELECT. If you return large result set, your bottleneck will be the query coordinator.
16. Loading exchange partitions when your partition key is not the load date. Tim had a neat technique for dealing with the dribble-over between partitions.
17. Data Vault. Looks promising for creating point-in-time representations of your data.

Tags: Data Warehousing, ETL, Oracle