-- 
-- $Header: dbmssum.sql 02-jun-99.14:47:09 bpanchap Exp $
-- 
-- dbmssum.sql
-- 
--  Copyright (c) Oracle Corporation 1997, 1998, 1999. All Rights Reserved.
-- 
--    NAME
--      dbmssum.sql - PUBLIC interface FOR SUMMARY refresh
-- 
--    DESCRIPTION
--      defines specifification FOR packages dbms_summary
--   
-- 
--    NOTES
--      <other useful comments, qualifications, etc.>
-- 
--    MODIFIED   (MM/DD/YY)
--    bpanchap    06/02/99 - Adding user info
--    btao        04/23/99 - Disable anchorness
--    wnorcott    10/14/98 - Enable anchorness
--    ncramesh    08/10/98 - change for sqlplus
--    wnorcott    08/19/98 - Logging and set on/off qsmkganc
--    qiwang      08/07/98 - Rename verify_dimension to validate_dimension
--    sramakri    06/30/98 - Replace CREATE LIBRARY dbms_sumadv_lib with 
--                           @@dbmssml.sql
--    wnorcott    06/16/98 - procedure set_logfile_name
--    wnorcott    06/16/98 - get rid of set echo
--    ato         06/18/98 - remove set echo off to allow debugging
--    wnorcott    06/02/98 - Add DBMS_OLAP synonym
--    qiwang      05/28/98 - Add interface for Verify Dimension
--    wnorcott    05/22/98 - change specification for compute_variance, compute
--    wnorcott    05/18/98 - Required changes to refresh interface
--    wnorcott    04/09/98 - Move private interfaces to prvtsum.sql
--    wnorcott    04/03/98 - set_session_longops changed without warning
--    wnorcott    04/22/98 - Add on/off switch for cleanup_sumdelta
--    sramakri    04/08/98 - Summary Advisor functions
--    wnorcott    04/07/98 - procedure for nullness in stat functions
--    wnorcott    04/03/98 - set_session_longops changed without warning
--    wnorcott    02/21/98 - add refresh_mask output to qsmkrfx
--    wnorcott    02/12/98 - Add Refresh_in_C
--    wnorcott    02/04/98 - Add 3gl for callout to kprb
--    wnorcott    01/28/98 - Move qsmkanc out of ICD vector
--    wnorcott    01/20/98 - New ICD to test for anchorness
--    wnorcott    12/31/97 - make anchorlist a package global
--    wnorcott    12/30/97 - rename a couple procedures for clarity
--    wnorcott    12/23/97 - Split refresh into 3 packages in the same file
--    wnorcott    11/17/97 - Add entry for qsmkscn
--    wnorcott    10/16/97 - PUBLIC interface for summary refresh.
--    wnorcott    10/16/97 - Created
-- 
CREATE OR REPLACE PACKAGE dbms_summary                                        
  /*                                                                            
 || Program: dbms_summary                                                      
 ||  Author: William D. Norcott, Oracle Corportation
 ||    File: dbmssum.sql                                                  
 || Created: September 11, 1997 15:11:36                                       
 */                                                                            
