Home » RDBMS Server » Performance Tuning » How to decide Partiitioning columns? (Oracle 10.2.0.4)
| How to decide Partiitioning columns? [message #549097] |
Wed, 28 March 2012 03:05  |
 |
orapratap
Messages: 134
Registered: November 2011
Location: Canada
|
Senior Member |
|
|
Hello
What could be the strategy on deciding which columns to create partitions on?
I understand for deciding this, first, we need to know the columns we are using in the WHERE clause
consider following scenario
assume that emp table is very large
(1)
Query - select * from emp where empno=<pk_value>
what could the partitioning column here?
This is confusing as we access with, quite selective criteria here but we access lot of data
No particular date range Or No particular flag, value to check with!
would hash partition on the pk_column will help here?
(2)
select * from emp where empno=<pk_value> and deptno=<some value>
what could the partitioning column here?
I assume deptno here. Right?
In general what could be the considerations in deciding the partitioning columns?
whether the column is not a unique key column
Or the column is preferrable if used in joins
Or the column is not updateable
Finally will the pruning help (take place) if the query spans across multiple partitions, though Not all partitions?
Thanks and Regards
OraPratap
|
|
|
|
|
|
|
|
| Re: How to decide Partiitioning columns? [message #549111 is a reply to message #549101] |
Wed, 28 March 2012 03:41  |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
@roachcoach, if there are many concurrent PK lookups and insertions, you can get bad contention (buffer busy wait and so on) on both the table and the index. To avoid that, one can reverse key and hash partition the index and hash partition the table, using the PK as the partitioning key. That will spread the I/O across the entire range of all partition.
In this case, one might need to be cleverer: perhaps reverse and hash the empno index, hash partition dept on deptno, and reference partition emp to guarantee partitionwise joins to dept:drop table emp;
drop table dept;
create table dept (deptno number, constraint dpk primary key (deptno))
partition by hash (deptno) partitions 4;
create table emp (empno number, deptno number not null, constraint dcon foreign key (deptno) references dept(deptno))
partition by reference (dcon);
create index ei on emp(empno) reverse global partition by hash(empno) partitions 4;
@orapratap, this is quite sophisticated stuff. You need to be absolutely clear on what problem you want to solve, and how partitioning will help. You need to prove this mathematically. Or the results may be disastrous.
|
|
|
|
| Re: How to decide Partiitioning columns? [message #549167 is a reply to message #549111] |
Wed, 28 March 2012 07:07 |
|
orapratap
Messages: 134
Registered: November 2011
Location: Canada
|
Senior Member |
|
|
Hello
Thanks for your replies
Michel thanks for the link I am reading it but it looks it does not have specific section 'which column are good for partitioning'
I have a query
suppose I have table similar to dba_objects and created partitions on object_type
such as
partition by list(object_type)
( partition part_1 values ( 'INDEX', 'CONTEXT', 'TYPE BODY', 'INDEXTYPE', 'PROCEDURE','RULE','JAVA RESOURCE'),
partition part_2 values ( 'JAVA CLASS', 'SCHEDULE', 'TABLE PARTITION','WINDOW','WINDOW GROUP' ),
partition part_3 values ( 'TABLE', 'TYPE', 'VIEW','SYNONYM' ),
partition part_4 values ( 'PACKAGE BODY', 'SEQUENCE', 'INDEX PARTITION','JOB' )
)
Now I have index on object_id field
Will query like following benefit from the partitioning? (in global as well as local index)
select * from <my_table> where object_id=<object_id>;
Thanks and Regards
Orapratap
|
|
|
|
| Re: How to decide Partiitioning columns? [message #549170 is a reply to message #549167] |
Wed, 28 March 2012 07:12 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You are doing this backwards: deciding on a partitioning strategy, and then asking if it useful. Go in the other direction: consider what query do you want to run, and then work out partitions that will assist.
So in your example, the question becomes, "what partitioning (if any) of index or table will assist a query that uses an equality predicate on a primary key?" What do you think?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to decide Partiitioning columns? [message #549237 is a reply to message #549226] |
Thu, 29 March 2012 01:37 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hello again. Going by this,
Quote:The primary reason to give a thought of partition is massively increasing data.
May it be queries that access index or queries doing FTS - accessing / handling this large amount of data is an issue
accessing a hunderds of thousand records with ID search using index or doing FTS with parallel will be increasing issue in our case and this is why I seek guidence from your experts I would say that you do not have any problem at all. You are trying to fix something that does not exist, and you do not appear to have conducted any tests that show a problem might exist. You say the table might have 4 million rows. That is not big. It is tiny. Oracle can scan that many rows in seconds, if a scan is necessary. Insert a few million rows, and try it. And if there is a problem, then think about how to address it.
Do not use potentially dangerous (and very expensive) technology to a non-existant problem. I would rather concentrate on upgrading to release 11.2.0.3, which has many other zero cost solutions to performance issues that require little or no configuration.
|
|
|
|
|
|
| Re: How to decide Partiitioning columns? [message #549251 is a reply to message #549237] |
Thu, 29 March 2012 02:35 |
|
orapratap
Messages: 134
Registered: November 2011
Location: Canada
|
Senior Member |
|
|
Hello John
Many Thanks for your suggestions
you said oracle can access millions of rows in seconds
Ok
Lets' assume millions are rows are accessed using index ..then it will demand much upgraded hardware to get results in seconds using single block access
and if millions of rows are accessed using FTS ..then too we need good hardware but won't partitioning too will help here?
In our application
A table with 4 million records is the most accessed table
The other much accessed tables has 12 - 48 milliom records
as a bottomline do you suggest that
if I am accessing few million records then Partitioning is Not feasible?
In our case data is growing rapidaly and these figure will be doubled in couple of years won't it benefit it doing partitioning at this stage only
Finally what could be the sql statements which 1) won't benefit from partitioning 2) will behave worst 3) will benefit from partitioning
I have been referring several links but no particular link covers these scenario collectively
Also is it possible to 'yes' 'No' on following
will performance degrade if I query across partitions?
will partition help if I am selecting 30000 records from 4-10 milion records using index acess?
will partition help if I am joining partitioning keys to non-partitined table?
I have prepared a case but having a space issue and thus unable to continue at the moment
create sequence s;
create table dbo as select s.nextval id,a.* from dba_objects a;
insert into dbo select s.nextval id,a.* from dba_objects a;
........
--continue till get at least 1M records
insert into dbo select s.nextval id,a.* from dba_objects a;
commit;
create table dbo_part
(
ID NUMBER,
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
SUBOBJECT_NAME VARCHAR2(30),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19),
CREATED DATE,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2(19),
STATUS VARCHAR2(7),
TEMPORARY VARCHAR2(1),
GENERATED VARCHAR2(1),
SECONDARY VARCHAR2(1)
)
partition by list(object_type)
( partition part_1 values ( 'INDEX', 'CONTEXT', 'TYPE BODY', 'INDEXTYPE', 'PROCEDURE','RULE','JAVA RESOURCE'),
partition part_2 values ( 'JAVA CLASS', 'SCHEDULE', 'TABLE PARTITION','WINDOW','WINDOW GROUP' ),
partition part_3 values ( 'TABLE', 'TYPE', 'VIEW','SYNONYM' ),
partition part_4 values ( 'PACKAGE BODY', 'SEQUENCE', 'INDEX PARTITION','JOB' )
);
insert into dbo_part select * from dbo where object_type in( 'INDEX', 'CONTEXT', 'TYPE BODY', 'INDEXTYPE'
, 'PROCEDURE','RULE','JAVA RESOURCE', 'JAVA CLASS', 'SCHEDULE', 'TABLE PARTITION','WINDOW','WINDOW GROUP' ,'TABLE', 'TYPE'
, 'VIEW','SYNONYM','PACKAGE BODY', 'SEQUENCE', 'INDEX PARTITION','JOB') ;
create index i_dbo on dbo(ID);
create index i_dbo_part on dbo_part(ID);
exec dbms_stats.gather_table_stats(user,'DBO' cascade=>true);
exec dbms_stats.gather_table_stats(user,'DBO_PART' cascade=>true);
select * from dbo where object_type = 'VIEW';
--access single partition
select * from dbo_part where object_type = 'VIEW';
select * from dbo where object_type in( 'VIEW', 'INDEX');
--query across partitions
select * from dbo_part where object_type in( 'VIEW', 'INDEX');
select * from dbo where object_type in( 'VIEW', 'TABLE');
--query single partition with multiple values
select * from dbo_part where object_type in( 'VIEW', 'TABLE');
select * from dbo where object_type = 'VIEW' and ID = 22;
--query single partition but this time with unque key on other column
select * from dbo_part where object_type = 'VIEW' and ID = 22;
select * from dbo where ID = 11;
--query with unque key access, equlaity predicate
select * from dbo_part where ID = 11;
select * from dbo where ID > 9 and ID < 27;
--query with unque key access, range predicate - single partition
select * from dbo_part where ID > 9 and ID < 27;
select * from dbo where ID > 9 and ID < 23009;
--query with unque key access, range predicate - across partition
select * from dbo_part where ID > 9 and ID < 23009;
-- joining with non-partitioned table on non-partitioned key
select * from dbo, dbo_part
where dbo.ID = dbo_part.ID
and dbo.id<10000;
Thanks and Regards
Orapratap
|
|
|
|
| Re: How to decide Partiitioning columns? [message #549259 is a reply to message #549251] |
Thu, 29 March 2012 03:55 |
|
orapratap
Messages: 134
Registered: November 2011
Location: Canada
|
Senior Member |
|
|
and here are the results of the test
I do not understand why more data is accessed while querying on partition table than on non-partitioned table
sql >set lines 200
sql >set pages 200
sql >
sql >set autotrace traceonly explain
sql >
---------PERFORMANCE COMPARISON - partitioned v/s non-partitioned table
[1]
--access single partition
**********************************************************************
sql >select * from dbo where object_type = 'VIEW';
Execution Plan
----------------------------------------------------------
Plan hash value: 2675347415
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 77611 | 7427K| 3246 (1)| 00:00:39 |
|* 1 | TABLE ACCESS FULL| DBO | 77611 | 7427K| 3246 (1)| 00:00:39 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='VIEW')
sql >select * from dbo_part where object_type = 'VIEW';
Execution Plan
----------------------------------------------------------
Plan hash value: 97391151
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 137K| 12M| 1738 (1)| 00:00:21 | | |
| 1 | PARTITION LIST SINGLE| | 137K| 12M| 1738 (1)| 00:00:21 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | DBO_PART | 137K| 12M| 1738 (1)| 00:00:21 | 3 | 3 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='VIEW')
[2]
--query across partitions
**********************************************************************
sql >select * from dbo where object_type in( 'VIEW', 'INDEX');
Execution Plan
----------------------------------------------------------
Plan hash value: 2675347415
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 112K| 10M| 3249 (1)| 00:00:39 |
|* 1 | TABLE ACCESS FULL| DBO | 112K| 10M| 3249 (1)| 00:00:39 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='INDEX' OR "OBJECT_TYPE"='VIEW')
sql >select * from dbo_part where object_type in( 'VIEW', 'INDEX');
Execution Plan
----------------------------------------------------------
Plan hash value: 1583882680
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 121K| 11M| 1933 (1)| 00:00:24 | | |
| 1 | PARTITION LIST INLIST| | 121K| 11M| 1933 (1)| 00:00:24 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS FULL | DBO_PART | 121K| 11M| 1933 (1)| 00:00:24 |KEY(I) |KEY(I) |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='INDEX' OR "OBJECT_TYPE"='VIEW')
[3]
--query single partition with multiple values
*****************************************************
sql >select * from dbo where object_type in( 'VIEW', 'TABLE');
Execution Plan
----------------------------------------------------------
Plan hash value: 2675347415
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106K| 9M| 3249 (1)| 00:00:39 |
|* 1 | TABLE ACCESS FULL| DBO | 106K| 9M| 3249 (1)| 00:00:39 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='TABLE' OR "OBJECT_TYPE"='VIEW')
sql >select * from dbo_part where object_type in( 'VIEW', 'TABLE');
Execution Plan
----------------------------------------------------------
Plan hash value: 1583882680
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 121K| 11M| 1739 (1)| 00:00:21 | | |
| 1 | PARTITION LIST INLIST| | 121K| 11M| 1739 (1)| 00:00:21 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS FULL | DBO_PART | 121K| 11M| 1739 (1)| 00:00:21 |KEY(I) |KEY(I) |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='TABLE' OR "OBJECT_TYPE"='VIEW')
[4]
--query single partition but this time with unque key on other column
*****************************************************
sql >select * from dbo where object_type = 'VIEW' and ID = 22;
Execution Plan
----------------------------------------------------------
Plan hash value: 1710677120
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DBO | 1 | 98 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_DBO | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='VIEW')
2 - access("ID"=22)
sql >select * from dbo_part where object_type = 'VIEW' and ID = 22;
Execution Plan
----------------------------------------------------------
Plan hash value: 783359823
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 96 | 5 (0)| 00:00:01 | | |
|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| DBO_PART | 1 | 96 | 5 (0)| 00:00:01 | 3 | 3 |
|* 2 | INDEX RANGE SCAN | I_DBO_PART | 2 | | 3 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='VIEW')
2 - access("ID"=22)
[5]
--query with unque key access, equlaity predicate
*****************************************************
sql >select * from dbo where ID = 11;
Execution Plan
----------------------------------------------------------
Plan hash value: 1710677120
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DBO | 1 | 98 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_DBO | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=11)
sql >select * from dbo_part where ID = 11;
Execution Plan
----------------------------------------------------------
Plan hash value: 2846662584
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 4 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| DBO_PART | 1 | 99 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | I_DBO_PART | 1 | | 3 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=11)
[6]
--query with unque key access, range predicate - single partition
*****************************************************
sql >select * from dbo where ID > 900 and ID < 906;
Execution Plan
----------------------------------------------------------
Plan hash value: 1710677120
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 588 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DBO | 6 | 588 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_DBO | 6 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">900 AND "ID"<906)
sql >select * from dbo_part where ID > 900 and ID < 906;
Execution Plan
----------------------------------------------------------
Plan hash value: 2846662584
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 594 | 8 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| DBO_PART | 6 | 594 | 8 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | I_DBO_PART | 6 | | 3 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">900 AND "ID"<906)
[7]
--query with unque key access, equality predicate - across partition
*****************************************************
sql >select * from dbo where ID = 46 or ID = 906;
Execution Plan
----------------------------------------------------------
Plan hash value: 426573914
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 196 | 5 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DBO | 2 | 196 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_DBO | 2 | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=46 OR "ID"=906)
sql >select * from dbo_part where ID = 46 or ID = 906;
Execution Plan
----------------------------------------------------------
Plan hash value: 1829813444
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 198 | 6 (0)| 00:00:01 | | |
| 1 | INLIST ITERATOR | | | | | | | |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| DBO_PART | 2 | 198 | 6 (0)| 00:00:01 | ROWID | ROWID |
|* 3 | INDEX RANGE SCAN | I_DBO_PART | 2 | | 4 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=46 OR "ID"=906)
[8]
-- joining with non-partitioned table on partitioned key and indexed predicate on non-partitioned table
**********************************************************************************************************
sql >select * from dbo, dbo_part
2 where dbo.ID = dbo_part.ID
3 and dbo.id<10000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1157843163
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9596 | 1846K| | 3401 (1)| 00:00:41 | | |
|* 1 | HASH JOIN | | 9596 | 1846K| 1048K| 3401 (1)| 00:00:41 | | |
| 2 | PARTITION LIST ALL | | 9596 | 927K| | 3128 (1)| 00:00:38 | 1 | 4 |
|* 3 | TABLE ACCESS FULL | DBO_PART | 9596 | 927K| | 3128 (1)| 00:00:38 | 1 | 4 |
| 4 | TABLE ACCESS BY INDEX ROWID| DBO | 9947 | 951K| | 169 (0)| 00:00:03 | | |
|* 5 | INDEX RANGE SCAN | I_DBO | 9947 | | | 25 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DBO"."ID"="DBO_PART"."ID")
3 - filter("DBO_PART"."ID"<10000)
5 - access("DBO"."ID"<10000)
[9]
-- joining with non-partitioned table on partitioned key and indexed predicate on partitioned table
**********************************************************************************************************
sql >select * from dbo, dbo_part
2 where dbo.ID = dbo_part.ID
3 and dbo_part.id<10000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1157843163
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9596 | 1846K| | 3401 (1)| 00:00:41 | | |
|* 1 | HASH JOIN | | 9596 | 1846K| 1048K| 3401 (1)| 00:00:41 | | |
| 2 | PARTITION LIST ALL | | 9596 | 927K| | 3128 (1)| 00:00:38 | 1 | 4 |
|* 3 | TABLE ACCESS FULL | DBO_PART | 9596 | 927K| | 3128 (1)| 00:00:38 | 1 | 4 |
| 4 | TABLE ACCESS BY INDEX ROWID| DBO | 9947 | 951K| | 169 (0)| 00:00:03 | | |
|* 5 | INDEX RANGE SCAN | I_DBO | 9947 | | | 25 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DBO"."ID"="DBO_PART"."ID")
3 - filter("DBO_PART"."ID"<10000)
5 - access("DBO"."ID"<10000)
For Reference
select object_id from dbo where object_type='VIEW' AND ROWNUM<5;
OBJECT_ID
----------
900
902
904
906
select object_id from dbo where object_type='INDEX' AND ROWNUM<5;
OBJECT_ID
----------
46
11
3
49
select object_id from dbo where object_type='TABLE' AND ROWNUM<5;
OBJECT_ID
----------
28
19
56
13
select count(*), object_type from dbo_part group by object_type;
COUNT(*) OBJECT_TYPE
---------- -------------------
34325 INDEX
75 CONTEXT
2200 TYPE BODY
100 INDEXTYPE
100 RULE
7475 PROCEDURE
15550 JAVA RESOURCE
329825 JAVA CLASS
25 SCHEDULE
5500 TABLE PARTITION
50 WINDOW
25 WINDOW GROUP
33700 TABLE
25850 TYPE
74450 VIEW
416775 SYNONYM
17650 PACKAGE BODY
5275 SEQUENCE
125 JOB
5800 INDEX PARTITION
20 rows selected.
|
|
|
|
| Re: How to decide Partiitioning columns? [message #549282 is a reply to message #549259] |
Thu, 29 March 2012 06:52 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:I do not understand why more data is accessed while querying on partition table than on non-partitioned table What makes you think that more data is accessed? Your test so far proves nothing, you need to show the statistics as well as the exec plans. All you have so far are the optimizer's estimates.
|
|
|
|
| Re: How to decide Partiitioning columns? [message #549289 is a reply to message #549250] |
Thu, 29 March 2012 07:00 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
@RC, I've seen awful contention for concurrent inserts with PKs from a sequence. It is particularly bad in a RAC. Often, you need to hash partition both the index and the table, and build the instance number into the partitioning key: that way you will tend to get an affinity between partitions and instances, which helps a lot with the block mastering algorithm as masgtering will tend to be done by the instance which is busiest with each partition. And you need quite a few hash partitions per instance.
If you don't have RAC or the partitioning licence, a single table hash cluster may give the same effect of distributing the work throughout the object, but clustering can introduce other issues.
|
|
|
|
|
|
| Re: How to decide Partiitioning columns? [message #549369 is a reply to message #549259] |
Thu, 29 March 2012 22:31 |
|
orapratap
Messages: 134
Registered: November 2011
Location: Canada
|
Senior Member |
|
|
Hello John
gathering stats with following changed the plans to some extent though it did not flip-flop the plans entirely
exec dbms_stats.gather_table_stats (user,'DBO_PART','PART_3', granularity => 'ALL');
exec dbms_stats.gather_table_stats (user,'DBO_PART','PART_1', granularity => 'ALL');
exec dbms_stats.gather_table_stats (user,'DBO_PART','PART_2', granularity => 'ALL');
exec dbms_stats.gather_table_stats (user,'DBO_PART','PART_4', granularity => 'ALL');
I shall send you the actual execution plan
Meanwhile is it possible to answer original questions?
Quote:
Is it possible to 'yes' 'No' on following
will performance degrade if I query across partitions?
will partition help if I am selecting 30000 records from 4-10 milion records using index acess?
will partition help if I am joining partitioning keys to non-partitined table?
Thanks and Regards
Orapratap
|
|
|
|
| Re: How to decide Partiitioning columns? [message #549378 is a reply to message #549369] |
Fri, 30 March 2012 00:58 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Orapratap, you must read! I didn't say anything about gathering object statistics. I said that your claim that the queries that used partition pruning accssed more data were unsubstantiated. You need to show this. How many blocks did the queries actually read? The autotrace facility will show you this, excpet that you have switched off the statistics by invoking it with the EXP[LAIN] option. You should also SET TIMING ON before you run the statements. THen you will have the answer to your questions.
|
|
|
|
Goto Forum:
Current Time: Wed Apr 17 19:34:12 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
].