1 / 13

Orien IT - The best Oracle and Hadoop institute in Hyderabad

Best Oracle and Hadoop Institute: orienit is the best Oracle Institute in Hyderabad.Providing oracle courses and Hadoop courses by realtime faculty in hyderabad.

orienit
Download Presentation

Orien IT - The best Oracle and Hadoop institute in Hyderabad

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Hints in Oracle Presented By www.orienit.com

  2. What are Hints? • Hints give specific information that we know about our data and application. • A way to override the default query optimization in the DBMS • Influence the execution plan of query www.orienit.com

  3. Why Use Hints? • Oracle optimizer may not always choose the best execution plan • Using hints may improve the performance by changing the execution plan oracle takes. www.orienit.com

  4. Using Hints • Hints can be used in the Select, Delete, and Update clauses. • In each statement, the hint goes directly after the Select, Delete, or Update keyword. A few hints use Insert. • Hints are placed in the /*+ */ tag, where the hint goes after the + sign • Ex: SELECT /*+ ALL_ROWS */ From… www.orienit.com

  5. Types of Hints • Approach hints • Access hints • Join hints • Misc. hints www.orienit.com

  6. Approach Hints • ALL_ROWS: Minimizes total resource consumption. Results will be returned only after all processing has been completed • FIRST_ROWS(n): Minimized response time, or minimal resource usage to return the first n rows. www.orienit.com

  7. Access Hints • FULL(table): Chooses a full table scan for the table, even if there is an index available. • INDEX(table [index [index]...]): Chooses an Index scan for the table. www.orienit.com

  8. Index Hint Example • CIS Department has far more males than females • SELECT /*+ FULL(s) */ id, name FROM Student s WHERE sex = ‘m’; • SELECT /*+ Index(s sex_index) */ id, name FROM Student s WHERE sex = ‘f’; www.orienit.com

  9. Join hints • ORDERED: tables are joined in the order in which they appear in the FROM clause. • LEADING(table): specified table is the first table used in the join order. • USE_HASH(table [table] …): Tables are joined using a hash join. Smaller table is used to make a hash table on join key. The larger table is scanned using hash table to find joined rows. www.orienit.com

  10. Join Hints cont. • USE_NL(table [table]…): Joins tables using nested loops join, using specified table as inner join. For every row in outer table, oracle accesses every row in inner table. • USE_MERGE(table [table]…): Joins tables using a sort-merge join. Sorted list are made and then merged together. Best if tables are already sorted. www.orienit.com

  11. Misc. Hints • APPEND: Data is appended to the end of table, rather then using existing space. • CACHE(table): Blocks received are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is preformed. • NOCACHE(table): Blocks received are placed at the least recently used end of the LRU list in the buffer cache. www.orienit.com

  12. Sources • Oracle documentation at: http://oracle.cis.ksu.edu/oradocs/index.htm www.orienit.com

More Related