|Subject||Re: [firebird-support] Expression index for use with the GROUP BY clause|
|Author||Walter R. Ojeda Valiente|
Ok, thank you Karol.
On Mon, Aug 24, 2015 at 2:32 PM, liviuslivius@... [firebird-support] <firstname.lastname@example.org> wrote:
Hi,as i say previously you can not do thisas index can not be updated if something was changed in ASIENTOSCAB.And any subseclect is not recognized by plan parser as expression index defregards,Karol Bieniaszewski
Hello KarolThank 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] <email@example.com> wrote:
Hi,No – you can not create single index on more then one tableBut you really need it?How big is resultset? I do not see any filter in this query no HAVING nor WHEREregards,Karol BieniaszewskiHello everybodyI had the following query:SELECTD.ASI_ANOEJE,D.ASI_CODSUC,EXTRACT(MONTH FROM C.ASC_FECHAX) AS ASI_NUMMES,D.ASI_NUMCUE,D.ASI_NUMSUBFROMASIENTOSDET DJOINASIENTOSCAB CON D.ASI_CODSUC = C.ASC_CODSUC ANDD.ASI_IDECAB = C.ASC_IDENTIGROUP BYD.ASI_ANOEJE,D.ASI_CODSUC,EXTRACT(MONTH FROM C.ASC_FECHAX),D.ASI_NUMCUE,D.ASI_NUMSUBIs 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 SMALLINTASI_CODSUC is SMALLINTASC_FECHAX is DATEASI_NUMCUE is VARCHAR(16)ASI_NUMSUB is CHAR(5)I'm using Firebird 2.5.4Greetings.Walter.