Posts

Oracle SQL Optimization Tricks: Table Elimination and OR-Expansion Explained

-
Oracle optimizer's job is to find the most efficient way to execute SQL statements and provide the requested data to the client. We all know it is a complex code and gets improved as part of every product release. Today Oracle Optimizer is very evolved and has very rich useful features in-built in it (adaptive plans, dynamic statistics etc) Below is a basic optimization technique ,which may be there from 8i/9i, may be not relevant today , but still posting as sometimes below things may come up in some execution plan which you are checking. Table discard/elimination ========================= The query involves 2 tables - students and teachers , with student table having a FK relationship with the teachers table. CREATE TABLE STUDENTS ( STUDENT_ID NUMBER(10) NOT NULL, NAME VARCHAR2(50 BYTE) NOT NULL, TEACHER_ID NUMBER(10) ) ALTER TABLE STUDENTS ADD ( FOREIGN KEY (TEACHER_ID) REFERENCES TEACHERS (TEACHER_ID) ENABLE VALI...
Post a Comment
Read more

Row Locking and ITL in Oracle.

-
Image
Locking is very important aspect in a database. It is must to regulate access to (shared) resource so that one process/user doesn't change it while other is modifying the same. If we question how database may be locking rows/data, we usually may think there may be a process/manager which must be doing the task internally. The manager process maintains the information which process id wants/has to lock particular rows etc. Seems perfect for simple logical explanation, However if we consider this for OLTP systems, we can easily understand that such a single point of control may cause bottleneck. All processes competing for locks(acquire or release) on resources via a single manager will ensure that the application/workload cant scale at all. One of the major reason for Oracle being the top database is the locking mechanism which it implements. There is no such concept as lock manger etc. Oracle places the lock related information in the actual data block itself. Ex:  when a row in a ...
Post a Comment
Read more