Learning Center
Plans & pricing Sign in
Sign Out



									Oracle optimizer ---------------Rowid- it records the physical location where row is stored. Three modes of operation 1) Rule(RBO) 2) Cost(CBO) (Use analyze command for generating statiatics) 3) Choose invokes CBO if statistics available otherwise RBO. If a query references tables that have been analyzed and tables that have not been analyzed, then CBO may choose to perform full table scan of table that were not analyzed. Can be specified by optimizer_mode in init.ora file. these settings can be overriden at query or session level. Different operations -------------------1) Table Access Full-this is used when there is no where clause. 2) Table Access By Rowid-Oracle uses indexes to correlate data values with rowid. Related Hints ------------Can specify hints that direct CBO in processing of query. Immediately after select, enter /*+ Then hint such as FULL(worker) Close hint with */ Two type of hints- FULL,ROWID Types of Indexes ---------------1) Unique indexes 2) Non Unique indexes Index Unique Scan ----------------Here two types-if value requested by query had been contained within the index, then oracle would not use Table Access by Rowid.Since data would be in index, the index would be all that was needed to satisfy the query. Index Range Scan ---------------If queried based on range of values or if query using nonunique index. Even If a single value is evaluated then index range scan will be used in case of non unique index. When indexes are used/not used -----------------------------1) Query that compares a value to a column(indexed) in a where clause. When there is a nonunique index, then two operations are there-

a) Index Range Scan b) Table Access by rowid 2) When specifying a range of values for a column, an index will not be used, if the first character specified is wildcard. 3) If any functions are performed on column in the where clause index will not be used on that column. 4) If you concatenate two columns together or string to a column, then index will not be used. 5) Null values are not stored in indexes. So if is null or is not null checks are used for indexed columns, index is not used. 6) When limiting conditions in where are inequalities, then index is not used.So 'not in' will not use index but 'IN' will use index. 7) If index is on multiple columns and in where clause leading column is not used, then index is not used. 8) In case of Max or Min functions indexes are used. Selectivity of Index -------------------The index is very much selective for eg. 80% distinct values or can be less selective. If you are using CBO then optimiser chooses whether to use index or not. If more selective then more chances of using indexes. Combining Output from nultiple index scans -----------------------------------------If limiting conditions are specified for multiple columns in a query, optimizer may be able to use multiple indexes. for eg. select * from lodging where lodging >'M' and Manager >'M'; The sequence of operations isIndex Range Scan of Index on Lodging And-Equal------> Table Access by Rowid. Index Range Scan of Index on Manager The rowid's returned by both are scanned. Rowid's returned from comparision are used for Table Access by rowid. In query like 'where Manager in('Thom','Ken');'. The optimizer uses index unique scan for both differently then concatenates the result of two and then performs Table Access by Rowid. Related Hints ------------1) Index hint e.g. select /*+ INDEX(lodging) */ lodging from lodging where manager='KEN'; you can specify a list also as /*+ INDEX(lodging manager) */ Other Index related hints- INDEX_ASC, INDEX_DESC

Operations that manipulate Data sets -----------------------------------Union,Minus,Interestion-most of these operations do not return records to the users until the entire operation is completed. Ordering Rows-Three oracle operations sort rows without grouping the rows. a) SORT ORDER BY (As in order by) b) SORT UNIQUE (As is distinct) c) SORT JOIN Grouping Rows-Two oracle opertions sort rows while grouping like records together. a) SORT AGGREGATE e.g. select max(age) from worker; here age is not indexed. B) SORT GROUP e.g. select count(*) from worker group by lodging; Operations using rownum ----------------------Two operations- COUNT, COUNT STOPKEY

(Rownum Counter)

Union, Minus, Interest ---------------------1) select name from prospect UNION select name from longtime; Table Access full of prospect Union-All ---> Sort Unique Tbale Access full of Longtime 2) In case of Minus and Intersect Table Access full of prospect ---> Sort Unique (Minus or Intersect) Table Access full of Longtime ---> Sort Unique Selecting rows for update ------------------------Select * from worker for update of name; Table Access full returns the rows as soon as they are retrieved. But FOR UPDATE is a set-based operation. Selecting from views -------------------While querying the view, the limiting condition of query may be merged with that of view's. But if there are group by clause in view's query then these can not be merged hence degrades the performance. Table Access full ---> Sort Group by ---> Filter ---> View Selecting from Subqueries ------------------------Whenever possible,the optimizer will combine the text from subquery with the rest of the query.

