Oracle Consulting and Training
For Better, Faster, Smarter
Oracle Training To Help Make Your Systems Better, Faster
For more valuable Oracle tips and tricks just subscribe to our ezine and every month we'll send you some
tips and tricks to help you make your Oracle systems better, faster and smarter.
What is the impact of using NCHAR in place of CHAR?
What are HINTS?
Is there any ESCAPE character in Oracle?
From: Sumeet Srivastava - What is the impact if we use NCHAR in place of CHAR?
The Oracle SQL CHAR and NCHAR data types 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 in your Oracle
database as CHAR data if AL16UTF16 encoding is used and three times as much if UTF8 encoding is
From: Farook Quisar - What are HINTS?
Hints are used with the Oracle 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
SELECT /*+ hint */ ...
UPDATE /*+ hint */
DELETE /*+ hint */
Common uses of hints are to force the use of an index when, for what ever reason, the optimiser is ignoring
an available index or to instruct it not to use an index when a full table scan would be more efficient,
This is too big a topic to do justice too here so we’ll going to continue this as a separate article. In the
meantime for the official definition see the Oracle Resources page for links to the documentation.
From: Subhasis Goswami - Can u pls. tell is there any ESCAPE character in Oracle?
It depends what you mean by escape character. If you mean that you want to use a wild card character as a
literal in Oracle SQL queries with a LIKE condition then, yes, there is. It can be any character 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.
for more help, see the many free articles and tutorials we have on our site or consider taking some of our
formal Oracle training courses.
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
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