Implement Google Signer to implement Google Map Search

import javax.crypto.Mac;
import javax.crypto.spec.SecretKeySpec;
import org.springframework.stereotype.Service;
import com.test.gcrmws.core.context.GCRMWSContext;


public class GoogleUrlSigner {

public String getLicnsedGeoCoderURL(String address,String inputUrl, String inputKey, String clientId)
throws InvalidKeyException, NoSuchAlgorithmException,
URISyntaxException {
 String request = null;
 URL url = null;

try {


byte[] key = convertBase64toBinary(inputKey);
url = new URL(inputUrl);

request = signRequest(url.getPath(), url.getQuery(),key);

} catch (MalformedURLException e) {
} catch (UnsupportedEncodingException e) {
return request;

private byte[] convertBase64toBinary(String keyString) {

// Convert the key from 'web safe' base 64 to binary

keyString = keyString.replace('-', '+');
keyString = keyString.replace('_', '/');
byte[] key = Base64.decode(keyString);
return key;

private String signRequest(String path, String query,byte[] key)
throws NoSuchAlgorithmException, InvalidKeyException,

UnsupportedEncodingException, URISyntaxException {
// Retrieve the proper URL components to sign
String resource = path + '?' + query;
// Get an HMAC-SHA1 signing key from the raw key bytes
SecretKeySpec sha1Key = new SecretKeySpec(key, "HmacSHA1");
// Get an HMAC-SHA1 Mac instance and initialize it with the HMAC-SHA1
// key
Mac mac = Mac.getInstance("HmacSHA1");

// compute the binary signature for the request
byte[] sigBytes = mac.doFinal(resource.getBytes());
// base 64 encode the binary signature
// String signature = Base64.encodeBytes(sigBytes);
String signature = Base64.encodeToString(sigBytes, true);
// convert the signature to 'web safe' base 64
signature = signature.replace('+', '-');
signature = signature.replace('/', '_');
return signature;

Main class – How to call this above method:


public it.units.GoogleGeocoding.GeocodeResponse getLocation(
StringBuilder geocodingIP, GoogleUrlSigner googleUrlSigner,

@GWSSuppressLogging Map<String, String> grlSyncLogMap,
RestConnector googleMapRestConnector, String addr,String... addressElements) {

Map<String, String> queryParams = new LinkedHashMap<String, String>();

GeocodeResponse geoResponse = null;

   String signature = null;
   StringBuilder address = new StringBuilder();

for (String addrelem : addressElements) {
  if (address.length() > 0) {

try {

address.append(URLEncoder.encode(addrelem, "UTF-8").replace("+", "%20"));
} catch (UnsupportedEncodingException e) {


Map<Param, String> map = googleMapRestConnector.getConfig();
String endpoint = java.text.MessageFormat.format(
map.get(Param.ENDPOINT_URL), geocodingIP);
map.put(Param.ENDPOINT_URL, endpoint);

try {

signature = googleUrlSigner.getLicnsedGeoCoderURL(
address.toString(), endpoint,
} catch (InvalidKeyException e) {

} catch (NoSuchAlgorithmException e) {
} catch (URISyntaxException e) {

queryParams.put("sensor", "false");
queryParams.put("address", addr);
queryParams.put("client", grlSyncLogMap.get("google.geocoder.client"));
queryParams.put("signature", signature);

try {
geoResponse = (it.units.GoogleGeocoding.GeocodeResponse) googleMapRestConnector.invoke(HttpMethod.GET, null, null, queryParams);


* System.out.println("LocationType: "+

* geoResponse.getResult().getGeometry().getLocationType());

* System.out.println("FormattedAddress: "+

* geoResponse.getResult().getFormattedAddress());

* System.out.println("AddressTypes: "+

* geoResponse.getResult().getAddressTypes());

* System.out.println("Lat: "+

* geoResponse.getResult().getGeometry().getLocation().getLat());

* System.out.println("Lan: "+

* geoResponse.getResult().getGeometry().getLocation().getLng());


} catch (NullPointerException ne) {
} catch (RestException re) {
} catch (Exception e) {
return geoResponse;


Git vs SVN – Advantages of GIT

  • Integration– Git has very flexible integration with Stash(GIt UI), Bamboo (Build tool) , JIRA (Agile). We can associate feature (a separate branch of code for a given story) branch with any JIRA story ( part of a sprint) by creating branch from Stash. So that all code changes/commits can be easily audit/trackable. Bamboo create build and show status of build (pass or fail) to Stash/Git and JIRA board.
  • Cloning – GIT creates a separate mirror branch for a minor change.
  • Easy switching between different branches- feature, develop, release, master, tag with the same set of local code or same folder. No need to replicate like SVN. Code merge and roll back is also easy and quicker.
  • Forking – A new project can be created outside of your project space by external team. Other team can work in parallel and merge their code like XYZ team has their own developers and development process, however they can work on same set of code and later on merge and release using same original remote repository.
  • Easy Code Review– We can do peer review by using Git/Stash tool by creating pull request. There we have set rules like- at least 2 approvals,1 successful build etc.
  • Easy to manage various branches and code merge is easy by its own merge tool/console commands.
  • Light wight and faster than SVN.

How HashMap works in Java

Note: Original article –

How HashMap works in Java

How HashMap works in Java or sometime how get method work in HashMap is common interview questions now days. Almost everybody who worked in Java knows what hashMap is, where to use hashMap or difference between hashtable and HashMap then why this interview question becomes so special? Because of the breadth and depth this question offers. It has become very popular java interview question in almost any senior or mid-senior level java interviews.

Questions start with simple statement 

“Have you used HashMap before” or “What is HashMap? Why do we use it “

Almost everybody answers this with yes and then interviewee keep talking about common facts about hashMap like hashMap accpt null while hashtable doesn’t, HashMap is not synchronized, hashMap is fast and so on along with basics like its stores key and value pairs etc.

This shows that person has used hashMap and quite familiar with the functionality HashMap offers but interview takes a sharp turn from here and next set of follow up questions gets more detailed about fundamentals involved in hashmap. Interview here you and come back with questions like

“Do you Know how hashMap works in Java” or

“How does get () method of HashMap works in Java”

And then you get answers like I don’t bother its standard Java API, you better look code on java; I can find it out in Google at any time etc.

But some interviewee definitely answer this and will say “HashMap works on principle of hashing, we have put () and get () method for storing and retrieving data from hashMap. When we pass an object to put () method to store it on hashMap, hashMap implementation calls

hashcode() method hashMap key object and by applying that hashcode on its own hashing funtion it identifies a bucket location for storing value object , important part here is HashMap stores both key+value in bucket which is essential to understand the retrieving logic. if people fails to recognize this and say it only stores Value in the bucket they will fail to explain the retrieving logic of any object stored in HashMap . This answer is very much acceptable and does make sense that interviewee has fair bit of knowledge how hashing works and how HashMap works in Java.

But this is just start of story and going forward when depth increases a little bit and when you put interviewee on scenarios every java developers faced day by day basis. So next question would be more likely about collision detection and collision resolution in Java HashMap e.g 

“What will happen if two different objects have same hashcode?”

Now from here confusion starts some time interviewer will say that since Hashcode is equal objects are equal and HashMap will throw exception or not store it again etc. then you might want to remind them about equals and hashCode() contract that two unequal object in Java very much can have equal hashcode. Some will give up at this point and some will move ahead and say “Since hashcode () is same, bucket location would be same and collision occurs in hashMap, Since HashMap use a linked list to store in bucket, value object will be stored in next node of linked list.” great this answer make sense to me though there could be some other collision resolution methods available this is simplest and HashMap does follow this.

But story does not end here and final questions interviewer ask like 

“How will you retreive if two different objects have same hashcode?”

Interviewee will say we will call get() method and then HashMap uses keys hashcode to find out bucket location and retrieves object but then you need to remind him that there are two objects are stored in same bucket , so they will say about traversal in linked list until we find the value object , then you ask how do you identify value object because you don’t value object to compare ,So until they know that HashMap stores both Key and Value in linked list node they won’t be able to resolve this issue and will try and fail.

But those bunch of people who remember this key information will say that after finding bucket location , we will call keys.equals() method to identify correct node in linked list and return associated value object for that key in Java HashMap. Perfect this is the correct answer.

In many cases interviewee fails at this stage because they get confused between hashcode () and equals ()and keys and values object in hashMap which is pretty obvious because they are dealing with the hashcode () in all previous questions and equals () come in picture only in case of retrieving value object from HashMap.

Some good developer point out here that using immutable, final object with proper equals () and hashcode () implementation would act as perfect Java HashMap keys and improve performance of Java hashMap by reducing collision. Immutability also allows caching there hashcode of different keys which makes overall retrieval process very fast and suggest that String and various wrapper classes e.g Integer provided by Java Collection API are very good HashMap keys.

Now if you clear all this java hashmap interview question you will be surprised by this very interesting question “What happens On HashMap in Java if the size of the Hashmap exceeds a given threshold defined by load factor ?”. Until you know how hashmap works exactly you won’t be able to answer this question.
if the size of the map exceeds a given threshold defined by load-factor e.g. if load factor is .75 it will act to re-size the map once it filled 75%. Java Hashmap does that by creating another new bucket array of size twice of previous size of hashmap, and then start putting every old element into that new bucket array and this process is called rehashing because it also applies hash function to find new bucket location. 

If you manage to answer this question on hashmap in java you will be greeted by “do you see any problem with resizing of hashmap in Java” , you might not be able to pick the context and then he will try to give you hint about multiple thread accessing the java hashmap and potentially looking for race condition on HashMap in Java

So the answer is Yes there is potential race condition exists while resizing hashmap in Java, if two thread at the same time found that now Java Hashmap needs resizing and they both try to resizing. on the process of resizing of hashmap in Java , the element in bucket which is stored in linked list get reversed in order during there migration to new bucket because java hashmap doesn’t append the new element at tail instead it append new element at head to avoid tail traversing. if race condition happens then you will end up with an infinite loop. though this point you can potentially argue that what the hell makes you think to use HashMap in multi-threaded environment to interviewer 🙂

I like this question because of its depth and number of concept it touches indirectly, if you look at questions asked during interview this HashMap questions has verified

Concept of hashing

Collision resolution in HashMap

Use of equals () and hashCode () method and there importance?

Benefit of immutable object?

race condition on hashmap in Java

Resizing of Java HashMap

Just to summarize here are the answers which does makes sense for above questions

How HashMAp works in Java

HashMap works on principle of hashing, we have put () and get () method for storing and retrieving object form hashMap.When we pass an both key and value to put() method to store on HashMap, it uses key object hashcode() method to calculate hashcode and they by applying hashing on that hashcode it identifies bucket location for storing value object.

While retrieving it uses key object equals method to find out correct key value pair and return value object associated with that key. HashMap uses linked list in case of collision and object will be stored in next node of linked list.

Also hashMap stores both key+value tuple in every node of linked list.

What will happen if two different HashMap key objects have same hashcode?

They will be stored in same bucket but no next node of linked list. And keys equals () method will be used to identify correct key value pair in HashMap.

In terms of usage HashMap is very versatile and I have mostly used hashMap as cache in electronic trading application I have worked . Since finance domain used Java heavily and due to performance reason we need caching a lot HashMap comes as very handy there.

to check some article on hashMap see here 

 Use of ConcurrentHashMap

 SynchrnozedHashMap and ConcurrentHashMap

Difference between hashtable and hashMap

Read more:

Comparable and Comparator example



/* Source Code- Example */

/* ------------ Sorting thru Comparable Interface (Natural Sorting) ---------
 * Implements mandatory Comparable interface
 class Person implements Comparable<Object>{

String firstname;
 String lastname;
 public Person(String firstname, String lastname) {
 this.firstname = firstname;
 this.lastname = lastname;

public String getFirstname() {
 return firstname;
 public String getLastname() {
 return lastname;
 * return -1 : If this object is lesser than the passed object</pre>
<div>        return  0 : If this object is same the passed object
return  1 : If this object is greater than the person */</div>

public int compareTo(Object obj){
 Person person=(Person) obj;
 return this.firstname.compareTo(person.getFirstname());

public class <b>PersonComparable</b> {

public static void main(String[] args) {

List<Person> person=new ArrayList<Person>();

 person.add(new Person("Rajiv","Srivastava"));
 person.add(new Person("Akshay","Kumar"));
 person.add(new Person("Prashant","Gupta"));

/* Sorting- sort method will use compareTo(Object obj) override implementation for natural sorting


for (Person p:person){
 System.out.println(p.getFirstname()+" "+p.getLastname());
/* Result:

Akshay Kumar
Prashant Gupta
Rajiv Srivastava

/* ———— Sorting thru Comparator Interface (Custom Sorting)
* Implements mandatory Comparator interface


public class <b>MyCustomComparator</b> implements Comparator<Object> {

public int compare(Object obj1, Object obj2){
Empl p1=(Empl) obj1;
Empl p2=(Empl) obj2;

String p1name=p1.getFirstname()+" "+p1.getLastname();
String p2name=p2.getFirstname()+" "+p2.getLastname();

return p1name.compareTo(p2name);

public static void main(String[] args) {
List <Empl> plist= new ArrayList<Empl>();

plist.add(new Empl("Arvind","Upadhyay"));
plist.add(new Empl("Arvind","Tendulkar"));
plist.add(new Empl("Arvind","Kejriwal"));

Collections.sort(plist, new MyCustomComparator());

for(Empl p:plist){
System.out.println(p.firstname+ " "+p.getLastname());


class Empl{

String firstname;
String lastname;
public Empl(String firstname, String lastname) {
this.firstname = firstname;
this.lastname = lastname;

public String getFirstname() {
return firstname;
public String getLastname() {
return lastname;

/* Output:
* Arvind Kejriwal
Arvind Tendulkar
Arvind Tripathi */

How to work with Java 6′s NavigableSet and NavigableMap

How to work with Java 6′s NavigableSet and NavigableMap:

This is my first published article on  popular site –


Also, writing same article on my blog. Hope it will be helpful for you- Rajiv.

You can use latest Java 6′s Collection API to navigate a set and Map collections. These API gives a lot of flexibility to find out required result from the collection.

1. NavigableMap Example

import java.util.NavigableMap;
import java.util.TreeMap;
public class NavigableMapDemo {
 public static void main(String[] args) {
 NavigableMap<String,Integer> navigableMap=new TreeMap<String, Integer>();
 navigableMap.put("X", 500);
 navigableMap.put("B", 600);
 navigableMap.put("A", 700);
 navigableMap.put("T", 800);
 navigableMap.put("Y", 900);
 navigableMap.put("Z", 200);
 System.out.printf("Descending Set  : %s%n",navigableMap.descendingKeySet());
 System.out.printf("Floor Entry  : %s%n",navigableMap.floorEntry("L"));
 System.out.printf("First Entry  : %s%n",navigableMap.firstEntry());
 System.out.printf("Last Key : %s%n",navigableMap.lastKey());
 System.out.printf("First Key : %s%n",navigableMap.firstKey());
 System.out.printf("Original Map : %s%n",navigableMap);
 System.out.printf("Reverse Map : %s%n",navigableMap.descendingMap());


Descending Set  : [Z, Y, X, T, B, A]
Floor Entry  : B=600
First Entry  : A=700
Last Key : Z
First Key : A
Original Map : {A=700, B=600, T=800, X=500, Y=900, Z=200}
Reverse Map : {Z=200, Y=900, X=500, T=800, B=600, A=700}

2. NavigableSet Example

import java.util.Arrays;
import java.util.Iterator;
import java.util.NavigableSet;
import java.util.TreeSet;
public class NavigableSetDemo {
  public static void main(String[] args) {
 NavigableSet<String> navigableSet = new TreeSet<String>(Arrays.asList(
   "X", "B", "A", "Z", "T"));
 Iterator<String> iterator = navigableSet.descendingIterator();
 System.out.println("Original Set :");
 while (iterator.hasNext()) {
 iterator = navigableSet.iterator();
 System.out.println("Sorted Navigable Set :");
 while (iterator.hasNext()) {
 System.out.printf("Head Set : %s.%n", navigableSet.headSet("X"));
 System.out.printf("Tail Set : %s.%n", navigableSet.tailSet("T", false));
 System.out.printf("Sub Set : %s.%n",
   navigableSet.subSet("B", true, "X", true));
 System.out.printf("Last Element : %s%n", navigableSet.last());
 System.out.printf("First Element : %s%n", navigableSet.first());
 System.out.printf("Reverse Set : %s%n", navigableSet.descendingSet());
 System.out.printf("Original Set : %s%n", navigableSet);


Original Set :
Sorted Navigable Set :
Head Set : [A, B, T].
Tail Set : [X, Z].
Sub Set : [B, T, X].
Last Element : Z
First Element : A
Reverse Set : [Z, X, T, B, A]
Original Set : [A, B, T, X, Z]

Latest Cloud based technology for Java Development- CloudBees

Today, I wanna share about new Cloud based technology for Java Development. The CloudBees PaaS provides middleware on top of IaaS.


The Java Cloud Revolution is here – and it’s being led by CloudBees! CloudBees is the only Platform as a Service (PaaS) company focused on moving the entire Java application lifecycle to the cloud. Developers are freed from the drudgery of maintaining infrastructure and are able to focus on developing great software.
The CloudBees Java in the Cloud PaaS includes DEV@cloud, a service that lets developers code, build and test applications in the cloud, and RUN@cloud, which lets development teams seamlessly deploy applications to production in the cloud and maintain them. With CloudBees platform, the need for IT support is eliminated and an organization transitions to a pay-for-what-you-use, when-you-use-it approach.

How it works:

Difference between mvc1 and mvc2

n MVC-1 architecture (also referred as Model 1), the request is first handled by a JSP, that interacts with a Bean. Here the JSP page may have partial processing logic, although a bulk of processing logic may be handled by the beans that may interact with the database. The JSP in this case in addition to being responsible for ‘View’ of MVC also takes the resposibility as ‘Controller’, and the beans acting as Model. For small applications that do not have complex processing, this model may be fine, but in case of bigger applications where a lot of processing and decision making is required (Authentication, Logging, Conditional redirection, database interactions, network connections) this is not the best option.

In such cases MVC2 or Model 2 architecture is the better option. It has a Controller Servlet that handles all the incoming requests (You may refer to the Front Controller pattern) and acts as the ‘Controller’, it determines what comes next, a View (JSP) or further processing by Model (Beans doing all the complex tasks), and will decide the view to display the results from the Model.
The links on the JSP pages for next view may also pass through the controller servlet to determine the next view, unlike in MVC-1 where the links on a JSP page will directly point to another JSP page.

Comparable Interface : Problem while adding object to TreeSet

Nice Article:

Comparable Interface : Problem while adding object to TreeSet

<div id="post-body-2478627897226170008" class="post-body entry-content">
<div dir="ltr">public class X{</div>
<div dir="ltr"></div>
<div dir="ltr">public static void main(String [] arg){
Set s=new TreeSet();
s.add(new Person(20));
s.add(new Person(10));
<div dir="ltr"></div>
<div dir="ltr">class Person{
int i;
Person(int i){
i = this.i;
<div dir="ltr">
Can anyone tell me why this code fragment shows me the “ClassCastException”, Using Java 2 1.5 compiler.
In the above code
Set s=new TreeSet();
Set S which is a TreeSet accepts an object that is very true when developer adds an object new Person(20) to code, code will not fail. Now when developer will add another object new Person(10) the code will fail.
Now replace the code with a fresh code
Set s=new TreeSet();
s.add(new Integer(10)); // replace person object with Integer object
s.add(new Integer(20)); // replace person object with Integer object
<div dir="ltr">

The above code will not fail.
Now lets dig in to the concept.
TreeSet is a class belongs to Set interface, being a set it does not accept duplicates. First object insertion goes fine here,Now when we are trying to add the second object then there must be a criteria based on which the 2 object should be comparable. And in the above code there is no such criteria, for uniqueness the set internally use compareable interface to check object equality which is not implemented by the Person class,So in this case developer have to implement the comparable interface in his Person Class.
There are fourteen classes in the Java 2 SDK, version 1.2, that implements the Comparable interface.
BigDecimal, BigInteger, Byte, Double, Float, Integer, Long, Short, Character, CollationKey, Date, File, ObjectStreamField, String
So when developer will try t add the above objects they won’t give any problem, as developer is facing in above scenario.
Below code for person class will solve the developer’s problem.

public class Person implements Comparable{
int i;
Person(int i){
i = this.i;
}public int compareTo(Object o1) {
if (this.i == ((Person) o1).i)
return 0;
else if ((this.i) > ((Person) o1).i)
return 1;
return -1;

Why ConcurrentHashMap is better than Hashtable and HashMap ???

ConcurrentHashMap is a pretty ignored class. The class offers a very robust and fast (comparatively, we all know java concurrency isn’t the fastest) method of synchronizing a Map collection.
There is no way you can compare the two, one offers synchronized methods to access a map while the other offers no synchronization whatsoever. What most of us fail to notice is that while our applications, web applications especially, work fine during the development & testing phase, they usually go tits up under heavy (or even moderately heavy) load in PRODUCTION. This is due to the fact that we expect our HashMap’s to behave a certain way but under load they usually misbehave.
Hashtable’s offer concurrent access to their entries, with a small caveat, the entire map is locked to perform any sort of operation. While this overhead is ignorable in a web application under normal load, under heavy load it can lead to delayed response times and overtaxing of your server for no good reason.
This is where ConcurrentHashMap’s step in. They offer all the features of Hashtable with a performance almost as good as a HashMap. ConcurrentHashMap’s accomplish this by a very simple mechanism. Instead of a map wide lock, the collection maintains a list of 16 locks by default, each of which is used to guard (or lock on) a single bucket of the map. This effectively means that 16 threads can modify the collection at a single time (as long as they’re all working on different buckets). Infact there is no operation performed by this collection that locks the entire map. The concurrency level of the collection, the number of threads that can modify it at the same time without blocking, can be increased. However a higher number means more overhead of maintaining this list of locks.

Retrieval operations on a ConcurrentHashMap do not block unless the entry is not found in the bucket or if the value of the entry is null. In such a case the map synchronizes on the bucket and then tries to look for the entry again just in case the entry was put or removed right after the get in synchronized mode.
Removal operations do require a bit of overhead. All removal operations require the chain of elements before and after to be cloned and joined without the removed element. Since the value of the map key is volatile (not really, the value of the inner Entry class is volatile) if a thread already traversing the bucket from which a value is removed reaches the removed element, it automatically sees a null value and knows to ignore such a value.
Traversal in a ConcurrentHashMap does not synchronize on the entire map either. Infact traversal does not synchronize at all except under one condition. The internal LinkedList implementation is aware of the changes to the underlying collection. If it detects any such changes during traversal it synchronizes itself on the bucket it is traversing and then tries to re-read the values. This always insures that while the values recieved are always fresh, there is minimalistic locking if any.
Iteration over a ConcurrentHashMap are a little different from those offered by other collections. The iterators are not fail-fast in the sense that they do not throw a ConcurrentModificationException. They also do not guarantee that once the iterator is created it will list/show all elements that are added after its creation. The iterators do however guarantee that any updates or removal of items will be reflected correctly in their behaviour. They also guarantee that no element will be returned more than once while traversal.
In conclusion, give it a try, replace some Hashtable’s in your application with ConcurrentHashMap and see how they perform under load. The two are interchangeable so it shouldn’t be hard to update your app.

Optimizing SQL Queries

 I have found a nice article on Oracle query optimzation:
  D.1 Optimizing Single-Table Queries
To improve the performance of a query that selects rows of a table based on a specific column value, create an index on that column. For example, the following query performs better if the NAME column of the EMP table has an index.

WHERE NAME = 'Smith'; 

D.2 Optimizing Join Queries

The following can improve the performance of a join query (a query with more than one table reference in the FROM clause).

D.2.1 Create an Index on the Join Column(s) of the Inner Table

In the following example, the inner table of the join query is DEPT and the join column of DEPT is DEPT#. An index on DEPT.DEPT# improves the performance of the query. In this example, since DEPT# is the primary key of DEPT, an index is implicitly created for it. The optimizer will detect the presence of the index and decide to use DEPT as the inner table. In case there is also an index on EMP.WORKS_IN column the optimizer evaluates the cost of both orders of execution;DEPT followed by EMP (where EMP is the inner table) and EMP followed by DEPT (where DEPT is the inner table) and picks the least expensive execution plan.

AND e.JOB_TITLE = 'Manager';

D.2.2 Bypassing the Query Optimizer

Normally optimizer picks the best execution plan, an optimal order of tables to be joined. In case the optimizer is not producing a good execution plan you can control the order of execution using the HINTS feature SQL. For more information see the Oracle9i Lite SQL Reference.
For example, if you want to select the name of each department along with the name of its manager, you can write the query in one of two ways. In the first example which follows, the hint /++ordered++/ says to do the join in the order the tables appear in the FROM clause with attempting to optimize the join order.

SELECT /++ordered++/ d.NAME, e.NAME


SELECT /++ordered++/ d.NAME, e.NAME

Suppose that there are 10 departments and 1000 employees, and that the inner table in each query has an index on the join column. In the first query, the first table produces 10 qualifying rows (in this case, the whole table). In the second query, the first table produces 1000 qualifying rows. The first query will access the EMP table 10 times and scan the DEPT table once. The second query will scan the EMP table once but will access the DEPT table 1000 times. Therefore the first query will perform much better. As a rule of thumb, tables should be arranged from smallest effective number rows to largest effective number of rows. The effective row size of a table in a query is obtained by applying the logical conditions that are resolved entirely on that table.

In another example, consider a query to retrieve the social security numbers and names of employees in a given location, such as New York. According to the sample schema, the query would have three table references in the FROM clause. The three tables could be ordered in six different ways. Although the result is the same regardless of which order you choose, the performance could be quite different.

Suppose the effective row size of the LOCATION table is small, for example select count(*) from LOCATION where LOC_NAME = 'New York' is a small set. Based on the above rules, the LOCATION table should be the first table in the FROM clause. There should be an index on LOCATION.LOC_NAME. Since LOCATION must be joined with DEPT, DEPT should be the second table and there should be an index on the LOC column of DEPT. Similarly, the third table should be EMP and there should be an index on EMP#. You could write this query as:

SELECT /++ordered++/ e.SS#, e.NAME
l.LOC# = d.LOC AND

D.3 Optimizing with Order By and Group By Clauses

Various performance improvements have been made so that SELECT statements run faster and consume less memory cache. Group by and Order by clauses attempt to avoid sorting if a suitable index is available.

D.3.1 IN subquery conversion

Converts IN subquery to a join when the select list in the subquery is uniquely indexed.
For example, the following IN subquery statement is converted to its corresponding join statement. This assumes that c1 is the primary key of table t2:

c2 IN (SELECT c1 FROM t2);


SELECT c2 FROM t1, t2 WHERE t1.c2 = t2.c1;

D.3.2 ORDER BY optimization with no GROUP BY

This eliminates the sorting step for an ORDER BY clause in a select statement if ALL of the following conditions are met:

  1. All ORDER BY columns are in ascending order or in descending order.
  2. Only columns appear in the ORDER BY clause. That is, no expressions are used in the ORDER BY clause.
  3. ORDER BY columns are a prefix of some base table index.
  4. The cost of accessing by the index is less than sorting the result set.

D.3.3 GROUP BY optimization with no ORDER BY

This eliminates the sorting step for the grouping operation if GROUP BY columns are the prefix of some base table index.

D.3.4 ORDER BY optimization with GROUP BY

When ORDER BY columns are the prefix of GROUP BY columns, and all columns are sorted in either ascending or in descending order, the sorting step for the query result is eliminated. If GROUP BY columns are the prefix of a base table index, the sorting step in the grouping operation is also eliminated.

D.3.5 Cache subquery results

If the optimizer determines that the number of rows returned by a subquery is small and the query is non-correlated, then the query result will be cached in memory for better performance. Currently the number of rows is set at 2000. For example:

select * from t1 where
t1.c1 = (select sum(salary)
from t2 where t2.deptno = 100);