Re: Oracle Optimizer Questions.

In article <1ahve6INNjgv_at_agate.berkeley.edu>, mao_at_triplerock.CS.Berkeley.EDU (Mike Olson) writes:
|> the oracle version 6 optimizer doesn't use cost analysis. oracle does
|> "semantic" query optimization, which is to say, it executes joins in
|> the order in which you write them, and will use an index if one happens
|> to be around. it doesn't reorder the query in any way.
|>
|> i believe that version 7 provides cost-based optimization.

I guess you wanted to say that the Oracle V6 optimizer is a _syntactic_ optimizer. From what I understand, it does not take the actual distribution of the data into account as some of the cost-based ("statistical") optimizers do.

Most people use the term semantic query optimizer to mean that some of the semantics of the database is taken into account when optimizing queries. Unfortunately, "semantics" is a somewhat fuzzy term. One example of semantic query optimization is to use (semantic) integrity constraints in order to "simplify" given queries. Simplification in this case means the following: given a database consisting of (1) a set of facts, (2) a set of deduction rules, and (3) a (nonempty!) set of integrity constraints, and given a query Q, find a query Q' which is cheaper to evaluate than Q and returns the same result as Q, provided that all integrity constraints are satisfied. (A simple example is the following: assume you have a constraint which says that all oil tankers weigh more than 10 tons and assume that you want a list of all oil tankers which weigh less than 8 tons. Then it is obvious that if the constraint is satisfied you will get an empty list as an answer, provided that the constraint is not violated.)

I do not know of any commercial systems which perform this kind of semantic query optimization. (Indeed, I do not know a commercial system which allows the specification of purely declarative integrity constraints which go beyond what Codd has termed entity integrity and referential integrity.)

Papers on semantic query optimization are J.J. Kings 1981 (?) VLDB paper on his QUIST system and Chakravarthy's paper in Minker's "Foundations of Logic Programming and Deductive Databases". (Sorry, I don't have the references handy at the moment.)

-- 
Robert Marti                    |  Phone:    +41 1 254 72 60
Informationssysteme             |  FAX:      +41 1 262 39 73
ETH-Zentrum                     |  E-Mail:   marti_at_inf.ethz.ch
CH-8092 Zurich, Switzerland     |