jklein@.com (Jonathan Klein) (02/04/91)
In article <10737@pasteur.Berkeley.EDU> mao@eden.Berkeley.EDU (Mike Olson) writes: >a couple of days ago i posted a query about oracle to this newsgroup. >since i got no response, i thought i would repost with a more provocative >subject line. > >here's the question: i have heard that oracle does not have a query >optimizer. i find this surprising. is it true? > >email or post responses. i'm eager to hear the answer to this one. > > mike olson > postgres research group > uc berkeley > mao@postgres.berkeley.edu All versions of oracle that I have worked with have had an query optimizer. Jonathan Klein Oracle Corporation
nigelc@cognos.UUCP (Nigel Campbell) (02/06/91)
In article <1991Feb3.182558.14411@oracle.com> jklein@oracle.UUCP (Jonathan Klein) writes: >In article <10737@pasteur.Berkeley.EDU> mao@eden.Berkeley.EDU (Mike Olson) writes: >>a couple of days ago i posted a query about oracle to this newsgroup. >>since i got no response, i thought i would repost with a more provocative >>subject line. >> >>here's the question: i have heard that oracle does not have a query >>optimizer. i find this surprising. is it true? >> [stuff deleted] >All versions of oracle that I have worked with have had an query optimizer. > >Jonathan Klein Would you like to give a comparison on the merits of the Oracle approach vs say Rdb/Vms,Interbase,Sybase etc especially based on a database where the user has not taken to using physical row,table placement schemes . -- Nigel Campbell Voice: (613) 783-6828 P.O. Box 9707 Cognos Incorporated FAX: (613) 738-0002 3755 Riverside Dr. uucp: nigelc@cognos.uucp || uunet!mitel!cunews!cognos!nigelc Ottawa, Ontario || nigelc@cognos.uucp.@ccs.carleton.ca CANADA K1G 3Z4
rubi@lgc.com ( Ernest Rubi) (02/06/91)
In article <9292@cognos.UUCP> nigelc@cognos.UUCP (Nigel Campbell) writes: >In article <1991Feb3.182558.14411@oracle.com> jklein@oracle.UUCP (Jonathan Klein) writes: >>In article <10737@pasteur.Berkeley.EDU> mao@eden.Berkeley.EDU (Mike Olson) writes: >>>a couple of days ago i posted a query about oracle to this newsgroup. >>>since i got no response, i thought i would repost with a more provocative >>>subject line. >>> >>>here's the question: i have heard that oracle does not have a query >>>optimizer. i find this surprising. is it true? >>> > >[stuff deleted] > >>All versions of oracle that I have worked with have had an query optimizer. >> >>Jonathan Klein > >Would you like to give a comparison on the merits of the Oracle approach >vs say Rdb/Vms,Interbase,Sybase etc especially based on a database where >the user has not taken to using physical row,table placement schemes . > > > > >-- >Nigel Campbell Voice: (613) 783-6828 P.O. Box 9707 >Cognos Incorporated FAX: (613) 738-0002 3755 Riverside Dr. >uucp: nigelc@cognos.uucp || uunet!mitel!cunews!cognos!nigelc Ottawa, Ontario >|| nigelc@cognos.uucp.@ccs.carleton.ca CANADA K1G 3Z4 Oracle's syntactically-based optimizer approach permits fine-tuning of the SQL statement for added performance. This advantage, however, is generally lost during ad hoc querying by less sophisticated users since fine-tuning requires a good understanding of the database structure and an appreciation for Oracle's optimizer strategies. The rumor mill has it that Oracle is preparing a statistically-based optimizer. I would not be surprise if this optimizer comes to be regarded as the most effective in the industry. --- Ernest Rubi Phone : (713) 579-4789 Landmark Graphics Corp. Fax : (713) 579-4814 333 Cypress Run E-Mail: rubi@lgc.com Houston, TX 77094
dbc@cimage.com (David Caswell) (02/07/91)
.In article <1991Feb3.182558.14411@oracle.com> jklein@oracle.UUCP (Jonathan Klein) writes: .>In article <10737@pasteur.Berkeley.EDU> mao@eden.Berkeley.EDU (Mike Olson) writes: .>>here's the question: i have heard that oracle does not have a query .>>optimizer. i find this surprising. is it true? .>> .> .>All versions of oracle that I have worked with have had an query optimizer. .> .>Jonathan Klein .>Oracle Corporation . How bad is Oracle -- Not nearly as bad as Unify.
mcintoshc@ctl.co.nz (02/07/91)
> > Oracle's syntactically-based optimizer approach permits fine-tuning of the > SQL statement for added performance. This advantage, however, is generally > lost during ad hoc querying by less sophisticated users since fine-tuning > requires a good understanding of the database structure and an appreciation > for Oracle's optimizer strategies. The rumor mill has it that Oracle is > preparing a statistically-based optimizer. I would not be surprise if this > optimizer comes to be regarded as the most effective in the industry. > --- > Ernest Rubi Phone : (713) 579-4789 May I summarize - If you get the query statement right, Oracle can optimize it, if you get it wrong (by that I mean in the syntactically correct, "wrong" order) that Oracle cannot optimise :-( Another well-known database product has an intelligent optimiser which is _already_ regarded as the most effective in the Industry :-) - OK you guessed - INGRES. ----------------------------------------------------------------------------- From : Colin McIntosh, Project Manager # .=====. ======== Computertime Ltd, P.O. Box 6243 # / / / / Wellington, New Zealand # / / / Phone: (04) 852 195 # / / / Fax : (04) 828 288 # / / / / Internet: MCINTOSHC@NZFC.CO.NZ # ====== / ====== Disclaimer: My speak is my speak not their speak ... -----------------------------------------------------------------------------
jklein@.com (Jonathan Klein) (02/07/91)
In article <9292@cognos.UUCP> nigelc@cognos.UUCP (Nigel Campbell) writes: >In article <1991Feb3.182558.14411@oracle.com> jklein@oracle.UUCP (Jonathan Klein) writes: >>In article <10737@pasteur.Berkeley.EDU> mao@eden.Berkeley.EDU (Mike Olson) writes: >>>a couple of days ago i posted a query about oracle to this newsgroup. >>>since i got no response, i thought i would repost with a more provocative >>>subject line. >>> >>>here's the question: i have heard that oracle does not have a query >>>optimizer. i find this surprising. is it true? >>> > >[stuff deleted] > >>All versions of oracle that I have worked with have had an query optimizer. >> >>Jonathan Klein > >Would you like to give a comparison on the merits of the Oracle approach >vs say Rdb/Vms,Interbase,Sybase etc especially based on a database where >the user has not taken to using physical row,table placement schemes . > > Query optimization is not my area of development; I was just posting a response to correct some misinformation about whether oracle does perform any optimization processing. > > >-- >Nigel Campbell Voice: (613) 783-6828 P.O. Box 9707 >Cognos Incorporated FAX: (613) 738-0002 3755 Riverside Dr. >uucp: nigelc@cognos.uucp || uunet!mitel!cunews!cognos!nigelc Ottawa, Ontario >|| nigelc@cognos.uucp.@ccs.carleton.ca CANADA K1G 3Z4
r_anderson@banzai.enet.dec.com (Rick Anderson) (02/07/91)
In article <1991Feb6.181906.17145@cimage.com>, dbc@cimage.com (David Caswell) writes: > How bad is Oracle -- Not nearly as bad as Unify. Everyone is entitled to their opinion, but could you please elaborate on this statement - what makes Oracle not nearly as bad as Unify? On what facts, features or frustrations do you base this statement? Rick ********************************************************** * Rick Anderson * Digital Equipment Corporation * * 603-884-4284 * Database Systems Division * * Mailstop: NUO 1-1/F12 * 55 Northeastern Boulevard * * "My timing is Digital" * Nashua, NH 03062 * ********************************************************** * UUNET: ...{decwrl|decvax}!nova.enet.dec.com!r_anderson * * Internet: r_anderson%nova.enet.dec@decwrl.dec.com * **********************************************************
miket@blia.sharebase.com (Mike Tossy) (02/08/91)
In article <1991Feb6.144339.10316@lgc.com>, rubi@lgc.com ( Ernest Rubi) writes: > In article <9292@cognos.UUCP> nigelc@cognos.UUCP (Nigel Campbell) writes: > >In article <1991Feb3.182558.14411@oracle.com> jklein@oracle.UUCP (Jonathan Klein) writes: > >>In article <10737@pasteur.Berkeley.EDU> mao@eden.Berkeley.EDU (Mike Olson) writes: [stuff deleted] > >>>here's the question: i have heard that oracle does not have a query > >>>optimizer. i find this surprising. is it true? [stuff deleted] > >>All versions of oracle that I have worked with have had an query optimizer. > >> > >>Jonathan Klein > > > >Would you like to give a comparison on the merits of the Oracle approach > >vs say Rdb/Vms,Interbase,Sybase etc especially based on a database where > >the user has not taken to using physical row,table placement schemes . > >Nigel Campbell Voice: (613) 783-6828 P.O. Box 9707 > >Cognos Incorporated FAX: (613) 738-0002 3755 Riverside Dr. [stuff deleted] > Oracle's syntactically-based optimizer approach permits fine-tuning of the > SQL statement for added performance. This advantage, however, is generally > lost during ad hoc querying by less sophisticated users since fine-tuning > requires a good understanding of the database structure and an appreciation > for Oracle's optimizer strategies. The rumor mill has it that Oracle is > preparing a statistically-based optimizer. I would not be surprise if this > optimizer comes to be regarded as the most effective in the industry. > --- > Ernest Rubi Phone : (713) 579-4789 > Landmark Graphics Corp. Fax : (713) 579-4814 > 333 Cypress Run E-Mail: rubi@lgc.com > Houston, TX 77094 The ShareBase optimizer (and I think most other statistical based optimizers) have an over-ride feature. Given that, it is hard to understand any advantage for the Oracle approach. The override feature allows you to explicitly request a strategy. The Oracle approach requires that you muck up your SQL query (for example, with meaningless function calls that prevent the use of some indices.) Every system ALLOWS fine-tuning of the SQL statement for performance; Oracle REQUIRES fine-tuning of the SQL statement for performance. My opinion: "syntactically-based optimizer" is an oxymoron. -- >>>>>>> The above are only my opinions <<<<<<<< Mike Tossy ShareBase Coropration miket@sharebase.com 14600 Wichester Blvd (408) 378-7575 ext2200 Los Gatos, CA 95030 (ShareBase is a subsidiary of Teradata Corportation)
pavlov@canisius.UUCP (Greg Pavlov) (02/08/91)
In article <1991Feb6.144339.10316@lgc.com>, rubi@lgc.com ( Ernest Rubi) writes: > > .... The rumor mill has it that Oracle is > preparing a statistically-based optimizer. I would not be surprise if this > optimizer comes to be regarded as the most effective in the industry. > --- I would be interested in knowing what this assessment/prediction is based on. thanks, greg pavlov, fstrf, amherst, ny pavlov@stewart.fstrf.org
nico@Unify.Com (Nico Nierenberg) (02/09/91)
In article <1991Feb6.181906.17145@cimage.com> dbc@dgsi.UUCP (David Caswell) writes: >.In article <1991Feb3.182558.14411@oracle.com> jklein@oracle.UUCP (Jonathan Klein) writes: >.>In article <10737@pasteur.Berkeley.EDU> mao@eden.Berkeley.EDU (Mike Olson) writes: >.>>here's the question: i have heard that oracle does not have a query >.>>optimizer. i find this surprising. is it true? >.>> >.> >.>All versions of oracle that I have worked with have had an query optimizer. >.> >.>Jonathan Klein >.>Oracle Corporation >. > >How bad is Oracle -- Not nearly as bad as Unify. Gee, what a thought provoking comment. Thank you for this insight which clearly is based on a tremendous amount of analysis :-( (Actually this whole thread is moronic. It was started by a cheap shot comment by someone working on the postgres project directed at Oracle). -- --------------------------------------------------------------------- Nicolas Nierenberg "No matter where you go, Unify Corp. there you are." nico@unify
allbery@NCoast.ORG (Brandon S. Allbery KB8JRR) (02/11/91)
As quoted from <1991Feb6.181906.17145@cimage.com> by dbc@cimage.com (David Caswell):
+---------------
| How bad is Oracle -- Not nearly as bad as Unify.
+---------------
Which Unify? Unify 2000 doesn't seem to have any problems with queries being
phrased in the "wrong" order or etc., and (once we figured out where we needed
indexes on some large tables) runs quite fast. Certainly faster than the old
Unify 4.0/5.0.
++Brandon
--
Me: Brandon S. Allbery VHF/UHF: KB8JRR on 220, 2m, 440
Internet: allbery@NCoast.ORG Packet: KB8JRR @ WA8BXN
America OnLine: KB8JRR AMPR: KB8JRR.AmPR.ORG [44.70.4.88]
uunet!usenet.ins.cwru.edu!ncoast!allbery Delphi: ALLBERYmarti@mint.inf.ethz.ch (Robert Marti) (02/11/91)
In article <13470@blia.sharebase.com> miket@blia.sharebase.com (Mike Tossy) writes: >My opinion: "syntactically-based optimizer" is an oxymoron. Maybe. However, there is a research field called "semantic query optimization" in which systems attempt to exploit the "knowledge" expressed by semantic integrity constraints to simplify queries. These optimizations may go beyond the "purely syntactic" transformations such as pushing selection and projection operations in the parse tree as far down as possible). Of course, even these semantic optimization are ultimately syntactic. > >>>>>>> The above are only my opinions <<<<<<<< Same here. Robert Marti | Phone: +41 1 254 72 60 Institut fur Informationssysteme | FAX: +41 1 262 39 73 ETH-Zentrum | E-Mail: marti@inf.ethz.ch CH-8092 Zurich, Switzerland |
dant@microsoft.UUCP (Dan TYACK) (02/15/91)
If you define an optimizer as some code that chooses a join order and a set of access methods to execute a query, based on statistics on the cardinality of the relations involved, and the join selectivity of the join columns (as does DB2, SQL/DS, DBM, Informix, etc) or based additionaly on histograms of the distribution of the data (Ingres), then Oracle doesn't have one. If you regard an optimizer as some code that makes efficient use of indexes, based on a join order specified in the syntax of a retrieval request, then Oracle has a pretty good one. Note that there are disadvantages to the statistics based optimizers used by Ingres, Sybase, DB2, etc. A significant amount of time is spent in the optimizer in evaluating alternative join strategies (Ingres has a useful optimization default whereby the optimizer will quit if it determines that it has spent longer trying to optimize a query than it would have taken to run the best plan analyzed in the optimization phase). Oracle will just go ahead and execute a query given to it, without a great deal of overhead. Note that given equal query execution speed (which is not true, in reality) a properly hand tuned Oracle query will outperform an equivalent query on a system that uses sophisticated optimization. However, non-tuned queries will generally perform more evenly when statistics based optimization is used, especially when complex queries are used, or when the optimizer has the capability to 'flatten' nested subqueries into equivalent explicit joins. In article <1991Feb3.182558.14411@oracle.com> jklein@oracle.UUCP (Jonathan Klein) writes: >In article <10737@pasteur.Berkeley.EDU> mao@eden.Berkeley.EDU (Mike Olson) writes: >>a couple of days ago i posted a query about oracle to this newsgroup. >>since i got no response, i thought i would repost with a more provocative >>subject line. >> >>here's the question: i have heard that oracle does not have a query >>optimizer. i find this surprising. is it true? >> >>email or post responses. i'm eager to hear the answer to this one. >> >> mike olson >> postgres research group >> uc berkeley >> mao@postgres.berkeley.edu > >All versions of oracle that I have worked with have had an query optimizer. > >Jonathan Klein >Oracle Corporation
nobody@blia.sharebase.com (Nobody at all) (02/19/91)
In article <70683@microsoft.UUCP> dant@microsoft.UUCP (Dan TYACK) writes: >If you define an optimizer as some code that chooses a join order ........some comments about systems that optimize...... >Note that there are disadvantages to the statistics based optimizers used by Ingres, >Sybase, DB2, etc. A significant amount of time is spent in the optimizer in >evaluating alternative join strategies (Ingres has a useful optimization >default whereby the optimizer will quit if it determines that it has spent >longer trying to optimize a query than it would have taken to run the best plan analyzed in the optimization phase). Oracle will just go ahead and execute >a query given to it, without a great deal of overhead. True enough, but: A good DBMS should beable to optimize at compile time and find the best query plan without help from the user and then store the plan so that time critical applications have acceptible performance. I believe that it takes less time overall and less work from the user if the optimizer takes the extra time to generate a good plan than for the user to have to try various orderings of the query and then figure out which one runs fastest. When the database grows and performance becomes unaceptible because the plan chosen by the user is no longer optimal the process must be repeated. The designer of the database may not be available to redo the optimization and more work will be needed. If a good optimizer stores the plan, the DBA need only issue a recompile to get a new (and better) plan when the database changes in size.
jeffl@sybase.Sybase.COM (Jeff Lichtman) (02/19/91)
> Note that there are disadvantages to the statistics based optimizers used by Ingres, > Sybase, DB2, etc. A significant amount of time is spent in the optimizer in > evaluating alternative join strategies (Ingres has a useful optimization > default whereby the optimizer will quit if it determines that it has spent > longer trying to optimize a query than it would have taken to run the best plan analyzed in the optimization phase). If the DBMS has pre-compiled queries, the cost of optimization is paid only once. The "advantage" of a syntax-based optimizer disappears in such a case. In fact, you wouldn't even want to apply Ingres's strategy of stopping the optimization process if the time spent in optimization is too great, again because it is a one-time cost - you'd rather pay the price of finding the absolute best query plan once than the price of running a sub-optimal plan thousands of times. In fact, the "give up early" strategy has other disadvantages - it causes the query plan to become worse when there is a load on the system, which is when you really want the best query plan. One other thing about a syntax-based optimizer: one change in the schema can force you to re-write a bunch of queries. Suppose you drop an index, for example, to make certain updates run faster. Now you may have to change all of the queries that depend on that index. This could be a lot of work, especially if you haven't kept track of which queries use the index. With a cost-based optimizer, you don't have to change your queries at all - the optimizer will figure out the best query plan for each query, regardless of what indexes exist or how the query is written. This is the real issue, IMHO. Relational database systems were invented to improve data independence, that is, to make programs less dependent on the physical schema than they were with older types of database systems. Syntax- based optimizers force the programmer to think about the physical schema when writing queries, and to change the application when the physical schema changes. Cost-based optimizers come much closer to the ideal of separating the application from the physical schema. --- Jeff Lichtman at Sybase {mtxinu,pacbell}!sybase!jeffl -or- jeffl@sybase.com "Saints should always be judged guilty until they are proved innocent..."
barrym@informix.com (Barry Mednick) (02/20/91)
In article <13512@blia.sharebase.com> mike@woodstock.UUCP (Mike Ubell) writes: > >True enough, but: A good DBMS should beable to optimize at compile time >and find the best query plan without help from the user and then store >the plan so that time critical applications have acceptible performance. Without help from the user, yes. At compile time, perhaps not. Consider what could change between compile time and run time: Indexes can be dropped and created The sizes of tables can change All the factors used to determine a good query plan may be different when the program is run. Wouldn't it make more sense to optimize at run time?
rene@ingres.com (Account Manager) (02/20/91)
In article <12376@sybase.sybase.com> jeffl@sybase.Sybase.COM (Jeff Lichtman) writes: >> Note that there are disadvantages to the statistics based optimizers used by >Ingres, >> Sybase, DB2, etc. A significant amount of time is spent in the optimizer in >> evaluating alternative join strategies (Ingres has a useful optimization >> default whereby the optimizer will quit if it determines that it has spent >> longer trying to optimize a query than it would have taken to run the best >plan analyzed in the optimization phase). > >If the DBMS has pre-compiled queries, the cost of optimization is paid only >once. The "advantage" of a syntax-based optimizer disappears in such a case. >In fact, you wouldn't even want to apply Ingres's strategy of stopping the >optimization process if the time spent in optimization is too great, again >because it is a one-time cost - you'd rather pay the price of finding the >absolute best query plan once than the price of running a sub-optimal plan >thousands of times. In fact, the "give up early" strategy has other >disadvantages - it causes the query plan to become worse when there is >a load on the system, which is when you really want the best query plan. > some lines left out . . . >Jeff Lichtman at Sybase >{mtxinu,pacbell}!sybase!jeffl -or- jeffl@sybase.com >"Saints should always be judged guilty until they are proved innocent..." Jeff is correct in his statement on cost-based optimizers in general, but the concept of pre-compiled queries has also certain disadvantages, that come very close to those of pre-programmed queries in syntax-based systems. Data size and distribution may vary between queries (unless you would want to pre-compile each query), and they may vary in time. How does a similar system knows when to re-compile ? How does a DBA know for sure that the pre-compiled versions is the fastest ? We (I mean RDBMS suppliers) have often criticized DB2 for doing this, so what solution does Jeff see in this field ? Rene ====* ASK Computer Systems Inc. Ingres Products Division Amsterdam, The Netherlands <normal disclaimers apply to the opinions above>
rickcole@milkbones.colorado.edu (Rick Cole) (02/28/91)
In article <1991Feb19.204354.16211@informix.com>, barrym@informix.com (Barry Mednick) writes: |> In article <13512@blia.sharebase.com> mike@woodstock.UUCP (Mike |> Ubell) writes: |> > |> >True enough, but: A good DBMS should beable to optimize at compile |> time |> >and find the best query plan without help from the user and then |> store |> >the plan so that time critical applications have acceptible |> performance. |> Without help from the user, yes. At compile time, perhaps not. |> Consider what could change between compile time and run time: |> Indexes can be dropped and created |> The sizes of tables can change |> All the factors used to determine a good query plan may be different |> when the program is run. Wouldn't it make more sense to optimize |> at run time? Late bound variables can also create nasty problems for compile time optimizers. Perhaps a bit of run time code could detect when the state of the database had changed sufficiently to warrent run time reoptimization. Many database systems already reoptimize at run time when indexes necessary to a compile time plan have been dropped. Of course, detecting the existence of new interesting indexes, significant changes to data, unexpected late bound variables, and whatever else might degrade the performance of a compile time plan would also effect run time performance. Maybe a database daemon could reoptimize compile time plans before they might be needed for execution?
mark@apexepa.UUCP (Mark Richter) (02/28/91)
In article <1991Feb19.204354.16211@informix.com> barrym@informix.com (Barry Mednick) writes: >In article <13512@blia.sharebase.com> mike@woodstock.UUCP (Mike Ubell) writes: >> >>True enough, but: A good DBMS should beable to optimize at compile time >>and find the best query plan without help from the user and then store >>the plan so that time critical applications have acceptible performance. >Without help from the user, yes. At compile time, perhaps not. >Consider what could change between compile time and run time: > Indexes can be dropped and created > The sizes of tables can change >All the factors used to determine a good query plan may be different >when the program is run. Wouldn't it make more sense to optimize >at run time? You are both right. In fact a good DBMS will privide flexibility here. If I have a fairly non-volatile database, and want lightning fast performance, I'll want my DBMS to compile and save the whole access plan at compile time. But when my database changes I'll want to reoptimize. And, if my database is volatile I'll want some amount of runtime optimization on every query. So, I continue to be amazed that there is a debate over when optimization should be done. Ya gotta be flexible. It all depends on how the database is used! A good DBMS must offer such flexibility to the customer. ---- Mark D Richter ----
nobody@blia.sharebase.com (Nobody at all) (03/01/91)
In article <1991Feb19.204354.16211@informix.com> barrym@informix.com (Barry Mednick) writes: >In article <13512@blia.sharebase.com> mike@woodstock.UUCP (Mike Ubell) writes: >> >>True enough, but: A good DBMS should beable to optimize at compile time >>and find the best query plan without help from the user and then store >>the plan so that time critical applications have acceptible performance. >Without help from the user, yes. At compile time, perhaps not. >Consider what could change between compile time and run time: > Indexes can be dropped and created > The sizes of tables can change >All the factors used to determine a good query plan may be different >when the program is run. Wouldn't it make more sense to optimize >at run time? (Gee I hate to keep plugging our features...) ShareBase III automaticly marks a query for recompile when indices are changed on any table in the query and the query is recompiled the next time it is run. (In fact you can drop and recreate a table in the query and the same thing happens.) We do not, but it would not be too hard, to detect when the sizes of the tables change "significantly" and recompile the query. I don't believe that the fact that the plan may get out of date is a reason not to compile it. We have a customer with a query that runs in about 1/3 of the time it takes to compile it. (Our older optimizer which did not do complete searches could not find an acceptible plan and they had to hand optimize it, they are much happer with not having to do that anymore.) Michael Ubell mike@sharebase.com
nigelc@cognos.UUCP (Nigel Campbell) (03/02/91)
In article <13594@blia.sharebase.com> mike@sharebase.com (Mike Ubell) writes: >In article <1991Feb19.204354.16211@informix.com> barrym@informix.com (Barry Mednick) writes: >>In article <13512@blia.sharebase.com> mike@woodstock.UUCP (Mike Ubell) writes: >>> >>>True enough, but: A good DBMS should beable to optimize at compile time >>>and find the best query plan without help from the user and then store >>>the plan so that time critical applications have acceptible performance. >>Without help from the user, yes. At compile time, perhaps not. >>Consider what could change between compile time and run time: >> Indexes can be dropped and created >> The sizes of tables can change [stuff deleted] > >(Gee I hate to keep plugging our features...) >ShareBase III automaticly marks a query for recompile when indices >are changed on any table in the query and the query is recompiled >the next time it is run. (In fact you can drop and recreate a Dg/Sql will recompile and update the compile unit however at least for rev 4 if it was a read only transaction that used the unit the compilation was done in your local cache and the database was not updated with the reopt code . What does Sharebase do in the situation . Dg/Sql Dba tools allow you to issue an update command to recompile the units marked dirty which can be part of jobs known to cause the stats to be skewed . Dg also has the handy feature of letting you setting the row count on a table . -- Nigel Campbell Voice: (613) 783-6828 P.O. Box 9707 Cognos Incorporated FAX: (613) 738-0002 3755 Riverside Dr. uucp: nigelc@cognos.uucp || uunet!mitel!cunews!cognos!nigelc Ottawa, Ontario || nigelc@cognos.uucp.@ccs.carleton.ca CANADA K1G 3Z4
jeffl@sybase.Sybase.COM (Jeff Lichtman) (03/02/91)
> Consider what could change between compile time and run time: > Indexes can be dropped and created > The sizes of tables can change > All the factors used to determine a good query plan may be different > when the program is run. Wouldn't it make more sense to optimize > at run time? I will divide this into two problems: 1) Stored query plans can become invalid - for example, if an index is dropped, a plan that uses the index won't work. 2) Plans can become obsolete - for example, if a table grows significantly, the plan may be too slow. Problem 1 is easily solved: before running a plan, the DBMS validates that the access paths it uses are still there. If they aren't, recompile the query automatically. The user doesn't have to do anything. Problem 2 is more difficult, because there is no good way to test for obsolete plans at runtime. Fortunately, this doesn't seem to happen much in practice. If it does happen, some systems with compiled plans allow users to ask for recompilation at any time. Also, most systems don't force the user to pre-compile all their queries - if the plans become obsolete too quickly, the user can always avoid pre-compilation for those particular queries. What it comes down to is that compilation is costly. The relative cost of compilation (not the absolute cost) is greatest with simple queries, which are the ones that are most likely to have stable query plans. The cost of having to force recompilation every so often is usually less than the cost of compiling every time. Also, those queries that are troublesome don't have to be pre-compiled. --- Jeff Lichtman at Sybase {mtxinu,pacbell}!sybase!jeffl -or- jeffl@sybase.com "Saints should always be judged guilty until they are proved innocent..."
mike@blia.sharebase.com (Mike Ubell) (03/05/91)
Newsgroups: comp.databases Subject: Re: how bad is oracle? (Really: RDBMS Optimizers) Summary: Expires: References: <10737@pasteur.Berkeley.EDU> <1991Feb3.182558.14411@oracle.com> <70683@microsoft.UUCP> Sender: Reply-To: mike@sharebase.com (Mike Ubell) Followup-To: Distribution: Organization: ShareBase Keywords: optimizer In article <9385@cognos.UUCP> nigelc@cognos.UUCP (Nigel Campbell) writes: > >Dg/Sql will recompile and update the compile unit however at least for >rev 4 if it was a read only transaction that used the unit the compilation >was done in your local cache and the database was not updated with the >reopt code . What does Sharebase do in the situation . Dg/Sql Dba tools >allow you to issue an update command to recompile the units marked dirty >which can be part of jobs known to cause the stats to be skewed . >Dg also has the handy feature of letting you setting the row count on a >table . > > > >-- >Nigel Campbell Voice: (613) 783-6828 P.O. Box 9707 >Cognos Incorporated FAX: (613) 738-0002 3755 Riverside Dr. >uucp: nigelc@cognos.uucp || uunet!mitel!cunews!cognos!nigelc Ottawa, Ontario >|| nigelc@cognos.uucp.@ccs.carleton.ca CANADA K1G 3Z4 Never having used Dg/Sql I really don't know what the local Cache or reopt code are. ShareBase III will let you recompile a stored program or command even if it is not invalidated. There is no restriction on the command being readonly or read/write. Read/write commands tend to get invalidated more often due to some internal constraints. The DBA can change the row and page counts and the index selectivity factors to any alternate relaity that seems apropriate and can grant others the permission to do so. Michael Ubell mike@sharebase.com