Oracle 18cR3 DBMS_FREQUENT

Oracle DBMS_FREQUENT_ITEMSET Version 18.3.0.1

General Information

Library Note

Morgan's Library Page Header

For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.

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

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 (new 18.1)

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 (new 18.1)

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 (new 18.1)

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

TBD