Oracle – TRUNCATE vs DELETE – Spoiler: TRUNCATE Wins & Why? Hours vs Seconds!

Why DELETE is bad? In Oracle, when it comes to deleting  ** A L L **   the data from a table (clearing the contents of a staging table for example), using a DELETE statement is the most inefficient choice. Here are some reasons why DELETE is bad It uses UNDO It uses REDO It fires … Continue reading Oracle – TRUNCATE vs DELETE – Spoiler: TRUNCATE Wins & Why? Hours vs Seconds!

Oracle’s APPEND Hint – The Key Secret To Fast BULK Loading With NOLOGGING!

Oracle's logical storage structures: To developers who work with Oracle, the concept of tablespace is familiar.  We create tables and indexes in tablespaces taking appropriate care not to place all of our data into one tablespace. Let us take a quick look at the logical structure of an Oracle database A database has one or … Continue reading Oracle’s APPEND Hint – The Key Secret To Fast BULK Loading With NOLOGGING!

Oracle – Explain Plan Basics & Brief History

Whenever a SQL statement is submitted to the database server (be it Oracle or SQL Server), the server generates an execution plan which it uses to get the data in a well-defined order. The engine that does this work is called the “optimizer”. History of optimizers: RBO – Rule based optimizer Back in the day, … Continue reading Oracle – Explain Plan Basics & Brief History

Oracle – Move Tables/Indexes/LOB’s To Another Tablespace In Parallel Using DBMS_SCHEDULER

  Developers don't care about TABLESPACEs! Developers mostly don't care about where their tables go in terms of TABLESPACEs or other sizing parameters. They just script out an existing table, replace a few things and they are done. This ends up creating a mess where ALL objects are in a single TABLESPACE. DBA's on the other … Continue reading Oracle – Move Tables/Indexes/LOB’s To Another Tablespace In Parallel Using DBMS_SCHEDULER