Hi All, Thanks to all for your valuable reply's
The table is huge and the query is below SELECT status, ROWID FROM account_master WHERE account_num = :b1 its explained plan as below PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 22 | 2 | | 1 | INDEX RANGE SCAN | IND_ALL_ACC | 1 | 22 | 2 | --------------------------------------------------------------------- Even the above query cost is too low why its take time to execute? please could you suggest on this? Thanks Daiesh On Apr 21, 6:28 pm, ddf <orat...@msn.com> wrote: > Comments embedded. > > On Apr 21, 7:57 am, Javier Montani <jmont...@gmail.com> wrote: > > > Some times the issue is the way the indexes are managed. > > The query plan already states an index is used; please explain your > comment. > > > Try something like: > > SELECT /*+ INDEX(l item_order_ix) */ l.line_item_id, order_id, l.unit_price > > * l.quantity FROM order_items l WHERE l.order_id = :b1; > > Again, an index IS being used, the OP believes that the index range > scan is performing poorly. As I stated earlier it's not likely that > the index range scan is misbehaving, it's more likely that the data > volume is so great from that access operation that Oracle is having to > filter the results on one or more remaining conditions. I've > personally seen situations where two columns, always used in queries, > are indexed independently and only one of the indexes is used, causing > Oracle to filter the remaining data on the second, indexex value. > Creating a concatenated index on the paired columns substantially > reduced the resultset and improved the query time from 18+ minutes to > less than a second. So what could appear to be an index scan issue > (based solely upon the query plan) became an issue of excessive > filtering (revealed by the output from autotrace). > > This is why I question the OP's assumption the index range scan is at > fault. > > > or > > SELECT /*+NO_EXPAND */ p.header_id, l.line_id, l.revenue_amount FROM > > so_lines_all p, so_lines_all l WHERE p.header_id = :b1 > > AND (l.parent_line_id = p.line_id OR l.service_parent_line_id = p.line_id); > > From the documentation: > > "The NO_EXPAND hint prevents the cost-based optimizer from considering > OR-expansion for queries having OR conditions or IN-lists in the WHERE > clause." > > Not knowing how his query is written you cannot state that the > NO_EXPAND hint will actually provide any improvement. And it won't > improve a filter operation on a large subset of data. > > > No_expand game me very good performance improvement. > > Because the query you posted using that hint met the conditions that > NO_EXPAND addresses. We have no idea how the OP has written this > 'problematic' query so such blanket suggestions are out of place, in > my opinion. > > > > > 2009/4/21 ddf <orat...@msn.com> > > > > On Apr 21, 7:09 am, Daiesh <mcavenkad...@gmail.com> wrote: > > > > Hi All, > > > > > How do speed up the index range scan in Oracle? > > > > > Advance wishes!!!.. > > > > > Thanks > > > > Daiesh > > > > Why do you 'need' to 'speed up the index range scan'? Why do you > > > think that is the bottleneck? I'd be using autotrace instead of > > > simply using explain plan to see what I/O activity your generating > > > with that query. The index range scan is probably running as fast as > > > possible but it's generating a large result set to filter on another > > > criteria. Filtering large volumes of data will consume time, time you > > > might be able to save with a concatenated index. > > > > David Fitzjarrell- Hide quoted text - > > > - Show quoted text - > > David Fitzjarrell --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
