• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Regarding performance in Oracle DB

 
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic