Subject Re: [firebird-support] Expression index for use with the GROUP BY clause
Author Walter R. Ojeda Valiente
Post date 2015-08-24T19:31:09Z
Ok, thank you Karol.

Greetings.

Walter.


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

Hi,
 
as i say previously you can not do this
as index can not be updated if something was changed in ASIENTOSCAB.
And any subseclect is not recognized by plan parser as expression index def
 
 
regards,
Karol Bieniaszewski
 
Sent: Monday, August 24, 2015 7:55 PM
Subject: Re: [firebird-support] Expression index for use with the GROUP BY clause
 
 

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.