Why won't my DML statement use partition pruning?

I had a case today where a developer issued a DELETE statement against a 600 mill row table.
The table is RANGE PARTITIONED, with partitions per month. The table is created with parallel degree 8.

The DML statement looked correct, and was using the partition key in the predicate:

delete 
from  mytable 
where val_date = to_date('31.10.2013','DD.MM.YYYY');

The execution plan looked as follows:

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |            |   611K|    16M|  5597   (2)| 00:01:08 |       |       |        |      |            |
|   1 |  DELETE               | MYTABLE    |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR      |            |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000   |   611K|    16M|  5597   (2)| 00:01:08 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |            |   611K|    16M|  5597   (2)| 00:01:08 |   118 |   118 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL| MYTABLE    |   611K|    16M|  5597   (2)| 00:01:08 |   118 |   118 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("VAL_DATE"=TO_DATE(' 2013-10-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

It looks like oracle is ignoring the predicate and performs a full table scan.
True or not?
When explaining without parallel slaves:

explain plan for 
delete /*+ no_parallel (a) */ 
from mytable a
where val_date = to_date('31.10.2013','DD.MM.YYYY');
select * from table(dbms_xplan.display);

Then the execution plan is revealed as follows:

------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT        |            |   611K|    16M| 40443   (2)| 00:08:06 |       |       |
|   1 |  DELETE                 | MYTABLE    |       |       |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|            |   611K|    16M| 40443   (2)| 00:08:06 |   118 |   118 |
|*  3 |    TABLE ACCESS FULL    | MYTABLE    |   611K|    16M| 40443   (2)| 00:08:06 |   118 |   118 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("VAL_DATE"=TO_DATE(' 2013-10-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note that when you use the NO_PARALLEL hint, you need to reference the table by an alias, in my case "a". The statement below

delete /*+ no_parallel */ 
from mytable...

will not, to my surprise, override the parallel settings, while

delete /*+ no_parallel (a)*/ 
from mytable a...

will.

In conclusion, simulating a non-parallel execution revealed that the query was indeed using partition pruning (as can be seen from line 2 above that reads "PARTITION RANGE SINGLE"). In the original explained plan, this is not pointed out by oracle, and can easily lead you think that oracle is not taking advantage of partitioning.

How to hide the input from the user when using the accept directive in sqlplus

accept sch prompt "Schema : "
accept pwd prompt "Password (not displayed) : " hide

Errors ORA-31693 and ORA-29913 thrown during import

Yesterday when importing some test data from production to test, I encountered the following error during import:

. . imported "< schema name >"."< table name > ":"< partition name >" 77.08 KB 0 rows
ORA-31693: Table data object "< schema name >"."< table name > ":"< partition name >" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
. . imported "< schema name >"."< table name > ":"< partition name >" 25.69 KB 0 rows

The preceeding export executed successfully, without any errors or warnings. I used multiple threads, and the result was 7 dump files.

I first tried reimporting the table, which in many similar cases actually work. This time, it didn't; the same error was thrown.

The solution was to remove the parallel directive from the import parameter file and reimport only the missing table:

USERID='/ AS SYSDBA'
DIRECTORY=DPUMP
DUMPFILE=EXDP_SCHEMA_EXPORT_201311181340_%U.DMP
LOGFILE=IMPDP_missing_tables.log
PARALLEL=1
TRANSFORM=OID:N
TABLE_EXISTS_ACTION=REPLACE
REMAP_SCHEMA=< schema_name >:
TABLES=< schema_name >.< table_name >

. . imported "< schema name >"."< table name > ":"< partition name >" 77.08 KB 0 rows
. . imported "< schema name >"."< table name > ":"< partition name >" 1.205 GB 6758593 rows
. . imported "< schema name >"."< table name > ":"< partition name >" 25.69 KB 0 rows

How to use the APPEND hint to optimize INSERT statements

When using the APPEND hint, you are telling the optimizer to use direct-path INSERT.

Serialized inserts:
Simply specify the APPEND hint in each INSERT statement, either immediately after the INSERT keyword or immediately after the SELECT keyword in the subquery of the INSERT statement.

INSERT /*+ APPEND */ INTO sales 
       SELECT product_id, customer_id, TRUNC(sales_date), 3, promotion_id, quantity, amount
       FROM sales_activity_direct;

Parallelized inserts:
First, enable parallel DML in your session:

ALTER SESSION ENABLE PARALLEL DML;

Then, specify the APPEND hint with the PARALLEL hint:

INSERT /*+ APPEND PARALLEL */ INTO sales 
       SELECT product_id, customer_id, TRUNC(sales_date), 3, promotion_id, quantity, amount
       FROM sales_activity_direct;

You can use NOLOGGING with APPEND to make the process even faster. NOLOGGING allows the direct-path INSERT operation to generate a negligible amount of REDO:

INSERT /*+ APPEND PARALLEL NOLOGGING */ INTO sales 
       SELECT product_id, customer_id, TRUNC(sales_date), 3, promotion_id, quantity, amount
       FROM sales_activity_direct;

Note:
* If the database or tablespace is in FORCE LOGGING mode, then direct path INSERT always logs, regardless of the logging setting.
* Direct-path INSERT is the default insert method when running in parallel mode, but unless you have specified the DEGREE attribute for your table, you still need to use the PARALLEL hint for each insert operation.
* Oracle Database performs index maintenance at the end of direct-path INSERT operations on tables that have indexes.

What types of indexes can be created on a partitioned table?

My customer wanted to introduce partitioning on certain tables. I was asked me to give an overview of the different indexing options available after a table has been partitioned.

In short, there are four types of indexes to be considered for partitioned tables:

1. Local Partitioned Indexes
2. Global Partitioned Indexes
3. Global Non-partitioned Indexes
4. Bitmap Indexes



1. Local Partitioned Indexes

Local partitioned indexes always inherit the partitioning key from the table and are fully aligned with the table partitions. Oracle calls this concept "equipartitioning": "each partition of a local index is associated with exactly one partition of the table"

Local indexes can only be unique if the partitioning key is a part of the index key.

New partitions are added to local indexes only when you add a partition to the underlying table. Likewise, they are dropped only when you drop a partition from the underlying table. In other words, you cannot explicitly add a partition to a local index.

Two types of local indexes

There are two types of local indexes: Prefixed and non-prefixed.

Prefixed means that the table's partition key column is referenced first in the index, while a non-prefixed key does not.

Oracle states that non-prefixed local indexes are useful for historical databases, although I haven't had any expirence yet that would confirm this.

For an example of a unique local prefixed index along with some useful queries, click here
For an example of a local non-prefixed index along With some useful queries, click here

The obvious advantage of using local indexes is managebility and flexibilty. Any operation on a single partition will only affect the relevant index partition, and not the intire index as such.

2. Global Partitioned Indexes

Partitioned indexes whose patitions are not aligned with the table's partitions are called global partitioned indexes. In other Words, there is no relation between a table's partitions and a global index partition.

The keys in a particular index partition may refer to rows stored in multiple underlying table partitions or subpartitions.

Global partitioned indexes give the flexibility to choose a partitioning key that is most optimal for performance. Oracle also claims that under some circumstances, having multiple segments for an index can be beneficial for performance.

Two types of global partitioned indexes

There are two types of Global Partitioned Indexes:

* Global Hash Partitioned indexes: improves performance by spreading out contention when the index is monotonically growing on the right side of the index. Using a hashing algorithm, Oracle creates the specified number of hash partitions and spread the index Blocks uniformly accross these.

For an example, see this post

* Global Range Partitioned indexes: adds flexibility: the degree of partitioning and the partitioning key are independent from the table's partitioning method.

For an example, see this post

Both of these types can be defined on any type of partitioned table (list, range, hash etc.).

3. Global Non-partitioned Indexes

These are simply normal indexes. The fact that the underlying table is partitioned does not matter. The index spans all partitions.

Global indexes can be unique.

4. Bitmap Indexes
You can create bitmap indexes on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table. They cannot be global indexes.

What is the difference between direct-path INSERT and conventional INSERT?

• During conventional INSERT operations, the database reuses free space in the table, interleaving newly inserted data with existing data. During such operations, the database also maintains referential integrity constraints.

• During direct-path INSERT operations, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored. These procedures combined can enhance performance.


Source: Oracle Documentation

Possible reason for RMAN-06059: "expected archived log not found, lost of archived log compromises recoverability"

After having changed the archive log destination, I received the following when performing ARCHIVELOG backups:

Starting backup at 25-NOV-2008
current log archived
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/25/2008 10:57:56
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /app/oracle/admin/PRODDP/archive670098694_1_1.arc
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

I needed to run a crosscheck after having pointed out the new archive log destination to the recovery catalog:

allocate channel for maintenance type disk; 
run {
set archivelog destination to '/app/oracle/admin/PRODDB/archive';
change archivelog all crosscheck;
}

After this has been run, I could backup up the ARCHIVELOGS successfully using tape (or disk) backups:

run {
allocate channel t1 type sbt PARMS="BLKSIZE=1048576" maxpiecesize 32G maxopenfiles 64;
send 'NB_ORA_POLICY=O_SYR_loc1,NB_ORA_CLIENT=prodserver1-bkp.mydomain.net,NB_ORA_SCHED=O_SYR_loc1_user';
backup archivelog all; 
release channel t1;
}