Hi,
I have a question related to random number generation performance.
We have a table in production on Oracle, with several millions of records(more than 100 Million).
We have many EJB APIs deployed in weblogic which access this table. Things are fine from the performance point of view except one API, which has an internal logic of generating random number. The logic is as below:
There is one crucial colums in this table called resource_value. Basically all APIs retrives this resource_value column based on several conditions.
But for the API which needs to select this value randomly, we have a performance problem. The cuurent logic we are using is as below.
1) Generate a 5 digit random number using Java rand() in the EJB, and then fires a query :
select resource_value from table where resource_value like '%random number' and other where clause;
if not found in above query we generate a 4 digit random number using Java rand() in the EJB, and then fires same query. We continue like this till 1 digit random number.
We found this is not a good approach from performance point of view, since it uses %value in where clause and also because we have to do muliple query.
Any ideas for a better performance logic?? Something can be done directly in Oracle???
Ideas are much appreciated
