Re: Strange Performance Behavior on INSERT INTO SELECT statement...

EUREKA!!!! Thank to all that posted responses. As Jonathan mentioned, EXPLAIN PLAN did not reveal the entire story with regard to the performance: tkprof revealed a very large cartesian merge join when using insert into select ( a join resulting in 5M rows), that did not occur when using naked select.

The solution turned out to be quite simple: I have always had both OPTIMIZER_GOAL and OPTIMIZER_MODE set to the default: apparently the default OPTIMIZER_GOAL must depend on if a statement is a select or insert. As soon as I set my session to all_rows, like this:

alter session set optimizer_goal = all_rows;

my insert miraculously performed the same as the naked select.

What's really odd is that I would expect reverse behavior: wouldnt it seem more logical for Oracle to choose all_rows as the default for an insert and first_rows as the default for a select from sql*plus? It would appear that the REVERSE is true.

Anyway, does anybody have the "OFFICIAL" story on this?

Thanks
Dave

Andrew Babb wrote:
>
> Dave,
>
> These are thoughts and not backed up with solid proven experience.
>
> NOLOGGING simply means that the database inserts are not recorded in the
> Redo Logs and therefore the operation is not recoverable in the event that
> you need to perform Media Recovery. I believe that the dictionary
> modifications are still logged though.
>
> ROLLBACK logging is required with a simple insert as select because if the
> user fails or gets logged off, the PMON (or SMON) is still going to tidy up
> the connection and abort the transaction. Maybe as suggested, the amout of
> Rollback generated is reduced when using CREATE as SELECT.
>
> Rgds
> Andrew
>
Received on Mon May 03 1999 - 12:07:24 CDT