Home » RDBMS Server » Performance Tuning » Table Partitioning (Oracle 10g)
Table Partitioning [message #343163] Tue, 26 August 2008 08:52 Go to next message
mariaandreou
Messages: 3
Registered: August 2008
Junior Member
I have two tables that are exactly the same (indexes fields etc) but in different schemas. The one is partitioned, and the other one is not.. Both tables have about 1000000 records.

The partition table has 5 partitions with the following number of data per partition:

P1 : 0
P2 : 271697
P3 : 1200172
P4 : 10113986
P5 : 451153

I also have a global index on the partitioned table.

Now I am running a query that selects some fields from this table based on the partitioning key which is also the indexing key for tha global index (that is thhe where field is the partition key). With this query I try to retrieve data that exist in the second partition (with 271697 records)

The strange is that for this query I get the same execution time for both tables!
Thought I suppose that I should need less time for the execution in the partitioned table!

The only case I get less time, is when I specified the partition in the FROM of the query for the partition table.

Can anybody help me to understand why this is working like this? And how I can tune this?


To be more specific the query is the bellow :

select “frditransactions”.”incmsgserno”
from “frditransactions”
where “frditransactions”.”bin” = ‘400000′
and “frditransactions”.”timestamp” >= TO_TIMESTAMP(’2005-10-13 00:00:00′, ‘YYYY-MM-DD HH24:MI:SS’)
and “frditransactions”.”timestamp” < TO_TIMESTAMP(’2005-10-14 00:00:00′, ‘YYYY-MM-DD HH24:MI:SS’)
order by “frditransactions”.”bin”, “frditransactions”.”authtimestamp” desc


In both tables (partitioned & non partitioned) the execution plan has only a TABLE ACCESS FULL (frditransactions).
The Cost/ Cardinality / Bytes for the non partitioned table are: 8650 / 2528 /73312 and for partitioned table are
8651 / 2500 / 62500. The query needs almost the same time in both tables.

Now, if I change the query as bellow:

select “frditransactions”.”incmsgserno”
from “frditransactions” partition (INCMSG_P2)
where “frditransactions”.”bin” = ‘400000′
and “frditransactions”.”timestamp” >= TO_TIMESTAMP(’2005-10-13 00:00:00′, ‘YYYY-MM-DD HH24:MI:SS’)
and “frditransactions”.”timestamp” < TO_TIMESTAMP(’2005-10-14 00:00:00′, ‘YYYY-MM-DD HH24:MI:SS’)
order by “frditransactions”.”bin”, “frditransactions”.”authtimestamp” desc


(...Actually I just specifed the partition) I get the same execution plan with only one TABLE ACCESS FULL (on “frditransactions”) and the Cost/ Cardinality / Bytes are 1925/ 557 / 13368. The time of this query is much less.

What I cannot understant is why i need to specified the partition in order to get better results?
I shouldn’t get better results in the partitioned table anyway?
Is there something else I have to specified?

Thanks for your help
Re: Table Partitioning [message #344675 is a reply to message #343163] Sun, 31 August 2008 22:50 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What is the partition key column and what is its data type?

Post the full explain plan including the PSTART and PSTOP values

Ross Leishman
Re: Table Partitioning [message #344702 is a reply to message #343163] Mon, 01 September 2008 01:19 Go to previous messageGo to next message
mariaandreou
Messages: 3
Registered: August 2008
Junior Member
Dear rleishman,

thanks a lot for your reply.
Actually the problem was that it was scanning all the partitiong I had in the database.
After a lot of searvhing I found that I should run the query as bellow:

select "frditransactions"."incmsgserno"
from "frditransactions"
where "frditransactions"."bin" = '400000'
and "frditransactions"."timestamp" >= date '2005-10-13'
and "frditransactions"."timestamp" < date '2005-10-14'
order by "frditransactions"."bin", "frditransactions"."authtimestamp" desc

I change the TO_Timestamp function with date. Thought I am not quite sure why there was such a problem. The partitioning key is the timestamp. I read that we might have such problem if we use functions on the partitioning key but I am not sure if this is the case. I wasn't using any function on "frditransactions"."timestamp" and I cananot understand the problem when using such functions on the comparing part!

Thanks a lot
Re: Table Partitioning [message #344935 is a reply to message #344702] Mon, 01 September 2008 23:03 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Is "frditransactions"."timestamp" defined as a TIMESTAMP datatype or a DATE datatype.

If it is a DATE, then when you compare it to a timestamp constant expression it will CAST the date to a timestamp, which is the same as enclosing it in a function.

Ross Leishman
Re: Table Partitioning [message #344965 is a reply to message #343163] Tue, 02 September 2008 00:49 Go to previous message
mariaandreou
Messages: 3
Registered: August 2008
Junior Member
Dear Ross,

Thanks a lot for your answer!
This clarifies my questions!
Previous Topic: Query optimisation using meaningless condition
Next Topic: How to make the response time less for this query
Goto Forum:
  


Current Time: Sun Dec 04 10:41:10 CST 2016

Total time taken to generate the page: 0.15813 seconds