sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ken Krugler <kkrugler_li...@transpac.com>
Subject Slow queries with table that has index when using OraOop
Date Wed, 31 Aug 2011 22:47:46 GMT
Hi there,

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

Ken Krugler
+1 530-210-6378
custom data mining solutions

View raw message