| how oracle organises records in partitions [message #546807] |
Fri, 09 March 2012 01:47  |
 |
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 #546817 is a reply to message #546807] |
Fri, 09 March 2012 02:04  |
John Watson
Messages: 8929
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 #546839 is a reply to message #546831] |
Fri, 09 March 2012 04:44 |
John Watson
Messages: 8929
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 |
|
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));
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
