Oracle | DBMS_FREQUENT

Oracle DBMS_FREQUENT_ITEMSET Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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	ORA_FI_T
ITEMSETS	ORA_FI_RIMP_T

Documented Yes: Packages and Types Reference

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_TRANSACTIONAL_OUTER
FI_HORIZONTAL_INNER	FI_TRANSACTIONAL_INNER

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_HORIZONTAL_INNER

dbms_frequent_itemset.fi_horizontal_inner( 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 itemsets PIPELINED PARALLEL_ENABLE USING ora_fi_Imp_t;

TBD

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));

FI_TRANSACTIONAL_INNER

dbms_frequent_itemset.fi_transactional_inner( 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 itemsets PIPELINED PARALLEL_ENABLE USING ora_fi_Imp_t;

TBD

FI_TRANSACTIONAL_OUTER

dbms_frequent_itemset.fi_transactional_outer(cur IN sys_refcursor) RETURN sys.AnyDataSet PIPELINED USING ora_fi_t;

TBD