| Tablespace for table with 1,000,000 rows [message #211647] |
Sat, 30 December 2006 08:00  |
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 #211660 is a reply to message #211650] |
Sat, 30 December 2006 14:06  |
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 #211713 is a reply to message #211647] |
Mon, 01 January 2007 10:26 |
 |
BlackSwan
Messages: 26766
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 ...
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
