Hi Peter, On Sep 1, 2011, at 5:21pm, Peter Hall wrote:
> Hi Ken, > > OraOop should be using the NO_INDEX hint, we'll get that fixed for the next > release. > > Until then you may be able to work around this issue with session > initialization statements. > > Try > ALTER SESSION SET optimizer_index_cost_adj=10000; > to tell oracle to not use the index. You may need to experiment with this a > little to find a value that will have the desired effect. [snip] It's much faster with that change, thanks for that suggestion! Though it's still not as fast as the other DB that doesn't have the composite index - 3M rows/min vs. 13M rows/min. It would be great to get a version that has the NO_INDEX hint soon - any idea when that might be released? Thanks, -- Ken >> We ran into an interesting performance issue recently, using OraOop to pull >> data. >> >> We've got two tables in two different Oracle DBs. They use identical >> schemas, but one of the tables has a composite index on a number of columns, >> one of which is used in our WHERE clause. >> >> The table without this composite index has good performance - about 10M >> rows/minute using 8 mappers. And very low load on the DB server. >> >> The table access is via "TABLE ACCESS BY ROWID RANGE" >> >> The table with the composite index has really bad performance - only 33K >> rows/minute using 8 mappers. And very high load. >> >> The table access is via "TABLE ACCESS BY LOCAL INDEX ROWID" and then by >> "INDEX SKIP SCAN" on the composite index. >> >> It looks like we need to provide an SQL hint >> (http://ss64.com/ora/select_hints.html) that tells Oracle to avoid using an >> index scan. E.g. >> >> SELECT /*+ NO_INDEX(<table name>) */ <columns> FROM <table owner>.<table >> name> WHERE ... >> >> Though it would seem like OraOop should always provide this hint, as >> otherwise accessing rows by id will have really bad performance, yes? >> >> If this isn't the case, then where should this hint support be added - in >> Sqoop (and picked up by OraOop), or just in OraOop? > -------------------------- Ken Krugler +1 530-210-6378 http://bixolabs.com custom big data solutions & training Hadoop, Cascading, Mahout & Solr
