不解一个嵌套查询报ORA-01476: divisor is equal to zero！！

bj-jn

我希望通过如下sql查询出索引列上的选择性，可是报ORA-01476的错误
SQL> select * from
  2  (
select b.table_name,a.column_name,
       b.num_rows,
       a.num_distinct Cardinality,
       round(a.num_distinct / b.num_rows * 100, 2) selectivity
  from dba_tab_col_statistics a, dba_tables b,dba_ind_columns c
where a.owner = b.owner
   and b.owner = c.table_owner
   and a.table_name = b.table_name
   and a.column_name = c.column_name
   and a.owner = 'SCOTT'
)
14  where selectivity <50;
       round(a.num_distinct / b.num_rows * 100, 2) selectivity
                            *
ERROR at line 6:
ORA-01476: divisor is equal to zero

于是我单独运行内层的查询语句却不报任何错误
SQL> select b.table_name,a.column_name,
  2         b.num_rows,
       a.num_distinct Cardinality,
       round(a.num_distinct / b.num_rows * 100, 2) selectivity
  from dba_tab_col_statistics a, dba_tables b,dba_ind_columns c
where a.owner = b.owner
   and b.owner = c.table_owner
   and a.table_name = b.table_name
   and a.column_name = c.column_name
10     and a.owner = 'SCOTT';

TABLE_NAME           COLUMN_NAME            NUM_ROWS CARDINALITY SELECTIVITY
-------------------- -------------------- ---------- ----------- -----------
DEPT                 DEPTNO                        4           4         100
EMP                  DEPTNO                       13           3       23.08
EMP                  EMPNO                        13          13         100

3 rows selected.

通过ora错误可以判断当b.num_rows为0时会引发此错误，可是在内层查询中并没有这些不符的数据，为什么加了一层嵌套查询后会报ora-01476错误呢，请指教？

psufnxk2000

SQL>  select * from
  2      (
  3  select b.table_name,a.column_name,
  4         b.num_rows,
  5         a.num_distinct Cardinality,
  6         round(a.num_distinct / b.num_rows * 100, 2) selectivity
  7    from dba_tab_col_statistics a, dba_tables b,dba_ind_columns c
  8  where a.owner = b.owner
  9     and b.owner = c.table_owner
10     and a.table_name = b.table_name
11     and a.column_name = c.column_name
12     and a.owner = 'SCOTT'
13  ) ;

TABLE_NAME                     COLUMN_NAME                      NUM_ROWS CARDINALITY SELECTIVITY
------------------------------ ------------------------------ ---------- ----------- -----------
EMP                            DEPTNO                                 14           3       21.43
DEPT                           DEPTNO                                  4           4         100
EMP                            EMPNO                                  14          14         100

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

lunar

select * from
(
select b.table_name,a.column_name,
       b.num_rows,
       a.num_distinct Cardinality,
       round(a.num_distinct / b.num_rows * 100, 2) selectivity
  from dba_tab_col_statistics a, dba_tables b,dba_ind_columns c
where a.owner = b.owner
   and b.owner = c.table_owner
   and a.table_name = b.table_name
   and a.column_name = c.column_name
   and a.owner = 'SYS'
   and b.num_rows <>0
)
where selectivity <50;

bj-jn

lunar 发表于 2013-2-14 19:23
select * from
(
select b.table_name,a.column_name,

感谢lunar，根据你在嵌套查询中加的b.num_rows <>0这个条件后不再报错了也得到了我想要的结果！
可是这里我还是不太理解，为什么我在不加b.num_rows<>0这个条件时单独运行最内层的select不报错，可是嵌套了一层查询后又报错了，这里不是很理解，希望给予解答！

Maclean Liu(刘相兵

可能与优化器 查询转换有关， 你可以对比 2个SQL做一个10053 trace

alter system flush shared_pool;
alter session set events '10053 trace name context forever, level 1';

RUN YOUR STATEMENT

26856649

话说这个帖子是不是该换个位置