Optimizer

Optimizer

Ask any DBA which part of the database they dislike and chances are they will reply, the optimizer. This is really a bit unfair, because the optimizer is an amazing component of a relational database and it does differentiate between the database engines in the market place.

Before relational, databases didn't need optimizers, because the only way the data could be retrieved was by access paths, previously defined by the DBA. All that changed with the introduction of relational databases, because now a mechanism was needed to join and retrieve data from tables, where no access path had been clearly defined. This is when the optimizer was born.

The role of the optimizer is to review an SQL statement and then decide which is the best way to retrieve the data. To be fair, the optimizer usually gets it right and does a really good job. Unfortunately, when it chooses a bad retrieval strategy it can means that a query takes minutes to return its row instead of seconds. If this occurs too often, the users become annoyed and this is why the optimizer is so disliked.

Two optimizers are available within Oracle8; rule and cost based. The cost based optimizer is the one to use and the rule based one exists to support older applications.

The cost based optimizer cannot make good decisions unless it has some statistics about your data. Therefore, it is very important to run the ANALYZE statement regularly if your data is volatile, to...

< Previous Excerpt Next Excerpt >

Purchase This Book

Oracle8 on Windows NT

TABLE OF CONTENTS

Optimizer

Contact Preferences

This is embarrasing...

Customize Your GlobalSpec Experience

Select Your Free Newsletters

Industry Newsletters

Select Your Free Product Alerts

This is embarrasing...