The number of pages in each index of interest. The number of distinct keys in each index of interest.Ĥ. The number of pages in each relation of interest.ģ. Cardinality of each relation of interest.Ģ. Often, the query optimizer would at least retrieve the following information:ġ. The metadata or system catalog consists of descriptions of all the databases that a DBMS maintains. In formulating the plans, the query optimizer obtains relevant information from the metadata that the system maintains and attempts to model the estimated costs of performing many alternative query plans and then selects the best amongst them. It relates the symbolic names in the query to data base objects and checks their existence and checks if the user is authorized to perform the operations that the query specifies. The optimizer carries out a number of operations. The optimizer is then invoked with the internal representation of the query as input so that a query plan or execution plan may be devised for retrieving the information that is required. It is then necessary to check the query for semantic correctness by consulting the system catalogues and check for semantic errors and type compatibility in both expressions and predicate comparisons. If the parser returns with no errors, and the query uses some user-defined views, it is necessary to expand the query by making appropriate substitutions for the views. The parser basically checks the query for correct syntax and translates it into a conventional parse-tree (often called a query-tree) or some other internal representation.
The query processing of such queries by a DBMS usually involves the following four phases: In most database systems, queries are posed in a non-procedural language like SQL and as we have noted earlier such queries do not involve any reference to access paths or the order of evaluation of operations.