Data Warehouse with Oracle Data Integrator (ODI)

Building a Data Warehouse: typical steps and milestones

• Research and specify business needs (Key Indicators)
• Identify data sources relevant to generate key indicators
• Define business rules to transform source information into key indicators
• Model the data structure of the target warehouse to store the key indicators
• Populate the indicators by implementing business rules
• Measure the overall accuracy of the data by setting up data quality rules
• Develop reports on key indicators
• Make key indicators and metadata available to business users through ad-hoc query tools or predefined reports
• Measure business users’ satisfaction and add/modify key indicators

Using Oracle Data Integrator (ODI) in a Data Warehouse project: actors involved and some assigned tasks

Business User	Access the final calculated key indicators Use reports and ad-hoc queries May need to understand the definition of an indicator May need to be aware of data quality issues when was the last time the table was updated? How many records were added, update, removed in the table? What are the rules that calculate a particular indicator? Where does the data come from, and how is it transformed?
Business Analyst	Define the key indicators Identify the source applications How many different source applications need to be considered? Is the data needed for key indicators available in the selected pool of source applications? What data quality issues are present in the source systems? Specify business rules to transform source data into meaningful target indicators Projects can use the ODI Designer to directly specify the business rules For each target table, specify also: Target datastore - name the target datastore Description of transformation - describe its purpose Integration strategy - How data should be written to target (replace table, append, update, etc). Each strategy specified will correspond to an ODI Integration Knowledge Module. Refresh frequency Dependencies - what datastores need to be loaded or jobs executed prior to this one Source datastores - source databases, applications, etc used Source ODI Model - Datastore name, Fiel Mappings and transformations, Links or Join criteria, Filters, Data Quality requirements, constraint names and expressions, etc Maintain translation data from operational semantics to the Data Warehouse semantic
Developer	Implement the business rules as specified by business analysts Provide executable scenarios to the production team Must understand infrastructure details and have business knowledge of source applications
Metadata Administrator	Reverse engineer source and target applications Understand content and structure of source applications Connect to source applications and capture their metadata Define data quality business rules (specified by Business Analyst) in ODI repository What level of Data Quality is required? who are the business owners of source data? What should be done with rejected records? There should be an error recycling strategy? How would business users modify erroneous source data? Should a GUI be provided for source data correction? Guarantee the overall consistency of Metadata in the various environments (dev, test, prod: repository) Participate in the data modeling of key indicators Add comments, descriptions and integrity rules (PK, FK, Check, etc)in the metadata Provide version management
Database Administrator	Define technical database structure supporting the various layers in the data warehouse project (and ODI structure) Create database profiles needed by ODI Create schemas and databases for the staging areas Describe columns inside the data dictionary of database - COMMENT ON TABLE/COLUMN Avoid using PKs of source systems as PKs in target tables. Use counter or identity columns instead. Design Referential Integrity and reverse engineerd FKs in ODI models. Do not implement RIs on target database (for performance). Data quality control should guarantee data integrity. Standardize obejct naming conventions Distributed and maintain the descriptions of the environments (topology)
System Admin	Maintain technical resources for the Data Warehouse project May install and monitor schedule agents May backup and restore repositories Install and monitor ODI console Setup the various enviroments (dev, test, prod)
Security Admin	Define the security policy for the ODI Repository Creates ODI users and grant rights on models, projects and contexts
Operator	Import released and tested scenarios into production environment Schedule the execution of production scenarios Monitor execution logs and restart failed sessions

Conceptual Architecture of an ODI solution

• The ODI Repository is the central component.
• ODI Repository stores configuration information about
• IT infrastructure and topology
• metadata of all applications
• projects
• Interfaces
• Models
• Packages
• Scenarios
• Solutions - versioning control
• You can have various repositories within an IT infrastructure, linked ti separated environments that exchange metadata and scenarios (i.e. development, test, user acceptance test, production, hot fix)

Example: two environments, components, actors and tasks

(source: Oracle 2008)

Suggested environment configuration for a Data Warehouse project with ODI:

(1) A single master repository:

• holds all the topology and security information.
• All the work repositories are registered here.
• Contains all the versions of objects that are committed by the designers.

(2) “Development” work repository:

• Shared by all ODI designers
• Holds all the projects and models under development.

(3) “Testing” work repository

• shared by the IT testing team.
• Contains all the projects and models being tested for future release.

(4) “User Acceptance Tests” work repository:

• shared by the IT testing team and business analysts.
• Contains all the projects and models about to be released.
• Business analysts will use the ODI Console on top of this repository to validate the scenarios and transformations before releasing them to production.

(5) “Production” work repository

• shared by the production team, the operators and the business analysts.
• Contains all the projects and models in read-only mode for metadata lineage, as well as all the released scenarios.

(6) “Hot fix” work repository:

• shared by the maintenance team and the development team.
• Usually empty, but whenever a critical error happens in production, the maintenance team restores the corresponding projects and models in this repository and performs the corrections with the help of the development team.
• Once the problems are solved, the scenarios are released directly to the production repository and the new models and projects are versioned in the master repository.

Typical environment (I):

(source: Oracle 2010)

Typical environment (II): Separate Master Repository for Production

(source: Oracle 2010)

Wanderlust

(coffee break)

Spending lots of your time configuring RAC, freshing MVs, checking Grid Control charts..
So I go for a walk. Wander about, wonder...

Oracle On-line Analytic Processing (OLAP) (I)

OLAP Cubes can play three (3) roles in an Oracle Installation:

(a) Full-featured, fully integrated multidimensional server

• Applications can query all data using a dimensional query language (Oracle OLAP API or MDX)
• OLAP engine runs within the kernel of Oracle Database.
• Dimensional objects (cubes, measures, dimensions, levels, hierarchies, attributes) are stored in Oracle Database in their native multidimensional format.
• Cubes and other dimensional objects are first class data objects represented in the data dictionary.
• Data security is administered in the standard way, by granting and revoking privileges to users and roles.
• Applications can query dimensional objects using SQL.

(b) A Summary management solution

• The Oracle cube is used to manage summary data and is accessed transparently by SQL-based BI applications as a Cube-organized Materialized View using Query rewrite feature.
• Cube-Organized MVs were introduced on Oracle 11g, and play the same role as table-based MVs
• Cube-organized MVs allow any application to transparently access summary data managed by the cube.
• Cube-organized MVs provide substantial query performance enhancement with much lower maintenance overhead than table-based MV alternatives
• With Cube-organized MVs, applications query the detail tables and the database automatically rewrites the query to access summary data in the materialized view.

