CONCEPT中关于Data Warehousing Considerations段话理解

KFC

CONCEPT第6章《dependencies among schema objects》中有这么一小段

Data Warehousing Considerations
Some data warehouses drop indexes on tables at night to facilitate faster loads.
--下面这句我的理解“所有依赖于相关基表的索引被删除后将会使得此视图变得无效”
However, all views dependent on the table whose index is dropped get invalidated.
This means that subsequently running any package that reference these dropped
views will invalidate the package.
Remember that whenever you create a table, index, and view, and then drop the index,
all objects dependent on that table are invalidated, including views, packages, package
bodies, functions, and procedures. This protects updatable join views.
To make the view valid again, use one of the following statements:
SELECT * FROM vtest;
or
ALTER VIEW vtest compile;

在10,11上测试发现视图在user_objects中的status状态为可用的(valid);
不知道是不是我理解错了；下面是测试的过程（10G）

SQL> desc a
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
V                                                  VARCHAR2(8)
N                                                  NUMBER(8)

SQL> create view v_a as select * from a;

View created.

SQL> desc a
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
V                                                  VARCHAR2(8)
N                                                  NUMBER(8)

SQL> create index idx_a on a(v);

Index created.


SQL> drop index idx_a;

Index dropped.

SQL> select status from user_objects
  2  where object_name='V_A';

STATUS
-------
VALID

Maclean Liu(刘相兵

1. 你看的文档什么版本
2. 你做的实验什么版本

KFC

O,不好意思，忘加说明文档是10G，做实验也是10G（有说明）

Stone

经过测试的结果才是正确的 ：），我们经常说“眼见为实，耳听为虚”，不过很多时候，即使看见纸面上写的东东，也不要轻易相信，记得jonathan lewis有一句类似的话，忘记了原话咋说啦，找到了再分享过来 ;)

1. SQL> select * from v$version where rownum < 3;
   
2. 
   
3. BANNER
   
4. --------------------------------------------------------------------------------
   
5. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
   
6. PL/SQL Release 11.2.0.3.0 - Production
   
7. 
   
8. SQL> select * from tab;
   
9. 
   
10. TNAME                          TABTYPE  CLUSTERID
    
11. ------------------------------ ------- ----------
    
12. T1                             TABLE
    
13. 
    
14. SQL>
    
15. SQL> desc t1
    
16. Name                                      Null?    Type
    
17. ----------------------------------------- -------- ----------------------------
    
18. OWNER                                              VARCHAR2(30)
    
19. OBJECT_NAME                                        VARCHAR2(128)
    
20. SUBOBJECT_NAME                                     VARCHAR2(30)
    
21. OBJECT_ID                                          NUMBER
    
22. DATA_OBJECT_ID                                     NUMBER
    
23. OBJECT_TYPE                                        VARCHAR2(19)
    
24. CREATED                                            DATE
    
25. LAST_DDL_TIME                                      DATE
    
26. TIMESTAMP                                          VARCHAR2(19)
    
27. STATUS                                             VARCHAR2(7)
    
28. TEMPORARY                                          VARCHAR2(1)
    
29. GENERATED                                          VARCHAR2(1)
    
30. SECONDARY                                          VARCHAR2(1)
    
31. NAMESPACE                                          NUMBER
    
32. EDITION_NAME                                       VARCHAR2(30)
    
33. 
    
34. SQL> create index idx_t1 on t1(OWNER);
    
35. 
    
36. Index created.
    
37. 
    
38. SQL> create view v_t1 as select * from t1;
    
39. 
    
40. View created.
    
41. 
    
42. SQL> set lines 130
    
43. col OWNER for a20
    
44. col OBJECT_NAME for a35
    
45. select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects
    
46. where OBJECT_NAME='&obj_name';SQL> SQL> SQL>   2
    
47. Enter value for obj_name: V_T1
    
48. old   2: where OBJECT_NAME='&obj_name'
    
49. new   2: where OBJECT_NAME='V_T1'
    
50. 
    
51. OWNER                OBJECT_NAME                         OBJECT_TYPE         STATUS
    
52. -------------------- ----------------------------------- ------------------- -------
    
53. STONE                V_T1                                VIEW                VALID
    
54. 
    
55. SQL> drop index idx_t1;
    
56. 
    
57. Index dropped.
    
58. 
    
59. SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects
    
60. where OBJECT_NAME='&obj_name';  2
    
61. Enter value for obj_name: V_T1
    
62. old   2: where OBJECT_NAME='&obj_name'
    
