I am willing to concede victory to you.... -----Original Message----- Kanagaraj Sent: Friday, December 07, 2001 5:15 PM To: Multiple recipients of list ORACLE-L 2001,
Hi all, Beat this one: (this is just *one* of the ugly ones in an Apps environment - A developer and I are still working on tuning it) - runs for about 8 hours with *lots* and *lots* of unnecessary LIO due to nested joins on multiple tables each of which are >1 million.... Cannot tune this using CBO since this is *not* supported in Oracle Apps 10.7/7.3.4 (or even 11.0/8.0.6) for that matter. I win! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Wanna know the reason for the season? Click on 'http://www.needhim.org' ** The opinions and statements above are entirely my own and not those of my employer or clients ** select ood.organization_code vendor_serial_number , vendor_lot_number , sph.date_shipped parent_serial_number , substr ( mil . segment1 , 2 , 7 ) order_number , msi . segment1 , msi . description , msn . attribute1 , msn . serial_number , sph . date_shipped , rac . customer_name , sha . attribute8 , cis . item_cost , gsb . name , ood . organization_code , gsb . currency_code , sota . name order_type , sha . attribute6 InSync , decode ( msn . serial_number , '' , sum ( moq . transaction_quantity ) , 1 ) trx_qty from snrrrep . cst_item_costs cis , snrrrep . xxg_in_product_structure_v xips , snrrrep . mtl_system_items msi , snrrrep . mtl_serial_numbers msn , snrrrep . mtl_item_locations mil , snrrrep . so_headers_all sha , snrrrep . so_picking_headers_all sph , snrrrep . so_picking_lines_all spl , snrrrep . so_order_types_all sota , snrrrep . ra_customers rac , snrrrep . gl_sets_of_books gsb , snrrrep . org_organization_definitions ood , snrrrep . mtl_secondary_inventories msec , snrrrep . mtl_onhand_quantities moq where ood . organization_id = msi . organization_id and gsb . set_of_books_id = ood . set_of_books_id and sha . customer_id = rac . customer_id (+) and msec.attribute3 = 'SNRR' and moq . locator_id = mil . inventory_location_id and moq . organization_id = mil . organization_id and moq . inventory_item_id = msi . inventory_item_id and moq . organization_id = msi . organization_id and msec . organization_id = moq . organization_id and msec . secondary_inventory_name = moq . subinventory_code and sha . order_type_id = sota . order_type_id (+) and moq . organization_id = msn . current_organization_id (+) and moq . inventory_item_id = msn . inventory_item_id (+) and moq . locator_id = msn . current_locator_id (+) and nvl ( msn . current_status , '9999' ) != 4 and substr ( mil . segment1 , 2 , 7 ) = to_char ( sha . order_number (+) ) and sha . header_id = sph . order_header_id (+) and sph . picking_header_id = spl . picking_header_id (+) and ( ( moq . inventory_item_id = spl . inventory_item_id ) OR ( substr ( mil . segment1 , 2 , 7 ) is null ) OR ( substr ( mil . segment1 , 2 , 7 ) not in ( Select to_char ( order_number ) from snrrrep . so_headers_all ) ) ) and ( ( spl . picking_line_id = ( select max ( spla1 . picking_line_id ) from snrrrep . so_picking_headers_all spha1 , snrrrep . so_picking_lines_all spla1 where spha1 . order_header_id = sha . header_id and spha1 . picking_header_id = spla1 . picking_header_id and spla1 . inventory_item_id = msi . inventory_item_id ) ) OR ( substr ( mil . segment1 , 2 , 7 ) not in ( Select to_char ( order_number ) from snrrrep . so_headers_all ) ) OR ( substr ( mil . segment1 , 2 , 7 ) is null ) ) and msi . inventory_item_id = xips . inventory_item_id and msi . organization_id = xips . organization_id and cis . inventory_item_id = msi . inventory_item_id and cis . organization_id = msi . organization_id and cis . cost_type_id = 1 and ood . organization_code between nvl ( : p_org_from , ood . organization_code ) and nvl ( : p_org_to , ood . organization_code ) and moq . subinventory_code between nvl ( : p_subinv_from , moq . subinventory_code ) and nvl ( : p_subinv_to , moq . subinventory_code ) and xips . product_line between nvl ( : p_prod_from , xips . product_line ) and nvl ( : p_prod_to , xips . product_line ) and msi . segment1 between nvl ( : p_item_from , msi . segment1 ) and nvl ( : p_item_to , msi . segment1 ) and nvl ( rac . customer_name , '@@@' ) between nvl ( : p_customer_from , nvl ( rac . customer_name , '@@@' ) ) and nvl ( : p_customer_to , nvl ( rac . customer_name , '@@@' ) ) and nvl ( substr ( mil . segment1 , 2 , 7 ) , '000' ) between nvl ( : p_so_from , nvl ( substr ( mil . segment1 , 2 , 7 ) , '000' ) ) and nvl ( : p_so_to , nvl ( substr ( mil . segment1 , 2 , 7 ) , '000' ) ) and nvl ( sha . attribute5 , '@@@' ) between nvl ( : p_insync_from , nvl ( sha . attribute5 , '@@@' ) ) and nvl ( : p_insync_to , nvl ( sha . attribute5 , '@@@' ) ) and nvl ( sph . date_shipped , sysdate ) between nvl ( : p_ship_date_from , nvl ( sph . date_shipped , sysdate ) ) and nvl ( : p_ship_date_to , nvl ( sph . date_shipped , sysdate ) ) and nvl ( msn . attribute1 , '@@@' ) between nvl ( : p_lo_from , nvl ( msn . attribute1 , '@@@' ) ) and nvl ( : p_lo_to , nvl ( msn . attribute1 , '@@@' ) ) group by substr ( mil . segment1 , 2 , 7 ) , msi . segment1 , msi . description , msn . attribute1 , msn . serial_number , sph . date_shipped , rac . customer_name , sha . attribute8 , cis . item_cost , gsb . name , sota . name , ood . organization_code , gsb . currency_code , sha . attribute6 , vendor_serial_number , vendor_lot_number , parent_serial_number , spl . picking_line_id ORDER BY 2 ASC,1 ASC,3 ASC,4 ASC,10 ASC,16 ASC,17 ASC > -----Original Message----- > From: Eric D. Pierce [mailto:[EMAIL PROTECTED]] > Sent: Friday, December 07, 2001 4:20 PM > To: Multiple recipients of list ORACLE-L > Subject: ugly sql contest entry / Re: ORACLE-L Digest -- Volume 2001, > Number 333 > > > 1 UPDATE > 2 TRIO_STUDENT_MASTERF00S01part5 T > 3 SET > 4 ( > 5 T.T08_STUDENT_RACE_ETHNICITY > 6 ) > 7 = ( > 8 SELECT > 9 decode( > 10 decode( X.STU_ETH_CODE, > 11 'D', '0', /* Declined to State */ > 12 '8', '0', /* Other (Not Listed) > (10/8/1999 ??) */ > 13 '9', '0', /* No Response */ > 14 '1', '1', /* American Indian/ Native American */ > 15 '2', '3', /* Black/ African American */ > 16 '3', '4', /* Chicano/ Mexican American */ > 17 '4', '4', /* Other Hispanic (Not Listed) */ > 18 'A', '4', /* Central American */ > 19 'B', '4', /* South American */ > 20 'P', '4', /* Puerto Rican */ > 21 'Q', '4', /* Cuban */ > 22 '5', '2', /* Other Asian */ > 23 'C', '2', /* Chinese */ > 24 'J', '2', /* Japanese */ > 25 'K', '2', /* Korean */ > 26 'L', '2', /* Laotian */ > 27 'M', '2', /* Cambodian */ > 28 'R', '2', /* Asian Indian */ > 29 'S', '2', /* Other Southeast Asian */ > 30 'T', '2', /* Thai */ > 31 'V', '2', /* Vietnamese */ > 32 '6', '6', /* Other Pacific Islander > (new 10/8/1999) */ > 33 'F', '6', /* Filipino "" */ > 34 'G', '6', /* Guamanian "" */ > 35 'H', '6', /* Hawaiian "" */ > 36 'N', '6', /* Samoan "" */ > 37 '7', '5', /* White/ Caucasian */ > 38 decode( Z.STU_ETH_CODE, > 39 'D', '0', /* Declined to > State */ > 40 '8', '0', /* Other (Not > Listed) (10/8/1999 ??) */ > 41 '9', '0', /* No Response > */ > 42 '1', '1', /* American > Indian/ Native American */ > 43 '2', '3', /* Black/ African > American */ > 44 '3', '4', /* Chicano/ > Mexican American */ > 45 '4', '4', /* Other Hispanic > (Not Listed) */ > 46 'A', '4', /* Central > American */ > 47 'B', '4', /* South American > */ > 48 'P', '4', /* Puerto Rican > */ > 49 'Q', '4', /* Cuban > */ > 50 '5', '2', /* Other Asian > */ > 51 'C', '2', /* Chinese > */ > 52 'J', '2', /* Japanese > */ > 53 'K', '2', /* Korean > */ > 54 'L', '2', /* Laotian > */ > 55 'M', '2', /* Cambodian > */ > 56 'R', '2', /* Asian Indian > */ > 57 'S', '2', /* Other Southeast > Asian */ > 58 'T', '2', /* Thai > */ > 59 'V', '2', /* Vietnamese > */ > 60 '6', '6', /* Other Pacific > Islander (new 10/8/1999) */ > 61 'F', '6', /* Filipino > "" */ > 62 'G', '6', /* Guamanian > "" */ > 63 'H', '6', /* Hawaiian > "" */ > 64 'N', '6', /* Samoan > "" */ > 65 '7', '5', /* White/ > Caucasian */ > 66 '*** no/bad data ***' > 67 ) > 68 ), > 69 '1', '1', > 70 '2', '2', > 71 '3', '3', > 72 '4', '4', > 73 '5', '5', > 74 '6', '6', > 75 '7', '7', > 76 '0', > 77 decode( t.student_ssn, > 78 '[several deleted]', '5', > > .. > > 101 'x' > 102 ), > 103 '*' > 104 ) > 105 FROM > 106 TRIO_STUDENT_MASTERF00S01part5 T2, > 107 SIS_CSUS_ALL_spring2001_eos1 X, > 108 SIS_CSUS_ALL_fall_2000_eos1 Z > 109 WHERE > 110 T.STUDENT_SSN = T2.STUDENT_SSN > 111 AND > 112 T2.STUDENT_SSN = X.STU_ID (+) > 113 AND > 114 T2.STUDENT_SSN = Z.STU_ID (+) > 115 ) > 116 WHERE > 117 T.T08_STUDENT_RACE_ETHNICITY = '-' > 118 AND > 119 T.STUDENT_SSN IN > 120 ( > 121 SELECT > 122 T3.STUDENT_SSN > 123 FROM > 124 TRIO_STUDENT_MASTERF00S01part5 T3 > 125 WHERE > 126 T3.T08_STUDENT_RACE_ETHNICITY = '-' > 127* ) > > 79 rows updated. > > > Commit complete. > > > > COUNT(*) T > --------- - > 4 0 > 6 1 > 9 2 > 28 3 > 29 4 > 124 5 > 3 6 > 1 7 > --------- > 204 > > 8 rows selected. > > 1 select > 2 count(*), > 3 T.T08_STUDENT_RACE_ETHNICITY > 4 from > 5 TRIO_STUDENT_MASTERF00S01part5 T > 6 group by > 7* T.T08_STUDENT_RACE_ETHNICITY > > > -------------------------------------------------------------- > ---------- > > On 7 Dec 2001, at 15:27, [EMAIL PROTECTED] wrote: > > > > > > I think ugly SQL is appropriate. > > > > We certainly see enough of it here anyway. :) > > .. > > >> do we need to have a "ugliest SQL statement" contest? > >> > >> OT list? > >> > >> I have an entry ready > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Eric D. Pierce > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
