Most efficient way to query for existing UUIDs within large tables in Oracle

Oracle 19 is used at the moment as DBMS. The problem is that there is a large table (ca. 4,700,000) entries that must be queried for existing records. So a query is quite expensive; the queried column already has an index.

The UUID is needed at least as returnValue, as I have to compare which one are existing and which one have to be created.

How to get the most performance out of this. Currently this is the statement used to accomplish the task:

select uuid from LT where uuid in(:uuidList)")

One point that is already on my mind is to reduce the amount of queries as to increase the number of UUIDs within the list as far as possible. But then this might be a problem if the list for the in operator becomes too large? What is a good size the DBMS can deal with?

Maybe there a better way to design the query than using in, e.g. a subselect?

Edit: Forget to mention that the UUID is not the primary key of the table, there is a numerical primary key for the entity within the database the uuid is used by multiple databases and there for matching same objects that may have different ids within different DBMS.

Solution

The UUID is needed at least as returnValue, as I have to compare which one are existing and which one have to be created.

Don't do multiple round-trips to the database by trying to find the non-existing rows and then performing a DML statement to insert. If you are going to create new rows from the non-existing data then MERGE the two data sets.

MERGE INTO your_table dst
USING (
  SELECT HEXTORAW('00000000000000000000000000000000') AS uuid,
         'AAA' AS column1,
         1 AS column2
  FROM DUAL UNION ALL
  SELECT HEXTORAW('00000000000000000000000000000001') AS uuid,
         'BBB' AS column1,
         2 AS column2
  FROM DUAL UNION ALL
  SELECT HEXTORAW('00000000000000000000000000000002') AS uuid,
         'CCC' AS column1,
         3 AS column2
  FROM DUAL UNION ALL
  SELECT HEXTORAW('00000000000000000000000000000003') AS uuid,
         'DDD' AS column1,
         4 AS column2
  FROM DUAL
) src
ON (dst.uuid = src.uuid)
WHEN NOT MATCHED THEN
  INSERT (uuid, column1, column2)
  VALUES (src.uuid, src.column1, src.column2);

fiddle

If you want to also UPDATE existing rows then you can include a WHEN MATCHED THEN UPDATE ... clause.

If you want you can load the data into a temporary table and then MERGE from that (rather than using SELECT ... FROM DUAL UNION ALL ...).