Column-less Table Access

2014-01-02

Tags

While writing some JUnit tests after fixing bugs in dependency analysis views, I came up with the following query:

1) test query

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:

2) unrelated expression

SELECT sys_guid() 
  FROM dual;

3) all-column wildcard aggregation

SELECT COUNT(*) 
  FROM bonus;

4) pseudo column

SELECT rownum AS row_num
  FROM dual
CONNECT BY rownum <= 1000;

5) Cartesian product

SELECT e.empno, e.ename
  FROM emp e, dept d;

Based on that I’ve built the test case as follows:

6) test setup 1/2

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;

7) test setup 2/2

tvdca.sh user=tvdca password=tvdca host=groemitz sid=phs112

8) test run and results

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:

9) JUnit test

@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);
}

Leave a Reply Cancel reply