I have (say) an order document with a foreign key to a customer. I need to get page worth’s of customers from the orders. I have indexing in place to quickly retrieve customer ids given the ‘order’ predicates.
I need to return the customers ordered by a field on the ‘order’
the problem is that I need to filter by a field or two on the ‘customer’.
So I can’t do a join because that will fetch all the customers in the db before the filtering.
So I’m looking to use intersect like this:
Select raw customerId from (Select customerId from default where type = ‘order’ order by createDate desc)
Select raw customerId from default where type = ‘customer’ and predicate 1…n (using covering indexes)
then I could apply my offset + limit + join to the customer doc.
Unfortunately it does not look like ordering is preserved upon exiting the intersect/except
And I don’t think I can include the ordering field in the projection for the first query (because then Intersect wont work)
a subquery in list will work for me, but I have to know whether the execution engine is smart enough to evaluate that subquery => hash table only once (and not again and again for every row in the in the potential results)
I am sorry to be obtuse. When you say ‘if is constant’ does that include where it is an uncorrelated subquery, which is constant only in the sense of some level of isolation, but not technically constant. @vsr1
hashing of IN clause (build hash once and probe) will automatically enabled when (right side of IN must be constant, named/positional parameter, CTE variable , non-correlated subquery and number of elements IN must be more than 16). i.e. value must not change for each document.