Re: Intermedia Catalog Indexing

Answer embedded

"Jusung Yang" <jusungyang_at_yahoo.com> wrote in message news: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.
>

At first, a programmer used rownum, but we realized that was incorrect. Currently just using the 'maxrecords' in the ADO recordset. I know we incur a performance hit, but typically
the sort is on a primary key, so I believe the optimizer 'sort of' ignores.

You can try an analytic function
http://www.quest-pipelines.com/newsletter-v3/0402_D.htm

> 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 Wed Aug 07 2002 - 21:16:44 CEST