| General |
| Dependent Objects |
| association$ |
|
|
|
dba_associations |
all_associations |
user_associations |
|
dba_ustats |
all_ustats |
user_ustats |
|
| Related System Privileges |
create |
| |
| Associate
Statistics |
Use the ASSOCIATE STATISTICS statement to associate a statistics type (or default statistics) containing functions relevant to statistics collection, selectivity, or cost with one or more columns, standalone functions, packages, types, domain indexes, or indextypes.
For a listing of all current statistics type associations, query the USER_ASSOCIATIONS data dictionary view. If you analyze the object with which you are associating statistics, then you can also query the associations in the USER_USTATS view. |
Create Column Association |
ASSOCIATE STATISTICS WITH COLUMNS <schema.table.column>
USING schema.statistics_type
[WITH <SYSTEM | USER> MANAGED STORAGE TABLES]; |
| TBD |
Create Domain Index Association |
ASSOCIATE STATISTICS WITH <FUNCTIONS|PACKAGES|TYPES|INDEXES|INDEXTYPES>
DEFAULT COST <cpu_cost, io_cost, network_cost>
[WITH <SYSTEM | USER> MANAGED STORAGE TABLES]; |
CREATE TABLE t (comments VARCHAR2(4000));
CREATE INDEX ix_t_domain
ON t(comments)
INDEXTYPE IS ctxsys.context PARAMETERS ('nopopulate');
SELECT table_name, index_type
FROM user_indexes
WHERE table_name = 'T';
desc user_associations
SELECT object_name, object_type, def_cpu_cost, def_io_cost, def_net_cost
FROM user_associations;
ASSOCIATE STATISTICS WITH INDEXES ix_t_domain
DEFAULT COST (100,5,1);
SELECT object_name, object_type, def_cpu_cost, def_io_cost, def_net_cost
FROM user_associations; |
Create Function Association |
ASSOCIATE STATISTICS WITH <FUNCTIONS|PACKAGES|TYPES|INDEXES|INDEXTYPES>
DEFAULT SELECTIVITY <default_selectivity>
[WITH <SYSTEM | USER> MANAGED STORAGE TABLES]; |
CREATE OR REPLACE
FUNCTION ftest RETURN VARCHAR2 IS
BEGIN
RETURN 'FTEST';
END ftest;
/
desc user_associations
SELECT object_name, def_selectivity
FROM user_associations;
ASSOCIATE STATISTICS WITH FUNCTIONS ftest DEFAULT SELECTIVITY 10;
SELECT object_name, def_selectivity
FROM user_associations; |
| |
| Disassociate
Statistics |
| Use the DISASSOCIATE STATISTICS statement to disassociate default statistics or a statistics type from columns, standalone functions, packages, types, domain indexes, or indextypes. |
| Statistics
Disassociation |
DISASSOCIATE STATISTICS FROM <columns|functions|packages|types|indexes|
indextypes> <schema.object_name> [FORCE]; |
DISASSOCIATE STATISTICS FROM FUNCTIONS ftest;
DROP FUNCTION ftest; |
| |
| Associate
Statistics Demo |
Based on code from the dbms_application_info
page of the library |
--
ASSOCIATE STATISTICS WITH FUNCTIONS app_info_wrapper
-- USING ExpressionIndexStats;
INSERT INTO airplanes
SELECT '787', 1, customer_id, order_date, delivered_date
FROM airplanes
WHERE rownum = 1;
exec dbms_application_info.set_client_info('787');
CREATE OR REPLACE FUNCTION app_info_wrapper
RETURN VARCHAR2 IS
x VARCHAR2(64);
BEGIN
dbms_application_info.read_client_info(x);
RETURN x;
END app_info_wrapper;
/
EXPLAIN PLAN SET STATEMENT_ID = 'abc' FOR
SELECT *
FROM airplanes
WHERE program_id = app_info_wrapper;
SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','abc','ALL'));
ASSOCIATE STATISTICS WITH FUNCTIONS
app_info_wrapper
DEFAULT SELECTIVITY 100;
EXPLAIN PLAN SET STATEMENT_ID = 'abc' FOR
SELECT *
FROM airplanes
WHERE program_id = app_info_wrapper;
SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','abc','ALL'));
DISASSOCIATE STATISTICS
FROM FUNCTIONS app_info_wrapper;
ASSOCIATE STATISTICS WITH FUNCTIONS
app_info_wrapper
DEFAULT SELECTIVITY 1;
EXPLAIN PLAN SET STATEMENT_ID = 'abc' FOR
SELECT *
FROM airplanes
WHERE program_id = app_info_wrapper;
SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','abc','ALL')); |
