Re: Intermedia Catalog Indexing

From: Jusung Yang <jusungyang_at_yahoo.com>
Date: 6 Aug 2002 13:14:13 -0700
Message-ID: <42ffa8fa.0208061214.6219bf15_at_posting.google.com>

1. There was an issue with returning large number of rows with an "order by" clause, as reported by a Metalink user on 5/13/2002. I am not sure however, how large is large. Given this situation, I am interested in how you limit your rows returned. You said

> eg 'select * from tabA where contains(fldX,'junk') > 0 order by fldB'
>
> We have not had a problem with sorting and we are doing the same thing.
> About 600+ characters, 100,000 records. But we also restrict the result list
> to <500.

Did you use rownum? Of course ROWNUM will not give you the desired results in the case when you need to sort the entire result set, as was the case with this Metalink poster.

2. An interesting note from ORACLE 9i DOC regarding the /* FIRST_ROWS */ (missing a + ?) hint with order by clause:

Improved Response Time with FIRST_ROWS(n) for ORDER BY Queries

---

declare
cursor c is
select /* FIRST_ROWS(10) */ article_id from articles_tab

   where contains(article, 'Oracle')>0 order by pub_date desc; begin

for i in c
loop
insert into t_s values(i.pk, i.col);
exit when c%rowcount > 11;
end loop;
end; /

---

ORACLE claims the FIRST_ROWS hint improves the performance. I have doubts on this claim. FIRST_ROWS hint is basically useless when used with the "oder by" clause - unless its behavior is somehow different with InterMedia, which I don't believe so. Order by score, now that's a different story. Or maybe the new /*+ FIRST_ROWS(n) */ is sowehow smarter?

Thoughts? Comments? Received on Tue Aug 06 2002 - 22:14:13 CEST