Hi,
I am on Oracle 11gR2. I have a table where I have created a text index.
I have written a query as below:
SELECT *
FROM my_transactions t
WHERE 1=1
AND CONTAINS (t.search_transactions_flag, '%'||:str||'%') > 0;
It prompts me for the bind variable 'str'. When I enter it as 'a', it returns me data, which is fine.
But when I pass it as NULL, it returns no data, whereas, here, I want it to return whole data set. Can anyone advice how to do it?
I tried rewriting query as below, but it does full table scan and does not use index. This is leading to poor performance:
SELECT *
FROM my_transactions t
WHERE 1=1
AND (:str IS NULL OR CONTAINS (t.search_transactions_flag, '%'||:str||'%') > 0);
Appreciate any help here!
Thanks.