Indexing in Oracle

Ever wondered what Indexing actually does :)

An index is a performance tuning mechanism that allows faster retreival of records.

Searching an indexed table is always faster than a normal table since indexing keeps the records sorted (B-Tree).

In normal search oracle has to scan the entire table, but in an indexed table there is no need to scan the entire table since the records are already sorted.

Index on Single column ->

CREATE INDEX PIndex ON TableName (ColumnName)

Index on multiple columns->

CREATE INDEX PIndex ON TableName (ColumnOne, ColumnTwo)

Following query fetches only the user defined Indexes->

SELECT

i.INDEX_NAME, i.TABLE_NAME, ic.COLUMN_NAME, ic.COLUMN_POSITION, UNIQUENESS

FROM

USER_INDEXES i JOIN USER_IND_COLUMNS ic ON i.INDEX_NAME = ic.INDEX_NAME

WHERE

i.TABLE_NAME IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='CITI_AMWS_CLUSTER_162');

Above you need to enter OWNER = schemaName.

To fetch all available Indexes use this ->

SELECT * FROM ALL_INDEXES;

Following DDL statement can be used to rebuild in Indexes.

ALTER INDEX index_name REBUILD;


Drop Index ->

DROP INDEX index_name;

Algorithms & Data Structures - Some Definitions

Variables - Placeholders for holding data.

Data Types - Set of data with predefined values.

Data Structure - A special format for organizing and storing data.

Algotithm - Step by Step instructions to solve a given problem.

Recursion - Any function which calls itself is called recursive. It is important to ensure that the recursion terminates. Each time the function should call itself with a slightly simpler version of the original problem. 

Hibernate load() and get() methods

Hibernate load() and get() methods->

1) Both are from session interface and we call them as session.get() and session.load().

2) When we call session.load() method, it will always return a proxy object - hibernate prepares
     a fake object without hitting the database.
              It will hit the database only when we try to retrieve the properties of the object. If that   
     object is not found it will throw a ObjectNotFoundException.

3) When we call session.get() method, it will hit the database immediately and returns the original
     object. If the row is not available in the database, it returns null.

Eager and Lazy Loading in Hibernate

Eager Loading - Means loading an object with its entire dataset in one go. This creates a performance hit since everything gets loaded as soon as we make the object, even if we dont want to use it.

Lazy Loading - Create the object but don't load the dataset. Load them only when they are required. Basically here we make a proxy object and operate on it.
We load this proxy object with the necessary data from dataset only when they are requested.

Use lazy loading when we are not sure what data we will need at runtime and eager loading when we know what data we want at runtime everytime we load the object.

To save memory, Lazy loading is generally used for one to many and many to many relationships. For one to one, generally Eager is used.


Memorize this :)
OneToMany:    LAZY
ManyToOne:    EAGER
ManyToMany: LAZY
OneToOne:       EAGER
Columns :          EAGER


Example->
public class Organization 
{
 private String employeeID;
 private String name;
 private String address;
 private List<Employees> employees;

 // setters and getters
}

Now when you load a Organization from the database, Hibernate loads its employeeID, name, and address fields for you. But you have two options for employees; to load it together with the rest of the fields (Eager Fetch) or to load it on-demand (Lazy Fetch) when you call the Organization's getListOfEmployees() method.

@ElementCollection(fetch=FetchType.LAZY)   OR @ElementCollection(fetch=FetchType.EAGER) // use either one of the two
@JoinTable(name="EMP_DTLS",joinColumns=@JoinColumn(name="EMP_ID"))

private Collection<Employees> listOfEmployees = new ArrayList<Employees>();
    
public Collection<Employees> getListOfEmployees() {
        return listOfEmployees;
}

Second Level Cache in Hibernate

Caching is a mechanism for storing the loaded objects into cache memory. The advantage of this is, whenever we want to load the same object from the database, instead of hitting the database once again, it loads from the local cache memory, so that the no. of round trips between an application and the database gets reduced.

Caching mechanism increases the performance of the application.

In hibernate we have two levels of caching
First Level Cache [Session Cache]
Second Level Cache [Session Factory Cache or JVM Level Cache]

Whenever we are load any object from the database, hibernate verifies whether that object is available in the local cache memory of that particular session [first level cache], if not, then hibernate verifies whether the object is available in global cache or factory cache [second level cache], if not, then hibernate hits the database and loads the object.

It first stores in the local cache of the session [first level] and then in the global cache [second level cache]

When another session needs to load the same object from the database, then hibernate copies that object from global cache [second level cache] into the local cache of this new session.

Second level cache is from 4 vendors
EHCache Cache from hibernate framework
OSCache from Open Symphony
SwarmCache
TreeCache from JBoss


Steps to enable second level cache in hibernate ->

1) Add provider class in hibernate configuration file.

<property name="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider </property>
<property name="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</property>


2) Add the following in hibernate mapping file.

<cache usage="read-only" />

3) Create ehcache.xml and store in at class path location [place where you have mapping and configuration xml’s] in web application.