Suggestions for BIG TABLE

[diepa]

Hi to all of you. I need some advices about how to improve performance on a table which will receive more than 2 million rows per day and AT THE SAME TIME, a lot of updates from 100 connections simultaneous. The table will be deleted at the end of the date.
Also suggestions about a HUGE table with 15 million rows that is going to be read it only (inserts will be done by a batch process, about 5 million rows per month).
Suggestions for creation, use and access?
Thanks a lot and God bless America.

Diego

 

[carp]

Both tables ought to be partitioned. I believe you'll probably want to use local indexes on the first table. On the second table, I would suggest using bitmap indexes on the columns you'll be using in your WHERE clause (except, of course, Primary Key and Unique columns). Also, you will probably find it works better to drop the indexes before doing your bulk load and then rebuilding them after the load is done.

That is my USD .02!

 

[diepa]

But Partition is Ok in Windows 2000 with RAID 5 ?
Any performance implications? What about the storage parameters for the table and the tablespaces?
Thanks

 

[Kenrae]

For the first table, if you update some rows you have retrieved in a query, you should use the FOR UPDATE clause in your select statement.
And carp's suggestions seem good to me (as usual ).

 

[rcurva]

These tips were taken from a Metalink note, I used it as a guide on my tables, to pass my judgement on these two parameters. You will have to adjust if you see that the table is chaining.

Tips for PCTUSED and PCTFREE:

· If the application frequently performs UPDATES that alter sizes of rows greatly, then PCTFREE can be set high and PCTUSED can be set low. This would allow for large amount of space in data blocks for row size growth.
· If there is more INSERT activity with less UPDATES, the PCTFREE can be set low with average value for PCTUSED to avoid chaining of rows.
· If the main concern is performance and more space is available, then PCTFREE can be set very high and PCTUSED very low.
· If the main concern in space and not performance, then PCTFREE can set very low and PCTUSED very high.

 

[jcote]

You have to be Entreprise edition to have the partition option. That is the first thing to check before plannig to use it.

In the case of the first table, will the updates be done "mostly" on the newly inserted rows, or not. This is important to know since this will tell you if you have a great probability of collision for data. If you don't do the updates on the recently inserted rows, I don't see the need of partition other than the fact that a partition table will always help for speading the load.

 

[raygg]

Won't partitioning assist in reducing the number of rows scanned in full table scans to the current partition? I think bit mapped indexing requires Enterprise Edition also. Another way to spread the load for the indexing is reverse indexes to avoid hot spots on the btree, which are best for serially numbered keys like sales orders and claim numbers.

 

[carp]

By definition, a full table scan has to scan ALL of the rows. However, using partitions DOES make it easier for the optimizer to figure out an effective method of using parallelism (if enabled). But I think what you are thinking of is narrowing down the search to a given partition, thus AVOIDING the full table scan.

As for reverse key indexes - they DO help eliminate hot spots if you have them; however, you also incur a lot of overhead using them so performance-wise, you might break even or even lose some ground. The great thing is that you can always try them and then drop them if they aren't working out for you!

 

[raygg]

We are considering the reverse indexing for some large tables - do you know any other considerations about how we should evaluate whether to try them out? Or a source where I can get more info to evaluate them? I was unaware of the overhead issues. What are they?

 

[carp]

The problem, as explained to me by somebody who works with the internals of Oracle's indexes, is that reversing the bytes requires its own processing time. Consequently, while you avoid contention by spreading index key values out over different blocks, every query that wants to access this index has to reverse the bytes before starting the index traversal (that is, if your query includes
"WHERE name='carp'",
the optimizer must now not only determine that there is a usable index on the name column, but also rewrite the query to look for 'prac'.
So this overhead has to be weighed against the potential of a hot spot in the index as well as the net effect a hot spot will actually have. Certainly, there are situations where reverse keys will be beneficial - but like so many features, they may or may not be as useful as you would like. But to reiterate - it never hurts to try; worst case - you drop the reverse key index and build a standard index in its place!

 

[raygg]

Thank you