Sign in or 
| Version | User | Scope of changes |
|---|---|---|
| Jun 16 2008, 10:10 AM EDT (current) | olapdba | 4 words added, 2 words deleted |
| May 29 2008, 10:25 AM EDT | Christopher.Kearney |
Changes
Key: Additions Deletions
In Oracel Database 11g there are new several new OlapOLAP objects. The two that are queryable through the CUBE_TABLE function are CUBES and CUBE_DIMENSIONS. To ensure optimal explain plans the objects must be analyzed via the DBMS_AW_STATS package. Below is a sample script that analyzes all CUBES and CUBE_DIMENSIONS in a schema.
set serverout on
declare
objName varchar2(30);
x number;
cursor awObjCur is
SELECT dimension_name AS objname, 1 as x FROM user_cube_dimensions
UNION ALL
select cube_name AS objname, 2 as x FROM user_cubes
order by x, objname;
begin
open awObjCur;
loop
fetch awObjCur INTO objName, x;
exit when awObjCur%NOTFOUND;
-- analyze object
dbms_output.put_line('analyzing ' || objName);
dbms_aw_stats.analyze(objName);
end loop;
close awObjCur;
end;
/
set serverout on
declare
objName varchar2(30);
x number;
cursor awObjCur is
SELECT dimension_name AS objname, 1 as x FROM user_cube_dimensions
UNION ALL
select cube_name AS objname, 2 as x FROM user_cubes
order by x, objname;
begin
open awObjCur;
loop
fetch awObjCur INTO objName, x;
exit when awObjCur%NOTFOUND;
-- analyze object
dbms_output.put_line('analyzing ' || objName);
dbms_aw_stats.analyze(objName);
end loop;
close awObjCur;
end;
/
