How to optimize your queries? Steps to Optimize your Oracle queries

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;

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;

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;

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');

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';

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);

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;

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!!!

How to UPDATE and INSERT into a table using ONE single statement?

 In Oracle PL/SQL, you can use the MERGE statement to perform both updates and inserts in a single operation. This is particularly useful when you want to synchronize data between two tables based on certain conditions. The MERGE statement essentially combines the functionality of UPDATE and INSERT into a single command. Here's the basic syntax:

MERGE INTO target_table 
USING source_table 
ON (condition) 
WHEN MATCHED THEN 
    UPDATE SET column1 = value1, column2 = value2 
WHEN NOT MATCHED THEN 
    INSERT (column1, column2) VALUES (value1, value2);

Let's break down the components:

• target_table: The table you want to update or insert data into.

• source_table: The table from which you're getting the data.

• condition: The condition that determines whether a match is found between the target_table and the source_table.

• WHEN MATCHED THEN UPDATE: Specifies the action to take when a match is found. You define the columns to update and their new values.

• WHEN NOT MATCHED THEN INSERT: Specifies the action to take when no match is found. You define the columns to insert data into and their corresponding values.

Here's a simple example:

Assume you have a employees table and a employees_updates table. You want to update existing employee records based on their employee_id, and if the employee doesn't exist in the main table, you want to insert the data as a new record.

MERGE INTO employees e 
USING employees_updates u 
ON (e.employee_id = u.employee_id) 
WHEN MATCHED THEN 
    UPDATE SET e.first_name = u.first_name, e.last_name = u.last_name 
WHEN NOT MATCHED THEN 
    INSERT (employee_id, first_name, last_name)
    VALUES (u.employee_id,                 u.first_name, u.last_name);

In this example, if an employee in the employees_updates table matches an employee in the employees table based on their employee_id, the UPDATE clause will be executed. If there's no match, the INSERT clause will be executed.

Using the MERGE statement is a powerful way to efficiently manage updates and inserts in a single command, reducing the need for multiple SQL statements.

Difference between TRUNCATE table and DELETE table

 TRUNCATE TABLE and DELETE are two SQL statements used to remove data from a table in a database. However, they differ in terms of their functionality, speed, and impact. Here's a breakdown of the differences between TRUNCATE TABLE and DELETE:

1. TRUNCATE TABLE:

• Functionality: TRUNCATE TABLE is used to remove all the data from a table while keeping the structure intact. It effectively resets the table, making it empty.
• Speed: TRUNCATE TABLE is generally faster than DELETE because it's a minimally logged operation. It removes data in large blocks and deallocates space, resulting in less overhead.
• Rollback: Unlike DELETE, TRUNCATE TABLE cannot be rolled back. Once data is truncated, it cannot be recovered using a rollback operation.
• Triggers: TRUNCATE TABLE does not activate delete triggers associated with the table.
• Restrictions: It's important to note that you cannot use TRUNCATE TABLE on a table that is referenced by a foreign key constraint or participates in an indexed view.
• In Memory: Truncate is a DDL statement. It also involves two commits, one before the statement execution and one after, meaning that after truncating a table, the data cannot be recovered. Due to this, truncate recovers space from memory.

Example:

sql
TRUNCATE TABLE employees;

2. DELETE:

• Functionality: DELETE is used to remove specific rows from a table based on a specified condition.
• Speed: Compared to TRUNCATE TABLE, DELETE can be slower, especially when deleting a large number of rows. Each row is logged individually, leading to more overhead.
• Rollback: A DELETE operation can be rolled back, allowing you to recover data if necessary.
• Triggers: DELETE activates delete triggers associated with the table, providing more control over the deletion process.
• Foreign Keys: When using DELETE, you need to handle foreign key constraints. Depending on how foreign key relationships are defined, you might need to delete related rows from other tables before deleting from the main table.
• In Memory: Delete is DML statement, merely data manipulation. After executing a delete statement, we can do a Rollback to get the data back, or do a commit. The delete does not recover memory.

Example:

sql
DELETE FROM employees WHERE department_id = 10;

In Summary:

• Use TRUNCATE TABLE when you want to quickly remove all data from a table and reset its structure. It's faster but less flexible compared to DELETE.
• Use DELETE when you want to remove specific rows from a table based on a condition. It provides more control, triggers activation, and allows rollback.

The choice between TRUNCATE TABLE and DELETE depends on your specific needs. If you need to quickly remove all data from a table, and you don't need the option to roll back, TRUNCATE TABLE is generally the better choice. If you need more control over the deletion process and want the option to roll back, use DELETE.