Index for a table - Factors to consider

• Creating an index Consider indexing keys that are used frequently in WHERE clauses.

• Consider indexing keys that are used frequently to join tables in SQL statements.

• Choose index keys that have high selectivity. The selectivity of an index is the percentage of rows in a table having the same value for the indexed key. An index's selectivity is optimal if few rows have the same value. Note: Oracle automatically creates indexes, or uses existing indexes, on the keys and expressions of unique and primary keys that you define with integrity constraints. Indexing low selectivity columns can be helpful if the data distribution is skewed so that one or two values occur much less often than other values.

• Do not use standard B-tree indexes on keys or expressions with few distinct values. Such keys or expressions usually have poor selectivity and therefore do not optimize performance unless the frequently selected key values appear less frequently than the other key values. You can use bitmap indexes effectively in such cases, unless the index is modified frequently, as in a high concurrency OLTP application.

• Do not index columns that are modified frequently. UPDATE statements that modify indexed columns and INSERT and DELETE statements that modify indexed tables take longer than if there were no index. Such SQL statements must modify data in indexes as well as data in tables. They also generate additional undo and redo.

• Do not index keys that appear only in WHERE clauses with functions or operators. A WHERE clause that uses a function, other than MIN or MAX, or an operator with an indexed key does not make available the access path that uses the index except with function-based indexes.

Truth about Java string pool and Intern

public static void main(String[] args)

{

String literalstr = "ABC";

String literalstr2 = "ABC";

String str = new String("ABC");

String str2 = new String("ABC");

if (literalstr == literalstr2)

{

System.out.println("Literal String... I use String Pooling");

}

if (str != str2)

{

System.out.println("Object String... I dont use String Pooling");

}

if (str.intern() == str2.intern())

{

System.out.println("Interning ... I use String Pooling");

}

// System.out.println(ric2);

}

Java string pool and intern

Two ways of creating string are

1) String s ="hello"; No. of string literal = 1 i.e. "hello" - No. of String objects on heap = 0

2) String s= new String("hello"); - No. of string literals =1 and No. of string objects =1

Java maintains a string pool of "LITERALS" and not of objects.

Advantage of string pooling: 1) Reduced memory usage*(PermGenSpace Issue) 2) Faster Comparision i.e == comparision 3) Faster lookup

Disadvantages: 1) Overhead maintaining pool

How to pool String objects? Use Intern() on a string to add it to the pool. Downside of interning: 1) You may forget to intern some strings and compare them by == leading to unexpected results.

*Interning large number of strings is a problem again. The internalized strings go to the Permanent Generation, which is an area of the JVM that is reserved for non-user objects, like Classes, Methods and other internal JVM objects. The size of this area is limited, and is usually much smaller than the heap. Calling intern() on a String has the effect of moving it out from the heap into the permanent generation, and you risk running out of PermGen space.