Subject Expression index for use with the GROUP BY clause
Author Walter R. Ojeda Valiente
Post date 2015-08-24T17:28:22Z
Hello everybody

I had the following query:

SELECT
   D.ASI_ANOEJE,
   D.ASI_CODSUC,
   EXTRACT(MONTH FROM C.ASC_FECHAX) AS ASI_NUMMES,
   D.ASI_NUMCUE,
   D.ASI_NUMSUB
FROM 
   ASIENTOSDET D
JOIN
   ASIENTOSCAB C
      ON D.ASI_CODSUC = C.ASC_CODSUC AND
         D.ASI_IDECAB = C.ASC_IDENTI
GROUP BY
   D.ASI_ANOEJE,
   D.ASI_CODSUC,
   EXTRACT(MONTH FROM C.ASC_FECHAX),
   D.ASI_NUMCUE,
   D.ASI_NUMSUB

Is it possible to have an expression index for use here with the GROUP BY clause?

The PLAN is the following:

PLAN SORT (JOIN (D NATURAL, C INDEX (PK_ASIENTOSCAB)))

As you can see there is a SORT there caused by the GROUP BY clause.

ASI_ANOEJE is SMALLINT
ASI_CODSUC is SMALLINT
ASC_FECHAX is DATE
ASI_NUMCUE is VARCHAR(16)
ASI_NUMSUB is CHAR(5)

I'm using Firebird 2.5.4

Greetings.

Walter.