Home » RDBMS Server » Performance Tuning » how oracle organises records in partitions (11gr2)
how oracle organises records in partitions [message #546807] Fri, 09 March 2012 01:47 Go to next message
gogol_1987
Messages: 12
Registered: March 2011
Location: kolkata
Junior Member
hi gems...

I have a question regrading how oracle fetches record in case of partitioning...

we have a table of nearly 6 crores of records..
that table has several columns among which the DBA has given the range partition on COL1 and hash subpartition on COL2...

CREATE TABLE ORDER_BOOK
(CUST_ID NUMBER(10),
ORDER_DATE DATE,
...........,
...........,
...........)
PARTITION BY RANGE (CUST_ID)
SUBPARTITION BY HASH (ORDER_DATE)
SUBPARTITION TEMPLATE(
SUBPARTITION SP1 TABLESPACE TS_PRT1,
SUBPARTITION SP2 TABLESPACE TS_PRT2,
SUBPARTITION SP2 TABLESPACE TS_PRT3,
SUBPARTITION SP2 TABLESPACE TS_PRT4)
(PARTITION P1
VALUES LESS THAN (10000000),
PARTITION P2
VALUES LESS THAN (20000000),
PARTITION P3
VALUES LESS THAN (30000000),
PARTITION P4
VALUES LESS THAN (40000000),
PARTITION P5
VALUES LESS THAN (50000000),
PARTITION P6
VALUES LESS THAN (MAXVALUE));



I want to know how the rows will get organised in the tablespaces...
the DBA didn't mention any tablespace name in the main partition and he has mentioned the tablespace names only in the subpartition template.
so how the records will get organised...please help...
thanks a lot in advance
Re: how oracle organises records in partitions [message #546814 is a reply to message #546807] Fri, 09 March 2012 01:54 Go to previous messageGo to next message
John Watson
Messages: 4677
Registered: January 2010
Location: Global Village
Senior Member
What you refer to as the "main partition" does not exist physically, only logically.
Re: how oracle organises records in partitions [message #546817 is a reply to message #546807] Fri, 09 March 2012 02:04 Go to previous messageGo to next message
John Watson
Messages: 4677
Registered: January 2010
Location: Global Village
Senior Member
Apart from your question, the partitioning strategy looks odd. Generally speaking, I would expect the table to be range partitioned by order_date, and hash subpartitioned by cust_id. This would allow queries that select all rows for a particular customer in a certain time frame to retrieved with the greatest efficiency: partitioning pruning with a non-equality date predicate, and hash key access for the customer with an equality predicate.
Your partitioning will help only if you are trying to get orders on a particular date for a group of customers with similar cust_id. An unusual requirement.
Re: how oracle organises records in partitions [message #546831 is a reply to message #546817] Fri, 09 March 2012 03:07 Go to previous messageGo to next message
gogol_1987
Messages: 12
Registered: March 2011
Location: kolkata
Junior Member
thanks a lot john for your suggestion...

i got the point..i will tell my DBA to change the partitioning accordingly...

actually i want to know how oracle will organize the data in the tablespaces...

if the main partitioning on ORDER_DATE(suppose i have already changed the partitioning strategy according to your suggestion) is logical and the subpartition on CUST_ID is physical, then why we are using the subpartition method....

we can just make range or hash partition on ORDER_DATE...

i am confused about the organising strategy of oracle in partition-subpartition method...
Re: how oracle organises records in partitions [message #546839 is a reply to message #546831] Fri, 09 March 2012 04:44 Go to previous messageGo to next message
John Watson
Messages: 4677
Registered: January 2010
Location: Global Village
Senior Member
Do you and your DBA have a clear understanding of exactly what problem(s) you want to address? And of precisely how your chosen partitioning strategy will fix them?
I always tell clients that partitioning is not a facility you use because it is there: you need to do a lot of research to work out what strategy is appropriate, or the results may be disastrous.

Are you following "ready, aim, fire" or "fire, aim, ready"?
Re: how oracle organises records in partitions [message #546847 is a reply to message #546839] Fri, 09 March 2012 04:57 Go to previous message
gogol_1987
Messages: 12
Registered: March 2011
Location: kolkata
Junior Member
thanks john...

i am explaining my goal below...

actually the table has lots of datas(nearly 6 crores)..

the primary key is CUST_ID, ORDER_BOOK_ID, SUB_ORDER_BOOK_ID, PRODUCT_ID and ORDER_DATE...

99% queries on the table will have search condition on equating the first four or first three or first two or first one columns mentioned above as well as with a ORDER_DATE range..

for example...
select * from ORDER_BOOK
where CUST_ID=10024
and ORDER_BOOK_ID=30952
and SUB_ORDER_BOOK_ID='BK0091'
and ORDER_DATE between (to_date(value1) and to_date(value2));

Previous Topic: Disk reads
Next Topic: Disk i/o
Goto Forum:
  


Current Time: Wed Oct 22 05:21:20 CDT 2014

Total time taken to generate the page: 0.23624 seconds