Subject Re: [firebird-support] Expression index for use with the GROUP BY clause
Author Walter R. Ojeda Valiente
Post date 2015-08-24T17:55:14Z
Hello Karol

Thank you for your answer.

The query is a very simplified one, just for show the idea.

I can create an index as the following:

CREATE INDEX IDX_ASIENTOSDET1 ON ASIENTOSDET COMPUTED BY (
   CAST(ASI_ANOEJE AS CHAR(5)) ||
   CAST(ASI_CODSUC AS CHAR(5)) || 
   CAST(EXTRACT(MONTH FROM (SELECT ASC_FECHAX FROM ASIENTOSCAB WHERE ASC_CODSUC = ASI_CODSUC AND ASC_IDENTI = ASI_IDECAB)) AS CHAR(2)) ||
   ASI_NUMCUE ||
   ASI_NUMSUB
);

But is is not used in the PLAN. I had tried several alternatives but without success until now. Using CAST(), without using CAST() and so on.

I don't want to add a column ASI_FECHAX to my table ASIENTOSDET if I can avoid create it, but I need rows grouped by that column.

ASIENTOSCAB and ASIENTOSDET have a parent-child relationship, where ASIENTOSCAB is the parent.

Greetings.

Walter.






On Mon, Aug 24, 2015 at 1:40 PM, liviuslivius@... [firebird-support] <firebird-support@yahoogroups.com> wrote:
 

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.