Oracle Optimizer Types of Optimizers There are different
- Slides: 47
Oracle Optimizer
Types of Optimizers • There are different modes for the optimizer ALTER SESSION SET optimizer_mode = {choose|rule|first_rows(_n)|all_rows} • RULE: Rule-based optimizer (RBO) – Deprecated; not updated since 1994 • CHOOSE: Default option. Chooses cost-based optimizer (CBO) if statistics available, rulebased optimizer otherwise.
Types of Optimizers • ALL_ROWS (CBO): Execute the query so that all of the rows are returned as quickly as possible – Merge Join has priority over Block Nested Loop Join • FIRST_ROWS(n) (CBO): execute the query so that all of the first n rows are returned as quickly as possible – Block Nested Loop Join has priority over Merge Join
Analyzing the Data analyze table | index <table_name> | <index_name> compute statistics | estimate statistics [sample <integer> rows | percent] | delete statistics; analyze table Sailors estimate statistics sample 25 percent;
Viewing the Execution Plan • You need a PLAN_TABLE table. So, the first time that you want to see execution plans, run the command: @$ORACLE_HOME/rdbms/admin/utlxplan. sql • Set autotrace on to see all plans – Display the execution path for each query, after being executed
Operations that Access Tables • TABLE ACCESS FULL: sequential table scan – Oracle optimizes by reading multiple blocks – Used whenever there is no where clause on a query select * from Sailors • TABLE ACCESS BY ROWID: access rows by their Row. ID values. – How do you get the rowid? From an index! select * from Sailors where sid > 10
Types of Indexes • Unique: each row of the indexed table contains a unique value for the indexed column • Nonunique: the row’s indexed values can repeat
Operations that Use Indexes • INDEX UNIQUE SCAN: Access of an index that is defined to be unique • INDEX RANGE SCAN: Access of an index that is not unique or access of a unique index for a range of values
When are Indexes Used/Not Used? • If you set an indexed column equal to a value, e. g. , sname = 'Jim' • If you specify a range of values for an indexed column, e. g. , sname like 'J%' – sname like '%m': will not use an index – UPPER(sname) like 'J%' : will not use an index – sname is null: will not use an index, since null values are not stored in the index – sname is not null: will not use an index, since every value in the index would have to be accessed
When are Indexes Used? (cont( • 2*age = 20: Index on age will not be used. Index on 2*age will be used. • sname != 'Jim': Index will not be used. • MIN and MAX functions: Index will be used • Equality of a column in a leading column of a multicolumn index. For example, suppose we have a multicolumn index on (sid, bid, day) – sid = 12: Can use the index – bid = 101: Cannot use the index
Optimizer Hints • You can give the optimizer hints about how to perform query evaluation • Hints are written in /*+ */ right after the select • Note: These are only hints. The Oracle optimizer can choose to ignore your hints
Hints • FULL hint: tell the optimizer to perform a TABLE ACCESS FULL operation on the specified table • ROWID hint: tell the optimizer to perform a TABLE ACCESS BY ROWID operation on the specified table • INDEX hint: tells the optimizer to use an indexbased scan on the specified table
Examples Select /*+ FULL (sailors) */ sid From sailors Where sname=‘Joe’; Select /*+ INDEX (sailors s_ind) */ sid From sailors S, reserves R Where S. sid=R. sid AND sname=‘Joe’;
Combining Output From Multiple Index Scans • Suppose we have 2 indexes: sname, rating • select * from sailors where sname = 'Jim' and rating = 10 -----------------0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'SAILORS‘ 2 1 3 2 INDEX (RANGE SCAN) OF 'SNAME_IND' (NON-UNIQUE) 4 2 INDEX (RANGE SCAN) OF 'RAT' (NON-UNIQUE) AND-EQUAL
Operations that Manipulate Data Sets • Up until now, all operations returned the rows as they were found • There are operations that must find all rows before returning a single row • Try to avoid these operations for online users! – SORT ORDER BY: query with order by select sname, age from Sailors order by age;
Operations that Manipulate Data Sets – SORT UNIQUE: sorting records while eliminating duplicates (e. g. , query with distinct; query with minus, intersect or union) – SORT AGGREGATE, SORT GROUP BY: queries with aggregate or grouping functions (like MIN, MAX)
Is the table always accessed? What if there is no index?
Operations that Manipulate Data Sets • Consider the query: – select sname from sailors union select bname from boats;
Operations that Manipulate Data Sets • Consider the query: – select sname from sailors minus select bname from boats; How do you think that Oracle implements intersect? union all?
Operations that Manipulate Data Sets • Select MIN(age), COUNT(*) from Sailors GROUP BY rating ---------------------0 SELECT STATEMENT Optimizer=CHOOSE 1 0 2 1 SORT (GROUP BY) TABLE ACCESS (FULL) OF 'SAILORS'
Distinct • What should Oracle do when processing the query (assuming that sid is the primary key): – select distinct sid from Sailors • Try it!!
Join Methods • Select * from Sailors, Reserves where Sailors. sid = Reserves. sid • Oracle can use an index on Sailors. sid or on Reserves. sid (note that both will not be used) • Join Methods: MERGE JOIN, NESTED LOOPS, HASH JOIN
Nested Loops Joins • Block nested loop join NESTED LOOPS TABLE ACCESS FULL OF our_outer_table TABLE ACCESS FULL OF our_inner_table • Index nested loop join NESTED LOOPS TABLE ACCESS FULL OF our_outer_table TABLE ACCESS BY ROWID OF our_inner_table INDEX RANGE SCAN OF inner_table_index
Nested Loops Joins • Select * from reserves r, sailors s where r. sid=s. sid; ---------------------------0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF 'RESERVES‘ 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'SAILORS‘ 4 3 INDEX (UNIQUE SCAN) OF 'SYS_C 00628777' (UNIQUE)
When Are Nested Loops Joins Used? • If tables are of unequal size • If results should be returned online
Hash Join Plan HASH JOIN TABLE ACCESS FULL OF table_A TABLE ACCESS FULL OF table_B
When Are Hash Joins Used? • If tables are small • If results should be returned online
Sort-Merge Join Plan MERGE JOIN SORT JOIN TABLE ACCESS FULL OF table_A SORT JOIN TABLE ACCESS FULL OF table_B
When Are Sort/Merge Joins Used? • Performs badly when tables are of unequal size. Why?
Hint Examples for Joins Select /*+ USE_NL (sailors) */ sid From sailors S, reserves R Where S. sid=R. sid AND sname=‘Joe’; inner table Select /*+ USE_MERGE (sailors, reserves) */ sid From sailors S, reserves R Where S. sid=R. sid AND sname=‘Joe’; Select /*+ USE_HASH */ sid From sailors S, reserves R Where S. sid=R. sid AND sname=‘Joe’;
XML (Extensible Markup Language) and the Semi-Structured Data Model
Motivation • We have seen that relational databases are very convenient to query. However: – There is a LOT of data not in relational databases!! • Perhaps the most widely accessed database is the web, and it certainly isn’t a relational database.
Querying the Web • The web can be queried using a search engine, however, we can’t ask questions like: – What is the lowest price for which a Jaguar is sold on the web? • Problems: – There are no facilities for asking complex questions, such as aggregation of data
Understanding the Web • In order to query the web, we must be able to understand it. • 2 Computer Science Approaches: – Artificial Intelligence Approach – Database Approach
Database Approach “The web is unstructured and we will structure it” • Sometimes problems that are very difficult can be solved easily by enforcing a standard • Encourage the use of XML as a standard for data exchange on the web
Example XML Document <addresses > <person friend="yes"> Opening Tag <name> Jeff Cohen</name> <tel> 04 -828 -1345 </tel> <tel> 054 -470 -778 </tel> <email> jeffc@cs. technion. ac. il </email> </person> Attribute <person friend="no"> <name> Irma Levy</name> <tel> 03 -426 -1142 </tel> <email>irmal@yourmail. com</email> </person> </addresses> Closing Tag Element
Very Unstructured XML <? xml version=“ 1. 0”? > <Damage. Report> The insured’s <Vehicle Make = “Toyota”> Corolla </Vehicle> broke through the guard rail and plummeted into the ravine. The cause was determined to be <Cause>faulty brakes </Cause>. Amazingly there were no casualties. </Damage. Report>
XML Vs. HTML • XML and HTML are brothers. They are both special cases of SGML. • HTML has specific tag and attribute names. These are associated with a specific meaning • XML can have any tag and attribute name. These are not associated with any meaning • HTML is used to specify visual style • XML is used to specify meaning
Characteristics of Semistructured Data (From http: //www 2. cs. uh. edu/~ceick/6340/intro-xml. ppt) • Missing or additional attributes • Multi-valued attributes • Different types in different objects • Heterogeneous collections Self-describing, irregular data, no a priori structure
Relational vs Semi-Structured Relational Semi-Structured Abstract Sets of Labeled Directed Model tuples Graph Concrete Tables XML Documents Standard Storing Data Exchange for Data Separating Content Model from Style
Data Exchange • Problem: Many data sources, each of a different type (different vendor), with a different schema. – How can the data be combined and used together? – How can different companies collaborate on their data? – What format should be used to exchange the data?
Separating Content from Style • Web sites develop over time • Important to separate style from data in order to allow changes to the site structure and appearance • Using XML, we can store data alone • CSS separates style from data only in a limited way • Using XSL, this data can be translated into HTML • The data can be translated differently as the site develops
Write Once Use Everywhere XML Data XS L WML (hand-held devices) XSL HTML (web browser L S X TEXT (Excel)
Using XML • Quering and Searching XML: There are query languages and search engines that query XML and return XML. Examples: Xpath, Xquery /SQL 4 X, Equix, XSEarch • Displaying XML: An XML document can have an associated style-sheet which specifies how the document should be translated to HTML. Examples: CSS, XSL
- Types of optimizer in oracle
- Analyzing plan diagrams of database query optimizers
- Antigentest åre
- How to describe autism
- Greek love
- How many different types of numbers are there
- How many different types of bread are there
- Toad sql tuning
- Sql server query optimizer
- Mlp optimizer
- Kaizen event charter
- Runzheimer logistics optimizer
- Orca optimizer
- Inside the sql server query optimizer
- Display campaign optimizer
- Pepperdata capacity optimizer
- Opennop
- Image optimizer
- Eq optimizer
- Randolf geist
- Axioma factor model
- The chemical detective
- Why do different polymers have different properties
- Why do different atoms produce different colors
- Sound will travel at different speeds in different mediums.
- Sound travels fastest through
- Different culture have different moral codes
- Different angle different story
- Acid base song
- Different materials have different
- We are all different but the same
- Venn diagram different same different
- How many different rocks are there
- Different kinds of sport
- Sign language
- Throughout the world there are different
- There are many kinds of sports
- Boolean operators
- How many types of sports are there?
- Tema there is there are
- There is there are part of speech
- There is there are negative form
- There is there are countable uncountable nouns
- There is there are
- There is there are
- The committee debates these questions carefully
- Adjetivos demonstrativos
- Some any