File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Regarding performance in Oracle DB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Regarding performance in Oracle DB" Watch "Regarding performance in Oracle DB" New topic

Regarding performance in Oracle DB

saran sadaiyappan
Ranch Hand

Joined: Dec 23, 2004
Posts: 39
Hi all,
I am writing a query to fetch records based on some condition. Instead of putting a where clause can I put a concat operation for the condition and join them and execute as a single condition ?
Which is the more efficient one ?

Instead of using

select * from table name where column1 ='21'
and column2 = 'TEST'

can I use

select * from table name where concat(concat(column1, ' '),column2)='21 TEST')

I would also like to know how the DB will execute this query internally.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1082

I may not be able to explain you how query will be parsed an execute in Oracle Database, but I can tell you this query will certainly reduce performance.

First you need you identify do you really need this ? and what would you achieve additionally if you do not write a query like

This query will compare all records in table against Column1 and Column2 however you query will cause on additional processing of concating COlumn1 and a Space(' ') then again concating Column2 to it. Then a comparison will happen on the result of concatation.

Top of all if you want to increase performance of a query then you can get it by creating index on table, In your case if you use a query like I have suggested then a composite index on column1 & column2 will increase your performance exponentially.

And if you use a query using concatation then a index or composite index will not give you performance. SO you need to identify if you really need concatation.

And If still answer is yes then go for function based index.

Hope this helps.


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

I would also like to know how the DB will execute this query internally.

...then you need to learn about the EXPLAIN PLAN statement. This is a statement used to get Oracle to output what it is doing to execute a query. Read more about it in the documentation.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
I agree. Here's the link:
subject: Regarding performance in Oracle DB
It's not a secret anymore!