table design
From what I read, it seems to indicate that smaller table performs better than bigger one. If you have an active table (OLTP) being inserted constantly then this table will grow fast (this table is also used for query at any time). 1. How do you set a table threshold ? 2. What do you do when the table exceeds the threshold ? Do you implement a 'table rotation' or something ? Then how do you support the query to look for all the table.X ? Thanks, Thanh Sent via Deja.com http://www.deja.com/ Before you buy.
In article <8rvbsl$6bj$1@nnrp1.deja.com>,
Thanh <tqma@my-deja.com> wrote:
> From what I read, it seems to indicate that smaller table
> performs better than bigger one. If you have an active table (OLTP)
> being inserted constantly then this table will grow fast (this table
> is also used for query at any time).
>
> 1. How do you set a table threshold ?
> 2. What do you do when the table exceeds the threshold ?
> Do you implement a 'table rotation' or something ? Then how
> do you support the query to look for all the table.X ?
>
> Thanks,
> Thanh
>
> Sent via Deja.com http://www.deja.com/
Thanh,
a table has to get awfully big before performance necessarily drags. In
most cases, I have seen performance stumble after the insert of about a
million rows, only to recover speed upon running UPDATE STATISTICS for
that table. And it need not be HIGH or MEDIUM, so IMO that mostly makes
your problem moot. And yes, proper indexing would be helpful as well.
If you insist on "rotating", well, in my experience, this does not work
out as you expect. For example, I had an indexed table with 100,000
rows taking up ~1000 pages. When I deleted 10,000 rows, the tablespace
grew to take up about 1100 pages. Something to do with the B-tree node-
splitting necessary to keep the B-tree structure balanced, I guess.
And unless the key values fit right into existing, partially depleted
index nodes, any new rows will not reuse that index space. (Yes, it
would probably reuse the space left by the deleted rows.)
OK, suppose I set a threshold of a million rows. This means that I
would have to set up an insert-trigger to check if the number of rows
has exceeded the threshold. Then, [not necessarily by stored procedure]
it would seek out the "oldest" row in the table to delete it.
Sounds like a lotta work!
More commonly, you could set up max and min thresholds. eg. When I have
1.1 million rows, start a sequence to delete the oldest 100,000 rows.
The query to locate these oldest rows has a correlated subquery so the
delete of the oldest 100,000 rows would look something like:
delete from mytable
where seq_num in (select seq_num
from mytable t1
where (select count(*) from mytable t2
where t2.seq_num <= t1.seq_num) <= 100000
);
Man, that's UUUUGLYYYY!!!!!
Still wanna do it?
--
+----- Jacob Salomon - DBA --------------------------------------------+
|------------------- Bulletin Board Announcement ----------------------|
| Congregants will please note that the bowl at the back of the church |
| bearing the sign "For the Sick" is for monetary contributions only. |
+----------------------------------------------------------------------+
Sent via Deja.com http://www.deja.com/
Before you buy.
Thanh wrote: > > From what I read, it seems to indicate that smaller table > performs better than bigger one. If you have an active table (OLTP) > being inserted constantly then this table will grow fast (this table > is also used for query at any time). > > 1. How do you set a table threshold ? > 2. What do you do when the table exceeds the threshold ? > Do you implement a 'table rotation' or something ? Then how > do you support the query to look for all the table.X ? A smaller table is definitely faster. There are several schemes you can use. You can use fragmentation on part of the table's primary key to allow the engine to perform fragment elimination and only search a subset of the table. You can break the table into several tables along the same lines but then your application must know somehow, perhaps using a master record in another table, which table to seach. Often at least as important is structuring queries efficiently and making sure there are required indexes present to speed queries and that the statistics are properly updated so that the optimizer can do the best job possible. Art S. Kagel