Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Regarding performance in Oracle DB

 
saran sadaiyappan
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ?

E.g.
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Shailesh
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic