SET TIMING—one of the most frequently used commands—displays timing statistics on each SQL command or PL/SQL block. What if you want to collect timing statistics for multiple group of statements? TIMING command becomes handy in such situation—one of things that I knew and have forgotten over time. It can be used to collect data to do …
The row source tree is the core of the execution plan. It contains the sequence of operations that the database performs to run the statement; each operation may have options associated with it. The operation in execution plan is also known as row source operator. If you look at the below example, TABLE ACCESS operation …
The SQL standard allows you to use an inline view in a DELETE statement, see the pseudo form below – If a condition such as key-preserved table is not met then it will result in error, “ORA-01752: cannot delete from view without exactly one key-preserved table.” I would like to show you the caveat of …
Virtual Indexes (aka NOSEGMENT Indexes or Fake Indexes) are useful to check whether the creation of an Index affects the execution plan with out having to create the actual index. Virtual index is just a definition without a physical index segment so it does not consume disk space. Additionally, virtual index is available to a …
Access paths are ways in which data is retrieved from the database. In general, index access paths should be used for statements that retrieve a small subset of table rows, while full scans are more efficient when accessing a large portion of the table. Online transaction processing (OLTP) applications, which consist of short-running SQL statements …