SCOTT@DMBLI> create table t1(x int, y varchar2(20));
Table created.
-- put some data.
SCOTT@DMBLI> create index t1idx on t1(y);
Index created.
SCOTT@DMBLI> create procedure testprc
2 as
3 var1 varchar2(20);
4 begin
5 select y into var1 from t1 where rownum=1;
6 end;
7 /
Procedure created.
SCOTT@DMBLI> create view v1
2 as select * from t1;
View created.
SCOTT@DMBLI> select object_name, object_type, status , timestamp, last_ddl_time from user_objects
2 where created > sysdate -1;
OBJECT_NAME OBJECT_TYPE STATUS TIMESTAMP LAST_DDL_TIME
------------------------------ --------------- ------- ------------------- --------------------------
T1 TABLE VALID 2010-09-07:16:45:19 07-SEP-2010 16:45:51
T1IDX INDEX VALID 2010-09-07:16:45:51 07-SEP-2010 16:45:51
TESTPRC PROCEDURE VALID 2010-09-07:16:46:58 07-SEP-2010 16:46:58
V1 VIEW VALID 2010-09-07:16:47:14 07-SEP-2010 16:47:14
=> All valid.
SCOTT@DMBLI> drop index T1IDX;
Index dropped.
SCOTT@DMBLI> select object_name, object_type, status , timestamp, last_ddl_time from user_objects
2 where created > sysdate -1;
OBJECT_NAME OBJECT_TYPE STATUS TIMESTAMP LAST_DDL_TIME
------------------------------ --------------- ------- ------------------- --------------------------
T1 TABLE VALID 2010-09-07:16:45:19 07-SEP-2010 16:48:22
TESTPRC PROCEDURE VALID 2010-09-07:16:46:58 07-SEP-2010 16:46:58
V1 VIEW VALID 2010-09-07:16:47:14 07-SEP-2010 16:47:14
=> Drop “ordinary” index does not invalidate proc and view.
Dropping Function Based index does ...
SCOTT@DMBLI> create index t1idx on t1(upper(y));
Index created.
SCOTT@DMBLI> select object_name, object_type, status , timestamp, last_ddl_time from user_objects
2 where created > sysdate -1;
OBJECT_NAME OBJECT_TYPE STATUS TIMESTAMP LAST_DDL_TIME
------------------------------ --------------- ------- ------------------- --------------------------
T1 TABLE VALID 2010-09-07:16:45:19 07-SEP-2010 16:48:50
T1IDX INDEX VALID 2010-09-07:16:48:50 07-SEP-2010 16:48:50
TESTPRC PROCEDURE VALID 2010-09-07:16:46:58 07-SEP-2010 16:46:58
V1 VIEW VALID 2010-09-07:16:47:14 07-SEP-2010 16:47:14
SCOTT@DMBLI> drop index T1IDX;
Index dropped.
SCOTT@DMBLI> select object_name, object_type, status , timestamp, last_ddl_time from user_objects where created > sysdate -1;
OBJECT_NAME OBJECT_TYPE STATUS TIMESTAMP LAST_DDL_TIME
------------------------------ --------------- ------- ------------------- --------------------------
T1 TABLE VALID 2010-09-07:16:45:19 07-SEP-2010 16:49:05
TESTPRC PROCEDURE INVALID 2010-09-07:16:46:58 07-SEP-2010 16:46:58
V1 VIEW INVALID 2010-09-07:16:47:14 07-SEP-2010 16:47:14
Select/execute to auto-compile ...
SCOTT@DMBLI> select * from v1;
no rows selected
SCOTT@DMBLI> exec testprc;
PL/SQL procedure successfully completed.
SCOTT@DMBLI> select object_name, object_type, status , timestamp, last_ddl_time from user_objects where created > sysdate -1;
OBJECT_NAME OBJECT_TYPE STATUS TIMESTAMP LAST_DDL_TIME
------------------------------ --------------- ------- ------------------- --------------------------
T1 TABLE VALID 2010-09-07:16:45:19 07-SEP-2010 16:49:05
TESTPRC PROCEDURE VALID 2010-09-07:16:49:32 07-SEP-2010 16:49:32
V1 VIEW VALID 2010-09-07:16:47:14 07-SEP-2010 16:49:22
Hidden column not analyzed ...
No hidden columns yet ...
SCOTT@DMBLI> select table_name column_name, num_distinct, hidden_column, virtual_column from user_tab_cols where virtual_column ='YES';
no rows selected
SCOTT@DMBLI> create index t1idx on t1(upper(y)) compute statistics;
Index created.
SCOTT@DMBLI> select last_analyzed , sysdate from user_indexes where index_name='T1IDX';
LAST_ANALYZED SYSDATE
-------------------------- --------------------------
07-SEP-2010 17:06:28 07-SEP-2010 17:06:46
SCOTT@DMBLI> select table_name column_name, num_distinct, hidden_column, virtual_column from user_tab_cols where virtual_column ='YES';
COLUMN_NAME NUM_DISTINCT HID VIR
------------------------------ ------------ --- ---
T1 YES YES
=> hidden column created by the FBI. But it is not analyzed even with the compute stats (it does not know there are a few rows in the table). Run dbms_stats to get stats on this table ...
SCOTT@DMBLI> exec dbms_stats.gather_table_stats(ownname=>null, tabname=> 'T1',estimate_percent=>10, cascade=>true, method_opt=> 'FOR ALL HIDDEN COLUMNS SIZE 1');
PL/SQL procedure successfully completed.
SCOTT@DMBLI> select table_name column_name, num_distinct, hidden_column, virtual_column from user_tab_cols where virtual_column ='YES';
COLUMN_NAME NUM_DISTINCT HID VIR
------------------------------ ------------ --- ---
T1 3 YES YES
... which matches what’s in there;
SCOTT@DMBLI> select * from t1;
X Y
---------- --------------------
1 a
2 b
3 c
If we update the table to look like this:
SCOTT@DMBLI> select * from t1;
X Y
---------- --------------------
1 a
2 A
3 a
then the analyze result changes, as expected.
SCOTT@DMBLI> exec dbms_stats.gather_table_stats(ownname=>null, tabname=> 'T1',estimate_percent=>10, cascade=>true, method_opt=> 'FOR ALL HIDDEN COLUMNS SIZE 1');
PL/SQL procedure successfully completed.
SCOTT@DMBLI> select table_name column_name, num_distinct, hidden_column, virtual_column from user_tab_cols where virtual_column ='YES';
COLUMN_NAME NUM_DISTINCT HID VIR
------------------------------ ------------ --- ---
T1 1 YES YES
No comments:
Post a Comment