2 Um
1...
etot hint govorit sisteme primerno tak - "Voz'mi skol'ko hochesh resursov no verni pervij resul'tat pochti mngnovenno". Kstati, SQL vozvrachaet vam kolichestvo recordov, oboznachennoe as "kolichestvo vozvrachaemih recordov po umolchaniu", chto obichno=255. Tak chto vremja, na kotoroe vi opiraetes' - eto vremja vosvrata etih pervih recordov.

From Oracle:
"Uses the cost based optimizer to provide the best response time for OLTP processing. Processing the SQL statements using FIRST_ROWS is the best practice for databases that have large numbers of OLTP users. The hint will force the statement to return rows to the user as soon as possible. Returning some rows immediately allows a screen buffer to fill quickly and present the rows to the user.

It is usually better to set the OPTIMIZER_MODE = FIRST_ROWS in the INIT.ora rather than apply the hint to individual statements. Setting FIRST_ROWS will often use indexes even though a full table scan will return the entire set of records using significantly less disk reads. It will also use nested loops in preference to sort merges and hash joins.

SELECT /*+ FIRST_ROWS */ a.acct_name, t.trans_date, t.trans_amt
FROM trans t, acct a
WHERE a.acct_no = 27217
AND a.acct_no = t.acct_no;

The optimizer ignores the FIRST_ROWS hint in DELETE and UPDATE statements as well as SELECTs, the use of UNION, INTERSECT, MINUS, UNION ALL, GROUP BY, FOR UPDATE or DISTINCT."

2...
"Table full scan" - a nichego v etom strashnogo i net, dage naoborot - teorija i praktika DB glasit: "Use the index if less than 15% of the rows are returned"
Mogno konechno iznasilovat' DB, no zachem...
Krome togo - Oracle predpolagaet, chto mi ispol'zuem "Parallel processing" kotorij vozmogen tol'ko pri "Full table scan". Poetomu to optimazer i pereskakivaet na full table scan, pitajas' ispol'zovat' "default degree of parallelism set for table"

3...
>>Ни разу не видел, чтоб hash_join работал >>быстрее чем nested loops.
Ne vse tak odnoznachno, prosto vipolnjaemie zadachi rasnie. Kstati primer Um pokazivaet eto prosto zamechatel'no- variant 2: nested loops, cost zashkalivaet, a pervij record vistrelivaetsja clientu za 0.08 sec.

"This hint is used to force tables being joined to get one row from the driving table and then get the associated rows from the joined table using an index. Rows are returned to the user immediately, which makes the access method ideal for users running online screens. As each row being joined to is returned, an additional physical read is often required to obtain additional columns specified in the select. If more than 15% of the rows are returned, a merge join will usually return the entire set of data faster.

SELECT /*+ USE_NL(a b) */ a.acct_no, b trans_date, b.trans_amt
FROM trans b, accts a
WHERE a.acct_no = 19181
AND a.acct_no = b.acct_no;

QUERY_PLAN
-----------------------------------------------
NESTED LOOPS
INDEX RANGE SCAN ACCTS_U1
TABLE ACCESS BY INDEX ROWID TRANS
INDEX RANGE SCAN TRANS_NDX1"

to all:
Zamerit' obchee vremja vipolnenia zaprosa - delo ves'ma ne blogodarnoe:
ja v svoej practike ispol'zuju select count(*) from (SQL-pod-vaprosom);
Ne umno-tak ne genij ja.
in Um case:

*** SCRIPT START : ?? 26-Jan-2003 10:19:12 ***
Processing ...
select count(*) from
(SELECT NAME, orders.id_goods
FROM emp, orders
WHERE emp.ID = orders.id_emp AND emp.ID > 1000)

1 row(s) selected
COUNT(*)
----------
989991

0 row(s) retrieved

*** SCRIPT END : ??? 26-Jan-2003 10:19:47 ***

variant 2:
*** SCRIPT START : ?? 26-Jan-2003 10:17:18 ***
Processing ...
select count(*) from
(SELECT /*+ FIRST_ROWS */ NAME, orders.id_goods
FROM emp, orders
WHERE emp.ID = orders.id_emp AND emp.ID > 1000)
1 row(s) selected
COUNT(*)
----------
989991

0 row(s) retrieved

*** SCRIPT END : ?? 26-Jan-2003 10:17:52 ***


Eto ne daet absolutnogo vremenj ispolnenija, no posvaljaet sravnivat' rasnie varianti dostatochno tochno - "I think". No eto pokazivaet Um-u ego oshibku v ocenke vremeni zaprosa - "I hope"

Horoshaja tema mogla bi poluchit'ca - "Kak i chem zamerit' SQL" ... ?

2 Scott Tiger

- Prav ti s count(*), Glup, ispravljus'.
- da, vsem Quest Central vonjaet, pisaju ja parom ot etogo dela, leniv stal. Goda 3 kak SQL+ dage na mashine ne stoit, vot i glupeju.