Oracle 查看表有哪些列在谓词条件过滤中

Oracle 查看表有哪些列在谓词条件过滤中！

有时候在优化的过程当中，需要查看某个表有哪些列在sql当中用作谓词过滤，我们就可以用如下sql查询。

1. 先创建test表

SQL> create table test as select * from dba_objects; Table created.

1 2 3

SQL > create table test as select * from dba_objects;   Table created.

2.通过sql查看这个表是否有其他sql用到了这个表的列当作过滤条件

SQL> select 2 r.name owner, 3 o.name table_name , 4 c.name column_name, 5 equality_preds, ---等值过滤 6 equijoin_preds, ---等值JOIN过滤 比如where a.id=b.id 7 nonequijoin_preds, ----不等JOIN过滤 8 range_preds, ----范围过滤 > >= < <= between and 9 like_preds, ----LIKE过滤 10 null_preds, ----NULL 过滤 11 timestamp 12 from 13 sys.col_usage$ u, 14 sys.obj$ o, 15 sys.col$ c, 16 sys.user$ r 17 where 18 o.obj# = u.obj# 19 and c.obj# = u.obj# 20 and c.col# = u.intcol# 21 and r.name='SCOTT' and o.name='TEST'; no rows selected

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SQL > select   2   r. name owner,   3   o. name table_name ,   4   c. name column_name,   5   equality_preds, ---等值过滤   6   equijoin_preds, ---等值JOIN过滤 比如where a.id=b.id   7   nonequijoin_preds, ----不等JOIN过滤   8   range_preds, ----范围过滤 > >= < <= between and   9   like_preds,    ----LIKE过滤 10   null_preds,    ----NULL 过滤 11    timestamp 12    from 13   sys.col_usage$ u, 14   sys.obj$ o, 15   sys.col$ c, 16   sys. user$ r 17    where 18   o.obj# = u.obj# 19    and c.obj# = u.obj# 20    and c.col# = u.intcol# 21    and r. name= 'SCOTT' and o. name= 'TEST';   no rows selected

3.执行一个简单的sql，用object_id过滤，执行完成之后，再次查看

SQL> select count(*) from test where object_id=23121; COUNT(*) ---------- 1 SQL> select 2 r.name owner, 3 o.name table_name , 4 c.name column_name, 5 equality_preds, ---等值过滤 6 equijoin_preds, ---等值JOIN过滤 比如where a.id=b.id 7 nonequijoin_preds, ----不等JOIN过滤 8 range_preds, ----范围过滤 > >= < <= between and 9 like_preds, ----LIKE过滤 10 null_preds, ----NULL 过滤 11 timestamp 12 from 13 sys.col_usage$ u, 14 sys.obj$ o, 15 sys.col$ c, 16 sys.user$ r 17 where 18 o.obj# = u.obj# 19 and c.obj# = u.obj# 20 and c.col# = u.intcol# 21 and r.name='SCOTT' and o.name='TEST'; no rows selected

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

SQL > select count(*) from test where object_id =23121;      COUNT(*) ----------          1   SQL > select   2   r. name owner,   3   o. name table_name ,   4   c. name column_name,   5   equality_preds, ---等值过滤   6   equijoin_preds, ---等值JOIN过滤 比如where a.id=b.id   7   nonequijoin_preds, ----不等JOIN过滤   8   range_preds, ----范围过滤 > >= < <= between and   9   like_preds,    ----LIKE过滤 10   null_preds,    ----NULL 过滤 11    timestamp 12    from 13   sys.col_usage$ u, 14   sys.obj$ o, 15   sys.col$ c, 16   sys. user$ r 17    where 18   o.obj# = u.obj# 19    and c.obj# = u.obj# 20    and c.col# = u.intcol# 21    and r. name= 'SCOTT' and o. name= 'TEST';   no rows selected

4. 这里可能就有疑问了，在第三步中有sql使用object_id谓词进行过滤，为什么sql还是不查询到呢？因为此时信息还没刷新到磁盘

SQL> select 2 r.name owner, 3 o.name table_name , 4 c.name column_name, 5 equality_preds, ---等值过滤 6 equijoin_preds, ---等值JOIN过滤 比如where a.id=b.id 7 nonequijoin_preds, ----不等JOIN过滤 8 range_preds, ----范围过滤 > >= < <= between and 9 like_preds, ----LIKE过滤 10 null_preds, ----NULL 过滤 11 timestamp 12 from 13 sys.col_usage$ u, 14 sys.obj$ o, 15 sys.col$ c, 16 sys.user$ r 17 where 18 o.obj# = u.obj# 19 and c.obj# = u.obj# 20 and c.col# = u.intcol# 21 and r.name='SCOTT' and o.name='TEST'; OWNER TABLE_NAME COLUMN_NAME EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP -------------------- -------------------- -------------------- -------------- -------------- ----------------- ----------- ---------- ---------- --------- SCOTT TEST OBJECT_ID 1 0 0 0 0 0 31-MAR-14 SCOTT TEST OBJECT_ID 1 0 0 0 0 0 31-MAR-14

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

SQL > select   2   r. name owner,   3   o. name table_name ,   4   c. name column_name,   5   equality_preds, ---等值过滤   6   equijoin_preds, ---等值JOIN过滤 比如where a.id=b.id   7   nonequijoin_preds, ----不等JOIN过滤   8   range_preds, ----范围过滤 > >= < <= between and   9   like_preds,    ----LIKE过滤 10   null_preds,    ----NULL 过滤 11    timestamp 12    from 13   sys.col_usage$ u, 14   sys.obj$ o, 15   sys.col$ c, 16   sys. user$ r 17    where 18   o.obj# = u.obj# 19    and c.obj# = u.obj# 20    and c.col# = u.intcol# 21    and r. name= 'SCOTT' and o. name= 'TEST';   OWNER                 TABLE_NAME            COLUMN_NAME           EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP -------------------- -------------------- -------------------- -------------- -------------- ----------------- ----------- ---------- ---------- --------- SCOTT                 TEST                  OBJECT_ID                          1               0                  0            0           0           0 31 -MAR -14 SCOTT                 TEST                  OBJECT_ID                          1               0                  0            0           0           0 31 -MAR -14

在过了一段时间之后，通过这个sql就可以查询到TEST表就有object_id这个列被sql当作谓词条件了。另外可以通过如下sql来刷新当前内存信息到磁盘，这样也可以立即看到信息

SQL> exec dbms_stats.flush_database_monitoring_info; PL/SQL procedure successfully completed.

1 2 3

SQL > exec dbms_stats.flush_database_monitoring_info;   PL / SQL procedure successfully completed.

• 本文固定链接: http://www.savedba.com/oracle-查看表有哪些列在谓词条件过滤中！/
• 转载请注明: 版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!