Partitioning Tables

75 pts.
Tags:
Partitioning
Table partitioning
I have two tables taht 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 follwing 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 strabnge is taht 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? Thanks for your help

Answer Wiki

Thanks. We'll let you know when a new response is added.

Can you post the SQL queries and explain plans for both? That may tell us nothing or may tell us the whole story. The partitioning will be faster if less blocks are retrieved, trace it, explain plan it and you will be able to see what each is doing and why it may or may not be faster.

————————————————————–
Try making your timestamp index local, not global. A global index has to be scanned completely, just like an index on a non-partitioned table.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • MariaAndreou
    Rdano, Thanks for your answer I have already tried execution plan and here are the results: 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 teh 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? Just to mention that I partitioned the partitioned table whith the bellow command: partition by range ("timestamp") (PARTITION p1 VALUES LESS THAN (TO_DATE('2005-10-1','YYYY-MM-DD')), PARTITION p2 VALUES LESS THAN (TO_DATE('2005-10-15','YYYY-MM-DD')), PARTITION p3 VALUES LESS THAN (TO_DATE('2005-10-30','YYYY-MM-DD')), PARTITION p4 VALUES LESS THAN (TO_DATE('2005-11-15','YYYY-MM-DD')), PARTITION p5 VALUES LESS THAN (TO_DATE('2005-11-30','YYYY-MM-DD')) ); Thanks for your help.
    75 pointsBadges:
    report
  • MariaAndreou
    Sorry for posting this three times! I have no I idea why it posted this three time and I do not know hoe to change this ...
    75 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following