To expose the cube as a Materialized View, you can use the Analytic Workspace Manager:

Both detail and summary data in the cube are exposed in the Materialized View.
When the detail tables are queried, the optimizer rewrite the query to use the cube-organized materialized view

SQL> select t.calendar_year_name,
       p.department_name, 
       cu.region_name,
       sum(s.quantity) as quantity, 
       sum(s.sales) as sales
from times t, 
     customers cu, 
     products p,
     sales_fact s
where cu.customer_key = s.customer
  and p.item_key = s.product
  and s.day_key  = t.day_key
group by t.calendar_year_name, p.department_name, cu.region_name;


Explain plan: 
OPERATION
-----------------------------------

SELECT STATEMENT
  HASH
    CUBE SCAN CB$SALES_CUBE

(c) A supplier of rich analytic content

• OLAP cubes are exposed as Cube views.
• Cube views include detail and aggregate data as individual columns (measures)
• Cube views can list complex analytic content, including hiearchical aggregations, statistical forecasts, additive and non-additive aggregations, calculated measures, etc

Cubes and Dimensions seen from a relational perspective

Since Oracle provides a complete SQL interface to the OLAP cube:

• Cubes and Dimensionas can be thought of as relational objects.
• Cubes as relational objects that offer improved performance and advanced analytic content, and
• Dimensions as relational objects that include columns with information useful to creating hiearchical queries.
• The cube is simply another data type in the database.

Some benefits OLAP Cubes bring to Business Intelligence applications

(a) Improved query performance for Ad-hoc query patterns

• As query patterns become less predictable, creation and maintenance of materialized views for specific queries becomes impractical.
• Consider a data model with four dimensions (time, customer, product, channel), each with six levels of summarization (i.e in the time dimension: day, week, month, quarter, half year and year).
• In this case, there are (4**6 -1) = 4095 possible combinations representing sumary level data that users might query.

(b) Improved query performance for summary data
(c) Fast incremental update
(d)Rich analytic content
(e)Metadata that describes the logical business model and the relational representations of the cube

(Q) What two categories can OLAP Metadata be grouped into?

(a) Metadata about the Cube's structure, data, and how it is calculated
(b) Metadata about how the cube is represented for query using SQL cube views

• Description of the cube's structure include information on: dimensions, hierarchies, levels, attributes and measures.
• For measures, metadata is available describing how the cube is calculated and the calculation expression of a measure.

SQL> select table_name from dict where table_name like '%CUBE%';
TABLE_NAME
------------------------------
...
DBA_CUBES
DBA_CUBE_ATTRIBUTES
DBA_CUBE_ATTR_VISIBILITY
DBA_CUBE_BUILD_PROCESSES
DBA_CUBE_CALCULATED_MEMBERS
DBA_CUBE_DIMENSIONALITY
DBA_CUBE_DIMENSIONS
DBA_CUBE_DIM_LEVELS
DBA_CUBE_DIM_MODELS
DBA_CUBE_DIM_VIEWS
DBA_CUBE_DIM_VIEW_COLUMNS

TABLE_NAME
------------------------------
DBA_CUBE_HIERARCHIES
DBA_CUBE_HIER_LEVELS
DBA_CUBE_HIER_VIEWS
DBA_CUBE_HIER_VIEW_COLUMNS
DBA_CUBE_MEASURES
DBA_CUBE_VIEWS
DBA_CUBE_VIEW_COLUMNS
...

=> Checking the existing dimensions in SALESTRACK analytic workspace 
SQL> select * from dba_cube_dimensions;

OWNER    DIMENSION_NA DIMENSION_TYPE   AW_NAME      DEFAULT_HIERARCHY_NAME       DESCRIPTION
---------- ------------ ----------------- ------------ ------------------------------ --------------------
OLAPTRAIN  PRODUCT2 STANDARD   SALESTRACK   STANDARD         product2
OLAPTRAIN  CHANNEL STANDARD   SALESTRACK   SALES_CHANNEL        Channel
OLAPTRAIN  GEOGRAPHY STANDARD   SALESTRACK   REGIONAL         Geography
OLAPTRAIN  PRODUCT STANDARD   SALESTRACK   STANDARD         Product
OLAPTRAIN  TIME  TIME    SALESTRACK   CALENDAR         Time

You can use SQL Developer to:

• check existing hierachies, levels and order within hierarchies in the 'PRODUCT2' dimension.

• List existing cubes, measures within the cube, whether a measure is derived or stored and the equation that generates that measure:

Cube Views

• OLAP Cubes are exposed through views on a star schema.
• As the structure of dimensions and cubes are updated,views are automatically maintained
• If you add new measures, the cube view will be updated
• Views are always in sync with the underlying dimensions and cubes
• A single cube view exposes (as columns) all the aggregations and all the calculations for a cube

SQL> select * from dba_cube_views;

OWNER    CUBE_NAME    VIEW_NAME
---------- --------------- -------------------
OLAPTRAIN  FORECAST    FORECAST_VIEW
OLAPTRAIN  SALES_CUBE    SALES_CUBE_VIEW
OLAPTRAIN  NEW_CUBE    NEW_CUBE_VIEW

SQL> select owner, cube_name, view_name, column_name from dba_cube_view_columns;

OWNER    CUBE_NAME    VIEW_NAME     COLUMN_NAME
---------- --------------- ------------------------------ ------------------------------
OLAPTRAIN  FORECAST    FORECAST_VIEW    BEST_FIT
OLAPTRAIN  FORECAST    FORECAST_VIEW    LINEAR_REGRESSION
OLAPTRAIN  SALES_CUBE    SALES_CUBE_VIEW    SALES
OLAPTRAIN  SALES_CUBE    SALES_CUBE_VIEW    QUANTITY
OLAPTRAIN  SALES_CUBE    SALES_CUBE_VIEW    SALES_YTD
OLAPTRAIN  SALES_CUBE    SALES_CUBE_VIEW    SALES_YTD_PY
...

=> The Cube's SQL interface is also available in SQL Developer:

Using Oracle External Tables

