Hello! I can't see your data so I'm not entirely sure, but:
The first query has expected number of records (500 + 300 + 200, every city is collocated to its country), hence collocated. The second query does not have expected number of records (expected (200 + 300) + (500 + 300) + (500 + 200) = 2000 but found just 800), displaying how uncollocated query fails to join tables in full. I think you're good now. Note that your data layout is not very good now. Your cache has 1024 partitions but you're only using 3 at maximum. Our planet does not actually have enough countries to make such collocation optimal. Optimal collocation assumes that all your affinity keys are equally frequent, and total number of affinity keys >> number of partitions. Maybe you should make Country table REPLICATED instead, and have no collocation by country_id? Regards, -- Ilya Kasnacheev пт, 22 мар. 2019 г. в 19:12, NileshKhaire <ngkha...@gmail.com>: > Hello, > > Sorry for late response. > > If you will see first message of this third, you will understand that > initially I had used same schema. But collocation were not working. > > By the way, now I have again inserted sample data (sent in previous > messages) as per your suggestion . > > 1000 cities and 3 countries. > > 1st Country = 500 cities > 2nd Country = 300 cities > 3rd Country = 200 cities > > CREATE TABLE Country ( > country_id INT(10), > country_name CHAR(30), > Continent CHAR(30), > PRIMARY KEY (country_id) > ) WITH "template=partitioned, backups=1"; > > > CREATE TABLE City ( > city_id INT(15), > country_id INT(10), > city_name CHAR(50), > Dist CHAR(20), > PRIMARY KEY (city_id,country_id) > ) WITH "template=partitioned, backups=1, affinityKey=country_id"; > > output : > > select count(*) from City c join Country cc on cc.country_id = > c.country_id > ; > => Count = 1000 > > select count(*) from City c join Country cc on cc.country_id != > c.country_id; > => Count = 800 > > Do you think data is collocated ? > > So what are the logical explanation for count=1000 for first query and > count > = 800 for second query. > > > Thanks, > Nilesh > > > > > > -- > Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >
