Using Loader to Append to Large (20M) Table

     We're running into difficulties using SQL*Loader to append rows to an existing large table. It has about 20 million rows, and each month we want to run a batch process to add about 270,000 more. We didn't have any difficulties until we created two indexes. Now, if we try to run in direct mode, we often get errors. Some are understandable, e.g., not enough free space, but at least twice we've gotten ORA-01502: "index 'xxx' is in direct load state" in situations where we didn't think that should be the case. What seems odd is that there were no prior failed attempts to load that could have left the indexes fouled up.

     A related question is: should we be using direct or conventional mode? The manual recommends the conventional path when loading " a relatively small number of rows into a large indexed table." At first thought, 270,000 doesn't seem like a small number, but maybe it is in the context of 20,000,000. If index processing is inefficient in direct mode in this situation (with two indexes), it could be much worse if we add three or four more, which we might.

     Any thoughts/experiences on either aspect of this situation would be appreciated. BTW, we're running Oracle 7.x on RS6000s.

                                 Dave McLaughlin
                                 Univ. of Michigan