存储过程的效率问题！！（今晚在线等）

biti_rainy

你的这个字段，是否约束标志为 NOT  NULL

如果，你想在这样的情况下不使用索引，那么请使用 NULL
如果想使用索引，那么请使用 NOT  NULL

mypengchen2000

NULL 和NOT NULL就有这样不同的结果??

biti_rainy

SQL> create table test(a) as select rownum from t where rownum < 101;

Table created.

SQL> create index test_index on test(a);

Index created.

SQL> set autotrace traceonly
SQL> select a+0 from test group by a+0;

100 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'TEST'




Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
5 consistent gets
0 physical reads
0 redo size
2477 bytes sent via SQL*Net to client
1091 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
100 rows processed

SQL> alter table test modify(a not null);

Table altered.

SQL> select a+0 from test group by a+0;

100 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'TEST'




Statistics
----------------------------------------------------------
112 recursive calls
15 db block gets
21 consistent gets
0 physical reads
0 redo size
2477 bytes sent via SQL*Net to client
1091 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
100 rows processed

SQL> analyze table test compute statistics ;

Table analyzed.

SQL> select a+0 from test group by a+0;

100 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=100 Bytes=200
)

1 0 SORT (GROUP BY) (Cost=3 Card=100 Bytes=200)
2 1 INDEX (FULL SCAN) OF 'TEST_INDEX' (NON-UNIQUE) (Cost=1 C
ard=100 Bytes=200)





Statistics
----------------------------------------------------------
39 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
2477 bytes sent via SQL*Net to client
1091 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
100 rows processed

SQL>alter table test modify(a null);

Table altered.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select a+0 from test group by a+0;

100 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=100 Bytes=200
)

1 0 SORT (GROUP BY) (Cost=3 Card=100 Bytes=200)
2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=1 Card=100 Bytes=200
)





Statistics
----------------------------------------------------------
29 recursive calls
12 db block gets
9 consistent gets
0 physical reads
0 redo size
2477 bytes sent via SQL*Net to client
1091 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
100 rows processed

SQL>


其实这个道理很简单
因为索引不存储为NULL 的值，所以如果优化器发现字段为 NOT NULL并且所有数据都可以仅仅从索引里面获取
则将选取只通过全索引扫描进行，因为通常索引包含字段总是比整个表的记录长度少的多
这样将降低IO，并且索引获取的数据是有序的
而如果字段约束为 NULL ,则优化器不能决定根据索引获取数据
那是因为通过索引扫描将漏掉 该字段为 NULL 的记录

mypengchen2000

其实这个道理很简单
因为索引不存储为NULL 的值，所以如果优化器发现字段为 NOT NULL并且所有数据都可以仅仅从索引里面获取
则将选取只通过全索引扫描进行，因为通常索引包含字段总是比整个表的记录长度少的多
这样将降低IO，并且索引获取的数据是有序的
而如果字段约束为 NULL ,则优化器不能决定根据索引获取数据
那是因为通过索引扫描将漏掉 该字段为 NULL 的记录
----------------------------------------------------------------------------------------------------------------------------------------
真的非常有道理,够经典,多谢!!


但从你所说的，我有两点不是很清楚，请继续指教：
1.其实，你刚才最后得出结论是否有一个前提，那就是对表进行了分析：analyze table test compute statistics，那是否analyze是必须的，如果不analyze，那就不会使用索引？？你的结论也就不对了呢？
我知道analyze操作对于效率来说很重要的，但不知道在什么情况下一定要用analyze，以及analyze所实现的根本目的是什么？是不是analyze后，oralce才知道那些字段是null,哪些是not null，并判断并且所有数据都可以仅仅从索引里面获取？


2.“如果优化器发现字段为 NOT NULL并且所有数据都可以仅仅从索引里面获取”
所有数据是什么意思？是指group by col1,col2,col3中的col1,col2,col3这些group by字段都要有索引（是要有一个索引，还是要有三个索引？？），并且都not null吗？？如果有一个不满足，比如col1没有索引，或者col2为null，那即使是analyze过，也不会用到索引了，是吗？

biti_rainy

1： 你应该检查你的查询中是否存在某一个或者某些对象被analyze 过，如果是 CHOOSE的话，当然你也可以自己测试。不analyze ,在我的测试中是没有使用索引的，也许你可以测试一下，不知道是否和版本或者其他因素有关系
2：所有数据的意思是 你查询 select 跟的字段都包含字索引中，至于 复合索引里面存在 null and  not null的问题，我想你可以做个测试来验证，你说呢？

