Re: Why so many LIO's for select statement doesn't fetch any rows?

From: Carlos <miotromailcarlos_at_netscape.net>
Date: 1 Apr 2005 10:47:44 -0800
Message-ID: <1de5ebe7.0504011047.51540da9@posting.google.com>

"ashiqsham" <ashiqsham_at_gmail.com> wrote in message news:<1112335624.878055.256750_at_g14g2000cwa.googlegroups.com>...
> This is my query plan
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 0 TABLE ACCESS BY INDEX ROWID ATTRIBUTE_VAL_HEADERS
> 0 INDEX RANGE SCAN I_ATTRIBUTE_VAL_HEADERS_FK4 (object
> id 33065)
> for the below query
>
> SELECT UNQ_ATT_ID
> FROM
> ATTRIBUTE_VAL_HEADERS WHERE OBJECT_ID = :b1 AND DEFINITION_ID = :b2
> AND
> DATA_SOURCE = :b3

So my guess was right ;-)

It seems your index statistics are wrong. See ANALYZE or, better, DBMS_STATS. In the meantime, try the query :

SELECT /*+ FULL(ATTRIBUTE_VAL_HEADERS) */ UNQ_ATT_ID FROM ATTRIBUTE_VAL_HEADERS WHERE OBJECT_ID = :b1 AND DEFINITION_ID = :b2 AND DATA_SOURCE = :b3

It should be better this way if the index is not ok.

Tom Kyte says: 'Full scans are not evil'.

HTH. Carlos. Received on Fri Apr 01 2005 - 12:47:44 CST