Jump to content
Roshan

datawarehouse tablespace design

Recommended Posts

Oracle Database 12.2.0 

RHEL 7.4 

  
Hi, 

Could you please help me on how I should build my tablespaces on my new environment. I am actually migrating tables from different sources and also loading into tables from flat files. 
  
1. First, from 1 source(source A), almost all tables are constant in size for last 3 months. The table size varies from 

DWH_BI_CB0 - 1kb - 23 mb(table size) 

DWH_BI_CB1 - 50 mb - 435 mb(table size) 

DWH_BI_CB2 - 800 mb - 3.77 gb(table size) 
  

For some tables, the daily increase could be like shown below 

TABLEX         289.5 mb 
            7 kb increase 290.2 mb 
            1 mb         291.1 mb 
                                 281.3 mb 
            7 mb         288.3 mb 
            2 mb         290.2 mb 
            1 mb        291.1 mb 
                                 291.0 mb 

How should I build the tablespaces? Should I build 3 tablespaces for example 


DWH_BI_CB0 - 1kb - 23 mb(table size) 

DWH_BI_CB1 - 50 mb - 435 mb(table size) 

DWH_BI_CB2 - 800 mb - 3.77 gb(table size) 

 where table sizings shown above go into their respective tablespaces? 

or should I build a single tablespace where all tables which are contant in size go into it(1KB - 3.77 GB)? 



or should I build tablespaces based on the size of the daily increment(for example monthly increment of 1g to into 1 tablespace and increment of 1mb go into another tablespace)? 

  
Taking into consideration the increment parameter and maximum file size 


Thanks, 

Roshan 

Capture.PNG

Share this post


Link to post
Share on other sites

Hi Rochan,

You file sizes look mighty small for a warehouse!

Are you using a STAR schema?

If so, use a separate tablespace for your FACT table, and each dimension table.

Of course, also partition the FACT tsble:

Please read carefully:

http://www.dba-oracle.com/art_dbazine_oracle_10g_data_warehouse.htm

First, are you going to use RAID?

If so, make sure to deploy RAID 0+1:

http://www.dba-oracle.com/t_raid.htm

And read this also:

http://www.dba-oracle.com/real_application_clusters_rac_grid/raid_administration.html

Most DBA’s will use ASM with OMF and I recommend it.  

http://www.dba-oracle.com/real_application_clusters_rac_grid/asm.html

Read here on OMF:

http://www.dba-oracle.com/real_application_clusters_rac_grid/omf.html

Just make sure that the storage on each mount point is not RAIDed:

http://www.dba-oracle.com/t_automated_storage_management_asm_configuration.htm

Always use max file size unlimited snd monitor usage st the mount point level:

http://www.dba-oracle.com/t_monitoring_tablespace_growth.htm

I use these scripts to monitor tablespace growth:

http://www.dba-oracle.com/t_tablespace_growth_scripts.htm

if you don’t want to write the scripts yourself, get the Oracle script download:

http://www.dba-oracle.com/oracle_dba_scripts.htm

Good luck!

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×