Re: Using Analytics......

From: Joel Garry <joel-garry_at_home.com>
Date: 3 Mar 2006 14:48:59 -0800
Message-ID: <1141426139.359584.127770@e56g2000cwe.googlegroups.com>

Jonathan:

>Depends where and how you do your ordering,
>but here's an example of the type of thing that
>you could do. It partitions the data before
>'ordering', but doesn't actually order it since
>you only want the highest and lowest.

Probably I'm misunderstanding something, but here is what I see with autotrace on XE beta:

        N1 FV        LV                CT

• --------- --------- ----------

         2 26-FEB-06 05-MAR-06 12          1 01-MAR-06 05-MAR-06 12 Execution Plan

---

Plan hash value: 326228303

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

    |

---

| 0 | SELECT STATEMENT | | 24 | 1056 | 5 (40)|
00:00:01 |
| 1 | HASH UNIQUE | | 24 | 1056 | 5 (40)|
00:00:01 |
| 2 | VIEW | | 24 | 1056 | 4 (25)|
00:00:01 |
| 3 | WINDOW SORT | | 24 | 528 | 4 (25)|
00:00:01 |
| 4 | TABLE ACCESS FULL| T1 | 24 | 528 | 3 (0)|
00:00:01 |

---

Note

---

• dynamic sampling used for this statement

Statistics

---

          0 recursive calls

          0 db block gets

          7 consistent gets

          0 physical reads

          0 redo size

        627 bytes sent via SQL*Net to client

        380 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          1 sorts (memory)

          0 sorts (disk)

          2 rows processed

        N1 FV        LV                CT

• --------- --------- ----------

         2 26-FEB-06 05-MAR-06 12          1 01-MAR-06 05-MAR-06 12 Execution Plan

---

Plan hash value: 326228303

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

    |

---

| 0 | SELECT STATEMENT | | 24 | 1056 | 5 (40)|
00:00:01 |
| 1 | HASH UNIQUE | | 24 | 1056 | 5 (40)|
00:00:01 |
| 2 | VIEW | | 24 | 1056 | 4 (25)|
00:00:01 |
| 3 | WINDOW SORT | | 24 | 528 | 4 (25)|
00:00:01 |
| 4 | TABLE ACCESS FULL| T1 | 24 | 528 | 3 (0)|
00:00:01 |

---

Note

---

• dynamic sampling used for this statement

Statistics

---

          0 recursive calls

          0 db block gets

          7 consistent gets

          0 physical reads

          0 redo size

        627 bytes sent via SQL*Net to client

        380 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          1 sorts (memory)

          0 sorts (disk)

          2 rows processed

Isn't that "window sort" a form of ordering? Does the view-ishness of analytics force the full table scan as well as the sort?

I can imagine with a much larger data set this might make a difference, if the intent is to skip some work.

jg

--
@home.com is bogus.
Jury says Google sucks your data:
http://www.silicon.com/ciojury/0,3800003161,39156914,00.htm

Received on Fri Mar 03 2006 - 16:48:59 CST