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 From: mailto:[email protected] Sent: Monday, August 24, 2015 7:55 PM To: [email protected] 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, [email protected] [firebird-support] <[email protected]> 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 From: mailto:[email protected] Sent: Monday, August 24, 2015 7:28 PM To: [email protected] 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.
