Monday, April 03, 2006

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)


Anonymous said...

An excellent and interesting article about Oracle, which I use at work.

Also, I like that you commented on the use of T1 internet which can make your job easier, because it offers more bandwidth and faster connections.

Adi said...

Oes Tsetnoc one of the ways in which we can learn seo besides Mengembalikan Jati Diri Bangsa. By participating in the Oes Tsetnoc or Mengembalikan Jati Diri Bangsa we can improve our seo skills. To find more information about Oest Tsetnoc please visit my Oes Tsetnoc pages. And to find more information about Mengembalikan Jati Diri Bangsa please visit my Mengembalikan Jati Diri Bangsa pages. Thank you So much.
Oes Tsetnoc | Semangat Mengembalikan Jati Diri Bangsa