思路已经很好地摆在这里了，你完全可以自己动手

mypengchen2000

我做了一个初步的实验，过程如下：（都是基于choose优化的！）

SQL> create table test (a number(2),b number(2));
     Table created
     Executed in 0.516 seconds
SQL> desc test;
Name Type      Nullable Default Comments
---- --------- -------- ------- --------
A    NUMBER(2) Y                        
B    NUMBER(2) Y                        

SQL> create index idx1 on test(a);
     Index created
     Executed in 0.594 seconds
SQL> create index idx2 on test(b);
     Index created
     Executed in 1.969 seconds

然后，select a from test group by a或者select a,b from test group by a,b都不会用到索引；
接着：SQL> analyze table test compute statistics;
      Table analyzed
      Executed in 0.109 seconds
但是：select a,b from test group by a,b或者select a,b from test group by a,b仍然都不会用到索引；

SQL> alter table test modify (a not null);
     Table altered
     Executed in 0.157 seconds
SQL> analyze table test compute statistics;
     Table analyzed
     Executed in 0.109 seconds
接着：select a from test group by a用上了a上的索引idx1；
但是：select a,b from test group by a,b不会用索引了；
但是：select a,sum(b) from test group by a也会用a上的索引idx1；

SQL> alter table test modify (b not null);
     Table altered
     Executed in 0.187 seconds
但是：select a,b from test group by a,b不会用索引了；
SQL> create index idx3 on test(a,b);
     Index created
     Executed in 0.156 seconds
SQL> analyze table test compute statistics;
      Table analyzed
      Executed in 0.172 seconds
但是：select a,b from test group by a,b就会用上idx3索引了；

因此，从以上实验结合biti_rainy所将我暂时得出以下观点：
1.确实是要有analyze做为基础，否则，在非常规索引条件下（没有使用where的字段），是不会使用到索引的；
  原因：analyze后，oralce才知道那些字段是null,哪些是not null，并判断出是否所有数据都可以仅仅从索引里面获取！！
2.并且所有数据都可以仅仅从索引里面获取？？
  这里的所有数据的意思确切是指select出的除列函数外的所有字段，并且，如果是多列，那这多列一定要在一个索引里，
   每个字段一个索引也是不会用到索引的（并不要求每个字段都有索引！）！！同时，这多列也一定要not null！

但总的来说，我上面说的就是对下面这段话的补充和备注：
因为索引不存储为NULL 的值，所以如果优化器发现字段为 NOT NULL并且所有数据都可以仅仅从索引里面获取
则将选取只通过全索引扫描进行，因为通常索引包含字段总是比整个表的记录长度少的多
这样将降低IO，并且索引获取的数据是有序的
而如果字段约束为 NULL ,则优化器不能决定根据索引获取数据
那是因为通过索引扫描将漏掉 该字段为 NULL 的记录

欢迎各位有兴趣的朋友继续来验证或实验、讨论！！
（顺便提醒一下：每次对表alter后，记得analyze一遍，否则结果还是基于你上次analyze后的状态！）

iamcoldwind

如果表没有进行过analyze，但也同样出现了SELECT A FROM TAB1时用上了A字段对应的索引的情况，那又该如何解释呢？

iamcoldwind

MATERIALIZE VIEW和SNAP SHOT是一样的么？如果某个MATERIALIZE VIEW因为误操作导致所对应的表空间的数据丢失了，而之后进行数据修复时，是否可能会把该MATERIALIZE VIEW转变成了实在的TABLE，从而导致其SNAP SHOT效果失效，所以导致楼主所说的那种情况呢？

怀疑中，学习中，也许想法很幼稚，恭请大侠指教！

iamcoldwind

有没有可能改MATERIALIZE VIEW被转换成了TABLE，但是ORACLE的内存中依然保存着该SNAP SHOT，所以在存储过程中的REFRESH语句的作用对象是该SNAP SHOT，而查询语句中实际对应的却是转换后的TABLE，这种情况下导致同样的存储过程在误删事件前后效率发生了变化？
因为楼主是说在DROP掉该MATERIALIZE VIEW后，再重建，然后存储过程的效率就回复了，与数据库参数的设置还没有直接的影响的！

希望大侠们看看我的猜测争取么？也许就这个问题我们能总结出一些宝贵的经验呢。

biti_rainy

最初由 iamcoldwind 发布
[B]如果表没有进行过analyze，但也同样出现了SELECT A FROM TAB1时用上了A字段对应的索引的情况，那又该如何解释呢？ [/B]

你得拿出事实，然后我们再来探讨这个问题！