tablespace design for datawarehouse

[Roshan]

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
Capture.PNG

Thanks,

Roshan

[ErmanArslansOracleBlog]

It is a matter of choice, but as I already mention;  you may have multiple tablespaces  and you may decide "which tables should go to which tablespaces" according to their sizes.

For ex: all the small objects should reside in a tablespace, and this tablespace should only be used by the small objects..

This will prevent the fragmentation..

But, you may also use multiple tablespaces and you may implement a tablespace model by taking consideration of the following rules.. (like we have EBS 12.2)

-- This kind of a model is prepared by taking consideration of size,life span,access methods and locking granularity..

Object Type                            Tablespace
AQ Tables                                         AQ
IOTs (Index Organized Tables) Transaction_Tables
Materialized Views                     Summary
Materialized View Logs             Summary
All other Indexes            Same Tablespace type as the table
Domain Indexes                        Transaction_Indexes
Indexes on Transaction Tables Transaction_Indexes

So, in these mode you are considering, object type + access methods..

so you choose one of these 2..

[Roshan]

ok thanks. Do the tablespaces have a naming convention?

[ErmanArslansOracleBlog]

If you are planning to use the OATM model, You can name your tablespaces with the following convention->

<Short_code_of_the_Application_name>_TS_<data access method>_<object_type>

Ex from EBS : APPS_TS_TX_DATA, APPS_TS_TX_IDX , APPS_TS_QUEUES etc..