Oracle Training To Help Make Your

Systems Better, Faster and Smarter

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.

Contents

• 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 used.                                                             --------- 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 follows: 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 linked to individuals. View woopra privacy policy.     View Smartsoft privacy policy Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.