Re: partitioned table-number of tablespaces (oracle 10g2)
Date: Sat, 18 Dec 2010 10:14:41 -0800 (PST)
Message-ID: <d07f51f3-00e5-48d3-8131-5cdc38f92dd6_at_r19g2000prm.googlegroups.com>
On Dec 16, 6:55 am, Michał Jabłoński <michal.jablo..._at_gmail.com> wrote:
> I've got non-partitioned table which I have to move to partitioned
> table. I've decided to move it to hash partitioned table with 16
> partitions. It is a good practise to create dedicated tablespace for
> each partition with one datafile?
That could be a good thing or it could be wasted effort on your part. How large is this table? How large are the partitions expected to be? Here's an example of a hash partitioned table using one datafile:
SQL> SQL> -- SQL> -- Create hash partitioned table SQL> -- SQL> SQL> create table emp_p
2 monitoring
3 partition by hash(empno)
4 (partition p_1 tablespace part1 , 5 partition p_2 tablespace part2 , 6 partition p_3 tablespace part3 , 7 partition p_4 tablespace part4 )
8 as select *
9 from emp;
Table created.
SQL> create index emp_p_empno
2 on emp_p(empno) local;
Index created.
SQL> select *
2 from emp_p
3 where empno = 7900;
EMPNO ENAME JOB MGR HIREDATE
SAL COMM DEPTNO
---------- ---------- --------- ---------- --------------------
---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-1981 00:00:00
950 30
Execution Plan
Plan hash value: 633904864
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 | | | | 1 | PARTITION HASH SINGLE | | 1 | 87 | 3 (0)| 00:00:01 | 2 | 2 | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| EMP_P | 1 | 87 | 3 (0)| 00:00:01 | 2 | 2 | |* 3 | INDEX RANGE SCAN | EMP_P_EMPNO | 1 | | 2 (0)| 00:00:01 | 2 | 2 | ------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
3 - access("EMPNO"=7900)
Note
- dynamic sampling used for this statement
Statistics
32 recursive calls
15 db block gets
13 consistent gets
0 physical reads
828 redo size
822 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> It's a small table but the access time for the query shown above is less than 1/100th of a second. Let's change this to a list partitioned table and add more data:
SQL> SQL> -- SQL> -- Drop hash partitioned table SQL> -- SQL> SQL> drop table emp_p purge;
Table dropped.
SQL> SQL> -- SQL> -- Create list partitioned table SQL> -- SQL> SQL> create table emp_p
2 monitoring
3 partition by list(empno)
4 (partition p_1 values (7788, 7902) tablespace part1, 5 partition p_2 values (7369, 7876) tablespace part2, 6 partition p_3 values (7900, 7934) tablespace part3, 7 partition p_4 values (7566, 7698) tablespace part4, 8 partition p_5 values (7782, 7839) tablespace part5, 9 partition p_6 values (7499, 7521) tablespace part6, 10 partition p_7 values (7654, 7844) tablespace part7)11 as select *
12 from emp;
Table created.
SQL> SQL> -- SQL> -- Add an index SQL> -- SQL> SQL> create unique index emp_p_empno
2 on emp_p(empno,hiredate);
Index created.
SQL> select count(*) From emp_p partition(p_1);
COUNT(*)
10002
SQL>
SQL> select count(*) From emp_p partition(p_2);
COUNT(*)
10002
SQL>
SQL> select count(*) From emp_p partition(p_3);
COUNT(*)
10002
SQL>
SQL> select count(*) From emp_p partition(p_4);
COUNT(*)
10002
SQL>
SQL> select count(*) From emp_p partition(p_5);
COUNT(*)
10002
SQL>
SQL> select count(*) From emp_p partition(p_6);
COUNT(*)
10002
SQL>
SQL> select count(*) From emp_p partition(p_7);
COUNT(*)
10002
SQL>
SQL> select *
2 from emp_p
3 where empno = 7900
4 and hiredate between sysdate and sysdate + 1;
no rows selected
Execution Plan
Plan hash value: 2344869950
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 87 | 0 (0)| 00:00:01 | | | |* 1 | FILTER | | | | | | | | | 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| EMP_P | 1 | 87 | 0 (0)| 00:00:01 | 3 | 3 | |* 3 | INDEX RANGE SCAN | EMP_P_EMPNO | 1 | | 0 (0)| 00:00:01 | | | -------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter(SYSDATE_at_!<=SYSDATE@!+1) 3 - access("EMPNO"=7900 AND "HIREDATE">=SYSDATE_at_! AND "HIREDATE"<=SYSDATE_at_!+1)
Note
- dynamic sampling used for this statement
Statistics
55 recursive calls
15 db block gets
72 consistent gets
0 physical reads
944 redo size
656 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> Access time is still around 1/100th of a second for a partitioned table using only one datafile; of course you may need more depending upon your server configuration, user load and partition size but you need to determine that by testing, not by relying upon outdated or incorrect 'rules of thumb'.
David Fitzjarrell Received on Sat Dec 18 2010 - 12:14:41 CST