Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Oracle - na ktorej hash-partycji jest rekord?

8 views
Skip to first unread message

Leszek Pachura

unread,
Apr 14, 2007, 5:27:10 PM4/14/07
to
Czesc,

Zalozmy ze mam taka partycjonowana tabele:

create table TABELA (
ID number primary key,
TXT varchar2(1000)
)
partition by hash (ID) (
partition P1 tablespace TS1,
partition P2 tablespace TS2,
...
)

Czy dysponujac wartoscia ID konkretnego rekordu moge jakos dojsc do tego na
ktorej partycji znajduje sie fizycznie ten rekord?

--
Wysłano z serwisu OnetNiusy: http://niusy.onet.pl

news997

unread,
Apr 18, 2007, 2:54:11 AM4/18/07
to
Leszek Pachura wrote:
> Czesc,
>
> Zalozmy ze mam taka partycjonowana tabele:
>
> create table TABELA (
> ID number primary key,
> TXT varchar2(1000)
> )
> partition by hash (ID) (
> partition P1 tablespace TS1,
> partition P2 tablespace TS2,
> ...
> )
>
> Czy dysponujac wartoscia ID konkretnego rekordu moge jakos dojsc do tego na
> ktorej partycji znajduje sie fizycznie ten rekord?

Tak można, ale po co? I tak będą one równomiernie rozłożone. W kolumnach
Pstart i Pstop masz numer partycji.


create tablespace ts1;
create tablespace ts2;

create table TABELA (
ID number primary key,
TXT varchar2(1000)
)
partition by hash (ID) (
partition P1 tablespace TS1,
partition P2 tablespace TS2

) ;

insert into tabela(id, txt) Values('1','Jeden');
insert into tabela(id, txt) Values('2','Dwa');


explain plan for select * from tabela where id = 1 ;
select * from table(dbms_xplan.display) ;


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2969649219

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
515 | 1 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| TABELA | 1 |
515 | 1 (0)| 00:00:01 | 2 | 2 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0010190 | 1 |
| 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------

14 rows selected.


explain plan for select * from tabela where id = 2 ;
select * from table(dbms_xplan.display) ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2969649219

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
515 | 1 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| TABELA | 1 |
515 | 1 (0)| 00:00:01 | 1 | 1 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0010190 | 1 |
| 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------


dap
--
http://dap.polanski.biz/?p=7


0 new messages