While writing some JUnit tests after fixing bugs in dependency analysis views, I came up with the following query:
|
1 2 3 4 5 |
SELECT owner, object_type, object_name, operation, table_name FROM tvd_object_usage_v MINUS SELECT owner, object_type, object_name, operation, table_name FROM tvd_object_col_usage_v |
The first view tvd_object_usage_v contains all table/view usages per object. The second view tvd_object_col_usages_v contains all column usages per object.
The idea was to check the completeness of the second view tvd_object_col_usages_v. I believed that there cannot be an object usage without one or more corresponding column usages. Therefore I assumed the query above should retrieve now rows, but obviously I was plain wrong.
Here are some examples of column-less table accesses:
|
1 2 |
SELECT sys_guid() FROM dual; |
|
1 2 |
SELECT COUNT(*) FROM bonus; |
|
1 2 3 |
SELECT rownum AS row_num FROM dual CONNECT BY rownum <= 1000; |
|
1 2 |
SELECT e.empno, e.ename FROM emp e, dept d; |
Based on that I’ve build the test case as follows:
|
1 2 3 4 5 6 7 8 9 |
INSERT INTO tvd_captured_sql_t (cap_id, cap_source) VALUES (-1007, 'SELECT sys_guid() FROM dual; SELECT COUNT(*) FROM bonus; SELECT rownum AS row_num FROM dual CONNECT BY rownum <= 1000; SELECT e.empno, e.ename FROM emp e, dept d;'); COMMIT; |
|
1 |
tvdca.sh user=tvdca password=tvdca host=groemitz sid=phs112 |
|
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 30 31 32 33 34 |
SQL> SELECT operation, table_name 2 FROM tvd_sql_usage_v 3 WHERE cap_id = -1007; OPERAT TABLE_NAME ------ ------------------------------ SELECT DUAL SELECT BONUS SELECT DUAL SELECT EMP SELECT DEPT SQL> SELECT operation, table_name, column_name 2 FROM tvd_sql_col_usage_v 3 WHERE cap_id = -1007; OPERAT TABLE_NAME COLUMN_NAME ------ ------------------------------ ------------------------------ SELECT EMP EMPNO SELECT EMP ENAME SQL> SELECT operation, table_name 2 FROM tvd_sql_usage_v 3 WHERE cap_id = -1007 4 MINUS 5 SELECT operation, table_name 6 FROM tvd_sql_col_usage_v 7 WHERE cap_id = -1007; OPERAT TABLE_NAME ------ ------------------------------ SELECT BONUS SELECT DEPT SELECT DUAL |
These tests are now part of my TVDCA test suite to ensure column-less table access is handled appropriately ;-)
BTW, here is an excerpt of my JUnit test:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
@Test public void testColumnLessTableAccess() { String tabSql = "SELECT COUNT(*) FROM tvd_sql_usage_v WHERE cap_id = -1007 AND table_name LIKE :table_name"; String colSql = "SELECT COUNT(*) FROM tvd_sql_col_usage_v WHERE cap_id = -1007 AND table_name LIKE :table_name and column_name LIKE :column_name"; int count; Map<String, String> namedParameters = new HashMap<String, String>(); // all tables namedParameters.put("table_name", "%"); namedParameters.put("column_name", "%"); count = jdbcTemplate.queryForObject(tabSql, namedParameters, Integer.class); Assert.assertEquals(5, count); count = jdbcTemplate.queryForObject(colSql, namedParameters, Integer.class); Assert.assertEquals(2, count); } |