Oracle DBMS_FREQUENT_ITEMSET
Version 19.3

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
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 18c
What's New In 19c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx