Re: ? Very large tables vs unioning smaller tables

In article <30C32376.41C67EA6_at_dmc.iris.washington.edu>

           sue_at_dmc.iris.washington.edu "Sue Schoch" writes:

: Do any of you have an
: opinion or know of any reference as to the performance differences
: of querying very large tables versus views created by unioning
: smaller (but still large) tables?
:

As another poster has already pointed out, Oracle is not very good (in current versions) at optimising joins on views of UNIONs.

However some improvements have been made in 7.2, and there is a promise that in 7.3 join conditions will be folded inside UNIONs to produce optimal performance.

Once this is in place, and works properly, then it is almost certainly a good idea to investigate the option in detail.

Having said that, the bottom line for on-line performance is rarely the inherent size of a table; it is the number of rows requested, the degree of row scatter, and the precision of indexes that has most impact. This will not really be affected by horizontal splitting of a table.

I would say that there are three main reasons for this type of split:

1. If you have bulk insert/delete phases, you can get good performance there by splitting the table into multiple tables because typically you can truncate a subtable, drop its indexes, insert, then recreate its indexes much faster than you can delete and insert very large numbers of rows when there are several large indexes around. e.g. 30 Million row table requires 2 Million deletes and inserts to take place as a batch once per week. Table may be better at 15 tables with a UNION ALL. (Obviously, the logic of the split must be appropriate for this to work).
2. Over time, certain types of index can become very inefficient. Rebuilding such an index can be extremely difficult when the table is extremely large. (Note, however, that in Oracle 7.3 there is an option to recreate an index by reading the current index, which should be a great benefit).
3. It can be a easier dealing with maintenance and load-balancing when you have a large number of (relatively) small objects.

-- 
Jonathan Lewis