Re: FAST_ROWS does not work properly?

Franz Mueller <franz.mueller_at_orbis.de> wrote in message news:37ba5d66.2701584_at_news.salink.net...
> Hi,
>
> I have got an app which issues the following SQL:
> SELECT .. FROM .. WHERE Col1 like 'A%' ORDER BY Col1
> Since the answer set can be quite large I would like the server to
> output rapidly the rows (instead of maximizing the overall troughput).
> Since there is an index on Col1 I use the hint /* FIRST_ROWS */ which
> should use the index (where the data is akready sorted) and output the
> data directly. On MS-SQL-Server 7 this approach works well. On ORACLE
> it seeems no to work.
>
> Any ideas?
> Thanks
> Franz

It looks OK.
In fact, it does NOT need the first_rows hint to do so.

SQL> select * from emp where ename like 'M%' order by ename   2 /

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- --------- --------- --------- --------- --------- ---------
     7654 MARTIN     SALESMAN       7698 28-SEP-81      1250      1400        30
     7934 MILLER     CLERK          7782 23-JAN-82      1300                  10

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'    2 1 INDEX (RANGE SCAN) OF 'IND_EMP_ENAME' (NON-UNIQUE) SQL> select /*+ first_rows */ * from emp where ename like 'M%' order by ename   2 /

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- --------- --------- --------- --------- --------- ---------
     7654 MARTIN     SALESMAN       7698 28-SEP-81      1250      1400        30
     7934 MILLER     CLERK          7782 23-JAN-82      1300                  10

Execution Plan

   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1 Card=2 B
          ytes=200)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=2 Byte
          s=200)

   2    1     INDEX (RANGE SCAN) OF 'IND_EMP_ENAME' (NON-UNIQUE) (Cost
          =2 Card=2)

You didn't discribe your SQL clearly, we can't know the fact. Maybe there are some impractical structure causes the result. Received on Thu Aug 19 1999 - 02:40:24 CDT