Join Chander Dhall for an in-depth discussion in this video Force range scan on a hash index, part of Cosmos DB: Import, Manipulate, Index and Query.
- [Narrator] Let's talk about range indexes. We've done this so many times in programming, that we have range queries like age is greater than 30 and less than 40, or something like salary is greater than 100,000 and less than 200,000, and it requires a range. And range indexing is a very expensive operation. It also results in a lot of request units that get charged, when it comes to Cosmos DB for a simple operation.
And it can also result in queries being throttled sooner. So, there are times when range indexing is, by design, not made available. And those cases are when you have an excluded path that excludes that particular path from being indexed. And then, you also have another situation where you only have a hash index. And in that case, range wouldn't work. Now, if, by design, we chose to do something like this, range indexes or indices would not be available.
However, we can always change that dynamically, by forcing a range index. And now, how do we do this? Let's go back to Visual Studio and see if we can make the range scan happen on a hash index. We'll leave this code as it is, and then make certain changes to our collection definition. In this case, we have a collection definition, and then we need to add some paths. So, the first thing I like to do is add the path, which is root star, because it involves everything, and then use the range indexes we talked about, and maybe use something like age or even salary and exclude both of them.
So now, this means that we don't really have age and salary included. That means we really cannot do any range indices here. And we can create the document collection here, add the collection definition. So, all this should be good to go. Well, now we should go and create some of these documents, maybe get rid of that document. So, we could do something like, add some kind of employee data here, and maybe reuse some of this code, and then add the document dynamically right here.
Say new, say id equals, let's say, one, and then we can have an employee name. And then, you can have some kind of age and maybe salary. And then, copy some of this to create some data. We need the self link.
And then, create few instances, so we have some good data here. Next, what we could do is say ben, change the name to Ben, fix the id's. Again, the actual data really doesn't matter. What matters is that we have some kind of range query going on here, cause that will actually break. You want to change this so it's different, at the same time, a little predictable.
Change the salary to 100,000, 150,000, 300,000, 250,000, and maybe just 50,000. That's number six.
And that should be good enough data to show what we're talking about here, which is the range indexing. We don't need this. Now is the time to do a query and see what works and what doesn't. So, as you know, we should now be able to do a range query. So, if I want data which is, give me everything in this collection, every single record that has age more than 25, as you can see, we have few people here that are older than 25. But you'll see that this query wouldn't work, and we will get an exception.
So, what we could do is wrap this inside a try catch, and then get rid of all this code here. And we can still leave the delete collection code here, because you want to tier this up once we're done. So, as you can see that this particular line will throw an exception, so I'm going to put a break point right here. And I don't think we need any other break point. So we're going to do is do F5.
So, you can see, there's an exception. It says, invalid query has been specified with filters against paths excluded from indexing, which is the same, exact error we got last time. Now, this is the error, and we need to solve this problem. And now, how do we make sure that we are able to do this query, even though we were excluded from doing this query by design? So, a better way of doing this would be, I'm going to copy this code and then comment it, but at the same time change what I was doing, so I can get the data back.
So, in this case, I'm going to have the same, exact query, but I'm going to add feed options and say enable scan in query equals true. Now, even though we can do this, our highlighted command, use it only when you need it, because this is an expensive operation, cause when you're going to enable range scans, it's very expensive. But if you really need it, well, you have to do it. So now, we should not get this exception, and we should be able to go to line number 112 directly and then we'll hover over the result to see if we get any answers.
Press F5. So, as you can see, we get a line number 112, and if we hover over result, well, we won't be able to get a result here, because it's part of the try catch. So, what we could do is we could put a break point right here at 105 and run this again. So now, you can see result is true, and we get the collection resource and everything, whatever we need here. And we can also run this query and get to the details by knowing what all came in, by just using using a to list.
So, why don't we do that? We're going to do a for each and change this to results. This way, we can e trade our results and just display them. And since results, we can leave it like that and do a to list.
This way, we get the entire list right here. And that's it. Or we could also do the to list here, to make more sense. Either ways, it should work. And now, I press F5. And as you can see, we got some data back here, and you can see we've got ID two, three, all the way to six, and that's because these are the people who are older than 25. That's exactly what we were expecting.
Which could be select start from r.
- Data import scenarios
- Creating a database
- Creating a partitioned collection
- Data manipulation
- Importing documents with a stored procedure
- User-defined functions
- Excluding indexing at a document level
- Range indexing on strings
- Querying with SQL parameters
- Range operations