| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/exfpbs.sql |
| Demo |
{ORACLE_HOME}/rdbms/demo/exfdemo.sql |
Dependencies |
| ALL_COL_TYPES |
DBMS_EXPFIL_DR
|
EXF$EXPISVALID
|
| ALL_INDEXES
|
DBMS_EXPFIL_IR
|
EXF$INDEXOPER
|
| ALL_OBJECTS
|
DBMS_EXPFIL_UTL
|
EXF$TABLE_ALIAS
|
| ALL_SYNONYMS
|
DBMS_OUTPUT
|
EXF$TEXT
|
| ALL_TAB_COLUMNS
|
DBMS_RLM4J_DICTMAINT
|
EXF$XPATH_TAGS
|
| ALL_TYPES
|
DBMS_RLMGR
|
NAMELIST
|
| ALL_TYPE_ATTRS
|
DBMS_RLMGR_IR
|
PLITBLM
|
| ALL_TYPE_VERSIONS
|
EXF$ATTRIBUTE_LIST
|
PUBLIC_DEPENDENCY
|
| ALL_USERS
|
EXF$CURRUSER
|
USER_TAB_COLUMNS
|
| DBMS_ASSERT
|
EXF$EXPFUNCREFS
|
USER_TYPE_ATTRS
|
| ALL_EXPFIL_ASET_FUNCTIONS |
Functions and packages approved for the attribute
set |
| ALL_EXPFIL_ATTRIBUTES |
Elementary attributes of the attribute set |
| ALL_EXPFIL_ATTRIBUTE_SETS |
Attribute set |
| ALL_EXPFIL_DEF_INDEX_PARAMS |
Default index parameters |
| ALL_EXPFIL_EXPRESSION_SETS |
Expression sets |
| ALL_EXPFIL_EXPRSET_STATS |
Predicate statistics for the expression sets |
| ALL_EXPFIL_INDEX_PARAMS |
Index parameters assigned to the expression set |
| ALL_EXPFIL_INDEXES |
Expression filter indexes |
| ALL_EXPFIL_PREDTAB_ATTRIBUTES |
Stored and indexed attributes for the indexes |
| USER_EXPFIL_PRIVILEGES |
Expression privileges of the current user |
|
| Evaluate Operator |
EVALUATE (expression_column, <dataitem>) |
| See expression filtering demo |
| Security Model |
execute is granted to PUBLIC. Runs
under AUTHID CURRENT_USER. |
| |
| ADD_ELEMENTARY_ATTRIBUTE
(new 11g parameter and overload ?) |
Adds the specified attribute to the attribute set
Overload 1 |
dbms_expfil.add_elementary_attribute (
attr_set IN VARCHAR2,
-- attribute set name
attr_name IN VARCHAR2,
-- attribute name
attr_type IN
VARCHAR2,
-- attribute type
attr_defvl IN VARCHAR2 DFEAULT NULL);
-- default value for attr |
exec
dbms_expfil.create_attribute_set('UW_Set');
desc user_expfil_attribute_sets
SELECT *
FROM user_expfil_attribute_sets;
set linesize 121
col data_type format a30
SELECT attribute_set_name, attribute, data_type
FROM user_expfil_attributes
WHERE attribute_set_name = 'UW_Set';
exec dbms_expfil.add_elementary_attribute('UW_Set',
'PCP_NAME', 'VARCHAR2(30)');
SELECT attribute_set_name, attribute, data_type
FROM user_expfil_attributes
WHERE attribute_set_name = 'UW_Set';
exec dbms_expfil.add_elementary_attribute
('UW_Set', 'PCP_ID',
'NUMBER(5)');
SELECT attribute_set_name, attribute, data_type
FROM user_expfil_attributes
WHERE attribute_set_name = 'UW_Set';
|
|
Overload 2 |
dbms_expfil.add_elementary_attribute (
attr_set IN VARCHAR2,
-- attribute set name
attr_name IN VARCHAR2, -- table alias (name)
tab_alias IN exf$table_alias); -- table alias for |
| TBD |
|
Overload 3 |
dbms_expfil.add_elementary_attribute (
attr_set IN VARCHAR2, -- attribute set name
attr_name IN VARCHAR2, -- table alias (name)
attr_type IN VARCHAR2, -- attr type
text_pref IN exf$text); -- table alias for |
| TBD |
| |
| ADD_FUNCTIONS |
Adds a user-defined function, package, or type representing a set of functions to the attribute set |
dbms_expfil.add_functions (
attr_set IN VARCHAR2, -- attribute set name
funcs_name IN VARCHAR2); -- function/package/type name |
CREATE OR REPLACE FUNCTION
age_val (valin NUMBER) RETURN INTEGER IS
BEGIN
RETURN TRUNC(valin);
END age_val;
/
set linesize 121
col attribute_set_name format a20
col udf_name format a15
col object_owner format a20
col object_name format a15
col object_type format 15
SELECT *
FROM user_expfil_aset_functions;
exec dbms_expfil.add_functions('CBC', 'AGE_VAL');
SELECT *
FROM user_expfil_aset_functions; |
| |
| ASSIGN_ATTRIBUTE_SET |
Assigns an attribute set to a VARCHAR2 column in a user table to create an Expression column |
dbms_expfil.assign_attribute_set (
attr_set IN VARCHAR2,
-- attribute set name
expr_tab IN VARCHAR2,
-- name of the table
expr_col IN VARCHAR2,
-- exp column in the table
force IN VARCHAR2 DEFAULT 'FALSE') -- use existing expressions |
CREATE TABLE cbc_watch (
watch_id NUMBER(10),
watch VARCHAR2(160));
exec dbms_expfil.assign_attribute_set('CBC', 'CBC_WATCH', 'WATCH');
set linesize 121
col exsowner format a20
col exstabnm format a20
col exscolnm format a20
col exsatsnm format a20
col exsprvtrig format a20
SELECT exsowner, exstabnm, exscolnm, exsatsnm, exsprvtrig
FROM exfsys.exf$exprset; |
| |
| BUILD_EXCEPTIONS_TABLE |
| Create exception table used in filter
validation |
dbms_expfil.build_exceptions_table (exception_tab IN VARCHAR2); |
exec dbms_expfil.build_exceptions_table('ExpFilExceptions');
SELECT table_name
FROM user_tables;
desc ExpFilExceptions |
| |
| CLEAR_EXPRSET_STATS |
| Clears the predicate statistics for the expression set stored in a table column |
dbms_expfil.clear_exprset_stats (
expr_tab IN VARCHAR2, -- table storing expression set
expr_col IN VARCHAR2); -- column in the table with set |
| exec dbms_expfil.clear_exprset_stats('CBC_WATCH',
'WATCH'); |
| |
| COPY_ATTRIBUTE_SET |
| Copies an attribute set along with its user-defined function list and default index parameters to another set |
dbms_expfil.copy_attribute_set (
from_set IN VARCHAR2, -- name of an existing attribute set
to_set IN VARCHAR2); -- new set name |
| exec dbms_expfil.copy_attribute_set('CBC', 'CBC_NEW'); |
| |
| CREATE_ATTRIBUTE_SET |
Creates an empty attribute set or an attribute set with a complete set of elementary attributes derived from an object type with a matching name |
dbms_expfil.create_attribute_set (
attr_set IN VARCHAR2,
-- attribute set name
from_type IN VARCHAR2 DEFAULT 'NO'); -- options YES | NO |
CREATE OR REPLACE TYPE cbc AS
OBJECT (
machine_id VARCHAR2(10),
pid NUMBER,
gender VARCHAR2(1),
age NUMBER,
gbc NUMBER,
hmo NUMBER,
rus NUMBER,
mit NUMBER,
smoker VARCHAR2(1),
risk NUMBER);
/
desc user_expfil_attribute_sets
SELECT *
FROM user_expfil_attribute_sets;
exec dbms_expfil.create_attribute_set('CBC', 'YES');
SELECT *
FROM user_expfil_attribute_sets;
desc user_expfil_attributes
SELECT attribute_set_name, attribute, data_type
FROM user_expfil_attributes;
|
| |
| DEFAULT_INDEX_PARAMETERS |
| Assigns default index parameters to an attribute
set and adds or drops a partial list of stored and indexed attributes to or from the default list associated with the attribute
list |
exec dbms_expfil.default_index_parameters (
attr_set IN VARCHAR2,
-- attribute set name
attr_list IN EXF$ATTRIBUTE_LIST, -- stored & indexed attrs
operation IN VARCHAR2 DEFAULT 'ADD'); -- to ADD or DROP |
| TBD |
| |
| DEFAULT_XPINDEX_PARAMETERS |
| Adds (or drops) a partial list of XPath parameters to the default index parameters associated with the attribute set |
exec dbms_expfil.default_xpindex_parameters (
attr_set IN VARCHAR2, --
attribute
set
xmlt_attr IN VARCHAR2, --
XMLType
attribute name
xptag_list IN EXF$XPATH_TAGS, -- common XPath expr elements/attributes
operation IN VARCHAR2 DEFAULT 'ADD'); -- to ADD/DROP |
| See demo at tahiti.oracle.com |
| |
| DEFRAG_INDEX |
| Rebuilds the bitmap indexes online and thus reduces the fragmentation |
exec dbms_expfil.defrag_index(idx_name IN VARCHAR2);
|
| TBD |
| |
| DROP_ATTRIBUTE_SET |
| Drops an attribute set not being used for
an expression set |
exec dbms_expfil.drop_attribute_set(attr_set IN VARCHAR2); --
set name
|
SELECT * FROM
user_expfil_attribute_sets;
exec dbms_expfil.drop_attribute_set('CBC');
SELECT * FROM user_expfil_attribute_sets;
|
| |
| GET_EXPRSET_STATS |
| Computes the predicate statistics for an expression set and stores them in the expression filter dictionary |
exec dbms_expfil.get_exprset_stats (
expr_tab IN VARCHAR2, -- table storing expression set
expr_col IN VARCHAR2); -- column in the table with set
|
| exec dbms_expfil.get_exprset_stats('CBC_WATCH', 'WATCH'); |
| |
| GRANT_PRIVILEGE |
Grants privileges on one or more Expression columns to
users |
exec dbms_expfil.grant_privilege (
expr_tab IN VARCHAR2, -- table w/ the expr column
expr_col IN VARCHAR2, -- column storing the expressions
priv_type IN VARCHAR2, -- type of priv to be granted
to_user IN VARCHAR2); -- user to which the priv is granted
Privilege Types: INSERT EXPRESSION, UPDATE EXPRESSION, ALL
|
exec dbms_expfil.grant_privilege('CBC_WATCH', 'WATCH', 'ALL', 'PSOUG');
desc exfsys.exf$expsetprivs
set linesize 121
col esowner format a15
col esexptab format a15
col esexpcol format a15
col esgrantee format a15
SELECT * FROM exfsys.exf$expsetprivs; |
| |
| INDEX_PARAMETERS |
| Fine-tunes the index parameters for each expression set before index creation |
exec dbms_expfil.index_parameters (
expr_tab IN VARCHAR2,
-- expression set table
expr_col IN VARCHAR2,
-- expression set column
attr_list IN EXF$ATTRIBUTE_LIST DEFAULT NULL,
operation IN VARCHAR2 DEFAULT 'ADD') -- options
ADD/DROP/CLEAR
|
| exec dbms_expfil.index_parameters('CBC_WATCH',
'WATCH', |
| |
| MODIFY_OPERATOR_LIST |
| Undocumented |
exec dbms_expfil.modify_operator_list (
attr_set IN VARCHAR2, -- attribute set name
attr_name IN VARCHAR2, -- attribute to be modified
attr_oper IN EXF$INDEXOPER); -- list of new operators
|
| The developer
advises that it not be used. |
| |
| REVOKE_PRIVILEGE |
| Revokes an expression privilege previously granted by the owner |
exec dbms_expfil.revoke_privilege (
expr_tab IN VARCHAR2, -- table with the expr column
expr_col IN VARCHAR2, -- column storing the expressions
priv_type IN VARCHAR2, -- type of privilege to be granted
from_user IN VARCHAR2); -- revoke privilege from username
|
| exec dbms_expfil.revoke_privilege('CBC_WATCH', 'WATCH', 'ALL', 'PSOUG'); |
| |
| SYNC_TEXT_INDEXES (new
in 11g ?) |
| Undocumented |
exec dbms_expfil.sync_text_indexes(expr_tab IN VARCHAR2);
|
| TBD |
| |
| UNASSIGN_ATTRIBUTE_SET |
| Unassigns an attribute set from a column storing expressions |
exec dbms_expfil.unassign_attribute_set (
expr_tab IN VARCHAR2, -- table with expr. column
expr_col IN VARCHAR2); -- column storing expr. set
|
| exec dbms_expfil.unassign_attribute_set('CBC_WATCH', 'WATCH'); |
| |
| VALIDATE_EXPRESSIONS |
| Validates the expressions in a set |
exec dbms_expfil.validate_expressions (
expr_tab IN VARCHAR2,
-- expressions table
expr_col IN VARCHAR2,
-- column storing expressions
exception_tab IN VARCHAR2 DEFAULT NULL); -- exception table
|
| exec dbms_expfil.validate_expressions('CBC_WATCH', 'WATCH',
'ExpFilExceptions'); |
| |
| XPINDEX_PARAMETERS |
| Used in conjunction with the INDEX_PARAMETERS procedure to fine-tune the XPath-specific index parameters for each expression set |
exec dbms_expfil.xpindex_parameters (
expr_tab IN VARCHAR2, -- expression set table
expr_col IN VARCHAR2,
-- expression set column
xmlt_attr IN VARCHAR2, -- XMLType attribute name
xptag_list IN EXF$XPATH_TAGS, -- common xpath
expr elements/attributes
operation IN VARCHAR2 DEFAULT 'ADD'); -- to ADD/DROP default
ADD
|
| See demo at tahiti.oracle.com |
| |
| Demo |
Expression Filtering Demo |
| Definition |
Abbrev. |
F Rng |
M Rng |
| Hematocrit |
HCT |
37 - 47 |
40 - 54 |
| Hemoglobin |
HGB |
12 - 16 |
14 - 18 |
| Red Blood Cell Count |
RBC |
3.9 - 5.2 |
4.2 - 5.6 |
-- create attribute set
CREATE OR REPLACE TYPE bchem AS
OBJECT (
gender VARCHAR2(1),
hct NUMBER,
hgb NUMBER,
rbc FLOAT(126));
/
exec dbms_expfil.create_attribute_set('BCHEM', 'YES');
-- add function ?
-- create table
CREATE TABLE test_rng (
test_name VARCHAR2(20),
test_abbrev VARCHAR2(3),
rule_set VARCHAR2(250));
-- assign attribute set to result column
-- converting it into an expression column
exec dbms_expfil.assign_attribute_set('BCHEM', 'TEST_RNG', 'RULE_SET');
INSERT INTO test_rng VALUES ('Anemic', '3', 'Gender=''F'' AND HCT < 37 AND HGB < 12 AND RBC < 3.9');
INSERT INTO test_rng VALUES ('Less Anemic','2', 'Gender=''F'' AND HCT < 39 AND HGB < 13 AND RBC < 4.5');
INSERT INTO test_rng VALUES ('VS Anemic', '1', 'Gender=''F'' AND HCT < 41 AND HGB < 15 AND RBC < 5.6');
-- more inserts
COMMIT;
SELECT *
FROM test_rng;
SELECT *
FROM test_rng
WHERE EVALUATE(test_rng.rule_set,'gender=>''F'',HCT=>36, HGB=>11, RBC=>3.8') = 1;
SELECT *
FROM test_rng
WHERE EVALUATE(test_rng.rule_set, 'gender=>''F'',
hct=>40, hgb=>14, rbc=>4.6') = 1;
-- exec dbms_expfil.unassign_attribute_set('bchem', 'RESULT');
exec dbms_expfil.drop_attribute_set('bchem'); |
