Oracle checks which columns of the table are in predicate condition filtering.

Oracle checks which columns of the table are in the predicate filter! Sometimes, during the optimization process, you need to check which columns of a table are used as predicate filtering in SQL, and we can use the following SQL query. 1. First create the test table SQLcreatetabletestasselect * fromdba_objects; Tablecreated.123SQLcreatetable

Oracle checks which columns of the table are in the predicate filter! Sometimes, during the optimization process, you need to check which columns of a table are used as predicate filtering in SQL, and we can use the following SQL query. 1. First create the test table SQL create Table test as select * from dba_objects; table created. 1 2 3 SQL create table

Oracle checks which columns of the table are in the predicate filter!

Sometimes, during the optimization process, you need to check which columns of a table are used as predicate filtering in SQL, and we can use the following SQL query.

1. Create a test table first

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

1 2 3

SQL> createtabletestasselect * fromdba_objects; Tablecreated.

2. Check whether other SQL statements in this table use the columns of this table as filter conditions.

SQL> select 2 r. name owner, 3 o. name table_name, 4 c. name column_name, 5 equality_preds, --- equivalent filter 6 equijoin_preds, --- equivalent JOIN filter such as where. id = B. id 7 nonequijoin_preds, ---- Wait for JOIN to filter 8 range_preds, ---- range filter >>=< = between and 9 like_preds, ---- LIKE to filter 10 null_preds, ---- NULL filter 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. nameowner, 3 o. nametable_name, 4 c. namecolumn_name, 5 equality_preds, --- equivalent Filtering 6 equijoin_preds, --- equi JOIN filter, for example, where a. id = B. id 7 nonequijoin_preds, ---- unequal JOIN Filtering 8 range_preds, ---- range filtering >>=< = between and 9 like_preds, ---- LIKE Filter 10 null_preds, ---- NULL Filter 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 Android. name = 'Scott 'ando. name = 'test '; Norowsselected

3. Execute a simple SQL statement and use object_id to filter the statements. After the execution is completed, view the statement again.

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, --- equivalent filter 6 equijoin_preds, --- equivalent JOIN filter such as where. id = B. id 7 nonequijoin_preds, ---- Wait for JOIN to filter 8 range_preds, ---- range filter >>=< = between and 9 like_preds, ---- LIKE to filter 10 null_preds, ---- NULL filter 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 & gt; selectcount (*) fromtestwhereobject_id = 23121; COUNT (*) ---------- 1 SQL> select 2 r. nameowner, 3 o. nametable_name, 4 c. namecolumn_name, 5 equality_preds, --- equivalent Filtering 6 equijoin_preds, --- equi JOIN filter, for example, where a. id = B. id 7 nonequijoin_preds, ---- unequal JOIN Filtering 8 range_preds, ---- range filtering >>=< = between and 9 like_preds, ---- LIKE Filter 10 null_preds, ---- NULL Filter 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 Android. name = 'Scott 'ando. name = 'test '; Norowsselected

4. There may be questions here. in step 3, SQL uses the object_id predicate to filter data. Why is the SQL still not found? This is because the information has not been refreshed to the disk.

SQL> select 2 r. name owner, 3 o. name table_name, 4 c. name column_name, 5 equality_preds, --- equivalent filter 6 equijoin_preds, --- equivalent JOIN filter such as where. id = B. id 7 nonequijoin_preds, ---- Wait for JOIN to filter 8 range_preds, ---- range filter >>=< = between and 9 like_preds, ---- LIKE to filter 10 null_preds, ---- NULL filter 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 invalid parameter RANGE_PREDS valid parameter TIMESTAMP valid parameter ---------------- -------------- valid ----------- ------------ ---------- --------- scott test OBJECT_ID 1 0 0 0 0 0 31-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. nameowner, 3 o. nametable_name, 4 c. namecolumn_name, 5 equality_preds, --- equivalent Filtering 6 equijoin_preds, --- equi JOIN filter, for example, where a. id = B. id 7 nonequijoin_preds, ---- unequal JOIN Filtering 8 range_preds, ---- range filtering >>=< = between and 9 like_preds, ---- LIKE Filter 10 null_preds, ---- NULL Filter 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 Android. 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 031-MAR-14 Scott test OBJECT_ID 1 0 0 0 031-MAR-14

After a period of time, you can use this SQL statement to find that the object_id column in the TEST table is treated as a predicate condition by SQL. In addition, you can use the following SQL statement to refresh the current memory information to the disk, so that you can see the information immediately.

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-alias table which are listed in condition filter! /