Hi Javior, sorry for the late reply. I have given the number of records in the mentioned table as required.
Select count(*) from account_master; >> 1920523 Select count (distinct account_num) from account_master; >> 1920523 Select account_num, count(account_num) from account_master group by account_num; >> all are single account numbers only Thanks Daiesh. On Apr 22, 5:21 pm, Javier Montani <jmont...@gmail.com> wrote: > Could you provide the following: > Select count(*) from account_master; > Select count (distinct account_num) from account_master; > Select account_num, count(account_num) from account_master group by > account_num; > > 2009/4/22 Daiesh <mcavenkad...@gmail.com> > > > > > 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 -~----------~----~----~----~------~----~------~--~---
