Re: My first large table

johnleslie_at_madasafish.com (John Leslie) wrote:

> Now we have a requirement to load large amounts of data into a table
> which will be held for 3 months, resulting in several million rows.
> The data will be loaded using sqlldr and thereafter queried for adhoc
> report purposes only.

John, interesting how relative a very large table (VLT) is.. a few million is not that large anymore. Heck, I think that a half billion is sizeable, but that is small fry compare to the guys running terrabyte warehouses with billions of rows. :-)

> Just want to know, what are the basic things I need to do in setting
> up this table to ensure decent performance.

SQL*Loader is the right approach IMO. Direct loading is compulsory though if you want to push large volumes into the table in the shortest time period.

> Do I need to reorganize the table indexes on regular basis to retain
> performance? If so how? As you can tell I'm quite new at this!

I will suggest looking at things like partitioning, local indexes, bitmap indexes and so on.

If the queries are specific (i.e. hitting a small data set), make sure that the indexes work for them. Always try and work from the smallest set to the largest when joining. Watch out for nested loop and hash joines.. both can turn nasty under the wrong conditions.

When the queries are not specific (you will see that when explain plans start showing index range scans), consider using parallel query. The time spend on range scanning indexes can often be put to better use hitting the table with a full scan in parallel. Or can be turned in to parallel index scans.

Stay away from any row-based processing. You may get away with a 5ms overhead per row on a small table. On a VLT, that can turn into many hours of runtime.

There's nothing really special about working with VLTS's. They only tend to show logic errors and physical design errors, much clearer and much sooner than small tables. :-)

Above all, I think its where the real fun is in Oracle... and where you get to see what it is truly capable of.

Oh yeah - last words. When in doubt. Explain plan and tkprof. I still find it amazing that many developers working on Oracle do not even seem to know what the plan table is.

--
Billy