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.
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