i.e make it as a join in main query. If the subquery cannot be resolved as a join, then the subquery will be resolved before the rest of the query. It is similar as in case of views. The automatic merging of the query text and the view text can be disabled via hints. Use the init.ora parameter named _optimizer_undo_changes. To disable this, set this parameter to TRUE. Operations that perform joins ----------------------------Oracle has 3 methods for processing joins-MERGE JOIN operations,NESTED LOOPS operations and HASH JOIN operations. A particular join operation is used on basis of conditions in query, the available indexes,the available statistics. Joins of more than two tables ----------------------------if a query joins more than two tables, then the optimizer treats the query as a set of multiple joins.Let there be 3 tables.Then optimizer will execute the joins by joining two of the tables, then joining the result set of that join to the third table. The order should be (Small-Medium)-Large. Merge Join ---------e.g. select, Lodging.Manager from Worker,Lodging where worker.lodging=lodging.lodging; To resolve the query, the optimizer may choose to perform the MERGE JOIN. Each table is read individually by a Table Access Full operation.The set of rows returned from the table scan are sorted by SORT JOIN opertion separately.The data from the SORT JOIN operations is then merged via a MERGE JOIN operation. Table Access Full of Lodging ----> Sort Join Merge Join Table Access Full of Worker ----> Sort Join This is most efficient when tables are of same size.This is used when no indexes are available. When index is available then other join operations are used. Nested Loops -----------In which type of operation, the primary available on atleast one column which are joined. To execute optimizer must first select a driving table for the join. The driving read first. For each record in the driving table, the second queried.

requirement is that index must be a NESTED LOOPS join, the table is the table that will be table in the join will be

Tables worker,lodging there is a index on lodging and no index on worker. Worker is selected as driving table. Table Access full of Worker---------------------------------------------->Nested Loops | | | | ---->Index unique Scan of Lodging---->Table Access by rowid of lodging----These are mostly used for online users.If u are using CBO then driving table will be selected based on statistics and selectivity. If this RBO then generally the last table in select statement will be used as driving table. These are useful when tables are of different size.Using smaller table as driving table. Hash Join --------During a hash join, the first table is scanned via a Table Access Full and the database applies 'hashing' functions to the data to prepare the table for the join. The values from the second table are then read and the hashing function is used to compare the second table with first table. The rows that result in match are returned to the user. The optimizer may choose to perform hash joins even if indexes are available. As Full scans are performed, so this most useful when parallel query option is used. The opertions involved in hash join return records quickly to the users. Table Access full of worker-----> Hash join Table Access full of Lodging----> Processing Outer joins ---------------------When processing outer joins, the optimizer will use one of three methods discussed earlier. For ex. if there is an outer join between Worker and Lodging then a Nested Loops Outer will be used. Here outer table is used as driving table. Related Hints ------------Hints can be used to override the optimizer's selection of a join method. Hints can be used for two purposes-Type of join method,goal of the join. Hints discussed earlier can also be used in addition to these. Hints about goals -----------------

Two goals related to joins are-All_Rows,First_Rows. Default is All_Rows. PreRequeist for using these hints is that tables must be analyzed. Hints about Methods ------------------You can specify the specific operations to use and the tables to use them on. The hints are USE_NL,USE_MERGE,USE_HASH. If you are using RBO, no hints are acceptable. Displaying the Execution Plan ----------------------------1) By setting set autotrace on 2) explain plan set statement_id='test' for Query Filtering of rows ----------------When index is available FILTER operation is not listed explicitly in a query's execution plan. It will be performed as part of the Table Access by Rowid operation. When a FILTER operation is listed explicitly in exection plan, it usually indicates that no index is available for limiting condition.

To top