| Table Partitioning [message #343163] |
Tue, 26 August 2008 08:52  |
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  |
rleishman
Messages: 3728
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 |
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 |
rleishman
Messages: 3728
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
