Re: 40 Million rows?

I work with Oracle 7.3.4 on a SUN machine so not all features that I have may be available to you but you should consider using a partitioned view if it available on the NT version. This has significant benefits which include performance and manageability. Another important but often forgotten area is the PCTFREE value. Oracle defaults to 10% (on my system anyway). If you are not planning any updates to this table then I suggest 2% PCTFREE. I never set to 0% as I have seen Oracle miraculously chain blocks when nothing has happened. A low PCTFREE can save you heaps of space in large tables. Also look into using multiple free lists as it can help your insert performance. Depending on how much work you want to do, you may also want to spread this table across multiple tablespaces. This is of a benefit if you are using partitioned views as you can bring certain tablespaces offline and backup/restore without affecting the rest of the tables. It also helps reduce the overall size of your backups and you can also set tablespaces to read only which saves backup space and time. I have used all of these (plus many more) techniques with my database and one of my partitioned views has over 2 billion rows in it and it still works super fast. I, however, have multi processors and the Parallel processing option to help me which leads into another realm of tuning opportunities. I hope some of this helps !

Regards,
Michael Ryan

PS: Use sqlloader direct method to load your data and make sure when you create your table that your place all nullable columns after your not null columns. Received on Thu Apr 16 1998 - 04:07:28 CDT