sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ken Krugler <kkrugler_li...@transpac.com>
Subject Re: [sqoop-user] Slow queries with table that has index when using OraOop
Date Sat, 03 Sep 2011 23:35:28 GMT
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.


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?


-- 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
custom big data solutions & training
Hadoop, Cascading, Mahout & Solr

View raw message