cpl@bhpmrl.oz.au (Paul Lloyd) (02/04/91)
Mike Olsen asks regarding Oracle: >i know that early versions of oracle did not include a query optimizer. >qualifications were evaluated left-to-right in the user's query. this >led to some predictable problems: the user was not insulated from changes >in data representation, and had to expend a fair amount of energy in >optimizing queries by hand by ordering qual clauses cleverly. A recent article in DBMS ("Tuning Oracle SQL Statements", by Charles Peck, Oct. 90, p.81) answers this part of your question quite nicely. Oracle has a rule/syntax-based optimizer, not cost-based. Peck shows some of the pitfalls associated with this class of optimizer - it still requires a thorough knowledge of the physical schema and how Oracle works out its execution plan. It should be pointed out, however, that this manual optimization is mainly required only for performance improvements. It could be fairly significant in a distributed database, however, where inordinate amounts of data may needlessly be transferred over the network. Some of Peck's work-arounds do point out how kludgy you need to get to make those improvements. I understand that Oracle is moving towards a cost-based optimizer, despite a long campaign to show that it was not necessary. I believe that v7 will have this implemented, but I could be wrong there. Hope this helps. -- /\/\ Paul Lloyd, Superintendent Computer Systems / / /\ BHP Research - Melbourne Laboratories / / / \ 245-273 Wellington Rd Mulgrave Vic 3170 AUSTRALIA / / / /\ \ Phone : +61-3-560-7066 ext:7375 \ \/ / / / Fax : +61-3-561-6709 \ / / / ACSnet : cpl@bhpmrl.oz.au \/\/\/ Internet: cpl%bhpmrl.oz.au@uunet.uu.net