Oracle Training - Make Your Systems

Your Most Valuable Asset

A good way to learn about Oracle is to see answers to the questions other people have asked and here we answer a few questions from our subscribers. To send us your questions (or feedback) use the contact form  and to subscribe just click here. We also offer classroom-based Oracle training for developers and dbas both on site or off site and the resources page has recommendations for further reading and links to information on OTN and elsewhere. See here for more on-line Oracle training Contents • How to get the 2nd or the 3rd highest salary in the emp table? • Will indexing the underlying tables speed up queries on the view? From: amiya ranjan - how to get the 2nd or the 3rd highest salary in the emp table? There are a couple of ways to do this, depending on what      you want to do exactly. You could do it programmatically using PL/SQL (or any other programming language), for example as follows: DECLARE     i PLS_INTEGER := 0; BEGIN     FOR sal_recs IN ( SELECT sal FROM emp ORDER BY sal DESC) LOOP         i := i + 1;         IF i=3 THEN  <do something> END IF;     END LOOP; END; The other way to do it is use just pure SQL as follows: SELECT sal FROM (SELECT sal FROM emp WHERE sal < (SELECT MAX(sal) FROM emp) ORDER BY sal DESC) WHERE ROWNUM < 3 This method creates an in-line view (SELECT sal FROM emp ...) which returns all the salaries less than the maximum salary which in turn is obtained by use of a correlated sub-query (SELECT MAX(sal) FROM emp). Note the subtle differences between these 2 approaches. The procedural approach would just take the 3rd record returned. As the records are sorted in descending order you know that this will be the 3rd highest salary. Or do you?  If there are 2 or more records with the same highest (max) salary, this will be ignored which may not be quite what you want. On the other hand, the pure SQL approach ignores additional records that have the same highest salary and start counting only from the first record with a salary lower than this, but again, if there are 2 or more records with the same 2nd highest salary, that won't be filtered by your query. To ensure that any duplicates are eliminated you would have to do it programmatically. --------- From: Eric Dessureault - HI, after reading your articles, I have a question. I have an Oracle database with small tables ( 3000 rows max/table, ± 20 tables). I created a view which gathers all the information from all the tables. Will indexing the tables speed up queries on this view? The answer is ... it depends. It depends on how big each table is and how many rows you expect to retrieve by using the query. If the tables are very small, it would be better to cache them - this will keep the table's data blocks in memory. If the tables are larger and you are only selecting a few rows or just the indexed column(s), then indexing the columns used to determine which rows are required will speed up the query. You could also consider using index-organised tables whereby the data columns and the primary key columns are stored in a b-tree structure, like normal indexes. This can give fast access to the data. If your view is joining the tables together then you should consider indexing the join columns or creating a materialised view if the underlying data is relatively stable and queries against the view will be run frequently or by many different users simultaneously. With that said, the only way to know for sure is to test each of these possibilities to see which one offers the biggest overall performance gain. Remember that creating extra indexes may speed up queries but equally will slow down inserts and deletes on the table due to the need to maintain the indexes.  --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor-led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training. See our Oracle training schedule, or tell us your requirements. Oracle tips and tricks Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and make your systems better, faster and smarter. © Smartsoft Computing Ltd, Bristol, England  Need help? Contact Us This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.    View our privacy policy Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.