Received: (qmail 11012 invoked from network); 9 Sep 2011 03:51:27 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-85-25-126-90.inaddr.ip-pool.com with SMTP; 9 Sep 2011 03:51:13 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4BA78E40744; Fri, 9 Sep 2011 04:49:53 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1315558193; bh=ZNTNdZHp760meCweQPhlVhHHNJGY9NV1tSESuHtN Dz0=; h=Message-ID:From:To:References:Subject:Date:MIME-Version: Content-type:Content-Transfer-Encoding:Sender:Reply-To:List-help: List-unsubscribe:List-Id:List-subscribe:List-owner:List-post: List-archive; b=N/HkDPoSrJ1y4YbbOcv0nMmGDNMsgNkduBcfqYKgZyDO+vnUW3 gts2fSydAP50j4wbIFLYfnXWNKDf/smo6bl4tSVgZsrUEJtpA+JzueggAL/TY8vhKZ6 fIUU8eeyA/RMG55691jsBMz4ZtkJD5EaT9D1VWkdsbqZzocAvToytw= 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 D7iZlejj555W; Fri, 9 Sep 2011 04:49:52 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DBBA8E406F9; Fri, 9 Sep 2011 04:49:07 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 09 Sep 2011 04:48:26 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2CE50E406E5 for ; Fri, 9 Sep 2011 04:48:26 -0400 (EDT) Authentication-Results: turing.freelists.org; dkim=pass (1024-bit key) header.i=@yahoo.com 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 r5Ccnkew6xjC for ; Fri, 9 Sep 2011 04:48:26 -0400 (EDT) Received: from nm4.bt.bullet.mail.ukl.yahoo.com (nm4.bt.bullet.mail.ukl.yahoo.com [217.146.183.202]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 9F3B5E406F3 for ; Fri, 9 Sep 2011 04:47:59 -0400 (EDT) Received: from [217.146.183.195] by nm4.bt.bullet.mail.ukl.yahoo.com with NNFMP; 09 Sep 2011 08:47:46 -0000 Received: from [217.146.183.205] by tm1.bt.bullet.mail.ukl.yahoo.com with NNFMP; 09 Sep 2011 08:47:46 -0000 Received: from [127.0.0.1] by omp1003.bt.mail.ukl.yahoo.com with NNFMP; 09 Sep 2011 08:47:46 -0000 X-Yahoo-Newman-Id: 71200.7563.bm@omp1003.bt.mail.ukl.yahoo.com Received: (qmail 70335 invoked from network); 9 Sep 2011 08:47:46 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1315558066; bh=KbubJecQhNKqaCCDa07cSksX+kbEz3WucZRHIlbEPgQ=; h=X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:Message-ID:From:To:References:Subject:Date:MIME-Version:Content-Type:Content-Transfer-Encoding:X-Priority:X-MSMail-Priority:X-Mailer:X-MimeOLE; b=Deip8V44R9E1H+jhdZf4IbcH4ZddIhyA78ULe0+JlFH8tQc+ActconSBE0tTO2qmUPjRABFhTQzCyFuUyXBgoR+so/lvTBoL30MCnL4jWn4Mk1uqkwKpRqyDjD8GaK1jfFneO8vAoOwamk4uPzz6MiiAtMYn/wqZ2NB1y/xH8ec= X-Yahoo-Newman-Property: ymail-3 X-YMail-OSG: FLYH_xMVM1mba5Z0tTHypcvC52SHvzyP4BJKoXjgPXoverC MTShkc6_O X-Yahoo-SMTP: 4vWPFZSswBAs9FgodTSfpaeSlgxsILPLGEbCUJdD5X2Ag3l43R3FWMc- Received: from Primary (jonathan@86.161.118.244 with login) by smtp823.mail.ukl.yahoo.com with SMTP; 09 Sep 2011 08:47:44 +0000 GMT Message-ID: <7CF8509D95504B2892B6A21ED96E2163@Primary> From: "Jonathan Lewis" To: "Oracle L" References: <8C70DF85B6294ED0A15FAAD932821796@Primary> Subject: Re: getting in a little over my head Date: Fri, 9 Sep 2011 09:48:09 +0100 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6109 X-archive-position: 38680 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: jonathan@jlcomp.demon.co.uk Precedence: normal Reply-To: jonathan@jlcomp.demon.co.uk 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 > In the second plan > you do the work of accessing the pol_policy table twice for each row in the > materialized view rather than once per row That should have been "for each row selected from" And the doubling only occurs for one of the two link_types. Regards Jonathan Lewis http://jonathanlewis.wordpress.com ----- Original Message ----- From: "Jonathan Lewis" To: "Oracle L" Sent: Friday, September 09, 2011 8:27 AM Subject: Re: getting in a little over my head Tim's given you an argument for the second plan looking better, here's an argument for the first looking better: In the second plan you do the work of accessing the pol_policy table twice for each row in the materialized view rather than once per row - the work done in the subqueries may be the largest resource user. (And that may also be the case in the original query - so adding the account_id to the quote index and both the account_id and logid to the policy index may be the best way to improve performance.) -- http://www.freelists.org/webpage/oracle-l