Re: Doing large sort in RAM - sort workarea manipulation

From: GG <grzegorzof_at_interia.pl>
Date: Sat, 12 Nov 2011 19:38:42 +0100
Message-ID: <4EBEBD32.7020405_at_interia.pl>

On 11/12/11 18:45, Jonathan Lewis wrote:
> Unless you have defined ID to be NOT NULL (or added a not null constraint, or
> add a predicate "id is not null" to the query) then it isn't possible for the
> optimizer to produce a path that uses the index for your query as there may be
> rows in the table that don't appear in the index.
>

You right, I've added not null constraint and plan is like this:

SQL> select /*+ INDEX(t idx_id) */ * from sort_px t order by id;

2000000 rows selected.

Execution Plan

---

Plan hash value: 2456259678

---

| Id  | Operation                          | Name    | Rows  | Bytes |
Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         |  1730K|   107M|
36773   (1)| 00:07:22 |       |       |

| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| SORT_PX | 1730K| 107M| 36773 (1)| 00:07:22 | ROWID | ROWID |

|   2 |   INDEX FULL SCAN                  | IDX_ID  |  1730K|       | 
5736   (1)| 00:01:09 |       |       |
--------------------------------------------------------------------------------------------------------------

Note

---

• dynamic sampling used for this statement

Statistics

---

          4  recursive calls
          0  db block gets
      44792  consistent gets
      34834  physical reads
          0  redo size
   21702331  bytes sent via SQL*Net to client
      44481  bytes received via SQL*Net from client
       4001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)

    2000000 rows processed

So no sort operation but how to make that parallel ? Even I set index parallel 2 and table parallel 2 there is no parallel access when index is used .
Regards
GregG

---

Masz strone www? Dodaj ja do katalogu!
Sprawdz >> http://linkint.pl/f2a8a

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Nov 12 2011 - 12:38:42 CST