oracle 查看表有哪些列在谓词条件过滤中！ 有时候在优化的过程当中，需要查看某个表有哪些列在sql当中用作谓词过滤，我们就可以用如下sql查询。 1. 先创建test表 sql create table test as select * from dba_objects;table created. 1 2 3 sql create table
oracle 查看表有哪些列在谓词条件过滤中！
有时候在优化的过程当中，需要查看某个表有哪些列在sql当中用作谓词过滤，我们就可以用如下sql查询。
1. 先创建test表
sql> create table test as select * from dba_objects;table created.
1
2
3
sql>createtabletestasselect*fromdba_objects;
tablecreated.
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, ----范围过滤 > >=
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.nameowner,
3 o.nametable_name,
4 c.namecolumn_name,
5 equality_preds,---等值过滤
6 equijoin_preds,---等值join过滤 比如where a.id=b.id
7 nonequijoin_preds,----不等join过滤
8 range_preds,----范围过滤 > >=
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 andc.obj#=u.obj#
20 andc.col#=u.intcol#
21 andr.name='scott'ando.name='test';
norowsselected
3.执行一个简单的sql，用object_id过滤，执行完成之后，再次查看
sql> select count(*) from test where object_id=23121; count(*)---------- 1sql> 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, ----范围过滤 > >=
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>selectcount(*)fromtestwhereobject_id=23121;
count(*)
----------
1
sql>select
2 r.nameowner,
3 o.nametable_name,
4 c.namecolumn_name,
5 equality_preds,---等值过滤
6 equijoin_preds,---等值join过滤 比如where a.id=b.id
7 nonequijoin_preds,----不等join过滤
8 range_preds,----范围过滤 > >=
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 andc.obj#=u.obj#
20 andc.col#=u.intcol#
21 andr.name='scott'ando.name='test';
norowsselected
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, ----范围过滤 > >=
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.nameowner,
3 o.nametable_name,
4 c.namecolumn_name,
5 equality_preds,---等值过滤
6 equijoin_preds,---等值join过滤 比如where a.id=b.id
7 nonequijoin_preds,----不等join过滤
8 range_preds,----范围过滤 > >=
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 andc.obj#=u.obj#
20 andc.col#=u.intcol#
21 andr.name='scott'ando.name='test';
owner table_name column_name equality_predsequijoin_predsnonequijoin_predsrange_predslike_predsnull_predstimestamp
-------------------- -------------------- -------------------- -------------- -------------- ----------------- ----------- ---------- ---------- ---------
scott test object_id 1 0 0 0 0 031-mar-14
scott test object_id 1 0 0 0 0 031-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>execdbms_stats.flush_database_monitoring_info;
pl/sqlproceduresuccessfullycompleted.
http://www.savedba.com/oracle-查看表有哪些列在谓词条件过滤中！/