Ask Anantha

Rule #1 The design of a database must only be thought after the through analysis of the user requirements. Rule #2 Never implement any functionalities of Oracle if it is not required. Always adhere Rule #1. Rule #3 Never implement as per theory, but base your design on practical thought. Always adhere Rule #2. What does this mean? I will elaborate each and every point mentioned above. Also note that all the rules are related to one another. This ensures that the design is always as per user requirements. Rule #1 The first rule says that never start or think of database design before understanding what exactly the user requires. This is the major and very important rule which must be adhered, whatever comes. This rule thus verifies the importance of analysis of user requirements. Unless the requirements of user is fully understood, it will be nearly impossible to design the database. Rule #2 This rule again double verifies the rule #1. The design must be using only using such oracle fun...

The LIKE-clause can ignore indexes, causing queries to run forever while doing full table scans. This document describes how to tune such SQL statements by making use of Oracle Text or reverse key indexes . Tuning the ‘LIKE’ Clause: Generally, search arguments in the WHERE clause such as "IS NULL", "<>", " != ", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'" prevents Oracle from using an index to perform the search (however, not always). If you use LIKE in your WHERE clause, try to specify one or more leading characters if at all possible. For example, use LIKE 'm%' and not LIKE '%m'. If you specify a leading character, Oracle has a better chance of being able to use an index to perform the query - this will increase performance and reduce the load on the database server. To avoid such full table scans, consider the follo...

Author: Kathy Gleeson In this article I will discuss some SQL tuning guidelines that are simple but effective. SQL is the heart of the Oracle system. You can use many different SQL statements to attain the same result. It is often the case that only one statement will be the most efficient option in a given state of affairs. The guidelines below include information about whether one form of the statement is always more efficient or whether each statement is an alternative and the efficiency will differ depending on your application. >>Oracle processing of SQL: Oracle processes SQL in two steps: Parsing Execution. Tuning may speed up your SQL by reducing either parsing or execution or both. Tuning SQL should only be done after your code is working correctly. Beware that there is an inevitable tug-of-war between writing efficient SQL and understandable SQL. >>Oracle SQL Tuning - Identical SQL: SQL cannot be shared within Oracle unless it is absolutely ide...

Indexes, like tables, may be partitioned. There are two possible methods to partition indexes. You may either:   Equi-partition the index with the table - Also known as a local index . For every table partition, there will be an index partition that indexes just that table partition. All of the entries in a given index partition point to a single table partition and all of the rows in a single table partition are represented in a single index partition.   Partition the index by range - Also known as a global index . Here the index is partitioned by range, and a single index partition may point to any (and all) table partitions.   In the locally partitioned index, the index entries in a given partition, point into exactly one table partition. The globally partitioned index diagram however, shows that the index entries in a global index may point into any or all of the table partitions.   Also, note that the number of index partitions may in fact be dif...

The index skip scan is a new execution plan in Oracle9i whereby an Oracle query can bypass the leading-edge of a concatenated index and access the inside keys of a multi-values index. For example, consider the following concatenated index: create index sex_emp_id on emp (sex, emp_id ); Prior to Oracle9i, this index could only be used with both sex and emp_id were present in the SQL query, or when the sex column was specified.  The following query would not be able to use the concatenated index: select emp_id from emp where emp_id = 123; The Oracle9i skip scan execution plan allows for the concatenated index to be used , even though sex is not specified in the SQL query.  This feature promises that there is no need to provide a second index on the emp_id column.  Oracle acknowledges that the index skip scan is not as fast as a direct index lookup , but states that the index skip scan is faster than a full-table scan (otherwise why would oracle introd...

If you've been developing applications on Oracle for a while, you've no doubt come across the concept of « Bind Variables » . Bind variables are one of those Oracle concepts that is key to application performance. To understand bind variables, consider an application that generates thousands of SELECT statements against a table; for example: SELECT fname, lname, pcode FROM cust WHERE id = 674; SELECT fname, lname, pcode FROM cust WHERE id = 234; SELECT fname, lname, pcode FROM cust WHERE id = 332; Each time the query is submitted, Oracle first checks in the shared pool to see whether this statement has been submitted before. If it has, the execution plan that this statement previously used is retrieved, and the SQL is executed. If the statement cannot be found in the shared pool, Oracle has to go through th...