Re: Minus operator

In article <7igvl9$q3m$1_at_nnrp1.deja.com>,   dinks01_at_my-dejanews.com wrote:
> In a data warehouse operation, where the number
> of rows are in in to region of millions or >,
> what is the most efficient method for determinine
> the differences. Is the Minus op fast conpared to
> someting like doing insert/update ?
>
> --== Sent via Deja.com http://www.deja.com/ ==--
> ---Share what you know. Learn what you don't.---

Under the rule based optimizer minus can sometimes be used to replace a not in sub-query for performance gains. Based on the information in your post you would probably be better off to use the cost based optimizer which wil probably turn the not in sub-query into an anti-join. I doubt that you want to use a minus. You can compare the explain plan output of the minus vs a not in sub-query and probably be able to tell which plan is better. You might also be able to solve the problem using a not exists sub-query, but again I expect that you are going to want to use anti-join logic and the not in sub-query will probably get it for you. If you do not have statistics on your tables or your instance is ran rule based you can use hints with ver 7.3+ to cause the cost based optimizer to be used since I do not believe that the rule optimizer can utilize anti-join logic.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Wed May 26 1999 - 12:31:42 CDT