index="xyz_order_line"|join ORDER_NUMBER_KEY[|inputlookup sample_lookup1.csv|where serial_no>0 AND serial_no<50001]| where some_condition |table ORDER_NUMBER_KEY,serial_no,field_x,field_y,field_z|append[search index="xyz_order_line"|join ORDER_NUMBER_KEY[|inputlookup sample_lookup1.csv|where serial_no>50000 AND serial_no<100001]| where some_condition |table ORDER_NUMBER_KEY,serial_no,field_x,field_y,field_z|append[search index="xyz_order_line"|join ORDER_NUMBER_KEY[|inputlookup sample_lookup1.csv|where serial_no>100000 AND serial_no<150001]| where some_condition |table ORDER_NUMBER_KEY,serial_no,field_x,field_y,field_z|append[search index="xyz_order_line"|join ORDER_NUMBER_KEY[|inputlookup sample_lookup1.csv|where serial_no>150000 AND serial_no<200001]| where some_condition |table ORDER_NUMBER_KEY,serial_no,field_x,field_y,field_z]| outputlookup sample_lookup2.csv
Need to run this query for last 7 days, because of this there is a huge number of records inflow (index=”xyz_order_line”= 20 lakh records) and sample_lookup1 has 12 lakhs records.
When I run above query for first 2 lakh records it works fine and gives all results but when I run this query for 6 to 8 lakhs range it gives results only for first 50k records rest joins doesn’t work.
Tried after minimizing the range to till 10k (instead of 50k at a time) also but join is not working in that case too.
Whereas when I run all queries separately(not using append) for each bunch like (600000-650000, 650000-700000,700000-750000 and 750000-800000) it gives correct result that means join is working properly.
It keeps on changing the behavior, some day one range works fine but next day same range doesn’t work.
One possible solution could be that I can write 30 different saved searches for all 15lakh records but that doesn’t seems to be the best approach and its taking too much time in execution.
Please suggest a best approach to solve this issue.
Basic scenario
Data is pulled from Oracle database
DBConnect app has been used
Need to perform join on 4 different indexers
Need to pull last 7 days data
Due to large amount of data, storing it into lookup and processing 50k records at a time because sub search has limitation of 50k.
... View more