My Oracle Support Banner

How to perform partition pruning when CAST or TRUNC function used in query by adding check constraint (Doc ID 2626702.1)

Last updated on JANUARY 07, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 18.4.0.0.0 and later
Information in this document applies to any platform.

Goal

This document demonstrate how we can perform partition pruning when CAST or TRUNC function used in query.

SALES table of SH demo schema used for this testing purpose

Query used for testing purpose

select 'Q1 - Without Cast' as Q, sum(s.amount_sold) from sales s where date'2000-03-01' <= s.time_id and s.time_id < date'2000-04-01' ;

select 'Q2 - With Cast' as Q, sum(s.amount_sold) from sales s where date'2000-03-01' <= CAST(s.time_id AS DATE) and CAST(s.time_id AS DATE) < date'2000-04-01' ;

select 'Q3 - Check Constraint with CAST ' as Q, sum(s.amount_sold) from sales s where date'2000-03-01' <= CAST(s.time_id AS DATE) and CAST(s.time_id AS DATE) < date'2000-04-01' ;

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.