CODEWITHSUNDEEP
sqloracle-databasequery-optimization
Phil_Charly
Phil_Charly

Reputation: 237

SQL query optimization based on indexes

I have been given as an assignment the following queries and how to optimize them by creating indexes:

a)SELECT EmployeeID FROM Employee WHERE Name='John'AND Surname='Brown'

b)SELECT EmployeeID FROM Employee WHERE Salary=1300

c)SELECT EmployeeID FROM Employee WHERE Salary BETWEEN 1000 AND 1500

d)SELECT EmployeeID FROM Employee WHERE Salary+Bonus>1500

from a table Employee: EmployeeID, Name, Surname, Salary, Bonus

I've stated that for the first one a) a compound index would be best and a clustered better for the second one and a partioned for the third one and a some kind of clustered in (d.I am not sure about my choices could you please verify them and correct me as I am new to this.P.s.indexes better be in Oracle.Thanks in advance.

Upvotes: 0

Views: 175

Answers (2)

David Balažic
David Balažic

Reputation: 1474

For d) a function based index (FBI) would be appropriate:

CREATE INDEX emp_i3 ON Employee (Salary+Bonus);

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 415630

for the first one a) a compound index would be best

On what columns? Surname + Name, Name + Surname? The order can matter. In this case it likely doesn't matter at all, but normally you want to consider the entire application, and think about how you will be commonly doing lookups. If you have another query that looks up by surname alone, for example, you would want to make sure to put the surname column first in the index, so that this index will work for both queries. Over-indexing can be almost as bad for performance as under-indexing.

a clustered better for the second one

Again, you need to consider the entire table/application when choosing your indexes. You can only have one clustered index on a table. It's highly likely that your one clustered index will need to be on the EmployeeID column. Even if we don't see any queries using it here, that's the most common need. A regular index on Salary is probably good enough here.

a partitioned for the third one

A regular index on Salary will likely be good enough. The database will be able to go to the first record, and then "walk the index" until it no longer matches. But it depends on the table size... if the table is huge (into the 10s and 100s of millions of rows), partitioning can make sense (usually on the table itself). I don't know many businesses that have 10s of millions of employees. Again, one thing we want to do is avoid over-indexing, and so re-using the same index from b) is good.

some kind of clustered in (d

It depends on the database engine and version, but it's unlikely any index by itself will help this query. The reason is that expressions are very often not sargable, meaning the query optimizer won't be smart enough to know if the index will work or not. What you can do is create a computed column virtual column and put an index on that column.

In all cases, since you're only requesting the EmployeeID column, you want to add EmployeeID to the index, but don't actually index on that field. Just INCLUDE the column with the index. In this way, the database will be able to entirely fulfill your query from the index alone, without needing to go back to the table. The reason for just including the column, rather than indexing on it, is to improve performance of INSERT/UPDATE statements, to avoid needing to rebuild the index.

Upvotes: 3

Related Questions