Db2: perfomant way to check if item exists-CodePudding

what will be the fastest way to check if a specific item exists in table or not.

Select count(*)
From xyz 
where col = „xyz“
Group by col

I am evaluating the result of this select, but will be there a faster and Performance way?

CodePudding user response：

This depends on what you want to do with the result.
It's generally not recommended to use aggregate functions, since you don't need to scan all matches - you need just one of them at most.

If it's some procedure logic:

BEGIN
  DECLARE V_RES INT;
  
  -- You get 1 or NULL in the V_RES variable
  SELECT 1 INTO V_RES
  FROM SYSCAT.SCHEMATA
  WHERE SCHEMANAME LIKE 'SYS%'
  FETCH FIRST 1 ROW ONLY;

END@

If it's just a result of a single query:

SELECT
CASE 
  WHEN EXISTS
  (
    SELECT 1
    FROM SYSCAT.SCHEMATA
    WHERE SCHEMANAME LIKE 'SYS%'
  )
  THEN 'YES'
  ELSE 'NO'
END AS RES  
FROM SYSIBM.SYSDUMMY1