Update Sep 7, 2018: This article uses deprecated commands. Please checkout this blog post on "Alter Index Vs DBCC IndexDefrag Vs DBCC DBReIndex". In the newer versions, I prefer ALTER INDEX although it has not been good at defragmenting in all cases. A good defrag script is worth its weight in gold. This time, I copied … Continue reading SQL Server: Identify And Defragment Indexes Using DBCC SHOWCONTIG & DBCC INDEXDEFRAG
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 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!
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
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