Oracle Consulting and Training
For Better, Faster, Smarter
Oracle Training - Tips and Tricks To Make Your Oracle
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 and visitors. To send us your questions (or feedback) use the
contact form and to subscribe just click here.
We also offer 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
Oracle tips and tricks
How to get the 2nd or the 3rd highest salary in the
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:
i PLS_INTEGER := 0;
FOR sal_recs IN (SELECT sal FROM emp ORDER BY sal DESC) LOOP
i := i + 1;
IF i=3 THEN <do whatever is needed> END IF;
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
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 scheduled Oracle training courses, or let us know 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
Tel: 0845 003 1320
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
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their
© Copyright Smartsoft Computing Ltd 2014. All rights reserved.