Home » SQL & PL/SQL » SQL & PL/SQL » Creating a tablespace
Creating a tablespace [message #261122] Tue, 21 August 2007 22:07 Go to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
I have some large tables, of which all the data will not be used frequently. So I want to create a new table in a different tablespace. I want to know if this is better or partititoning the table by range will be a better option. Access to this backup table will be very rare. Please give me the format to create a table space called BACKUP.


Alister
Re: Creating a tablespace [message #261131 is a reply to message #261122] Tue, 21 August 2007 22:44 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>I have some large tables, of which all the data will not be used frequently.
Why does the frequency of access matter to which tablespace it resides?

>So I want to create a new table in a different tablespace.
What will be the benefit for doing this?

>I want to know if this is better or partititoning the table by range will be a better option.
Better based upon which metric(s)?
You do know what metrics are, don't you?

>Access to this backup table will be very rare.
Why does this matter other than you seem fixated on this?

If the new tablespace & the current tablespace reside on the same physical volume, EXACTLY what have you achived by moving these large infrequently used tables?

>Please give me the format to create a table space called BACKUP.
I don't think you should be making ANY changes.


SQL> select keyword from V_$RESERVED_WORDS where keyword like 'BACK%';

KEYWORD
------------------------------
BACKUP


Ready, Fire, AIM!

As a less dangerous way to occupy your time, you should strive to get your DB's Buffer Cache Hit Ratio (BCHR) above 95%.

Re: Creating a tablespace [message #261150 is a reply to message #261122] Wed, 22 August 2007 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If your queries have condition on partition key and use access via full table scan then you can have advantage to partition.

Having a new table implies modifying the queries and applications.

Regards
Michel
Re: Creating a tablespace [message #263382 is a reply to message #261122] Wed, 29 August 2007 22:38 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
The tables I am talking about contains details about the salary particulars of each employee. Since feteching or doing any manupulation on previous year will be very rare unless there is some arrears to be paid. So the use of such data will be very minumum. This is why I thought of Keeping data pertaining the say more than 2 years old in a seperate table rather than partition it. Only one application which queries the status of an old bill need to be modified. This will reduce the current execution time and the daily back through exp, creating a dmp file, if it is stored in a seperate tablespace. Kindly advice.

Regards.

Alister
Re: Creating a tablespace [message #263425 is a reply to message #263382] Thu, 30 August 2007 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the daily back through exp

exp is NOT a backup strategy.

The final answer partitions or different tables only depends on your application and you ability to modify the code.
You can create a partitioned table with partitions on different tablespaces.
You can have some partition in read only tablespace that you have to backup only once.
...

Regards
Michel
Re: Creating a tablespace [message #263672 is a reply to message #261122] Thu, 30 August 2007 13:04 Go to previous message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
partition will not reduced time for exp and imp command. But query will be better perform
Previous Topic: procedure for insert and update
Next Topic: Help with Grant statements...
Goto Forum:
  


Current Time: Tue Dec 06 06:43:09 CST 2016

Total time taken to generate the page: 0.10969 seconds