From oracle-l-bounce@freelists.org Thu Apr 21 17:51:51 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j3LMpjOt026981 for ; Thu, 21 Apr 2005 17:51:51 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j3LMpj4Z026977 for ; Thu, 21 Apr 2005 17:51:45 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 19BB6185B1A; Thu, 21 Apr 2005 16:49:25 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 24199-04; Thu, 21 Apr 2005 16:49:25 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 96AD2185AFB; Thu, 21 Apr 2005 16:49:24 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit Subject: rm RULE based optimizer != GOOD IDEA Date: Thu, 21 Apr 2005 16:46:38 -0500 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: rm RULE based optimizer != GOOD IDEA Thread-Index: AcVGtFR507YA6w30RMeu4BX6TCwWrgABZ/yA From: "Post, Ethan" To: X-OriginalArrivalTime: 21 Apr 2005 21:47:34.0157 (UTC) FILETIME=[B9E553D0:01C546BB] X-archive-position: 18772 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Ethan.Post@ps.net Precedence: normal Reply-To: Ethan.Post@ps.net X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.63 Just got finished working on a little SQL perf problem. The query was something like... select a.col, b.col, (select count(distinct foo) from tbl3 where batch_id=3Db.batch_id) errors from tbl1 a, tbl2 b where a.batch_id=3Db.batch_id and a.status not in ('X','Z')=20 and b.trans_type=3D'Q'; Now that ran really slow using the COST optimizer... but when we added the RULE hint, it ran about much faster (50 sec to < 1 sec). Of course we can't do that so we spent a lot of time writing the query in different ways, moved the select count(*) into an inline view and joined to that, query dropped from 50 seconds to about 20 seconds but did not approach the sub-second performance of the RULE method. The explain plan was not helpful because it looked the same as the plan generated using RULE but the difference was in the way the select count() was being handled, and the plan wasn't showing us that. So what is the gist of this post? Well, I am sure if we continued to look at this that we might come up with a solution but why waste all that time when /*+RULE */ does the trick? What would be the point of Oracle removing something that time and time again demonstrates it's usefulness like this? It essentially (in this particular case) is a GO_FASTER hint and it worked. So maybe they will get rid of the ability to set the optimizer mode to RULE but this will hopefully always be there at the statement level. -- http://www.freelists.org/webpage/oracle-l