I am working on performance tuning all the slow running queries. I am new to Oracle have been using sql server for a while. Can someone help me tune the query to make it run faster.
Select distinct x.a, x.b from
from xyz_view x
where x.date_key between 20101231 AND 20160430
Appreciate any help or suggestions
First, I'd start by looking at why the DISTINCT is there. In my experience many developers tack on the DISTINCT because they know that they need unique results, but don't actually understand why they aren't already getting them.
Second, a clustered index on the column would be ideal for this specific query because it puts all of the rows right next to each other on disk and the server can just grab them all at once. The problem is, that might not be possible because you already have a clustered index that's good for other uses. In that case, try a non-clustered index on the date column and see what that does.
Keep in mind that indexing has wide-ranging effects, so using a single query to determine indexing isn't a good idea.