Oracle 查看表有哪些列在谓词条件过滤中！
有时候在优化的过程当中，需要查看某个表有哪些列在sql当中用作谓词过滤，我们就可以用如下sql查询。
1. 先创建test表
|
1
2
3
|
SQL
>
create
table
test
as
select
*
from
dba_objects;
Table
created.
|
2.通过sql查看这个表是否有其他sql用到了这个表的列当作过滤条件
|
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过滤，执行完成之后，再次查看
|
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还是不查询到呢？因为此时信息还没刷新到磁盘
|
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来刷新当前内存信息到磁盘，这样也可以立即看到信息
|
1
2
3
|
SQL
>
exec
dbms_stats.flush_database_monitoring_info;
PL
/
SQL
procedure
successfully
completed.
|
- 本文固定链接: http://www.savedba.com/oracle-查看表有哪些列在谓词条件过滤中！/
- 转载请注明: 版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
扫一扫
358
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
