Archives of the TeradataForum

Message Posted: Tue, 06 Dec 2005 @ 11:33:31 GMT

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

You're right about not getting data from the database; what I'm concerned about is the control logic in the queries. Have a look at:

     replace view v1 as
     select * from dbc.sessioninfo
     where username not in ('Big Dave', 'Little Dave', 'Medium Dave');

     explain select * from v1;

     Explanation
       1) First, we lock DBC.SessionTbl for access.
       2) Next, we do an all-AMPs RETRIEVE step from DBC.SessionTbl by way
          of an all-rows scan with a condition of (
          "(DBC.SessionTbl.UserName <> 'Medium Dave ') AND
          ((DBC.SessionTbl.UserName <> 'Little Dave ') AND
          (DBC.SessionTbl.UserName <> 'Big Dave '))") into Spool 1
          (group_amps), which is built locally on the AMPs.  The size of
          Spool 1 is estimated with no confidence to be 5 rows.  The
          estimated time for this step is 0.03 seconds.
       3) Finally, we send out an END TRANSACTION step to all AMPs involved
          in processing the request.
       -> The contents of Spool 1 are sent back to the user as the result of
          statement 1.  The total estimated time is 0.03 seconds.

If that were a real query, you'd have three useids exposed in the EXPLAIN, another security problem (userids are points of attack). If you were in payroll or HR and had a generic query like this for salary lookups, but hid the executives with an in list of SSNs, you'd have the same kind of problems. If you're in the snooping business, this can be wonderful stuff.

We have end users building queries with tools that frequently include long lists of constants. Sometimes if the queries are particularly useful, the users capture the SQL and stash it as a view. That only makes the problem worse because the lists may contain a lot more entries than hand coded SQL.

/dave "all sizes" hough

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