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 Go to next message
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 #549099 is a reply to message #549097] Wed, 28 March 2012 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67925
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What could be the strategy on deciding which columns to create partitions on?


Data Warehousing Guide
Chapter 5 Partitioning in Data Warehouses
especially the sections "When to use..."

Regards
Michel

Re: How to decide Partiitioning columns? [message #549101 is a reply to message #549097] Wed, 28 March 2012 03:11 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
If PK means primary key, those will be unique index lookups and ridiculously fast regardless.

Partitioning is usually seen (IME) on tables where a query will bring back a decent chunk of data from the partitioned table. You know, things like transactions in <period> or all transactions of <Type>.

Don't recall seeing it used on single row access-style tables.


If it didn't mean Primary key, my apologies Smile
Re: How to decide Partiitioning columns? [message #549111 is a reply to message #549101] Wed, 28 March 2012 03:41 Go to previous messageGo to next message
John Watson
Messages: 8581
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8581
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 #549219 is a reply to message #549170] Wed, 28 March 2012 22:15 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello John

Thanks for your reply

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

Thanks and Regards
Oraratap
Re: How to decide Partiitioning columns? [message #549220 is a reply to message #549219] Wed, 28 March 2012 22:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Partitioning a table does not necessarily make all SQL that accesses the table faster.

Which flavor (Range, List, Hash) of partitioning is best for this table?

http://docs.oracle.com/cd/E11882_01/server.112/e25789/schemaob.htm#CNCPT112

[Updated on: Wed, 28 March 2012 22:24]

Report message to a moderator

Re: How to decide Partiitioning columns? [message #549221 is a reply to message #549220] Wed, 28 March 2012 22:33 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Thanks Blackswan

Of course partitioning wasn't the first thought

When I saw the increasing vloume of the data, initially I thought, how best I can process required data by handling minimal data.
It needs major application change - addition of columns, setting flags, some denormalisation and it becomes too big activity.

I understand FTS which do not span across multiple partitions will surely boost performance by replacing FTS on full table.

Regarding 1) Index access on big chunk of data 2) FTS across multiple partitions I need help.
Kindly help me on this

Regarding your quote
Quote:
Which flavor (Range, List, Hash) of partitioning is best for this table?

I did not understand it
In case it pertains to the example I mentioned it was just for understanding only. Could you help me getting the answer on it on the context of list partitioning?

Thanks and Regards
Orapratap
Re: How to decide Partiitioning columns? [message #549222 is a reply to message #549221] Wed, 28 March 2012 22:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>assume that emp table is very large
post results from following SQL

SELECT COUNT(*) FROM EMP;

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: How to decide Partiitioning columns? [message #549223 is a reply to message #549222] Wed, 28 March 2012 22:48 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello Blackswan

I have read the guidelines and this being a generic concept query (not a specific query) I am unable to post plan etc.

I trying to understand where partitioning can help me and where things can go wrong
I am not having only a particular table in our application in mind

For the question in my original post
assume that the emp table has 4 million records and I want to select 1.1 - 1.5 million records

Basically I need a guideline on

Quote:
Regarding 1) Index access on big chunk of data 2) FTS across multiple partitions


in above cases lets assume data is approx 4m

Thanks and Regards
Orapratap
Re: How to decide Partiitioning columns? [message #549224 is a reply to message #549223] Wed, 28 March 2012 22:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post results from following SQL

SELECT * FROM V$VERSION;
Re: How to decide Partiitioning columns? [message #549226 is a reply to message #549224] Wed, 28 March 2012 22:54 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello BlackSwan

As of now I am working on the following version but the implementation might take place on 10.2.0.4

SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production


Thanks and Regards
Orapratap
Re: How to decide Partiitioning columns? [message #549237 is a reply to message #549226] Thu, 29 March 2012 01:37 Go to previous messageGo to next message
John Watson
Messages: 8581
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 #549250 is a reply to message #549111] Thu, 29 March 2012 02:32 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
John Watson wrote on Wed, 28 March 2012 09:41
@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.


Interesting, thanks. Not something I've run into [yet Wink ].

I'd have considered reversing the key, but likely not going so far as to hash partition too.

Is a problem of that magnitude common?
Re: How to decide Partiitioning columns? [message #549251 is a reply to message #549237] Thu, 29 March 2012 02:35 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8581
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 Go to previous messageGo to next message
John Watson
Messages: 8581
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 #549294 is a reply to message #549289] Thu, 29 March 2012 07:11 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Interesting again, appreciated Smile
Re: How to decide Partiitioning columns? [message #549369 is a reply to message #549259] Thu, 29 March 2012 22:31 Go to previous messageGo to next message
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 Go to previous message
John Watson
Messages: 8581
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.
Previous Topic: Optimiser Cost Caclulation for nested loop join
Next Topic: Distributed query running slow
Goto Forum:
  


Current Time: Thu Aug 05 19:20:49 CDT 2021