• External tables do not reside in the database, and can be in any format for which an access driver is provided.
• You can select, join, or sort external table data.
• You can also create views and synonyms for external tables.
• You CANNOT execute DML operations (UPDATE, INSERT, or DELETE) , and CANNOT create indexes on external tables.

External tables and Data Warehouse

• Exporting data from a database:
• External tables provide a framework to unload the result of an arbitrary SELECT statement into a platform-independent Oracle-proprietary format that can be used by Oracle Data Pump.
• External tables provide a valuable means for performing basic extraction, transformation, and loading (ETL) tasks that are common for data warehousing.

Creating External tables

• CREATE TABLE...ORGANIZATION EXTERNAL statement.
• This statement creates only metadata in the data dictionary.
• External tables can be thought of as views that allows running any SQL query against external data without requiring that the external data first be loaded into the database.

Accessing External Data

• An access driver is the actual mechanism used to read the external data in the table.
• When you use external tables to unload data, the metadata is automatically created based on the data types in the SELECT statement.

Oracle Database provides two access drivers for external tables:

• ORACLE_LOADER driver (default)
• allows the reading of data from external files using the Oracle loader technology.
• provides data mapping capabilities which are a subset of the control file syntax of SQL*Loader utility.
• ORACLE_DATAPUMP driver
• lets you unload data: read data from the database and insert it into an external table, represented by one or more external files—and then reload it into an Oracle Database.

Scenario 1:
Unload data to flat files and create External Table.
ORACLE_LOADER driver








Step 1 - Export data from table sh.customers

/* Step 1 - Export data from table sh.customers 
   Procedure UNLOAD_DATA receives table_name and table_owner as parameter and 
     unloads the table data to a flat file */

set serveroutput on

DECLARE
  procedure unload_data( 
       p_table_name in varchar2,
       p_owner  in varchar2)
  IS
   sqltext varchar2(1000);
   v_textrecord varchar2(2000);
   cv sys_refcursor;

   fdest utl_file.file_type;
   type FileAttrRec is Record (
                      vfilexists BOOLEAN,
                      vfilelength number,
                      vblocksize  binary_integer);
   vfilerec fileattrrec;
   vfilename varchar2(30);

    procedure get_sql (
       p_sqltext in out varchar2,
       p_table_name in varchar2,
       p_owner in varchar2) 
    IS
      cursor c is
        select column_name
        from dba_tab_columns
        where table_name = p_table_name and owner = p_owner
        order by column_id;
      i integer;
      sql_stmt varchar2(2000);
      from_clause varchar2(100);
    begin
      i :=0;
      sql_stmt := 'SELECT ';
      from_clause :=  'FROM ' || p_owner ||'.' || p_table_name;
      for r in c loop
        if i = 0 then
          sql_stmt := sql_stmt || ' ' || r.column_name;
          i := 1;
        else
          sql_stmt := sql_stmt || '|| ''|''|| ' || r.column_name;
        end if;
      end loop;
      sql_stmt := sql_stmt || ' as qrystr ' || chr(10) || from_clause;
      p_sqltext := sql_stmt;
      -- dbms_output.put_line( sql_stmt);
    end get_sql; 

  begin
  -- 1. Get Query SQL
   dbms_output.enable(20000);
   sqltext := '';  
   get_sql(sqltext, p_table_name, p_owner);
   --dbms_output.put_line('Select stmt:' || sqltext); 

  -- 2. Open cursor for query. Write each row in the OS file
       vfilename := 'unload_' || p_owner ||'_'|| p_table_name || '.dat';
       utl_file.fgetattr('DATA_DUMP_DIR', vfilename, vfilerec.vfilexists, 
                              vfilerec.vfilelength, vfilerec.vblocksize);
       if vfilerec.vfilexists then 
          fdest := utl_file.fopen('DATA_PUMP_DIR', vfilename, 'a', 2048);
          dbms_output.put_line('Destination file exists. Appending..'); 
       else
          fdest := utl_file.fopen('DATA_PUMP_DIR', vfilename, 'w', 2048);
       end if;
  
   open cv for sqltext;
   LOOP
       fetch cv into v_textrecord;
       EXIT WHEN cv%NOTFOUND;
       utl_file.put_line(fdest, v_textrecord, true);
   END LOOP;
   utl_file.fclose(fdest);
   close cv; 
  END unload_data;

BEGIN
 unload_data('CUSTOMERS' , 'SH');
END;

Step 2 - Split unloaded file and create external table using the resulting files

$ ls -ltr
total 482928
...
-rw-r--r-- 1 oracle oinstall  10661903 2012-03-20 22:01 unload_SH_CUSTOMERS.dat

-- spliting the file in two smaller files
$ split -30000 unload_SH_CUSTOMERS.dat unload_SH_CUSTOMERS.dat
$ ls -ltr
total 503760
...
-rw-r--r-- 1 oracle oinstall  10661903 2012-03-20 22:01 unload_SH_CUSTOMERS.dat
-rw-r--r-- 1 oracle oinstall   5768182 2012-03-20 22:15 unload_SH_CUSTOMERS.dataa
-rw-r--r-- 1 oracle oinstall   4893721 2012-03-20 22:15 unload_SH_CUSTOMERS.datab

 Step 2 - Create external table using unloaded file(s)
 
 -- create the external table
