Search code examples
sqlspringoraclemybatisspring-mybatis

Fastest way to find existence of key in Oracle database

I'm using a mybatis query in my Spring web service to validate input from the user insofar as checking that a given key exists in the Oracle database before proceeding to do any additional work. The intent is that this validation runs quickly and is reused in several web methods for different tables/columns.

I pass the query a HashMap containing an identifier for the table (tableType), column (selectColumnType) and the key (id) I want to check for. My design was for the query to return only a 0 or 1, representing whether or not the key exists.

<select id="checkExistence" parameterType="java.util.HashMap"
    resultType="int">
    SELECT COUNT (*) FROM ( SELECT CAST(${ selectColumnType } AS 
    VARCHAR2(1000)) FROM ${ tableType } WHERE REGEXP_LIKE (${
    selectColumnType }, #{ id }) FETCH FIRST 1 ROW ONLY)
</select>

In a few instances unfortunately, the performance of this query isn't tolerable ex. from a table with approximately 27 million rows it takes around 20 seconds to run this query in Toad and upwards of 45 seconds in the context of the web service. The keys are not expected to be unique so I thought either using FETCH FIRST 1 ROW ONLY or ROWNUM = 1 would be the way to go but the performance still isn't there.

I'm hoping there's a more efficient way either using Oracle functionality or mybatis to only retrieve the first match and return.

Solution

  • I know nothing about any of tags you set, except a little bit of Oracle; therefore, sorry if it won't help much.

    Did you create index on the column you're referencing in the WHERE clause? If not, do it.

    Apart from that, why do you use regular expression here? Although it looks smart and enables you to do wonderful things, it might be dead slow on a table with 27 million rows. If possible, convert it to INSTR. For example:

    No : select * from some_table where regexp_like(some_column, id)
Yes: select * From some_table where instr(some_column, id) > 0

    Furthermore, if you restrict the result with ROWNUM, there might be some improvement ("1", as the result, means "yes, I found something"):

    select 1 from some_table where instr(some_column, id) > 0 and rownum = 1

    Sorry, but I don't know how to rewrite that code using syntax you do as I'm not familiar with it, but I hope you'll manage.