Oracle 12c DBMS_FREQUENT

Oracle DBMS_FREQUENT_ITEMSET Version 12.1.0.1

General Information

Library Note

Morgan's Library Page Footer

The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Purpose

Enables frequent itemset counting

AUTHID

CURRENT_USER

Data Types

CREATE OR REPLACE TYPE ora_fi_Imp_t AS OBJECT (
dummy NUMBER,
STATIC FUNCTION ODCITableDescribe(typ OUT SYS.AnyType, cur SYS_REFCURSOR) RETURN PLS_INTEGER IS
LANGUAGE C
LIBRARY ora_fi_lib
NAME "ODCITableDescribe"
WITH CONTEXT
PARAMETERS (CONTEXT, typ, typ INDICATOR, cur, cur TDO, RETURN INT));
/

Dependencies

ANYDATASET

ORA_FI_IMP_T

Documented

Yes

First Available

2002

Security Model

Owned by SYS with EXECUTE granted to PUBLIC

Source

{ORACLE_HOME}/rdbms/admin/dbmsfi.sql

Subprograms

FI_HORIZONTAL

FI_TRANSACTIONAL

FI_HORIZONTAL

Count all frequent itemsets given a cursor for input data which is in 'HORIZONTAL' row format, support threshold, minimum itemset length, maximum itemset length, items to be included, items to be excluded. The result will be a table of rows in form of itemset, support, length, total transactions counted.

dbms_frequent_itemset.fi_horizontal(
tranx_cursor       IN SYS_REFCURSOR,
support_threshold IN NUMBER,
itemset_length_min IN NUMBER,
itemset_length_max IN NUMBER,
including_items    IN SYS_REFCURSOR DEFAULT NULL,
excluding_items    IN SYS_REFCURSOR DEFAULT NULL)
RETURN SYS.AnyDataSet PIPELINED PARALLEL_ENABLE USING ora_fi_Imp_t;

SELECT si_status, type, installstatus, COUNT(*)
FROM serv_inst
WHERE si_status IN ('Activated', 'Pending', 'Disconnected')
AND type NOT IN ('MAC')
GROUP BY si_status, type, installstatus;

CREATE OR REPLACE TYPE fi_varchar_t AS TABLE OF VARCHAR2(30);
/

SELECT CAST(itemset AS fi_varchar_t)itemset,support,length,total_tranx
FROM TABLE(dbms_frequent_itemset.fi_horizontal(
CURSOR(SELECT si_status, CAST(type AS VARCHAR2(30)),
         CAST(installstatus AS VARCHAR2(30))
         FROM serv_inst), 0.1, 2, 5,
CURSOR(SELECT *
FROM TABLE(fi_varchar_t ('Activated','Pending','Disconnected'))),
CURSOR(SELECT *
FROM table(FI_VARCHAR_T('MAC')))));

SELECT CAST(itemset AS fi_varchar_t)itemset,support, length,total_tranx
FROM TABLE(dbms_frequent_itemset.fi_horizontal(
CURSOR(SELECT si_status, CAST(type AS VARCHAR2(30)),
         CAST(installstatus AS VARCHAR2(30))
         FROM serv_inst), 0.2, 2, 5,
CURSOR(SELECT *
FROM TABLE(fi_varchar_t ('Activated','Pending','Disconnected'))),
CURSOR(SELECT *
FROM table(FI_VARCHAR_T('MAC')))));

SELECT CAST(itemset AS fi_varchar_t)itemset,support, length,total_tranx
FROM TABLE(dbms_frequent_itemset.fi_horizontal(
CURSOR(SELECT si_status, CAST(type AS VARCHAR2(30)),
         CAST(installstatus AS VARCHAR2(30))
         FROM serv_inst), 0.3, 2, 5,
CURSOR(SELECT *
FROM TABLE(fi_varchar_t ('Activated','Pending','Disconnected'))),
CURSOR(SELECT *
FROM table(FI_VARCHAR_T('MAC')))));

FI_TRANSACTIONAL

Counts all frequent itemsets given a cursor for input data which is in 'TRANSACTIONAL' row format, support threshold, minimum itemset length, maximum itemset length, items to be included, items to be excluded. The result will be a table of rows in form of itemset, support, length, total number of transactions.

dbms_frequent_itemset.fi_transactional(
tranx_cursor       IN SYS_REFCURSOR,
support_threshold IN NUMBER,
itemset_length_min IN NUMBER,
itemset_length_max IN NUMBER,
including_items    IN SYS_REFCURSOR DEFAULT NULL,
excluding_items    IN SYS_REFCURSOR DEFAULT NULL)
RETURN SYS.AnyDataSet PIPELINED PARALLEL_ENABLE USING ora_fi_Imp_t;

SELECT si_status, type, installstatus, COUNT(*)
FROM serv_inst
WHERE si_status IN ('Activated', 'Pending', 'Disconnected')
AND type NOT IN ('MAC')
GROUP BY si_status, type, installstatus;

CREATE OR REPLACE TYPE fi_varchar_t AS TABLE OF VARCHAR2(30);
/

SELECT CAST(itemset AS fi_varchar_t) itemset, support, length, total_tranx
FROM TABLE(dbms_frequent_itemset.fi_transactional(
CURSOR(SELECT city_name, CAST(state_abbrev AS VARCHAR2(30))
FROM postal_code), 0.005, 2, 2, NULL, NULL));

SELECT CAST(itemset AS fi_varchar_t) itemset, support, length, total_tranx
FROM TABLE(dbms_frequent_itemset.fi_transactional(
CURSOR(SELECT city_name, CAST(state_abbrev AS VARCHAR2(30))
FROM postal_code), 0.0075, 2, 2, NULL, NULL));

SELECT CAST(itemset AS fi_varchar_t) itemset, support, length, total_tranx
FROM TABLE(dbms_frequent_itemset.fi_transactional(
CURSOR(SELECT city_name, CAST(state_abbrev AS VARCHAR2(30))
FROM postal_code), 0.009, 2, 2, NULL, NULL));

SELECT CAST(itemset AS fi_varchar_t) itemset, support, length, total_tranx
FROM TABLE(dbms_frequent_itemset.fi_transactional(
CURSOR(SELECT city_name, CAST(state_abbrev AS VARCHAR2(30))
FROM postal_code), 0.01, 2, 2, NULL, NULL));