| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: FAST_ROWS does not work properly?
On 19 Aug 1999 07:40:24 GMT, "fumi" <fumi_at_tpts5.seed.net.tw> wrote:
>
>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.
Firstly, it is not clear if Franz did use the hint correctly. Namely in his post the hint lacks the '+' character, making this syntactically incorrect hint being interpreted as simple comment.
Secondly, when dealing with character data, Oracle has to take into account the NLS settings. Correct me (Fumi or Franz or anybody else, because I'm just suspecting this) if I'm wrong, but I think your database uses US7ASCII and your nls_language is AMERICAN, while Franz uses some kind of 8-bit character set, probably WE8ISO8859P1 with GERMAN_GERMANY. Why should this make any difference? Because if you use US7ASCII and have NLS_LANG as AMERICAN_AMERICA then your linguistic sort corresponds to the binary in which strings are stored inside an index. Hence the optimizer knows that records fetched through index are already sorted in the desired order and can return first rows as soon as they are fetched from the table.
On the other hand, with GERMAN_GERMANY.WE8ISO8859P1 the order in the index doesn't directly correspond to the linguistic sort as special German characters don't have their binary sequences in the correct order in relation to other "standard" characters. Hence even if optimizer uses index, it must sort the result set at de end if you use explicit ORDER BY. This means that it can't return first rows as soon as they are fetched - it must first select *all* corresponding rows, sort them using linguistic sort and then return them to the caller.
Here is the same example you used. Two identical queries are executed differently, depending on the NLS settings in effect! Also note from the last query that NLS doesn't have any influence when result is sorted by numerical (or date) values.
SCOTT_at_PO73> ALTER SESSION SET NLS_LANGUAGE='AMERICAN'; Session altered.
SCOTT_at_PO73> REM Here optimizer does not need to perform a SORT! SCOTT_at_PO73> SELECT /*+ FIRST_ROWS*/ * FROM EMP 2 WHERE ENAME LIKE 'M%' ORDER BY ENAME; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------- ---------- --------- ------- -------- ------- ------- -------
7654 MARTIN SALESMAN 7698 28.09.81 1250 1400 30 7934 MILLER CLERK 7782 23.01.82 1300 10
Execution Plan
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=2 B
ytes=80)
1 0 TABLE ACCESS (BY ROWID) OF 'EMP' (Cost=2 Card=2 Bytes=80)
2 1 INDEX (RANGE SCAN) OF 'EMP$ENAME' (NON-UNIQUE)
SCOTT_at_PO73> ALTER SESSION SET NLS_LANGUAGE='GERMAN'; Session altered.
SCOTT_at_PO73> REM Now the optimizer will perform a SORT (with totally SCOTT_at_PO73 REM identical query)! SCOTT_at_PO73> SELECT /*+ FIRST_ROWS*/ * FROM EMP 2 WHERE ENAME LIKE 'M%' ORDER BY ENAME; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------- ---------- --------- ------- -------- ------- ------- ------- 7654 MARTIN SALESMAN 7698 28.09.81 1250 1400 30 7934 MILLER CLERK 7782 23.01.82 1300 10
Execution Plan
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=5 Card=2 B
ytes=80)
1 0 SORT (ORDER BY)
2 1 TABLE ACCESS (BY ROWID) OF 'EMP' (Cost=2 Card=2 Bytes=80
)
3 2 INDEX (RANGE SCAN) OF 'EMP$ENAME' (NON-UNIQUE)
SCOTT_at_PO73> REM With numeric or date data the sort operation is not
SCOTT_at_PO73> REM required!
7902 FORD ANALYST 7566 03.12.81 3000 20 7934 MILLER CLERK 7782 23.01.82 1300 10
Execution Plan
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=1 B
ytes=40)
1 0 TABLE ACCESS (BY ROWID) OF 'EMP' (Cost=2 Card=1 Bytes=40)
2 1 INDEX (RANGE SCAN) OF 'PK_EMP' (UNIQUE)
HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle DBA (7.3 & 8.0 OCP)
 |
 |