Subject Re: [firebird-support] Expression index for use with the GROUP BY clause
Author
Post date 2015-08-24T17:40:15Z
Hi,
 
No – you can not create single index on more then one table
But you really need it?
How big is resultset? I do not see any filter in this query no HAVING nor WHERE
 
regards,
Karol Bieniaszewski
 
Sent: Monday, August 24, 2015 7:28 PM
Subject: [firebird-support] Expression index for use with the GROUP BY clause
 
 

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.