Regarding performance in Oracle DB

[saran sadaiyappan]

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]

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

SELECT  * FROM TABLE NAME WHERE COLUMN1='21' AND COLUMN2='TEST'

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]

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.