Oracle Recycle bin and Flashback

To remove your own dropped object from the recycle bin do:-
PURGE RECYCLEBIN;

Alternatively issue
alter session set recyclebin=off ;

before dropping the object.

If you are important enough (i.e. have the privs)
Purge dba_recyclebin;
will drop all recyclebin objects.http://www.oracle.com/technology/pub/articles/10gdba/week5_10gdba.html

Parititions, global indexes - The magic "update indexes" clause

Global indexes need not go invalid when you change a table's partitioning if you use the magic 'update indexes' clause. Downside, it takes longer to perform the operation. Upside, users can continue to use the table without getting the error 'index is unusable'

All explained here :-

http://youngcow.net/doc/oracle10g/server.102/b14231/partiti.htm#i1006455
in the paragraph "Updating Indexes Automatically"

compression - gotcher number 1

You cannot drop a column on a compressed table.

Compression and bitmap indexes

When I first tried to compress an existing partitioned table with local bitmap indexes I got an error saying I 'could not compress the partition with a valid bitmap index' or word to that effect. I mistakenly took this to mean this was always the case. It appears that once the compress operation has taken place and the bitmap indexes rebuild from then on all will be OK and the erro does not reappear and this is the reason why :- http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10736/parpart.htm

DBMS_UTILITY.FORMAT_ERROR_STACK

Don't forget this
exception when others then
DBMS_UTILITY.FORMAT_ERROR_STACK

Confession about Compression

I've not look at compression before. What a mistakea to makea!

For warehouse and archive apps the improvements and savings appear to be amazing .

Test1
======
I did a small experiment with weekly_aggregates_fact and monthly_aggregates_fact. Both MV's built on pre-existing tables.

I compressed the partitions and performed a count(*) on weekly_aggregates_fact a partition at the same time.
Partitions that are not compressed returned results in about 2 secs (first time that is, faster subsequently, cos of cacheing) Partitions that have been compressed return results in 150msec. Not an exhaustive test, and the may be some caching going on, but promising I think.

Weekly_aggregates_fact was reduced from 9 Gb to 2.3 Gb monthly_aggregates_fact was reduced from 8.6 Gb to 2.1 Gb

Test2
======
Weekly_aggregates_fact and monthly_aggregates_fact have a similar number of rows, 29,874,318 and 29,095,901 respectively. I had already compressed weekly_aggregates_fact so I only have 'compressed' figure for that MV. For monthly_aggregates_fact I have before and after compression figures.

With compression -
Select Count(*) from Weekly_aggregates_fact took 38 secs initially, 2 secs subsequently. Cost 284,000 (toad)

With compression
select count(*),sum(gbp_stake)
from dwfacts.weekly_aggregates_fact
took 9secs initially and 9secs subsequently. Cost 287,000 (toad)

Without compression -
select Count(*) from monthly_aggregates_fact took 3mins 19 secs initially and 2 mins 40 secs subsequently. Cost 240,000 (toad)

Without compression -
select count(*),sum(gbp_stake)
from dwfacts.monthly_aggregates_fact
took 2mins 47 secs initially and 2 mins 47 secs subsequently (yes, the same). Cost 242,000 (toad)

With compression -
select Count(*) from monthly_aggregates_fact took 29secs initially and 1 secs subsequently. Cost 240,000 (toad) i.e. unchanged....hmmm oh dear

With compression -
select count(*),sum(gbp_stake)
from dwfacts.monthly_aggregates_fact
took 10 secs initially and 8 secs subsequently (yes, the same). Cost 242,000 (toad)


These results are truly remarkable , why aren't we using it?
To be fair, it says in tut'book that MVs benefit particularly well from compression because of the amount of repeated data in the aggregates (the group by columns) but even so..


references

http://boomslaang.wordpress.com/2008/06/05/table-compression-in-oracle/
http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_data_compression_10gr2_0505.pdf
http://husnusensoy.wordpress.com/2008/01/23/compressing-subpartition-segments/

Materialized View - exchange partition - primary key

When exchanging partitions I have always not included the global primary key index on the table to be moved in. This is because the exchange partition fails if it is present. Makes sense since a primary key index has to be global and how does that fit in with having the same index in a new partition. The database has to check for uniqueness globally not locally....Anyway.... In turns out the primary key index can be present on the table to be passed in IF you specify DISABLE VALIDATE;

e.g.
ALTER TABLE sales_01_2001 ADD CONSTRAINT sales_pk_jan01
PRIMARY KEY (sales_transaction_id) DISABLE VALIDATE;


This link explains it all http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10736/refresh.htm

The key thing is once the table is exchanged in, does the global index go invalid and need rebuilding? I know it does go invalid when the index is NOT present on the table to be exchanged in. We'll see ..... I guess.