Oracle Database 11g: Interactive Quick Reference, Your Essential Guide to Oracle Database 11g Release 2

Last fall, I posted a note about Oracle Database 11g Architecture and Background Processes. This post is just a short note to point out that last week, Oracle University has made Oracle Database 11g Interactive Quick Reference available to public for download; it is a flash application, available for offline use in compressed file (.ZIP) …

Oracle Database 11g: Interactive Quick Reference, Your Essential Guide to Oracle Database 11g Release 2Read More »

Enabling and Disabling Database Options

This is just a short note to point out that starting from Oracle database 11g Release 2, a command line utility, chopt (change option) is available to enable or disable a particular database feature; the tool is located under ORACLE_HOME/bin directory.  Currently, you can enable or disable the following features using this utility: Oracle Data Mining RDBMS Files, …

Enabling and Disabling Database OptionsRead More »

Execution Plan Operations and Options

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 …

Execution Plan Operations and OptionsRead More »

Relational Database Index Design and the Optimizers

Relational Database Index Design and the Optimizers (Wiley, July 2005) is an excellent book on how indexes work in general in a relational database. The authors focused on principles and not any particular database vendor. The book focuses on two things; first, the database optimizer of the relational system has to decide how to find …

Relational Database Index Design and the OptimizersRead More »

OVERLAPS predicate

OVERLAPS predicate is a useful feature, which is not available in most SQL implementations that can help to determine whether the two periods overlap each other. If two periods overlap then the predicate evaluates to TRUE otherwise FALSE. Let us create a test table with three rows – one with overlapping dates, second with non-overlapping …

OVERLAPS predicateRead More »

SQL Developer: Database Export Wizard to export DDL and Data as DML

Sometimes, you would want to generate a script that you would like to run repeatedly in many environments for many objects. In such situations, use of Database Export wizard helps you generate DDL and DML (INSERT) script; – you can select object types, specific objects and filter out or restrict the data exported. Step1: Source …

SQL Developer: Database Export Wizard to export DDL and Data as DMLRead More »

Removing table rows using an inline view

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 …

Removing table rows using an inline viewRead More »

Software Performance and Scalability: A Quantitative Approach

Software Performance and Scalability: A Quantitative Approach (Wiley, May 2009) is for people involved in designing, developing, testing, implementing and managing enterprise applications. This book provides the necessary quantitative foundation, analytical and measurement aspects of performance and scalability –  effective blend of studies and practical examples, which makes it thorough, it easier to read and informative. …

Software Performance and Scalability: A Quantitative ApproachRead More »

Joins, Join Conditions, Filters and Join Types

Joins A join is a query that combines rows from two or more tables. Oracle performs a join whenever multiple tables appear in the FROM clause of the query. Below is a SQL join statement, legacy syntax and ANSI/ISO syntax (also referred as new join syntax, was first introduced in Oracle 9i release) in a …

Joins, Join Conditions, Filters and Join TypesRead More »