63. new   2: where OBJECT_NAME='V_T1'
    
64. 
    
65. OWNER                OBJECT_NAME                         OBJECT_TYPE         STATUS
    
66. -------------------- ----------------------------------- ------------------- -------
    
67. STONE                V_T1                                VIEW                VALID
    

复制代码

其实这个问题很可能是10g早期或者以前的一些版本可能碰到的问题，但是Oracle10.2的这个文档(http://docs.oracle.com/cd/B19306_01/server.102/b14220/depend.htm)似乎并没有修正这个说法，11g肯定就没有这个问题啦 ：）

不过来自wordpress的这篇文章也正是对这个问题的一个探究，看完后应该就清楚点儿啦。当然为了方便大家，就顺便贴过来啦。
http://srivenukadiyala.wordpress.com/2010/09/25/does-dropping-an-index-invalidate-all-objects-that-depend-on-the-underlying-table-including-views-packages-package-bodies-functions-and-procedures/

Invalidation of dependent code objects for dropping an index on the base table?
Filed under: Uncategorized — srivenu @ 10:21 am
“When you drop an index, Oracle invalidates all objects that depend on the underlying table, including views, packages, package bodies, functions, and procedures.”

This statement has been part of Oracle documentation since 9i doc (At least it wasn’t there in the same section in 8i doc)

I never thought for a minute that dropping an index would invalidate the code objects dependant on the underlying base table. That was till a couple of days back !

As part of tuning some sql, i was creating new indexes ones and dropping some old ones. All the index changes were on look-up tables. Even though it was on a production system, I thought that it wouldn’t impact anyone as the tables were small & static. How wrong & ignorant i was !

Next day i had been told that the end-users experienced an application freeze for around 6 minutes. On analysis it was found that the database faced several latch free events on shared pool & library cache and library cache pins. It was also noted that several code objects got invalid during that time and had undergone implicit recompilation. We had a DDL trigger at database level to capture all DDL changes. And the only DDL’s that we saw prior to the impact was the INDEX DROP. Some of my colleagues suggested that the INDEX drop has invalidated the procedures. I immediately rubbished their claim that an index drop would cause object invalidations.

To find the culprit, i decided to do some log mining. I was searching for the details of the session which invalidated the procs. I looked for SQL_REDO where the STATUS column in the OBJ$ table for the PROC object (OBJ#) was updated to a status of 5(invalid). I found out the session and after further analysis, i found that it was my session! I looked at all the SQL_REDO’s of my session and found that it had first updated the status of all the dependent procedures to an INVALID STATUS before executing the DROP INDEX statement. I immediately ran a quick test.

Create table X(a number);

Create index ix on X(a);

Create or replace procedure PX as
begin
delete from X where a=10;
end;
/

I dropped the index IX and checked for the status of PX. It was still VALID

On close observation i found out that the index i dropped during the issue was a FUNCTION-BASED index. So i repeated the test with a function-based index

Create table X(a number);

Create index ix on X(to_char(a));

Create or replace procedure PX as
begin
delete from X where a=10;
end;
/

I dropped the index IX and checked for the status of PX. It was now INVALID

So dependent code objects on a base table are only invalidated if you drop a Function-Based index on that table. (I did not check for other types). I raised an SR for fixing the documentation.

But this left me pondering the reason for this invalidation !

As a commentor has pointed out, the invalidation is no longer happening in 11g. I had infact raised a SR to get the Oracle documentation fixed
(SR 3-2143692451)

KFC

thank you! :）

wind

Stone 发表于 2013-2-18 23:03
经过测试的结果才是正确的 ：），我们经常说“眼见为实，耳听为虚”，不过很多时候，即使看见纸面上写的东 ...

不错，写的很好。逻辑清晰。学习了。

wind

Stone 发表于 2013-2-18 23:03
经过测试的结果才是正确的 ：），我们经常说“眼见为实，耳听为虚”，不过很多时候，即使看见纸面上写的东 ...

衍生进一步的想到，如果看到一些没有用的index，是不能随便乱删除的，要考虑一些其他的object。。。

Stone

wind 发表于 2013-2-19 10:43
衍生进一步的想到，如果看到一些没有用的index，是不能随便乱删除的，要考虑一些其他的object。。。 ...

嗯，生产环境无儿戏 ：） 多多测试，然后再实施，多多考虑各种可能的不好的后果。另外要与时俱进，跟上Oracle前进的步伐，多多用变化的眼光看问题，再进一步考虑为什么变化，为什么这样变化，就能进步很多啦 ~