Ask The Real Tom

March 19, 2009



Howto: Oracle Vertical Partitioning

Filed under: oracle — Tags: , , , — admin @ 10:38 am

What is vertical partitioning?

Vertical partitioning is if you partition (physicaly separate) by columns (not by rows).

How can I implement vertical partitions?

There are basicaly two methods.

Method 1: With a Index

Demo Script:

connect test8/test8

create table t1 (
        id0 number,
        id1 number,
        id2 number,
        id3 number,
        id4 number
);

insert into t1 select 1,2,3,4,5 from dual connect by level <= 100000;
commit;

explain plan for select id0,id1 from t1;
select * from table(dbms_xplan.display);

create index x1 on t1 (id0,id1);
alter table t1 modify (id0 not null);
alter table t1 modify (id1 not null);

exec DBMS_STATS.GATHER_table_STATS (OWNNAME => 'TEST8', TABNAME => 'T1',cascade=>TRUE );

explain plan for select id0,id1 from t1 t;

select * from table(dbms_xplan.display);

Output:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 95936 |  2435K|    84   (2)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T1   | 95936 |  2435K|    84   (2)| 00:00:02 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3702471258

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 99418 |   582K|    55   (2)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| X1   | 99418 |   582K|    55   (2)| 00:00:01 |
-----------------------------------------------------------------------------

Note:
It is essential, that all the columns in the index are not NULL. Since this garanties, that all rows are in the index.

Method 2: Split a table to tow tables with a 1:1 relation ship and access them by view

Demo Script:

create table t1 (
        id0 number,
        id1 number,
        id2 number
);

create table t2 (
        id0 number,
        id3 number,
        id4 number
);

create view v1 as select t1.id0,id1,id2,id4 from t1,t2 where t1.id0=t2.id0;

insert into t1 select rownum,2,3 from dual connect by level <= 100000;
insert into t2 select rownum,4,5 from dual connect by level <= 100000;
commit;
explain plan for select id0,id1 from v1;
select * from table(dbms_xplan.display);

ALTER TABLE t1 add CONSTRAINT p1 PRIMARY KEY (id0);
ALTER TABLE t2 add CONSTRAINT p2 PRIMARY KEY (id0);

exec DBMS_STATS.GATHER_table_STATS (OWNNAME => 'TEST8', TABNAME => 'T1',cascade=>TRUE );
exec DBMS_STATS.GATHER_table_STATS (OWNNAME => 'TEST8', TABNAME => 'T2',cascade=>TRUE );

explain plan for select id0,id1 from t1 t;

select * from table(dbms_xplan.display);

Output:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2959412835

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 83107 |  3165K|       |   387   (2)| 00:00:05 |
|*  1 |  HASH JOIN         |      | 83107 |  3165K|  2544K|   387   (2)| 00:00:05 |
|   2 |   TABLE ACCESS FULL| T2   |   104K|  1322K|       |    56   (2)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   | 83107 |  2110K|       |    56   (2)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."ID0"="T2"."ID0")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|   783K|    56   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |   100K|   783K|    56   (2)| 00:00:01 |
--------------------------------------------------------------------------

December 24, 2008



Is it possible to execute a alter table drop unused columns parallel?

Filed under: Uncategorized — Tags: , , — admin @ 10:50 am

No.

Only following DDL can be executed parallel (in 10g):

The parallel DDL statements for nonpartitioned tables and indexes are:

  • CREATE INDEX
  • CREATE TABLE … AS SELECT
  • ALTER INDEX … REBUILD

For Partitioned Tables:

  • CREATE INDEX
  • CREATE TABLE … AS SELECT
  • ALTER TABLE … [MOVE|SPLIT|COALESCE] PARTITION
  • ALTER INDEX … [REBUILD|SPLIT] PARTITION

Btw: If you find my articels useful, support me with a click to the google adds.

May 14, 2008



Multi CPU bzip2

Filed under: operating system — Tags: , , , , — admin @ 9:08 pm

Not really oracle related, but a interesting thing and it’s performance related (which is my nr.1 passion)

Haven’t you been upset that bzip2 takes sooo long? Is CPU intensive but just uses 1 CPU (or core).

The solution is pbzip2 which can use multiple cpu’s

My Teste:

CPU: ntel(R) Core(TM)2 CPU 4400 @ 2.00GHz (cache: 2048 KB)

FIle: (mysql dump)

562M 2008-05-14 21:30 shopprod200805132200.dmp

bzip2 run

time bzip2 shopprod200805132200.dmp

real 3m23.115s
user 3m14.252s
sys 0m2.632s

time bunzip2 shopprod200805132200.dmp.bz2

real 1m43.184s
user 1m38.118s
sys 0m2.748s

pbzip2 run

time pbzip2 shopprod200805132200.dmp
real 2m29.084s
user 4m0.855s
sys 0m8.561s
time pbzip2 -d shopprod200805132200.dmp.bz2

real 0m59.406s
user 1m46.087s
sys 0m5.744s

So on my machine compression is about 36% faster. Decompression about 76 % faster.

Powered by WordPress