Hello! You can also try and set 'enforceJoinOrder' property to true, to force Ignite to join tables in the same order as they are specified in FROM clause.
Regards, -- Ilya Kasnacheev пн, 10 дек. 2018 г. в 06:54, joseheitor <[email protected]>: > Found the reason for the problem - and a solution... > > Problem is that the query-optimiser performs the final WHERE clause > *first*, > which results in a full table scan. > > The solution is to structure the query so that the original query (without > the WHERE clause) is forced to be performed first, then finally the WHERE > clause can be run on a small set of data. This can be achieved by setting > the whole original query (without the WHERE clause) as a 'Nested > Sub-query'... > > SELECT subQuery.* > FROM ( > *SELECT > mainTable.pk, mainTable.id, mainTable.k, mainTable.v > FROM > public.transactions AS mainTable > INNER JOIN > (SELECT id FROM public.transactions WHERE k = 'trans.cust.last_name' > AND v = 'Smythe-Veall') AS lastName ON lastName.id = mainTable.id > INNER JOIN > (SELECT id FROM public.transactions WHERE k = 'trans.date' AND v = > '2017-12-21') AS transDate ON transDate.id = mainTable.id > INNER JOIN > (SELECT id FROM public.transactions WHERE k = 'trans.amount' AND > cast(v AS integer) > 90000) AS transAmount ON transAmount.id = > mainTable.id* > ) AS subQuery > WHERE subQuery.k = 'trans.cust.first_name' > ORDER BY 1 > > > > -- > Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >
