Thursday, December 11, 2008

Thinking Smaller: The Shrink Command.

The question comes up often and is a good one to be asked; how can you reduce the wasted space in an Oracle standard table? There are different approaches that can be used. You could for example use an export and import or datapump process. However, there are easier and more efficient ways to accomplish a reorganization of tables, thereby reclaiming wasted space.

Some would use a CTAS to get the job done. Granted a CTAS is typically a fast solution because of the parallel feature; however, you lose all your grants, ects. And if it was a partitioned table you would lose your partitions using a “Create Table AS” statement as well.

I like using the “alter table” shrink command. The “shrink” command came along in Oracle release 10. You can’t use if for UNDO or Temporary segments. Neither will it work on Clustered tables. Nor tables with LONG datatypes. Tables with Materialized Views will also have problems.

To make this work you have to first issue an “alter table” statement allowing rows to be moved in the table you want to “shrink”. This is because rows normally when created within the base are assigned a permanent rowid that will be unique and will never change during the life of the row. However, as I stated, “normally”. This alter statement will override that normalcy. It also will require a row level lock to accomplish this. Once this is done, then the “alter table shrink” statement can be issued. The “shrink” statement moves the High Watermark. As you would expect, Oracle will require a table lock for this operation. You can also add a “cascade” to the “alter” statement if you want to include dependent objects.

Here is an example of this process:

TABLE_OPTOUT_TMP (table name) 4,309,647,360 (size in bytes)

15:25:55 SQL> alter table OWNER.TABLE_OPTOUT_TMP enable row movement;

Table altered.

Elapsed: 00:00:00.03

15:26:35 SQL> alter table OWNER.TABLE_OPTOUT_TMP shrink space;

Table altered.

Elapsed: 00:00:06.55

WIRELESS_DNC_OPTOUT_TMP 20,971,520

As you can see, we went from 4.3 gbs to 21 mbs in 7 seconds.

Nice… now you give it a try.

How about a list of tables to do this great little exercise on. Glad you asked. You can do a simple query against dba_advisor_findings as follows:

Select distinct message from dba_advisor_findings where message like '%shrink%';

This should return something that looks similar to this:

“Enable row movement of the table OWNER.TABLE_NAME and perform shrink, estimated savings is 21404475 bytes.”

Enjoy.

Tuesday, December 09, 2008

To Exist or Not to Exist

My how time seems to get away. I have wanted to add to my blog for some time.

The other day I was reviewing the load on the primary RDBMS (Sun E25K: 16 Quad Core Boards; 256gbs Ram; 45tbs useable Disk (RAID5); 15tbs 10R2 Database) and saw a CTAS that had been churning for over 4.5 hours. Here is what the CTAS looked like:

create table newtab

as select *

from tab1 c1

where exists (select 'x' from tab2 c2 where c1.field1 = c2.field2);

Table tab1 has 38,835,141 records

Table tab2 has 6,412,588 records

I killed off that create statement and rewrote it as follows:

create table sam_newtab

nologging

parallel(degree 6)

as

select *

from owner.tab1

where field1 in (select field1 from owner.tab2);

This new CTAS took 3 minutes and 5 seconds to complete; much better if I say so myself.

The reason this ran so much faster was because an “IN” subquery is only executed once, while an “EXISTS” subquery is executed once per row of the parent query. If there was an index involved on the subquery table, then and “EXISTS” would be able to take advantage of it. However, there were no indexes on these two tables and therefore negated any advantages of using an “EXISTS”.