Archives of the TeradataForum

Message Posted: Mon, 16 Dec 2002 @ 22:37:56 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Getting to column usage is fairly easy to do by parsing the access log on V2R4. I just loop through the statements using a cursor, then parse the text based on spaces. I pad all the key words and operators '( , + - etc with spaces, then remove double spaces. The tricky part is trapping the table names so you can get the table aliases. The total effort was about 1/2 day. Virtually all of our queries are generated from Business Objects so they are very structured, even when they are badly written. Everything is in tablename.columnname or tablealias.columnname form, so I don't have to worry about columns with no prefixes You could handle that with a lookup on the data dictionary.

I'm downloading to sqlserver (didn't have privileges to build dynamic sql on production), the code is only 120 lines. Having done it in sql, it seems like perl or c++ (using hashes or arrays) would be better implementation.

I haven't dealt with indexes yet, it is much more challenging. I' thinking I'll run insert explain on the access log statesments to load the QCD database then run an analysis. The QCD db has about 20 tables (without any documentation that I can find), so it's not a trivial task. I'm not totally sure how to tell when secondaries are used. Sometimes they are explicitly named in the explain, but my (limited) understanding is that they may affect the join type even though you don't see "using index # x" in the explain (example given was 2-amp join vs all amps join).

	<Prev	Next>		<<First	<Prev	Next>	Last>>