Received: (qmail 26741 invoked from network); 8 Aug 2011 13:38:37 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-85-25-126-90.inaddr.intergenia.de with SMTP; 8 Aug 2011 13:38:34 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 25D09E3A446; Mon, 8 Aug 2011 14:38:33 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1312828713; bh=V3P7vivWVxII3EOFR3TtkyWkXaPwadHCtOqYXC6r q6I=; h=MIME-Version:In-Reply-To:References:Date:Message-ID:Subject: From:To:Cc:Content-Type:Sender:Reply-To:List-help:List-unsubscribe: List-Id:List-subscribe:List-owner:List-post:List-archive; b=eHy1oq q/k8jEVed66gw8wW4n/nq8k+vaNNy9IoBvrVTCCbomSxPlbpQzZpJ0kYsghHzohBKB/ 22QdAfKAZp6W9HMhcDcQvOZtR5AkP0vPSXXC3eBylHJyWMh1SQlFz96lBFx16gP2f/i ck4vNzEA+AK2bEuX+6aqG0vD4xp6gUA= X-Virus-Scanned: Debian amavisd-new at localhost.localdomain Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 57ZSc+PL3BQP; Mon, 8 Aug 2011 14:38:32 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8D3C1E39D1B; Mon, 8 Aug 2011 14:37:50 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 08 Aug 2011 14:37:09 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4496EE39C19 for ; Mon, 8 Aug 2011 14:37:09 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 5XZsWVccEhic for ; Mon, 8 Aug 2011 14:37:09 -0400 (EDT) Received: from mail-vx0-f179.google.com (mail-vx0-f179.google.com [209.85.220.179]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 07087E39044 for ; Mon, 8 Aug 2011 14:37:08 -0400 (EDT) Received: by vxh2 with SMTP id 2so3499204vxh.10 for ; Mon, 08 Aug 2011 11:37:08 -0700 (PDT) MIME-Version: 1.0 Received: by 10.52.22.47 with SMTP id a15mr6449704vdf.175.1312828628087; Mon, 08 Aug 2011 11:37:08 -0700 (PDT) Received: by 10.52.182.129 with HTTP; Mon, 8 Aug 2011 11:37:08 -0700 (PDT) In-Reply-To: <00b901cc55e8$0ef2ab60$2cd80220$@rsiz.com> References: <60AAF908-AF26-4150-A894-E96164ECAD15@centrexcc.com> <65857EE5-361A-4BB4-B88C-644E3871FFF1@centrexcc.com> <38D36890-880B-4D0F-B582-2FD94734D54B@ingrambarge.com> <00b901cc55e8$0ef2ab60$2cd80220$@rsiz.com> Date: Mon, 8 Aug 2011 11:37:08 -0700 Message-ID: Subject: Re: Wow - has anyone used ROW_NUMBER() to get around using a UNION statement when UNION ALL doesn't work?? From: Greg Rahn To: mwf@rsiz.com Cc: oracle-l@freelists.org Content-Type: text/plain; charset=ISO-8859-1 X-archive-position: 37905 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: greg@structureddata.org Precedence: normal Reply-To: greg@structureddata.org List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l Mark -- the OP mentioned in the first email all 3 scenarions with some execution times and other comments. IMHO this makes it more than a logical discussion of theory. My point here is it's very difficult to comment on the "why" about something without knowing the technical bits of "what" it is doing. In this case, the "why" is about the performance, but the "what" required are the execution plans. On Mon, Aug 8, 2011 at 9:27 AM, Mark W. Farnham wrote: > In the vast majority of cases I agree with Greg about posting plans. > However, in this particular case the question is a purely logical > consideration not based on any particular plans the optimizer is choosing. > But a little bit more of context may be required. -- Regards, Greg Rahn http://structureddata.org -- http://www.freelists.org/webpage/oracle-l