Oracle DBMS_FREQUENT_ITEMSET
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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
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

Related Topics
Built-in Functions
Built-in Packages
Data Mining Functions
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx