7th March, John Seaman, Editor (editor@smart-soft.co.uk)
Submit Questions To:- oracle-questions@smart-soft.co.uk
To subscribe just go to
www.asktheoracle.net/oracle-tips-signup.html
-----------------------------------------------
Introduction
Answers to Your Most Pressing Oracle Questions
Further Reading/Other resources
In this issue:
The resources section has recommendations for further reading to enhance your knowledge of Oracle and contains links to information on OTN and other places. For more Oracle tips and tricks go here.
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 it is exactly that you want. 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 whatever is needed> 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 is determined 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, but do you ? What
you would actually do is process the 3rd record returned which, if there are
2 or more records with same highest salary, may not be quite what you want.
On the other hand, the pure SQL approach would ignore 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 determined 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. Do indexing the tables will speed up queries on this view? Regards, Eric Dessureault
The answer to this 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 should also consider making the tables index-organised 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.
---------
From: Subhasis Goswami - Can u pls. tell is there any ESCAPE charecter in Oracle 9i ?
It depends what you mean by escape character. If you mean that you want to use a wild card character as a literal in SQL queries with a LIKE condition then, yes, there is. It can be any character you like other than '%' or '_'.
For example in the following query:
SELECT name FROM dept WHERE name LIKE 'A%\_B%' ESCAPE '\';
The '_' character in this case would be treated as a literal
not a wildcard. Normally '_' is used to match any single character. This means
that names like "Aname_Bname"
or "Another_B" or
"Anything_BeginningwithAfollowedbyunderscoreB"
would be returned form the database i.e. any name starting with A and with
an underscore followed by B would match.
---------
From: Sumeet Srivastava - What is the impact if we use NCHAR in place of CHAR?
CHAR and NCHAR datatypes are both fixed length datatypes used for storing character data such as names and addresses. As they are fixed length any data will be blank-padded which means that if the string is smaller than the allocated variable size, then blanks (spaces) will be added to the end of the string to make it up to the full length. For example storing 'A' in a char(4) or nchar(4) datatype would actually result in 'A ' being stored ('A' followed by 3 spaces.)
The difference between NCHAR and CHAR datatypes is that NCHAR can only be used to store UNICODE data - character data for languages (for example Japanese) that have a multi-byte character set. Also NCHAR variables will store data in the (multi-byte) character set defined by the National Language character set, not the database character set. This also applies to NVARCHAR2 and NCLOB datatypes.
To answer the original question, the impact is that NCHAR will use twice
as much space as CHAR data if AL16UTF16
encoding is used and three times as much if UTF8
encoding is used.
---------
From: Farook Quisar - What are HINTS?
Hints are used with the cost-based optimiser and enable the developer to over-ride the default execution path chosen by the optimiser for SELECT (queries), UPDATE and DELETE statements although they are most commonly used to enhance the performance of queries.
Hints must be specified immediately after the verb in the SQL statement and take the form of comments, as follows:
SELECT /*+ hint */ ...
UPDATE /*+ hint */
DELETE /*+ hint */
The most common use of hints is to force the use of an index when, for what ever reason, the optimiser is ignoring an available index.
As this is such a big topic area, I'm going to continue this as a separate article which will be available in the next few days.
---------------------------------------
Looking for more Oracle tips and tricks ?
If you're looking for no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our ezine. Published monthly, it's jam-packed full of tips and tricks to help you make more of your Oracle systems and save you hours of blood, sweat and tears searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox.
E-mail us your questions and we'll answer them in the next newsletter. Send your questions to oracle-questions@smart-soft.co.uk
Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320
Contact Us
Click here to view our privacy policy .
This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.
© Copyright Smartsoft Computing Ltd 2001-2008. All rights reserved.