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?

On Dec 16, 6:55*am, Micha³ Jab³oñski <michal.jablo... (AT) gmail (DOT) com>
wrote:
If you assign each partition to its own tablespace probably should be
based on expected partition size and your space management policies
and backup/restore plan.

If each partition is only a couple of gigabytes and you use 32G files
then assigning each partition to its own tablespace seems overkill.
On the other hand if each is around 20g and you like to keep all your
tablespaces close to the same size for restore of any tablespace takes
about the same amount of time as restoring any other tablespace then
you may want to split them up. You may determine that assigning two
to four partitions to the same tablespace makes the most sense.

It is a matter of preference, not best practice.

HTH -- Mark D Powell --

On Thu, 16 Dec 2010 03:55:40 -0800, Michał Jabłoński wrote:


What would you like to achieve by splitting table into different
tablespaces, as opposed to splitting it into a single tablespace?


--
http://mgogala.byethost5.com

Mark D Powell wrote,on my timestamp of 17/12/2010 2:37 AM:

Absolutely. Or rather simply:

a matter of analysis of each situation and its requirements.

I'm getting a bit fed-up with all these "best practice" "cookie cutter"
"solutions" that 90% of the time have nothing to do with the particular problem
at play.

Oracle is a general purpose database, by definition there cannot be a "best
practice" that copes with ALL setup situations.


Enough already with the "press the button and switch off brain" dba!


(for those less versed in Usenet: this is a general rant prompted by what Mark
said. It is NOT directed at Mark or the OP.
Rest.
Go back to mindlessly clicking on grid screens.
Or whatever else takes your fancy...)

On 16 Gru, 21:05, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:
I think about performance, time of execution sql on this table. In
this case it is better to create more tablespaces with one datafile or
less with one or more datafiles? Or it doesn't matter?

On Dec 17, 2:33*am, Micha³ Jab³oñski <michal.jablo... (AT) gmail (DOT) com>
wrote:
Well, the only answer to that is It Depends. If you split the
datafiles across various devices with their own controllers, it might
make a difference. If everything is on a SAN anyways, the only
difference would be recovery time (which can be important!). With
heavy updating of the db (not even necessarily these tables), there
can be differences in how many file headers need to be updated and
whether you get a bottleneck updating controlfile information. On a
SAN, the number of spindles request are spread across and the size of
caches in various places can make such a question pointless, until you
hit yourself in the knee (that's a joke, see Cary Millsap's paper
thinking clearly about performance).

I agree with Noon's rant, though I would qualify it for situations
where many people have nearly identical configurations and apps - the
10%. At some relatively low point, rules of thumb can be useful to at
least get you into a ballpark where you can start scientific
investigations. It's only stupid when applied inappropriately. I
would red-flag the OP as possibly falling into that trap, from the
limited information posted.

The take-away for Michael is you need to properly empirically test for
your load and characteristics, and get to know people with similar
situations.

jg
--
@home.com is bogus.
Microsoft, Apple, EMC and Oracle walk into a bar...
Bartender says, "we don't see many patent blocks in here."
"Unless youse pays us $14B, youse won't see any more."
http://www.bnet.com/blog/technology-...nd-oracle/7432

On Fri, 17 Dec 2010 02:33:52 -0800, Michał Jabłoński wrote:

These days, "disk partition" is something allocated on SAN, probably a
RADI 10 device, striped across several physical disks and duplicated.
It's quite frequently cached in SAN based cached, typically several GB in
size, with write back strategy and a battery backup. So called "I/O"
performance depends on the speed of HBA and FC/AL link between the system
and the SAN, the amount of RAM on SAN and the caching, the type of disks
being used (SATA and SCSI are the most frequent alternatives),
optimization techniques employed by SAN (shortest path firs, priority
queue and FIFO are the most frequent examples). The whole philosophy of
"separating data to different disks" is as obsolete as the terminator
model 1.



--
http://mgogala.byethost5.com

Mladen Gogala wrote,on my timestamp of 18/12/2010 5:33 AM:

But you should hear the SAN vendors:
"Did you split your tables and indexes to different disks?"
"Have you got any hot-tables? They need their own disk, you know?"

My usual reply:
"Did you learn to suck eggs recently?..."

On Dec 16, 6:55*am, Micha³ Jab³oñski <michal.jablo... (AT) gmail (DOT) com>
wrote:

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

------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------

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

-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(SYSDATE@!<=SYSDATE@!+1)
3 - access("EMPNO"=7900 AND "HIREDATE">=SYSDATE@! AND
"HIREDATE"<=SYSDATE@!+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

Thanks for yours answers.