| General Information |
| Purpose |
Enables frequent itemset counting |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsfi.sql |
| First Available |
2002 |
| 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 |
|
| Security Model |
Created with AUTHID CURRENT_USER. Execute is granted to PUBLIC. |
| 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_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)); |
|
