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

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, ----范围过滤 > >= < <= 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

2r.nameowner,

3o.nametable_name,

4c.namecolumn_name,

5equality_preds,---等值过滤

6equijoin_preds,---等值JOIN过滤 比如where a.id=b.id

7nonequijoin_preds,----不等JOIN过滤

8range_preds,----范围过滤 > >= < <= between and

9like_preds,----LIKE过滤

10null_preds,----NULL 过滤

11timestamp

12from

13sys.col_usage$u,

14sys.obj$o,

15sys.col$c,

16sys.user$r

17where

18o.obj#=u.obj#

19andc.obj#=u.obj#

20andc.col#=u.intcol#

21andr.name='SCOTT'ando.name='TEST';

norowsselected

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>selectcount(*)fromtestwhereobject_id=23121;

COUNT(*)

----------

1

SQL>select

2r.nameowner,

3o.nametable_name,

4c.namecolumn_name,

5equality_preds,---等值过滤

6equijoin_preds,---等值JOIN过滤 比如where a.id=b.id

7nonequijoin_preds,----不等JOIN过滤

8range_preds,----范围过滤 > >= < <= between and

9like_preds,----LIKE过滤

10null_preds,----NULL 过滤

11timestamp

12from

13sys.col_usage$u,

14sys.obj$o,

15sys.col$c,

16sys.user$r

17where

18o.obj#=u.obj#

19andc.obj#=u.obj#

20andc.col#=u.intcol#

21andr.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, ----范围过滤 > >= < <= 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

2r.nameowner,

3o.nametable_name,

4c.namecolumn_name,

5equality_preds,---等值过滤

6equijoin_preds,---等值JOIN过滤 比如where a.id=b.id

7nonequijoin_preds,----不等JOIN过滤

8range_preds,----范围过滤 > >= < <= between and

9like_preds,----LIKE过滤

10null_preds,----NULL 过滤

11timestamp

12from

13sys.col_usage$u,

14sys.obj$o,

15sys.col$c,

16sys.user$r

17where

18o.obj#=u.obj#

19andc.obj#=u.obj#

20andc.col#=u.intcol#

21andr.name='SCOTT'ando.name='TEST';

OWNERTABLE_NAMECOLUMN_NAMEEQUALITY_PREDSEQUIJOIN_PREDSNONEQUIJOIN_PREDSRANGE_PREDSLIKE_PREDSNULL_PREDSTIMESTAMP

-------------------- -------------------- -------------------- -------------- -------------- ----------------- ----------- ---------- ---------- ---------

SCOTTTESTOBJECT_ID10000031-MAR-14

SCOTTTESTOBJECT_ID10000031-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-查看表有哪些列在谓词条件过滤中！/

本文原创发布php中文网，转载请注明出处，感谢您的尊重！