SQL>  Create table ext_customers1
     ( 
         "CUST_ID"                NUMBER,
         "CUST_FIRST_NAME"        VARCHAR2(20 BYTE),
         "CUST_LAST_NAME"         VARCHAR2(40 BYTE),
         "CUST_GENDER"            CHAR(1 BYTE),
         "CUST_YEAR_OF_BIRTH"     NUMBER(4,0),
         "CUST_MARITAL_STATUS"    VARCHAR2(20 BYTE),
         "CUST_STREET_ADDRESS"    VARCHAR2(40 BYTE),
         "CUST_POSTAL_CODE"       VARCHAR2(10 BYTE),
         "CUST_CITY"              VARCHAR2(30 BYTE),
         "CUST_CITY_ID"           NUMBER,
         "CUST_STATE_PROVINCE"    VARCHAR2(40 BYTE),
         "CUST_STATE_PROVINCE_ID" NUMBER,
         "COUNTRY_ID"             NUMBER,
         "CUST_MAIN_PHONE_NUMBER" VARCHAR2(25 BYTE),
         "CUST_INCOME_LEVEL"      VARCHAR2(30 BYTE),
         "CUST_CREDIT_LIMIT"      NUMBER,
         "CUST_EMAIL"             VARCHAR2(30 BYTE),
         "CUST_TOTAL"             VARCHAR2(14 BYTE),
         "CUST_TOTAL_ID"          NUMBER,
         "CUST_SRC_ID"            NUMBER,
         "CUST_EFF_FROM" DATE,
         "CUST_EFF_TO" DATE,
         "CUST_VALID" VARCHAR2(1 BYTE)
       )
  ORGANIZATION EXTERNAL 
  (
     TYPE ORACLE_LOADER
     DEFAULT DIRECTORY data_pump_dir
     ACCESS PARAMETERS
      (
          records delimited by newline
          badfile data_pump_dir:'cust1ext%a_%p.bad' 
          logfile data_pump_dir:'cust1ext%a_%p.log'
          fields terminated by '|'
          missing field values are null
      )
     LOCATION ('unload_SH_CUSTOMERS.dataa', 'unload_SH_CUSTOMERS.datab')
  )
  PARALLEL
  REJECT LIMIT UNLIMITED;

table EXT_CUSTOMERS1 created.

SQL> select table_name, type_name, default_directory_name, reject_limit, access_parameters, property 
 from dba_external_tables;
 
TABLE_NAME      TYPE_NAME       DEFAULT_DIRECTORY_NAME   REJECT_LIMIT    ACCESS_PARAMETERS                       
--------------- --------------- ------------------------ --------------- ------------------------------------------
EXT_CUSTOMERS1   ORACLE_LOADER   DATA_PUMP_DIR            UNLIMITED       records delimited by newline             
                                                                          badfile data_pump_dir:'cust1ext%a_%p.bad'
                                                                          logfile data_pump_dir:'cust1ext%a_%p.log' 
                                                                          fields terminated by '|'                  
                                                                          missing filed values are null             
 

SQL> select count(*) from ext_customers1;

  COUNT(*)
----------
     55500

Scenario 2: Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver





Step 1

• (a) Use CREATE TABLE ... ORGANIZATION EXTERNAL ... AS SELECT ...
• This will create a file with the data resulting from the specified query.
• The file is created with a binary format that can only be read by the ORACLE_DATAPUMP access driver
• The example below creates an external table and populates the dump file for the external table with the data from table sh.customers.

