Re: Doing large sort in RAM - sort workarea manipulation
- From: GG <grzegorzof@xxxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Sat, 12 Nov 2011 19:38:42 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
