RE: FIRST_ROWS vs. ALL_ROWS

Bill,

Besides statistics and how they are gathered, and other information, there are numerous parameters that influence the CBO and it's decisions. And just to clarify, you said the query ran in under a second when using FIRST_ROWS (and thus a nested loops indexed lookup approach if possible). And you had a "rownum < 5" in there so I don't doubt it. Would you normally have that "stopkey" in there?

The reason I ask is that when a nested loops approach is used (and you aren't ordering on non-indexed columns, aggregating, etc) you can start returning rows immediately before the query "completes". Hence the FIRST_ROWS hint favoring the execution plan you see -- you get the first rows quickly. When join choices such as merge or hash are used, all the data must be accessed before rows start returning. Depending upon the selectivity of your data, and if you aren't normally using the rownum < 5, total throughput might be better with the ALL_ROWS method. It's hard to say without knowing your data. But, I just wanted to make sure that you aren't falling into a trap by immediately seeing results when using the FIRST_ROWS approach.

For an exaggerated example, assume I am joining two 100,000 row tables with no constraining criteria other than the join between them. If I use first_rows, thus a very good chance of an indexed nested loops approach, I will start seeing results immediately. But in reality since I want every row in each table, I would be better off (in a report for example) with full table scans and hash joins. The mistake that one can make is to think the first rows approach is faster since they immediately see results. When in reality, for this example, it would take the query much longer to complete than if the FTS and HJ approach was used.

Or, maybe this is just one of those cases where the CBO is making a bad choice, and even without the rownum<5, a nested loops approach is still the preferred method. It wouldn't be the first time the CBO made a "bad" choice.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Carle,
> William T (Bill), NLCIO
> Sent: Monday, December 03, 2001 12:45 PM
> To: Multiple recipients of list ORACLE-L
> Subject: FIRST_ROWS vs. ALL_ROWS
>
>
> Howdy,
>
> I have a query which was taking an extremely long time
> to complete. The OPTIMIZER_MODE in the init.ora file is set to CHOOSE,
> meaning it will use the ALL_ROWS method to determine its access paths. I
> determined the query was not using the indexes I thought it should. When I
> changed to use the FIRST_ROWS optimizer_mode, the query ran in under a
> second. Now FIRST_ROWS is used to minimize response time; ALL_ROWS is used
> to minimize total execution time. I'm trying to figure out the difference.
> Using FIRST_ROWS, this is the output from tkprof:
>
> select ra.originatingclli, ra.terminatingclli,
> r.originatingclli,
> r.terminatingclli, r.deletedigits, r.prefixdigits,
> rs.originatingclli,
> rs.terminatingclli from routingassignmentpersistent ra,
> route r, routesegmentroute rs1, routesegmentpersistent rs
> where networktype = 'A
> C' and networksubtype
> = 'POTS' and ra.objid = r.ROUTINGASSIGNPERSFINAL_OBJID and
> r.objid = ROUTE_OBJID and ROUTESEGMENTPERSISTENT_OBJID =
> rs.objid
> and rownum < 5 and ra.startdate is not null
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ----------
> ---------- ----------
> Parse 1 0.00 0.01 0 0
> 0 0
> Execute 1 0.00 0.00 0 0
> 0 0
> Fetch 2 0.00 0.00 0 35
> 0 4
> ------- ------ -------- ---------- ---------- ----------
> ---------- ----------
> total 4 0.00 0.01 0 35
> 0 4
>
> Misses in library cache during parse: 1
> Optimizer goal: FIRST_ROWS
> Parsing user id: 29 (NTOPBIG)
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 4 COUNT STOPKEY
> 4 NESTED LOOPS
> 4 NESTED LOOPS
> 2 NESTED LOOPS
> 1 TABLE ACCESS BY INDEX ROWID
> ROUTINGASSIGNMENTPERSISTENT
> 1 INDEX RANGE SCAN (object id 16976)
> 2 TABLE ACCESS BY INDEX ROWID ROUTE
> 2 INDEX RANGE SCAN (object id 16962)
> 5 TABLE ACCESS BY INDEX ROWID ROUTESEGMENTROUTE
> 5 INDEX RANGE SCAN (object id 16971)
> 4 TABLE ACCESS BY INDEX ROWID ROUTESEGMENTPERSISTENT
> 7 INDEX UNIQUE SCAN (object id 16965)
>
>
> Rows Execution Plan
> -------
> ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: FIRST_ROWS
> 4 COUNT (STOPKEY)
> 4 NESTED LOOPS
> 4 NESTED LOOPS
> 2 NESTED LOOPS
> 1 TABLE ACCESS GOAL: ANALYZED
> (BY INDEX ROWID) OF
>
> 'ROUTINGASSIGNMENTPERSISTENT'
> 1 INDEX GOAL: ANALYZED (RANGE
> SCAN) OF
>
> 'IX_ROUTINGASSIGNMENTPERSIST5' (NON-UNIQUE)
> 2 TABLE ACCESS GOAL: ANALYZED
> (BY INDEX ROWID) OF
> 'ROUTE'
> 2 INDEX GOAL: ANALYZED (RANGE
> SCAN) OF 'IX_ROUTE4'
> (NON-UNIQUE)
> 5 TABLE ACCESS GOAL: ANALYZED
> (BY INDEX ROWID) OF
> 'ROUTESEGMENTROUTE'
> 5 INDEX GOAL: ANALYZED (RANGE
> SCAN) OF
> 'IX_ROUTESEGMENTROUTE3'
> (NON-UNIQUE)
> 4 TABLE ACCESS GOAL: ANALYZED
> (BY INDEX ROWID) OF
> 'ROUTESEGMENTPERSISTENT'
> 7 INDEX GOAL: ANALYZED (UNIQUE
> SCAN) OF
> 'IX_ROUTESEGMENTPERSISTENT'
> (UNIQUE)
>
> Using the ALL_ROWS mode, this is the tkprof
> output from the same query:
>
> select ra.originatingclli, ra.terminatingclli,
> r.originatingclli,
> r.terminatingclli, r.deletedigits, r.prefixdigits,
> rs.originatingclli,
> rs.terminatingclli from routingassignmentpersistent ra,
> route r, routesegmentroute rs1, routesegmentpersistent rs
> where networktype = 'A
> C' and networksubtype
> = 'POTS' and ra.objid = r.ROUTINGASSIGNPERSFINAL_OBJID and
> r.objid = ROUTE_OBJID and ROUTESEGMENTPERSISTENT_OBJID =
> rs.objid
> and rownum < 5 and ra.startdate is not null
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ----------
> ---------- ----------
> Parse 1 0.02 0.01 0 0
> 0 0
> Execute 2 0.05 0.06 0 0
> 5 0
> Fetch 2 40.91 471.49 197287 803265
> 3128 4
> ------- ------ -------- ---------- ---------- ----------
> ---------- ----------
> total 5 40.98 471.56 197287 803265
> 3133 4
>
> Misses in library cache during parse: 1
> Optimizer goal: ALL_ROWS
> Parsing user id: 29 (NTOPBIG)
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 4 COUNT STOPKEY
> 4 MERGE JOIN
> 4 SORT JOIN
> 344066 NESTED LOOPS
> 166100 HASH JOIN
> 102265 TABLE ACCESS BY INDEX ROWID
> ROUTINGASSIGNMENTPERSISTENT
> 164524 INDEX RANGE SCAN (object id 16976)
> 709657 TABLE ACCESS FULL ROUTE
> 344066 TABLE ACCESS BY INDEX ROWID ROUTESEGMENTROUTE
> 510165 INDEX RANGE SCAN (object id 16971)
> 4 SORT JOIN
> 155184 TABLE ACCESS FULL ROUTESEGMENTPERSISTENT
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: ALL_ROWS
> 4 COUNT (STOPKEY)
> 4 NESTED LOOPS
> 4 NESTED LOOPS
> 344066 HASH JOIN
> 166100 TABLE ACCESS GOAL: ANALYZED (FULL) OF
> 'ROUTINGASSIGNMENTPERSISTENT'
> 102265 TABLE ACCESS GOAL: ANALYZED (FULL) OF
> 'ROUTE'
> 164524 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
> OF
> 'ROUTESEGMENTROUTE'
> 709657 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> 'IX_ROUTESEGMENTROUTE3' (NON-UNIQUE)
> 344066 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
> OF
> 'ROUTESEGMENTPERSISTENT'
> 510165 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
> 'IX_ROUTESEGMENTPERSISTENT' (UNIQUE)
>
> So how can the optimizer think that this second way can
> possibly be better? Whether you base it on total execution time, resource
> cost, or whatever?
>
> Bill Carle
> AT&T
> Database Administrator
> 816-995-3922
> wcarle_at_att.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Carle, William T (Bill), NLCIO
> INET: wcarle_at_att.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).