connect sh/**;

SQL> create table customers1_xt
  organization external
  ( 
    type oracle_datapump
    default directory data_pump_dir
    location ('customers1_xt.dmp')
  )
  as select * from sh.customers;

table created.

SQL> 


(b) Check the OS files created: 

$ ls -ltr
total 60672
-rw-r----- 1 oracle oinstall 10334208 2012-03-21 00:33 customers1_xt.dmp
-rw-r--r-- 1 oracle oinstall      123 2012-03-21 00:36 CUSTOMERS1_XT_7165.log

(c) Compare descriptions of customers and customers1_xt tables.

SQL> desc customers;

Name                   Null     Type         
---------------------- -------- ------------ 
CUST_ID                NOT NULL NUMBER       
CUST_FIRST_NAME        NOT NULL VARCHAR2(20) 
CUST_LAST_NAME         NOT NULL VARCHAR2(40) 
CUST_GENDER            NOT NULL CHAR(1)      
CUST_YEAR_OF_BIRTH     NOT NULL NUMBER(4)    
CUST_MARITAL_STATUS             VARCHAR2(20) 
CUST_STREET_ADDRESS    NOT NULL VARCHAR2(40) 
CUST_POSTAL_CODE       NOT NULL VARCHAR2(10) 
CUST_CITY              NOT NULL VARCHAR2(30) 
CUST_CITY_ID           NOT NULL NUMBER       
CUST_STATE_PROVINCE    NOT NULL VARCHAR2(40) 
CUST_STATE_PROVINCE_ID NOT NULL NUMBER       
COUNTRY_ID             NOT NULL NUMBER       
CUST_MAIN_PHONE_NUMBER NOT NULL VARCHAR2(25) 
CUST_INCOME_LEVEL               VARCHAR2(30) 
CUST_CREDIT_LIMIT               NUMBER       
CUST_EMAIL                      VARCHAR2(30) 
CUST_TOTAL             NOT NULL VARCHAR2(14) 
CUST_TOTAL_ID          NOT NULL NUMBER       
CUST_SRC_ID                     NUMBER       
CUST_EFF_FROM                   DATE         
CUST_EFF_TO                     DATE         
CUST_VALID                      VARCHAR2(1)


SQL> desc customers1_xt;

Name                   Null     Type         
---------------------- -------- ------------ 
CUST_ID                NOT NULL NUMBER       
CUST_FIRST_NAME        NOT NULL VARCHAR2(20) 
CUST_LAST_NAME         NOT NULL VARCHAR2(40) 
CUST_GENDER            NOT NULL CHAR(1)      
CUST_YEAR_OF_BIRTH     NOT NULL NUMBER(4)    
CUST_MARITAL_STATUS             VARCHAR2(20) 
CUST_STREET_ADDRESS    NOT NULL VARCHAR2(40) 
CUST_POSTAL_CODE       NOT NULL VARCHAR2(10) 
CUST_CITY              NOT NULL VARCHAR2(30) 
CUST_CITY_ID           NOT NULL NUMBER       
CUST_STATE_PROVINCE    NOT NULL VARCHAR2(40) 
CUST_STATE_PROVINCE_ID NOT NULL NUMBER       
COUNTRY_ID             NOT NULL NUMBER       
CUST_MAIN_PHONE_NUMBER NOT NULL VARCHAR2(25) 
CUST_INCOME_LEVEL               VARCHAR2(30) 
CUST_CREDIT_LIMIT               NUMBER       
CUST_EMAIL                      VARCHAR2(30) 
CUST_TOTAL             NOT NULL VARCHAR2(14) 
CUST_TOTAL_ID          NOT NULL NUMBER       
CUST_SRC_ID                     NUMBER       
CUST_EFF_FROM                   DATE         
CUST_EFF_TO                     DATE         
CUST_VALID                      VARCHAR2(1)  



(d) Check data on customers1_xt and compare with original table

SQL> select count(*) from customers1_xt;

COUNT(*)               
---------------------- 
55500

SQL> select * from customers minus select * from customers1_xt;

no rows selected


(e) You CANNOT perform DML on the external table:

SQL> delete from customers1_xt where cust_id = 100055;
delete from customers1_xt where cust_id = 100055
            *
ERROR at line 1:
ORA-30657: operation not supported on external organized table


(f) You can also Create an external table with multiple dump files and with a degree of parallelism higher than one.

SQL> create table customers1_xtp3
 organization external
 (
   type oracle_datapump
   default directory data_pump_dir
   location('customers1_xtp31.dmp' ,'customers1_xtp32.dmp' ,'customers1_xtp33.dmp' )
 )
 parallel 3
 as select * from sh.customers;

$ ls -ltr
total 60672
-rw-r----- 1 oracle oinstall 10334208 2012-03-21 00:33 customers1_xt.dmp
-rw-r--r-- 1 oracle oinstall      123 2012-03-21 00:36 CUSTOMERS1_XT_7165.log
-rw-r--r-- 1 oracle oinstall       41 2012-03-21 10:13 CUSTOMERS1_XTP3_2690.log
-rw-r--r-- 1 oracle oinstall       41 2012-03-21 10:13 CUSTOMERS1_XTP3_2737.log
-rw-r--r-- 1 oracle oinstall       41 2012-03-21 10:13 CUSTOMERS1_XTP3_2739.log
-rw-r--r-- 1 oracle oinstall       41 2012-03-21 10:13 CUSTOMERS1_XTP3_2741.log
-rw-r----- 1 oracle oinstall  2990080 2012-03-21 10:13 customers1_xtp32.dmp
-rw-r----- 1 oracle oinstall  3792896 2012-03-21 10:13 customers1_xtp33.dmp
-rw-r----- 1 oracle oinstall  3592192 2012-03-21 10:13 customers1_xtp31.dmp


(g) You can transfer or copy the dump file(s) and use them for another external table either in the same or in a different database. 

SQL> Create table customers2_xt
     ( 
         "CUST_ID"                NUMBER,
         "CUST_FIRST_NAME"        VARCHAR2(20 BYTE),
         "CUST_LAST_NAME"         VARCHAR2(40 BYTE),
         "CUST_GENDER"            CHAR(1 BYTE),
         "CUST_YEAR_OF_BIRTH"     NUMBER(4,0),
         "CUST_MARITAL_STATUS"    VARCHAR2(20 BYTE),
         "CUST_STREET_ADDRESS"    VARCHAR2(40 BYTE),
         "CUST_POSTAL_CODE"       VARCHAR2(10 BYTE),
         "CUST_CITY"              VARCHAR2(30 BYTE),
         "CUST_CITY_ID"           NUMBER,
         "CUST_STATE_PROVINCE"    VARCHAR2(40 BYTE),
         "CUST_STATE_PROVINCE_ID" NUMBER,
         "COUNTRY_ID"             NUMBER,
         "CUST_MAIN_PHONE_NUMBER" VARCHAR2(25 BYTE),
         "CUST_INCOME_LEVEL"      VARCHAR2(30 BYTE),
         "CUST_CREDIT_LIMIT"      NUMBER,
         "CUST_EMAIL"             VARCHAR2(30 BYTE),
         "CUST_TOTAL"             VARCHAR2(14 BYTE),
         "CUST_TOTAL_ID"          NUMBER,
         "CUST_SRC_ID"            NUMBER,
         "CUST_EFF_FROM" DATE,
         "CUST_EFF_TO" DATE,
         "CUST_VALID" VARCHAR2(1 BYTE)
       )
  ORGANIZATION EXTERNAL 
  (
     TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY data_pump_dir
     LOCATION ('customers1_xtp31.dmp' ,'customers1_xtp32.dmp' ,'customers1_xtp33.dmp')
  );

SQL> select count(*) from customers2_xt;

COUNT(*)               
---------------------- 
55500   

SQL> Select * from sh.customers minus select * from customers2_xt;

no rows selected.

Data Warehouse: Basic Concepts (I)

@Amazon

Common requirements of Data Warehouse systems

• Must provide easy access to the organization's information
• Show consistency in the display of information
• Must be adaptive and resilient to change
• Must safely keep information
• Must serve as a foundation for improved decision making
• Must be accepted by the business community in the enterprise

Common components of a Data Warehouse (Kimball)

Four components:
(a) Operational Source System
(b) Data Staging Area
(3) Data Presentation Area
(4) Data Access Tools

"One of the biggest threats to DWH success is confusing the components' roles and functions."
i.e. Don't give access to end users to the Staging Area. Don't build the DWH on a normalized design.

Characteristics of source operational systems

• Main priorities of source operational system are processing performance and availability
• Queries against source systems are narrow, one-record-at-a-time queries, and are severy restricted in their demands on the Operating System
• Source systems are not queried in broad and unexpected ways: queries are tunned, specific, and known before hand.
• Source systems maintain little historical data

(Q) what are +two key architectural requirements for the staging area?

• (a) Staging area is off-limits to business users
• (b) Does not provide query and presentation services
• (c) Does not (should not) need to have a normalized structure to hold data

(Q) What are some common characteristics of the staging area?

• Dominated by tasks such as sorting and sequential processing
• Often not based on relational technology but instead on many flat files
• Usually most of the tasks of extraction, transformation and loading (ETL) of data from operational systems into the data warehouse is concentrated in the staging area.
• Tables in the staging area should be segregated from the "live" data warehouse, i.e., they should NOT be available for end users' queries
• A basic implementation here is to have an identical schema to the one that exists in the source operational system(s) but with some structural changes to the tables, such as range partitioning.
• Alternatively, in some implementations all data transformation processing is done “on the fly” as data is extracted from the source system before it is inserted directly into the Presentation area.

(Q) What are four common transformations done (usually in the staging area) to the extracted data during the ETL process?

• (a) Cleansing (correct misspellings, resolve domain conflicts, deal with missing data, reformatting)
• (b) Combining data from multiple sources
• (c) Deduplicating data
• (d) Assigning warehouse keys

(Q) What are some performance considerations when planning the loading stage of the ETL process?

• The goal in this phase is to load the data into the warehouse in the most expedient manner.
• Careful consideration should be given to where the data being-loaded resides and how you load it into the database.
• For example, do not use a serial database link or a single JDBC connection to move large volumes of data.
• Flat files are the most common and preferred mechanism of large volumes of data.

• The overall speed of your load will be determined by
• (A) how quickly the raw data can be read from staging area and
• (B) how fast it can be processed and inserted into the database.
• You should stage the raw data across as many physical disks as possible to ensure the reading it is not a bottleneck during the load.

(Q) What tasks are usually part of the loading of the Presentation area?

• Load each data Mart
• Index new data for query performance
• Supply/compute appropriate aggregates (i.e. build or refresh Materialized Views)

(Q) What is a Data Mart? How it relates to the Presentation Area and to the Data Warehouse?

• A Data Mart is a section of the overall presentation area, which usually consists of a series of integrated data marts.
• At its simplest form, it presents the data from a single business process
• Modern Data Marts may be updated. Changes in lables, hierarchies, status, and corporate ownership may trigger changes
• These changes are managed-load updates, however.
• The Data Warehouse presentation area in a large enterprise may consist of 20 or more similar-looking data marts.
• Each Data mart has similar dimensional models.
• Each data mart contains various fact tables, each having 5-15 dimension tables, that are often shared among the various fact tables.

(Q) What is dimensional modeling? How does it relate to 3NF models?

• The key difference between dimensional and 3NF models is the degree of normalization.
• Both types can be represented in E-R diagrams, but 3NF are intended to reduce data redundancy.
• Dimensional model contains the same information as a normalized model.
• Dimensional model's goals are understandability, query performance and resilience to change.
• 3NF modeling boosts performance of operational systems: an update or insert transaction only needs to touch the database in one place.
• UPDATES or INSERTS do not have to touch(read/write) many different structures in the database.
  3NF models are too complex for DWH queries.
• Dimensional modelings is applicable to both relational and multidimensional databases.

(Q) What are some key goals of dimensional modeling?

Dimensional model's goals are (a) understandability, (b) query performance and (c) resilience to change.

(Q) What does the concept of metadata represent in a Data Warehouse context?

Metadata is all the information in the Data Warehouse environment that is NOT the actual data itself

(Q) What types of metadata are there?

• (1) Operational source system metadata - Source schemas, copybooks that facilitate the extraction proces
• (2) Staging area metadata - Used to guide the transformation and loading processes. Includes
• staging file layouts
• Target table layouts
• transformation and cleaning rules
• conformed dimension and fact definitions
• aggregation definitions
• ETL transmission schedules and run-log results
• (3) DBMS metadata - System tables, partition settings, indexes and view definitions, security privileges and grants
• (4) data access tools metadata - Identify (a) business names and definitions for the presentation area's tables and columns; (b) application template specifications; (c) access and usage statistics
• (5) Security Settings metadata - From Source transactional data all the way to user desktops

(Q) Consider the vocabulary of dimensional modeling: What are Fact tables?

• Fact tables are the primary tables in a dimensional model, and store numerical performance measurements of the business
• Measurement data resulting from a business process should be stored in a single data mart.
• A Fact represents a business measure.
• A measurement is taken at the intersection of all the dimensions (day, product, store, channel), which defines the grain of the measurement
• All measurements in a fact table should have the same grain.
• The example below shows the SALES fact table and its associated dimension tables.

(Q) What types of facts can be stored in a fact table?

• Additive - can be rulled up. (i.e. amount_sold, quantity_sold)
• Semiadditive - can be added only along some of the dimensions
• Nonadditive - Cannot be added.. Averages and counts are possible.

(Q)what are the categories in which fact table grains fall into?

• Transaction - (i.e. amount_sold and quantity_sold
• Periodic Snapshot
• Accumulating Snapshot

• In the image below, quantity and amount are transactional additive facts

(Q) What are Dimension tables?

• Contain the textual descriptors of the business
• Tables should have as many meaningful columns as possible
• sometimes 50-100 attributes
• attributes here are the primary source of query constraints, groupings, and report labels
• Attributes often represent hierarchical relationships (i.e. product -> brand -> Category)
• Dimension tables are typically highly denormalized
• Check the attributes of TIMES and CUSTOMERS in the example below. Denormalization is extensive.

(Q) How dimension tables improve the quality of a Data Warehouse?

• Dimension tables should have many and robust attributes, all relevant to the business.
• Robust dimension attributes deliver robust analytic slicing and dicing capabilities
• Dimensions implement the user interface to the Data Warehouse

(Q) what characteristics Dimension Attributes should have?

• Attributes should consist of real words rather than cryptic abbreviations

(Q) When designing a Data Warehosue, how can you decide whether a data field extracted from an operational systems belongs to a Fact table or to a Dimension table?

• Ask whether
• (a) field is a measurement that takes on lots of values and participates in calculations (FACT)
• (b) filed is a discretely valued description that is more or less constant and participate in constraints (DIMENSION)

Q&As: Data Warehousing and Partitioning (II)

(Q) What is a Partition-Wise Join? How can SQL statements benefit from Full Partition-Wise Joins?

• A P-W Join divides a large join into smaller joins between a pair of partitions from the two joined tables.
• To use P-W Joins:
• (a) equipartition BOTH tables on their JOIN keys or
• (b) use Reference partitioning
• P-W Joins reduce query response time by minimizing the amount of data exchanged among parallel execution servers when joins execute in parallel
• Full P-W Joins: Divide a large join into smaller joins between a pair of partitions from the two joined tables
• Tables MUST be equipartitioned on their join keys, or use reference partitioning.

(Q) What are the two most common types of indexes in Data Warehouse environments?

• B-tree and bitmap indexes
• Both can be created as local indexes on a partitioned table, inheriting the table's partitining strategy
• Local nonprefixed indexes can improve performance because many index partitions can be scanned in parallel by range queries on the index key.

SQL> create index cust_last_name_ix ON customers(last_name) LOCAL PARALLEL NOLOGGING;

(Q) How can bitmap index improve performance in a Data Warehouse environment?

• Bitmap indexes use a very efficient storage mechanism for low cardinality columns.
• Fact table foreign key column are ideal candidates for bitmap indexes, since generally there are few distinct values relative to the total number of rows in the fact table. (Because dimension tables should not have many rows)

(Q) What is Partition Exchange Load (PEL)? When is it used? What benefits does it provides?

• Oracle strongly recommends that the larger tables or fact tables in a data warehouse are partitioned.
• with a partitioned table, you can load data quickly and easily with minimal impact on business users by using the exchange partition command.
• With the exchange partition command you can swap the data in a non-partitioned table into a particular partition in your partitioned table.
• NO physical data movement occurs with the exchange partition command.
• Instead the data dictionary is updated to exchange a pointer from the partition to the table and vice versa.
• Because there is no physical movement of data, an exchange does not generate redo and undo, making it a VERY fast operation.

• You can convert by exchanging their data segments:
• (a) a partition (or subpartition) into a nonpartitioned table and
• (b) a nonpartitioned table into a partition (or subpartition) of a partitioned table
• In DWH environments, exchanging partitions facilitates high-speed data loading of new, incremental data into an existing partitioned table.
• OLTP and DWH also can benefit from exchanging old data partitions out of a partitioned table.
• In the latter case, the data is purged from the partitioned table without being deleted and can be archived separately afterwards.

(1) create a separate table that looks exactly like a single partition in the existing table (including indexes, constraints and partition schema)
(2) Load the data in the new table, build indexes, enable constraints. This will not impact current users of the existing table
(3) Exchange the existing table partition with the newly created table

i.e. (a) create new partition on existing table 
 SQL> Alter table sales add partition p_sales_jun_2007 
                                                    values less than (to_date('01-JUL-2007', 'DD-MON-YYYY');

(b) create temp table that looks like existing partition 
 SQL> create table sales_jun_2007 COMPRESS FOR OLTP as select * from sales where 1=0;

(c) Populate table and create indexes and constraints 
 SQL> create bitmap index time_id_jun_2007_bix ON sales_jun_2007(time_id) nologging;
 SQL> create bitmap index cust_id_jun_2007_bix ON sales_jun_2007(cust_id) nologging;
 SQL> create bitmap index prod_id_jun_2007_bix ON sales_jun_2007(prod_id) nologging;
 SQL> create bitmap index promo_id_jun_2007_bix ON sales_jun_2007(promo_id) nologging;
 SQL> create bitmap index channel_id_jun_2007_bix ON sales_jun_2007(channel_id) nologging;
 
 SQL> ALTER TABLE sales_jun_2007 ADD CONSTRAINT prod_id_fk 
                                            FOREIGN KEY (prod_id) REFERENCES products(prod_id);
 SQL> ALTER TABLE sales_jun_2007 ADD CONSTRAINT cust_id_fk 
                                            FOREIGN KEY (cust_id) REFERENCES customers(cust_id);
 SQL> ALTER TABLE sales_jun_2007 ADD CONSTRAINT promo_id_fk 
                                            FOREIGN KEY (promo_id) REFERENCES promotions(promo_id);
 SQL> ALTER TABLE sales_jun_2007 ADD CONSTRAINT time_id_fk 
                                            FOREIGN KEY (time_id) REFERENCES times(time_id);
 SQL> ALTER TABLE sales_jun_2007 ADD CONSTRAINT channel_id_fk 
                                            FOREIGN KEY (channel_id) REFERENCES channels(channel_id);

(d) Exchange partition 
 SQL> Alter table sales EXCHANGE PARTITION p_sales_jun_2007 WITH TABLE sales_jun_2007
   including indexes;

Partition Exchange Load (PEL)

Data Guard: Switchover to a Physical Standby Database

(Step 1) Verify that the primary database can be switched to a physical standby database

• A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary can be switched to a standby role.

SQL> select name, db_unique_name, switchover_status from v$database;

NAME   DB_UNIQUE_NAME   SWITCHOVER_STATUS
--------- ------------------------------ --------------------
DG11G   atlanta    TO STANDBY

(Step 2) Initiate the switchover of the primary database to a physical standby database

• After completion of this statement, the Data Guard configuration will have two physical standby databases and no primary.

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

(Step 3) Shutdown and then mount the former primary database

• After completion of this statement, the original primary is a standby database.
• You can see a graphical view of the switchover process here.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
...
Database mounted.

SQL> select name, db_unique_name, switchover_status, database_role from v$database;

NAME   DB_UNIQUE_NAME   SWITCHOVER_STATUS    DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DG11G   atlanta    TO PRIMARY       PHYSICAL STANDBY

(Step 4) Check that the switchover target is ready to be switched to the primary role

• After completion of this statement, the original standby is a primary database.

SQL> select name, db_unique_name, switchover_status, database_role from v$database;


NAME   DB_UNIQUE_NAME   SWITCHOVER_STATUS    DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DG11G   fresno    TO PRIMARY       PHYSICAL STANDBY

(Step 5) Switch the target physical standby database role to the primary role

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> select name, db_unique_name, switchover_status, database_role from v$database;

NAME   DB_UNIQUE_NAME   SWITCHOVER_STATUS    DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DG11G   fresno    NOT ALLOWED       PRIMARY

(Step 6) Open the new primary database

SQL> alter database open;

Database altered.

(Step 7) Start Redo Apply on the new physical standby database

• You can see a graphical view of the switchover process here.

SQL> select name, db_unique_name, switchover_status, database_role from v$database;

NAME   DB_UNIQUE_NAME   SWITCHOVER_STATUS    DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DG11G   atlanta    RECOVERY NEEDED      PHYSICAL STANDBY


SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

Q&As: Data Warehosing and Partitioning (I)

(Q) In which ways partitions facilitate administrative operations?

• Partitioning enable administrative operations to work on subsets of data.
• You can add, organize or drop a partition with minimal or zero interruption to read-only applications
• Adding a partition: you can defer segment creation, which is useful when the segment is very large
• Changing partition granularity: You can split or merge partitions.
• Swapping partitions: you can easily add, remove, or swap a large amount of data quickly
• Partitioning enhances data access and improve overall application performance:
• More users can query the system, since each SQL is likely to access smaller amounts of data
• Complex queries run faster. Less IO access

(Q) List three advantages of using partitioned objects

(1) Backup and recovery can be performed on a lower level of granularity (at the partition level)
(2) Part of the database objects can be placed in compressed storage, while others remain uncompressed
(3) You can transparently store data on different storage tiers to lower costs

(Q) How can the database take advantage of partitioning when executing an SQL?

• Using parallel executions to speed up queries, DML and DDL.
• Individual parallel execution servers can work on their own data sets, identified by partition boundaries

(Q) what is Partition pruning?

• When executing a SQL statement, the Optimizer analyzes FROM and WHERE clauses to eliminate unneeded partitions from the execution plan's access list.
• In the example below, the database scans only four partitions when executing the query. In an non-partitioned table, all data would have to be scanned.

(Q) What is the difference between static pruning and dynamic pruning?

• Static pruning - occurs at compile-time.
• example: SQL stmt with WHERE clause with a constant literal on the partition key column
• Dynamic pruning - occurs at run-time, when the exact partitions to be accessed are not known before hand
• example: SQL stmt with operator or function in the WHERE condition

(Q) How can you identify when static pruning is used in an execution plan?

• In the execution plan, the OPERATION column shows PARTITION RANGE SINGLE, indicating that only a single (or range of) partition(s) is being accessed.
• If OPERATION = PARTITION RANGE ALL, then ALL partitions are being accessed

select * from sales where time_id = to_date('01-jan-2001', 'dd-mon-yyyy');
... Returns 766 rows

explain plan for select * from sales where time_id = to_date('01-jan-2001', 'dd-mon-yyyy');
plan FOR succeeded

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 3733446669

---------------------------------------------------------------------------------------------------------------------  
| Id | Operation                          | Name           | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT                   |                |  766 | 22980 |    27   (0)| 00:00:01 |       |       |
|  1 |  PARTITION RANGE SINGLE            |                |  766 | 22980 |    27   (0)| 00:00:01 |    17 |    17 |
|  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |  766 | 22980 |    27   (0)| 00:00:01 |    17 |    17 |
|  3 |    BITMAP CONVERSION TO ROWIDS     |                |      |       |            |          |       |       |
|* 4 |     BITMAP INDEX SINGLE VALUE      | SALES_TIME_BIX |      |       |            |          |    17 |    17 |
--------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):                
----------------------------------------
   4 - access("TIME_ID"=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))      

 16 rows selected 

(Q) What types of SQL statements are candidates for partition pruning?

• On Range or List partitioned columns: When you use range, LIKE, equality, and IN-list predicates
• On Hash Partitioned columns: When you use equlity and IN-list predicates

(Q) How can datatype conversions affect the type of partition pruning that may be used?

• Datatype conversion typically lead to dynamic, when static pruning would have otherwise been possible
• Static pruning provides better performance than dynamic pruning
• example: Proper use of TO_DATE function guarantees that the database can uniquely determine the date value and thus the partition that should be accessed

Compare the following queries:
SELECT SUM(amount_sold) total_revenue FROM sh.sales
(clause 1)   WHERE time_id between '01-JAN-00' and '31-DEC-00' ;

(clause 2)   WHERE time_id between '01-JAN-2000' and '31-DEC-2000' ;

(clause 3)   WHERE time_id between to_date('01-JAN-2000', 'dd-MON-yyyy') and 
                                   to_date('31-DEC-2000', 'dd-MON-yyyy');

(Clause 1:) 
Leads to dynamic pruning, because date needs to be implicitly converted and does not match nls_date_format

SQL> explain plan for 
 SELECT SUM(amount_sold) total_revenue 
 FROM sales
 WHERE time_id between '01-JAN-00' and '31-DEC-00';

plan FOR succeeded.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 3772476237   

---------------------------------------------------------------------------------------------------- 
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | 
---------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT           |       |     1 |    13 |   592  (19)| 00:00:08 |       |       | 
|   1 |  SORT AGGREGATE            |       |     1 |    13 |            |          |       |       | 
|*  2 |   FILTER                   |       |       |       |            |          |       |       | 
|   3 |    PARTITION RANGE ITERATOR|       |   230K|  2932K|   592  (19)| 00:00:08 |   KEY |   KEY | 
|*  4 |     TABLE ACCESS FULL      | SALES |   230K|  2932K|   592  (19)| 00:00:08 |   KEY |   KEY | 
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
                                                  
   2 - filter(TO_DATE('01-JAN-00')<=TO_DATE('31-DEC-00'))
   4 - filter("TIME_ID">='01-JAN-00' AND "TIME_ID"<='31-DEC-00')

 17 rows selected 


(Clause 2:) 
Uses STATIC pruning, because although date needs to be implicitly converted is DOES match nls_date_format

SQL> explain plan for 
 SELECT SUM(amount_sold) total_revenue 
 FROM sales
 WHERE time_id between '01-JAN-2000' and '31-DEC-2000';

plan FOR succeeded.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT 
-------------------------------------------------------------------------------------------------
Plan hash value: 1500327972 

--------------------------------------------------------------------------------------------------- 
| Id  | Operation                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | 
--------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT          |       |     1 |    13 |   136  (11)| 00:00:02 |       |       | 
|   1 |  SORT AGGREGATE           |       |     1 |    13 |            |          |       |       | 
|   2 |   PARTITION RANGE ITERATOR|       |   230K|  2932K|   136  (11)| 00:00:02 |    13 |    16 | 
|*  3 |    TABLE ACCESS FULL      | SALES |   230K|  2932K|   136  (11)| 00:00:02 |    13 |    16 | 
--------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):  
---------------------------------------------------  

   3 - filter("TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 15 rows selected 

(Clause 3:) 
Uses STATIC pruning, because although date is explicitly converted

SQL> Explain plan for
SELECT SUM(amount_sold) total_revenue 
FROM sales
   WHERE time_id between to_date('01-JAN-2000', 'dd-MON-yyyy') and 
                         to_date('31-DEC-2000', 'dd-MON-yyyy');

plan FOR succeeded.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT 
-------------------------------------------
Plan hash value: 1500327972

---------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |       |     1 |    13 |   136  (11)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE           |       |     1 |    13 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|       |   230K|  2932K|   136  (11)| 00:00:02 |    13 |    16 |
|*  3 |    TABLE ACCESS FULL      | SALES |   230K|  2932K|   136  (11)| 00:00:02 |    13 |    16 |
---------------------------------------------------------------------------------------------------
                                                                                                  
Predicate Information (identified by operation id): 
--------------------------------------------------- 

   3 - filter("TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 15 rows selected