Why is this?
It looks illogical to me as you seem to be forcing the DB to be doing
exactly what I expect it to be doing anyway!
I do not doublt what you are saying as I have seen other postings
suggesting something similar, but why does it make a difference?
ETA
"Terry Dykstra" <dontreply_tddykstra_at_forestoil.ca> wrote in message news:<phhsa.20253$yv1.1367285_at_news2.telusplanet.net>...
> If the Order By is indeed the culprit, you could change your query to do:
>
> select * from (your original query statement excluding the order by)
> order by arcos_items_id
>
> --
> Terry Dykstra
> Canadian Forest Oil Ltd.
> "Ed Stevens" <nospam_at_noway.nohow> wrote in message
> news:oqr2bvk9d1jegdmqio8obeitgar4u6fpd2_at_4ax.com...
> > On 1 May 2003 10:42:01 -0700, bogachkov_at_hotmail.com (gary b) wrote:
> >
> > >We have a client with a basic oracle setup used in conjunctin with an
> > >enterprise java project
> > >
> > >We are experiencing a performance problem when issuing queries against
> > >a specific table which has approximately 12 million records in it. The
> > >problem is that, although the query does eventually return correct
> > >results, it takes a very long time for it to execute. (approx 90
> > >seconds, and often more than 10 minutes when run consecutively)
> > >
> > >We have verified that the oracle execution time is the bottleneck (as
> > >opposed to transport of large result sets over the network) by
> > >modifying the query to return only one result - it was still just
> > >about as slow. We have also verified that the problem isn't connected
> > >to a lack of indexes by modifying the query to not have any joins and
> > >to only order by columns that have indexes placed on them -
> > >performance was the same.
> > >
> > >
> > >After various tweakings, we managed to narrow down the behaviour to be
> > >caused by the ORDER BY part of the query. Thus if we don't have an
> > >ORDER BY clause - the query runs considerably faster. (in the
> > >milliseconds) However, we DO have to have an ORDER BY in the query and
> > >it is not an option to have a seperate view for each one of the
> > >possible fields that we could possibly be ordering by in this
> > >dynamically generated query (since there are many possible
> > >combinations).
> > >
> > >We have tried setting the sort_area_size to a large value - but this
> > >did not result in any measurable performance improvement.
> > >
> > >
> > >the primary query (without any of the optimizations that didn't work
> > >that i mentioned above):
> > >-------------------------------
> > >
> > >SELECT
> > >itm.arcos_items_id, itm.item_type, itm.arcos_reports_id,
> > >itm.arcos_registrant_id, itm.txn_code,
> > >itm.action_code, itm.ndc_num, itm.wic_num, itm.qty, itm.unit_code,
> > >itm.assoc_dea_num, itm.order_num, itm.txn_date,
> > >itm.correction_num, itm.arcos_strength, itm.txn_num, itm.product_desc,
> > >itm.control_code, itm.pkg_size, itm.units_pkg,
> > >itm.unit_of_measure, itm.arcos_entered_date, itm.rejected_reason,
> > >itm.assoc_loc_num, itm.receiving_report_num,
> > >itm.users_id, itm.dttm_stamp, itm.shipment_num, itm.manual_txn_id,
> > >rep.report_date, rep.frequency AS report_frequency,
> > >rep.reported AS report_reported, reg.reg_dea_num, reg.dc_num,
> > >reg.frequency AS registrant_frequency
> > >FROM arcos_items itm, arcos_reports rep, arcos_registrant reg
> > >WHERE itm.arcos_reports_id=rep.arcos_reports_id
> > >AND itm.arcos_registrant_id=reg.arcos_registrant_id
> > >AND itm.item_type = ?
> > >AND rep.arcos_reports_id = ?
> > >AND (itm.rejected_reason IS NULL)
> > >ORDER BY itm.arcos_items_id
> > >
> > >
> > >thanks, i really appreciate any help on this
> > >-Gary Bogachkov
> > >
> > >
> > >
> > <snip duplicate verbage>
> >
> > I'm going to go out on a bit of limb (for me!) here . . .
> >
> > First, you say that you 'verified' that the problem wasn't from lack
> > of indexes. I'm not sure that your method actually verified anything.
> > Have you done an explain plan to see exactly what access plan is being
> > used?
> >
> > Second, the big thing that jumps out at me is "AND
> > (itm.rejected_reason IS NULL) " This will guarantee a full table
> > scan on the referenced table. Again, go back and do an explain plan
> > to verify.
Received on Tue May 06 2003 - 07:15:27 CDT
