Oracle 23c: Managing Flashback Database Logs Outside the Fast Recovery Area
Overview: In previous Database releases, you could store flashback database logs only in the fast recovery area Oracle 23c Database introduces the ... Read More
Apprenez-en plus sur les raisons pour lesquelles Eclipsys a été certifié comme un excellent lieu de travail au Canada, les meilleurs lieux de travail en Ontario et en technologie, et a été nommé le meilleur employeur de PME au Canada pendant 3 ans !
En savoir plus !In this blog, I’ll test executing UPDATE and DELETE commands with direct joins using the HR schema.
Prerequisites:
Use-Case: Increase the salaries of the Finance department by 5%.
1. Query the tables EMPLOYEES and DEPARTMENTS and look at the rows to be updated.
SELECT e.employee_id, e.department_id, e.first_name, e.Last_name, e.salary FROM employees e, departments d WHERE e.department_id=d.department_id AND d.department_name='Finance';
2. Writing UPDATE in pre-23c Oracle database using sub-query in the WHERE clause.
UPDATE employees e SET e.salary=e.salary*1.05 WHERE e.department_id in ( SELECT department_id FROM departments WHERE department_name='Finance');
3. Write UPDATE in Oracle 23c database using direct join then query the tables.
When we run the SQL plan, we notice that the join is using the index EMP_DEPARTMENT_IX to access the EMPLOYEES table. It is the same SQL plan and cost when using update with sub-query in the WHERE clause.
explain plan for UPDATE employees e SET e.salary=e.salary*1.05 FROM departments d WHERE e.department_id=d.department_id AND d.department_name='Finance';
UPDATE employees e SET e.salary=e.salary*1.05 FROM departments d WHERE e.department_id=d.department_id AND d.department_name='Finance';
Use-Case: delete the employees of the Finance department.
1. Query the tables EMPLOYEES and DEPARTMENTS and look at the rows to be updated.
SELECT e.employee_id, e.department_id, e.first_name, e.Last_name, e.salary FROM employees e, departments d WHERE e.department_id=d.department_id AND d.department_name='Finance';
2. Writing DELETE in pre-23c Oracle database using sub-query in the WHERE clause.
DELETE employees e WHERE e.department_id in ( SELECT department_id FROM departments WHERE department_name='Finance');
3. Writing DELETE in Oracle 23c database using direct join then query the tables.
When we run the SQL plan, we notice that the join is using the index EMP_DEPARTMENT_IX to access the EMPLOYEES table. It is the same SQL plan and cost when using update with sub-query in the WHERE clause.
DELETE employees e FROM departments d WHERE e.department_id=d.department_id AND d.department_name='Finance';
Overview: In previous Database releases, you could store flashback database logs only in the fast recovery area Oracle 23c Database introduces the ... Read More
Overview: Oracle 23c Database introduces a new feature to control whether a user or session is enabled for read-write operations, irrespective of the ... Read More