Home » RDBMS Server » Performance Tuning » Tablespace for table with 1,000,000 rows
Tablespace for table with 1,000,000 rows [message #211647] Sat, 30 December 2006 08:00 Go to next message
emilSverige
Messages: 21
Registered: October 2006
Junior Member
I have a few tables with apx 1,000,000 rows each, and am a uncertain how to optimize performance for them. Should I create one tablespace for each table? Perhaps even on different disks?

If it makes any difference: I will mostly query them with group by, and I would prefer those queries to be performance optimized. I will of course also insert data into them, but if I were to choose between performance for the selects and performance for the inserts/updates I would prefere the selects to be fast.

Any input is appreciated since I'm no dba.
Regards
Emil
Re: Tablespace for table with 1,000,000 rows [message #211650 is a reply to message #211647] Sat, 30 December 2006 09:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I have a few tables with apx 1,000,000 rows each, and am a uncertain how to optimize performance for them.
One does NOT optimize performance of TABLES.
One optimizes performance of SQL statements; one statement at a time.
Re: Tablespace for table with 1,000,000 rows [message #211660 is a reply to message #211650] Sat, 30 December 2006 14:06 Go to previous messageGo to next message
emilSverige
Messages: 21
Registered: October 2006
Junior Member
Ok, let me make myself clear: I want to create a database where some of the tables will have many rows. Isn't it possible to create different tables in different tablespaces for performance reasons? If not, what are tablespaces for?

I have been using a java framework (hibernate) to create a database. This has been working fine, but it doesn't do much more than creation of the tables, indexes and constraints. It doesn't care about tablespaces, data files, log files and such.

I figured I should drop the database and create it properly, but am a bit uncertain about how to set the different parameters. I thought a good starting point would be the tablespaces.

[Updated on: Sat, 30 December 2006 14:11]

Report message to a moderator

Re: Tablespace for table with 1,000,000 rows [message #211662 is a reply to message #211647] Sat, 30 December 2006 16:45 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I want to create a database where some of the tables will have many rows.
Many is a relative term.
A million row table is tiny for some/many applications.

>Isn't it possible to create different tables in different tablespaces for performance reasons?
It depends.
If all the tablespaces reside on the same disk, then NO performance gain will occur.

>If not, what are tablespaces for?
A tablespace is a logical collection of objects.
Read the fine Concepts Manual found at http://tahiti.oracle.com


>I thought a good starting point would be the tablespaces.
WHY?

What problem are you really trying to solve & how would an independent observer conclude the problem has been solved?
Re: Tablespace for table with 1,000,000 rows [message #211706 is a reply to message #211662] Mon, 01 January 2007 09:42 Go to previous messageGo to next message
emilSverige
Messages: 21
Registered: October 2006
Junior Member
Thank you for your input!

The problem I am facing is low performance of a query, check this thread:
http://www.orafaq.com/forum/?t=rview&goto=200125#msg_200125

I have fixed the index so that the query performes better, but after I have inserted more rows it is really slow again.

I have no good reason why I should start with the tablespaces, I just thought that since I have done nothing with them so far, they were a good starting point.
Re: Tablespace for table with 1,000,000 rows [message #211713 is a reply to message #211647] Mon, 01 January 2007 10:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
(RE)ANALYZE / refresh statistics on your tables.

>select table1.column1, sum(table1.column2), sum(table1.column3), >sum(table1.column4), sum(table1.column5)
>from table1, table2
>where table1.id=table2.table1_id
>and table2.column1=?

Since no column from table2 is being SELECTed, so it should not be in the FROM clause.

select table1.column1, sum(table1.column2), sum(table1.column3), sum(table1.column4), sum(table1.column5)
from table1
where table1.id IN ( SELECT table2.table1_id from table2 where table1.id = table2.id and table2.column1=?)
and ...


Re: Tablespace for table with 1,000,000 rows [message #211714 is a reply to message #211713] Mon, 01 January 2007 10:37 Go to previous message
emilSverige
Messages: 21
Registered: October 2006
Junior Member
Interesting... I will rebuild my query and see if it affects the performance.
Previous Topic: Tune the Update Process
Next Topic: Performance issue - Physical Reads / Buffer Gets - Statspack Report
Goto Forum:
  


Current Time: Sat Dec 10 16:18:46 CST 2016

Total time taken to generate the page: 0.13624 seconds