Oracle10g

Fast SPLIT PARTITION operation is unusually slow

I recently observed that the total time taken to split an empty partition, in 10.2.0.5, has increased by many fold—i.e. from under 30 seconds to 60 minutes. SPLIT PARTITION operation is, simply, creating two new partitions and redistributing the rows from the partition being split into the two new partitions. Often one new partition contains …

Fast SPLIT PARTITION operation is unusually slow Read More »

RECORD feature of TKPROF

This is just a short note the point out that RECORD option of TKPROF is extremely useful in recording and capturing the non-recursive SQL statements in the order of execution from the SQL trace file.  I have not had a requirement to use it until—today—one of my developers lost the original test script used when …

RECORD feature of TKPROF Read More »

SQL*Plus Feature: TIMING and SET TIMING command

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 …

SQL*Plus Feature: TIMING and SET TIMING command Read More »

SQL returns wrong result when the table order is swapped

I noticed a strange behavior—a SQL statement, which used to return no rows in the past, started returning rows in an environment where the test data set has not changed. I performed a code comparison and observed a difference in the order of a table in FROM clause. After looking through, I found a bug entry on MOS …

SQL returns wrong result when the table order is swapped Read 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 predicate Read 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 view Read 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 Types Read More »

Optimizer Index Access Paths

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 …

Optimizer Index Access Paths Read More »

Get a complete CREATE TABLE definition using DBMS_METADATA package

CREATE TABLE has lot of options to go with it; it is hard to remember all of them. Most people will get lost while reading the rail road diagrams. It is fairly quick to create the table in its simple form and obtain all the parameters associated with it. Once the table is created in …

Get a complete CREATE TABLE definition using DBMS_METADATA package Read More »

DBMS_XPLAN.DISPLAY_CURSOR results in error cannot fetch plan for SQL_ID: 9babjv8yq8ru3

You may have received the below error while you tried to lookup the execution plan using DBMS_XPLAN.DISPLAY_CURSOR procedure. The error is due to the SQL*Plus environment variable SERVEROUTPUT is turned on. One of the Prerequisites for running the package is to turn off SERVEROUTPUT. You should be able to see the execution plan after turning …

DBMS_XPLAN.DISPLAY_CURSOR results in error cannot fetch plan for SQL_ID: 9babjv8yq8ru3 Read More »