• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

Using Database Indices Effectively in JAVA

Hi Experts,

My application is built in Java and the back-end is Oracle Rally installed in a Thin Server. To maximize the performance, the Database is being indexed now.

I got little information from the DBA. He says it uses Sorted indices for range retrieval and hashed for direct look-up. He has also provided me the following information about the indices.

INSURED_INFO_0400_SORTED        with column WIP_NUMBER
                                and column LAST_NAME
                                and column MIDDLE_NAME
                                and column FIRST_NAME
                                and column SSN
                                and column BIRTH_DATE
 

INSURED_INFO_HASH               with column WIP_NUMBER
        in INSURED_INFO_AREA
*********This index is a placement index clustered with the data*************

INSURED_INFO_HASH_SSN           with column SSN
                                and column WIP_NUMBER
                                and column INSURED_SEQUENCE_CODE

 
INSURED_INFO_SORTED_001         with column LAST_NAME
                                and column FIRST_NAME
                                and column WIP_NUMBER


I would like to know, how effectively and efficiently we could make use of the indices in the Java application.

Please advice.

Thanks & Regards,
Madhu Manikandan, R
0
CIPL-Senthil
Asked:
CIPL-Senthil
  • 2
1 Solution
 
Tomas Helgi JohannssonCommented:
     Hi!

If you want Oracle to use the indexes you create a sql query
where the columns of the indexes are used as the matching columns
in where clause of the sql query.

If Oracle ( or any other database) cannot match the sql where clause columns
to a index column it will use table scan instead of index scan.

Regards,
  Tomas Helgi
0
 
CIPL-SenthilAuthor Commented:
Our backend is ORACLE RALLY [OLD VERSION]. I would like to know whether the indices created in oracle rally could be used effectively in JAVA. If so, could you please provide an example?
0
 
Tomas Helgi JohannssonCommented:
     Hi!

It should not matter what version of Oracle or what database it is as long as the indexes match
the where clause columns as I said before.

Regards.
   Tomas Helgi
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now