ok i've heard this term over and over again and i don't know what it's about! what are these hints? and how do you stick em in a reports query or a form sql block!! .and is the myth true? does it really make a performance difference? .if anyone has more info please patch it through. hmmm maybe i shouldn't use the term "patch" it might irritate a few overworked techies or in other words patchophobics! :lol:

Hints are added to SQL (in the main). . .You put them in the SELECT statement eg . .SELECT /*+ FIRST_ROWS(1) */ <select item list> . .HOWEVER you need to know what you are doing first. . .I would suggest taking a look at the ORacle Performance Tuning Guide first especially if you are totally new to this. . .In the wrong hands you can wreak havoc by poor or incorrect use of optimiser hints. .Trust me i know from experience how easy it is to get things wrong.

[quote:ad59da7f24]In the wrong hands you can wreak havoc by poor or incorrect use of optimiser hints. .Trust me i know from experience how easy it is to get things wrong.[/quote:ad59da7f24] . .So do several clients :lol: . .A point perhaps worth remembering is that most of the time the Cost-Based optimiser will do a lot of the figuring out for you provided you give it some reasonably up-to-date stats to work on. Eg a common problem encountered during datamigration excercises (particularly into new database instances) is that after the first couple of entities eg person/assignment thereafter performance starts to dip. This is usually because the stats process hasn't been run to update the stats on the affected tables. There is a standard process helpfully entitled 'Gather Schema Stats' within the SYSADMIN responsiblity that will address this problem. As Mr Brookes will doubtless attest it is possible to run this whilst a migration program is running and may even result in a noticeable improvement in throughput once complete.

ok.. so is it possible to optimize this SQL: . . SELECT . POV.vendor_name . POV.employee_id . A.assignment_id . POV.vendor_id . A.business_group_id . A.assignment_number . max(APC.check_date) effective_date . INV.invoice_num . INVD.distribution_line_number . INVD.description expense_item . L.DESCRIPTION pay_element_name . INVD.amount . . FROM . PO_VENDORS POV . AP_CHECKS_ALL APC . AP_INVOICES_ALL INV . AP_INVOICE_DISTRIBUTIONS_ALL INVD . per_all_assignments_f A . AP_LOOKUP_CODES L . . WHERE POV.vendor_id = APC.vendor_id . AND POV.vendor_id = INV.vendor_id . AND INV.invoice_id = INVD.invoice_id . AND POV.Employee_Id = A.Person_Id . AND UPPER(INVD.Description) = L.LOOKUP_CODE . AND POV.employee_id is not null . AND NVL(invd.amount 0) <> 0 . AND INVD.description like 'TAX%' . AND L.lookup_type = 'DWP_OIE_ELEMENT_MAPPING' . GROUP BY . POV.vendor_name . POV.employee_id . A.assignment_id . A.assignment_number . POV.vendor_id . A.business_group_id . INV.invoice_num . INVD.distribution_line_number . INVD.description . L.DESCRIPTION . INVD.amount;

Dunno why but that 'GROUP BY' makes me nervous...

what's wrong with it? .i'm using MAX so the statement has to have a group by! .so how can i make it better? this sql inserts rows into a staging table. the whole thing is part of a procedure (package). .help!! :?

Ok to me 'GROUP BY' suggests 'SORT-MERGE' on x-teen tables some of which will be potentially large. There may be a way to break that 'MAX' function out into an inline view of some sort (cue some 9i expert...). Another couple of issues spring to mind: . .1. I assume you're in a shared-HR install - eg purchasing/payables possibly iexpenses. If not (in fact even if it is) may I suggest you put some sort of date limitation on the assignments table eg . ....and trunc(sysdate) between a.effective_start_date and a.effective_end_date . .At least then all hell won't break loose on this query if and when your client friends do take the full HR product and you're facing the prospect of multiple assigment records because of the wonderful date-track... . .2. Since you're joining POV.employee_id to a.person_id (which is a not null column) then having 'AND POV.employee_id IS NOT NULL' doesn't really add anything. ie you could remove it . .3. The NVL function in 'AND NVL(invd.amount 0) <> 0' doesn't do much - it would ignore nulls anyway which by the looks of it is what you want. . .So with all of that in mind here's my (totally untested undocumented just like I was still at the Big O) stab at what you're after: . .NB Get a sysadmin bod to do a Gather Schema Stats ('ALL') first just to rule that out of the equation... . .SELECT .POV.vendor_name .POV.employee_id .A.assignment_id .POV.vendor_id .A.business_group_id .A.assignment_number .APC.check_date effective_date .INV.invoice_num .INVD.distribution_line_number .INVD.description expense_item .L.DESCRIPTION pay_element_name .INVD.amount .FROM .PO_VENDORS POV .AP_CHECKS_ALL APC .AP_INVOICES_ALL INV .AP_INVOICE_DISTRIBUTIONS_ALL INVD .per_all_assignments_f A .AP_LOOKUP_CODES L .WHERE POV.vendor_id = APC.vendor_id .AND POV.vendor_id = INV.vendor_id .AND INV.invoice_id = INVD.invoice_id .AND POV.Employee_Id = A.Person_Id .AND UPPER(INVD.Description) = L.LOOKUP_CODE .--AND POV.employee_id is not null .--AND NVL(invd.amount 0) <> 0 .AND invd.amount <> 0 .AND INVD.description like 'TAX%' .AND L.lookup_type = 'DWP_OIE_ELEMENT_MAPPING' .AND APC.CHECK_DATE = . (SELECT MAX (APC2.CHECK_DATE) . FROM AP_CHECKS_ALL APC2 . WHERE APC2.VENDOR_ID = APC.VENDOR_ID . ) .; . .PS The usual disclaimers apply! If it works then I'm happy to have helped 8) - if it made things worse (eg street-lights flickering outside whilst running it) then Brookes wrote it. :roll: . .Good luck!

A straight explain of the statement on my system (having no knowledge of the size of your tables etc) gives... . .SELECT STATEMENT CHOOSE . SORT(GROUP BY) . TABLE ACCESS(BY INDEX ROWID) APPLSYS.FND_LOOKUP_VALUES . NESTED LOOPS . NESTED LOOPS . NESTED LOOPS . NESTED LOOPS . NESTED LOOPS . TABLE ACCESS(BY INDEX ROWID) PO.PO_VENDORS . INDEX(FULL SCAN) PO.PO_VENDORS_N1 . TABLE ACCESS(BY INDEX ROWID) AP.AP_CHECKS_ALL . INDEX(RANGE SCAN) AP.AP_CHECKS_N6 . TABLE ACCESS(BY INDEX ROWID) AP.AP_INVOICES_ALL . INDEX(RANGE SCAN) AP.AP_INVOICES_N2 . TABLE ACCESS(BY INDEX ROWID) HR.PER_ALL_ASSIGNMENTS_F . INDEX(RANGE SCAN) HR.PER_ASSIGNMENTS_F_N12 . TABLE ACCESS(BY INDEX ROWID) AP.AP_INVOICE_DISTRIBUTIONS_ALL . INDEX(RANGE SCAN) AP.AP_INVOICE_DISTRIBUTIONS_U1 .INDEX(RANGE SCAN) APPLSYS.FND_LOOKUP_VALUES_U1 . .First port of call is to try and remove the full scan on the non-unque N1 index of PO_VENDORS. .Failing that break out the Max(apc.check_date) to a function call or a correlated sub-query to pass back the row with the max date. . .As you can see trying to tune a statement with no prior knowledge as to what it is trying to achieve is almost impossible. .I as an impartial observer would want to question why you are bringing back half the data items you have in the select list and inquire as to the purpose of the statement.