IS                                                                            
-- Package global variables
c_refresh NUMBER := 0;
cleanup_enabled NUMBER := 1;
number_failures binary_integer := 0;
dimensionnotfound EXCEPTION;
anchorness_optimization NUMBER := 0;
--------------------------------------------------------------------------
--  PROCEDURE DBMS_SUMMARY.REFRESH
--  
--  Purpose: To refresh a list of table-based summaries as a group.
--  
--  PROCEDURE DBMS_SUMMARY.REFRESH_ALL
--  
--  Purpose To refresh all table-based summaries that have not been
--  refreshed since the most recent bulk load to a dependent detail table.
--  
--  PROCEDURE DBMS_SUMMARY.REFRESH_DEPENDENT
--  
--  Purpose To refresh all table-based summaries that depend on a
--  specified detail table.  
--  
--                          PARAMETERS
--  
--  
--  list
--  
--  A comma-separated list or DBMS_UTILITY.uncl_array of the summaries to
--  be refreshed.
--  
--  method
--  
--  `ANY' - use the default refresh method, or if the default refresh
--  method is ANY, let Oracle choose incremental or full refresh,
--  whichever is expected to be most efficient.
--  
--  `FORCE_FULL' - use the full refresh method unless the default refresh
--  method is NONE, whether or not the summary appears to need to be re-
--  freshed.
--  
--  Summaries with default refresh method NONE are always ignored.
--  
--  refresh_after_errors
--  
--  If TRUE, then allow refresh to proceed to completion even if an error
--  is raised during a refresh operation, otherwise abort immediately and
--  return an error code.
--  
--                  EXCEPTION DESCRIPTION
--  
--  badlistformat
--  
--  The list parameter is not a comma separated list or
--  DBMS_UTILITY.uncl_array.
--  
--  badoption
--  
--  Unrecognized refresh method, or the maximum parallelism specified was
--  not a positive integer
--  
--  mustfullyrefresh
--  
--  Incremental refresh was not possible.  Incremental refresh was
--  specified indi- rectly by setting the method parameter to ANY and
--  setting the default refresh method of a summary to INCREMENTAL with
--  ALTER SUMMARY.
--  
--  privilegeviolation
--  
--  Insufficient privilege to perform the requested operation.
--  
--  outoftempspace
--  
--  Insufficient temporary space to perform refresh
--  
--  errorsoccurred
--  
--  refresh_after_errors was set to TRUE, and one or more errors occurred
--  during the operation.
--  
--  unsupportedoperation
--  
--  incremental refresh is not supported for this expression
--  
--  unabletocreatepipe
--  
--  was unable to create communications pipe used in refresh
--  
--  pipeioerror
--  
--  an I/O error occurred on the pipe used for summary refresh
--  
--  nosummaries
--  
--  an empty list of summaries, or list contains no summaries
--  
--  norefresh
--  
--  none of the items in list were summaries in need of refresh
--
--------------------------------------------------------------------------
--  
--  PROCEDURE DBMS_SUMMARY.PURGE_ROWID_RANGES
--  
--  Purpose To provide an interface to allow ROWID range entries to be
--  purged from SUMDELTA$ when those entries are no longer required.  This
--  is typically the case after all summaries or snapshots have been
--  refreshed, that depend upon those en- tries.
--  
--  
--  This interface is used by two products: summary management and
--  snapshots.  The function deletes any ROWID range entries for that
--  table from SUMDEL- TA$, that happen to be `older' than the oldest
--  refresh SCN or data recorded in ALL_REFRESH_DEPENDENCIES. Correct
--  operation requires that the ALL_REFRESH_DEPENDENCIES must return the
--  correct result for both summaries and snapshots.
--  
--  
--  
--                  PARAMETER DESCRIPTION
--  
--  owner
--  
--  The schema name of the owner of the table.
--  
--  table_name
--  
--  The name of  table for which to purge ROWID range entries from SUMDELTA$.  
--  
--  table_role
--  
--  The role of this table: the table is either a detail table for a
--  summary or snapshot, a summary container table, or a snapshot
--  container table:
--  
--      1 = ordinary detail table for a summary or snapshot (DEFAULT)
--  
--      2 = the container table for a summary
--  
--      3 = the container table for a snapshot
--  
--  parent_object_type
--  
--  The role of this table: the table is either a detail table for a
--  summary or snapshot, a summary container table, or a snapshot
--  container table:
--  
--      1 = summary  (DEFAULT)
--  
--      2 = snapshot
--  
--      3 = other
--  
--                  EXCEPTION DESCRIPTION
--  
--  badinput
--  
--  Invalid inputs were passed to the function
--  
--  privilegeviolation
--  
--  Insufficient privilege to perform the requested operation.
--  
--  nodependencyinfo
--  
--  There was no dependency information in ALL_REFRESH_DEPENDENCIES
--  
--  

/*                                                                    
 Examples of Package Usage                                                     
   */  

-- Enable/disable performance optimization for refresh with multiple fact tables
-- Has no effect on summaries having only one fact table.
PROCEDURE optimize_fact_tables(flag IN BOOLEAN);
-- Sets the name of the refresh logfile
PROCEDURE set_logfile_name(filename IN VARCHAR2);

-- Scalar computation of statistical aggregates
--
--  Compute average, allowing for null values and zero count
--
function compute_avg(
                     fullsum_avg IN NUMBER,
                     fullsum_count IN NUMBER,
                     deltasum_avg IN NUMBER,
                     deltasum_count IN NUMBER)
                     RETURN NUMBER;

PRAGMA RESTRICT_REFERENCES (compute_avg, WNDS , WNPS , RNDS , RNPS); 
function compute_avg2(
                      fullsum_sum IN NUMBER,
                      fullsum_count IN NUMBER,
                      deltasum_sum IN NUMBER,
                      deltasum_count IN NUMBER)
                      RETURN NUMBER;

PRAGMA RESTRICT_REFERENCES (compute_avg2, WNDS , WNPS , RNDS , RNPS); 
--
--  Compute variance
--
function compute_variance(
                          fullsum_variance IN NUMBER,
                          fullsum_sum IN NUMBER,
                          fullsum_count IN NUMBER,
                          deltasum_variance IN NUMBER,
                          deltasum_sum IN NUMBER,
                          deltasum_count IN NUMBER)
                          RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (compute_variance, WNDS , WNPS , RNDS , RNPS); 

--
--  Compute stddev
--
function compute_stddev(
                        fullsum_stddev IN NUMBER,
                        fullsum_sum IN NUMBER,
                        fullsum_count IN NUMBER,
                        deltasum_stddev IN NUMBER,
                        deltasum_sum IN NUMBER,
                        deltasum_count IN NUMBER)
                        RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (compute_stddev, WNDS , WNPS , RNDS , RNPS); 


PROCEDURE refresh_in_c (enable IN BOOLEAN := TRUE);

PROCEDURE enable_sumdelta_cleanup (enable IN BOOLEAN := TRUE);

PROCEDURE refresh (
                   list         		IN VARCHAR2,
                   method 			IN VARCHAR2 := 'ANY',
  refresh_after_errors 	IN BOOLEAN := FALSE);

PROCEDURE refresh (
                   tab                         IN dbms_utility.uncl_array,
                   method 			IN VARCHAR2 := 'ANY',
  refresh_after_errors 	IN BOOLEAN := FALSE);

PROCEDURE refresh_all (
                       method 			IN VARCHAR2 := 'ANY',
  refresh_after_errors 	IN BOOLEAN := FALSE);


PROCEDURE refresh_dependent (
                             list 	IN VARCHAR2,
                             method			IN VARCHAR := 'ANY',
  refresh_after_errors	IN BOOLEAN := FALSE);

PROCEDURE refresh_dependent 
  (
   tab                         IN dbms_utility.uncl_array,
   method			IN VARCHAR := 'ANY',
  refresh_after_errors	IN BOOLEAN := FALSE);


FUNCTION purge_rowid_ranges (
                             owner 			IN VARCHAR2,
                             table_name 		IN VARCHAR2,
                             table_role 		IN NUMBER := 1,
  parent_object_type	IN NUMBER := 1) 
                             RETURN NUMBER;

PROCEDURE  cleanup_sumdelta;

/* Required changes to refresh interface */

PROCEDURE refresh_api(
                  list                        IN VARCHAR2,
                  method                      IN VARCHAR2       := NULL,
rollback_seg                IN VARCHAR2       := NULL,                                    
push_deferred_rpc           IN BOOLEAN        := TRUE,
refresh_after_errors        IN BOOLEAN        := FALSE,
purge_option                IN BINARY_INTEGER := 1,
parallelism                 IN BINARY_INTEGER := 0,
heap_size                   IN BINARY_INTEGER := 0,
atomic_refresh              IN BOOLEAN        := TRUE,
  -- Fixing Bug # 902392
user_id                     IN NUMBER         := uid() 
                 );

PROCEDURE refresh_api(
                  tab                         IN dbms_utility.uncl_array,
                  method                      IN VARCHAR2       := NULL,
  rollback_seg                IN VARCHAR2       := NULL,                                    
  push_deferred_rpc           IN BOOLEAN        := TRUE,
  refresh_after_errors        IN BOOLEAN        := FALSE,
  purge_option                IN BINARY_INTEGER := 1,
  parallelism                 IN BINARY_INTEGER := 0,
  heap_size                   IN BINARY_INTEGER := 0,
  atomic_refresh              IN BOOLEAN        := TRUE,
  -- Fixing Bug # 902392
  user_id                     IN NUMBER         := uid() 
                 );

PROCEDURE refresh_dependent_api (
                             number_of_failures        OUT BINARY_INTEGER,
                             tab                       IN dbms_utility.uncl_array,
                             method                    IN VARCHAR2           := NULL,
  rollback_seg              IN VARCHAR2       := NULL,
  refresh_after_errors      IN BOOLEAN        := FALSE,
  atomic_refresh            IN BOOLEAN        := TRUE
                            );

PROCEDURE refresh_dependent_api (
                             number_of_failures        OUT BINARY_INTEGER,
                             list                      IN VARCHAR2,
                             method                    IN VARCHAR2           := NULL,
  rollback_seg              IN VARCHAR2       := NULL,
  refresh_after_errors      IN BOOLEAN        := FALSE,
  atomic_refresh            IN BOOLEAN        := TRUE
                            );
-- master tables are all local.
PROCEDURE refresh_all_mviews_api (
                              number_of_failures        OUT BINARY_INTEGER,
                              method                    IN VARCHAR2           := NULL,
  rollback_seg              IN VARCHAR2       := NULL,
  refresh_after_errors      IN BOOLEAN        := FALSE,
  atomic_refresh            IN BOOLEAN        := TRUE
  );

 /* Interface for the Validate_Dimension */
   
 /* PROCEDURE DBMS_SUMMARY.VALIDATE_DIMENSION
   
    PURPOSE: To verify that the relationships specified in a DIMENSION
             are correct.
   
    PARAMETERS:
   
        dimension_name: VARCHAR2
   
           name of the dimension to analyze
   
        dimension_owner: VARCHAR2
   
           owner of the dimension
   
        incremental: BOOLEAN (default: TRUE)
      
           if TRUE, then tests are performed only for the rows specified
           in the sumdelta$ table for tables of this dimension; if FALSE,
           check all rows.
   
        check_nulls: BOOLEAN (default: FALSE)
   
           if TRUE, then all level columns are verified to be non-null;
           if FALSE, this check is omitted. Specify FALSE when non-nullness
           is guaranteed by other means, such as NOT NULL constraints.
   
    EXCEPTIONS:
   
        dimensionnotfound
   
           The specified dimension was not found
 */  
   
 PROCEDURE validate_dimension
             (
               dimension_name           IN VARCHAR2,
               dimension_owner          IN VARCHAR2,
               incremental              IN BOOLEAN := TRUE,
               check_nulls              IN BOOLEAN := FALSE);
     

/* Interface for the Summary Advisor */

PROCEDURE recommend_summaries ( 
                               fact_table_filter IN VARCHAR2, 
                               storage_in_bytes  IN NUMBER, 
                               retention_list    IN VARCHAR2, 
                               retention_pct     IN NUMBER := 50); 

PROCEDURE recommend_summaries_w ( 
                                 fact_table_filter IN VARCHAR2, 
                                 storage_in_bytes  IN NUMBER, 
                                 retention_list    IN VARCHAR2, 
                                 retention_pct     IN NUMBER := 80); 

PROCEDURE recommend_mv       ( 
                               fact_table_filter IN VARCHAR2, 
                               storage_in_bytes  IN NUMBER, 
                               retention_list    IN VARCHAR2, 
                               retention_pct     IN NUMBER := 50); 

PROCEDURE recommend_mv_w ( 
                                 fact_table_filter IN VARCHAR2, 
                                 storage_in_bytes  IN NUMBER, 
                                 retention_list    IN VARCHAR2, 
                                 retention_pct     IN NUMBER := 80); 

PROCEDURE evaluate_utilization; 

PROCEDURE evaluate_utilization_w; 

PROCEDURE estimate_summary_size ( 
                                 stmt_id         IN VARCHAR2,
                                 select_clause   IN VARCHAR2,
                                 num_rows        OUT NUMBER,
                                 num_bytes       OUT NUMBER);

PROCEDURE enable_dependent ( 
                            detail_tables      IN  VARCHAR2); 

PROCEDURE disable_dependent ( 
                             detail_tables      IN  VARCHAR2); 

END dbms_summary;                                                             
/                                                                             
GRANT EXECUTE ON dbms_summary TO PUBLIC;
DROP PUBLIC SYNONYM dbms_summary;
CREATE PUBLIC SYNONYM dbms_summary FOR dbms_summary;
DROP PUBLIC SYNONYM dbms_olap;
CREATE PUBLIC SYNONYM dbms_olap FOR dbms_summary;
Rem The following line:
Rem CREATE OR REPLACE LIBRARY dbms_sumadv_lib AS '/vobs/rdbms/lib/libqsmashr.so';
Rem now comes from dbmssml.sql, which is generated from osds/dbmssml.sbs
@@dbmssml.sql
/

