We can optimize our queries in several ways that don't necessarily need an index. Of course, an INDEX is usually good, but there are other steps we can take to query faster.
1. Make sure to have your tables normalized in the third normal form.
The following table is an example of a table that is not normalized. Doing select everything from table Student and we have this,
The reason is that you can have many rows with the same data, meaning it is repeated. The reason is because this student happens to be assigned to more than one course. These kinds of situations will cause a query to perform poorly.
In this case, we need three tables, one for Student, one for Courses, and one for Course Assignment. The normalization process will get you something like this,
2. When designing the database, try to declare the columns that will probably have more nulls at the end, rather than in the middle or beginning. If the Null value is at the end of a Row, this will take less disk space, but if the null value is in the middle or at the beginning, then Oracle will take disk space to store it.
3. Use INTERSECT when possible instead of the INNER JOIN. The Intersect will perform better all the time.
The following query,
select e1.empFirstName, e1.empLastName
from employee e1
intersect
select e2.empFirstName, e2.empLastName
from employee e2;
from employee e1
intersect
select e2.empFirstName, e2.empLastName
from employee e2;
will perform better than the following one,
select distinct e1.empFirstName, e1.empLastName
from employee e1
inner join employee e2
on e1.pkEmployeeId = e2.pkEmployeeId;
from employee e1
inner join employee e2
on e1.pkEmployeeId = e2.pkEmployeeId;
4. Use MINUS instead of LEFT JOIN when possible. Like INTERSECT, the MINUS operation will always perform better than the LEFT JOIN.
5. Always use columns rather than "everything" or "all" in your query. It is always better to have this,
select e.pkEmployeeId, e.FirstName
from employee e;
from employee e;
Than this,
select * from employee;
6. Use INNER JOIN instead of correlated subqueries. The following is an example of a subquery,
select *
from employee e
where e.fkRoleId in (select r.pkRoleId
from rolesEmp r
where r.isExpired = 'N');
from employee e
where e.fkRoleId in (select r.pkRoleId
from rolesEmp r
where r.isExpired = 'N');
The query from before is of poor design. It is much better to do the following,
select e.pkEmployeeId,
e.firstName,
r.roleName
from employee e
inner join rolesEmp r
on r.pkRoleId = r.fkRoleId
where r.isExpired = 'N';
e.firstName,
r.roleName
from employee e
inner join rolesEmp r
on r.pkRoleId = r.fkRoleId
where r.isExpired = 'N';
Not only we are declaring what columns we want, but we are using a join, instead of a correlated subquery.
7. In most cases, use EXISTS, instead of IN. Note that I'm saying most cases. The general rule is the following,
- If the inner query is small as compared to the outer query, the IN keyword performs better.
- If the inner query is large as compared to the outer query, the EXISTS keyword performs better.
The EXISTS keyword will return TRUE if one record is found in the subquery. See the following example,
SELECT *
FROM customers
WHERE EXISTS (SELECT *
FROM order_details
WHERE customers.customer_id = order_details.customer_id);
FROM customers
WHERE EXISTS (SELECT *
FROM order_details
WHERE customers.customer_id = order_details.customer_id);
8. When joining two tables, use JOIN ON, and not the WHERE clause as the old way. The ON keyword performs a bit better plus the WHERE clause should only be used to filter out the results.
9. When writing a JOIN, try to put the table with the least number of rows first and do the same if there are many joins. Put the tables in ascending order starting with the tables with the least number of rows.
10. Avoid cartesian products at all costs. This happens especially when you don't specify a join when calling the tables. The following will cause a cartesian product,
SELECT *
FROM employee, rolesEmp;
FROM employee, rolesEmp;
11. Use the IN keyword instead of a series of ANDs and ORs. The IN keyword performs a bit better.
12. Know how to use INDEXES and TABLE PARTITIONS. Briefly, an index is used to quickly locate a row or a series of rows. By default, all primary keys have an index included. There are many indexes, unique (those that come with the primary key), BITMAP, B-TREE, etc. All of these will make joins work quicker and more efficient. If you notice that the same column or group of columns is queried in the where clause in multiple queries in several places, consider putting an index to those columns. This will significantly increase the performance; however, creating an index will take up disk space, so be aware of that as well.
13. Use the WITH statement. More of that here.
Hope you all have a nice day!!!
