Almost an entry for Oracle WTF

Ok, I know "why" but WHY!! Names have been changed to protect the guilty. SELECT * FROM T1 WHERE (STATUS IN ('STATUS1', 'STATUS2', 'STATUS3') AND T1TYPE NOT IN ('PRICE','BLANK') AND 'SOMETEXTVALUE' IN ((SELECT COMPANY FROM COMPANIES WHERE COMPANY = 'SOMETEXTVALUE' ) UNION (SELECT COMPANY FROM COMPANIES WHERE PARENTCOMPANY = 'SOMETEXTVALUE' )) AND ORGID= 'SOMEORGID') AND ( ((T1TYPE NOT IN ('PRICE', 'BLANK') OR PRIVATE= 'Y' ) AND SITEID= 'SOMESITE' ) OR (T1TYPE IN ('PRICE', 'BLANK') AND PRIVATE= 'N' ) ) / PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost -------------------------------------------------------------------------------- 0 SELECT STATEMENT 49362 7230K 334 1 MERGE JOIN CARTESIAN 49362 7230K 334 2 VIEW VW_NSO_1 2 100 9 3 SORT UNIQUE 2 90 9 4 UNION-ALL * 5 INDEX SKIP SCAN COMPANY_NDX 1 20 2 * 6 TABLE ACCESS BY INDEX ROWID COMPANIES 1 70 3 * 7 INDEX SKIP SCAN COMPANY_NDX 1 2 8 BUFFER SORT 24681 2410K 334 * 9 TABLE ACCESS FULL T1 24681 2410K 163 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("COMPANIES"."COMPANY"='SOMECOMPANY') filter("COMPANIES"."COMPANY"='SOMECOMPANY') 6 - filter("COMPANIES"."PARENTCOMPANY"='SOMECOMPANY') 7 - access("COMPANIES"."COMPANY"='SOMECOMPANY') filter("COMPANIES"."COMPANY"='SOMECOMPANY') 9 - filter(("T1"."STATUS"='STATUS1' OR "T1"."STATUS"='STATUS2' OR "T1"."STATUS"='STATUS3') AND "T1"."T1TYPE"<>'PRICE' AND "T1"."T1TYPE"<>'BLANK' AND "T1"."ORGID"='SOMEORIGID' AND (("T1"."T1TYPE"<>'PRICE' AND "T1"."T1TYPE"<>'BLANK' OR "T1"."PRIVATE"='Y') AND "T1"."SITEID"='SOMESITE' OR ("T1"."T1TYPE"='BLANK' OR "T1"."T1TYPE"='PRICE') AND "T1"."PRIVATE"='N')) Get a call from a really nice application support person - "Did YOU do anything, our "T1" reports are taking a long time." After a bit of digging - "We applied a patch on the weekend". Took about 30 seconds to track down SQL like the one above. Was ran for every entry (about 1,000 rows) in another table to "validate" the presence of data in T1. My answer "Back the patch out, call the vendor, remind them what a bind variable is, send this nice explanation of a cartesion join to them too." I love my job! (really I do)