Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Dynamic Partition Names in PL/SQL (8.1.5)
- Thread starter JulesF
- Start date
Jules,<br>
<br>
Why do you think that you have to name the specific partition?<br>
<br>
If you specify the columns comprising the partition key in the predicates of your WHERE clause, it will prune partitions appropriately, automatically.<br>
<br>
So, if your WHERE clause logically limits the SQL statement to only one partition, then that is the only partition that will be accessed. If your WHERE clauses crosses multiple partitions but not all, then only those will be accessed.<br>
<br>
Verifying this via EXPLAIN PLAN is possible but a little counter-intuitive. If you access only one partition, EXPLAIN PLAN returns a phrase like "TABLE ACCESS ...", which makes it sound like it's accessing the entire table with all partitions. In reality, it's indicating that it's accessing one segment (i.e. one partition), not multiple. If your SQL statement accesses multiple partitions (or all partitions), you'll see EXPLAIN PLAN display a phrase like "concatenate partition" or something. This means that its scanning multiple partitions...<br>
<br>
So, unless you're doing something unusual (i.e. DDL like CREATE, DROP, ALTER, or TRUNCATE), you should not need to specify the particular partition(s)...<br>
<br>
Hope this helps...<br>
<br>
-Tim
<br>
Why do you think that you have to name the specific partition?<br>
<br>
If you specify the columns comprising the partition key in the predicates of your WHERE clause, it will prune partitions appropriately, automatically.<br>
<br>
So, if your WHERE clause logically limits the SQL statement to only one partition, then that is the only partition that will be accessed. If your WHERE clauses crosses multiple partitions but not all, then only those will be accessed.<br>
<br>
Verifying this via EXPLAIN PLAN is possible but a little counter-intuitive. If you access only one partition, EXPLAIN PLAN returns a phrase like "TABLE ACCESS ...", which makes it sound like it's accessing the entire table with all partitions. In reality, it's indicating that it's accessing one segment (i.e. one partition), not multiple. If your SQL statement accesses multiple partitions (or all partitions), you'll see EXPLAIN PLAN display a phrase like "concatenate partition" or something. This means that its scanning multiple partitions...<br>
<br>
So, unless you're doing something unusual (i.e. DDL like CREATE, DROP, ALTER, or TRUNCATE), you should not need to specify the particular partition(s)...<br>
<br>
Hope this helps...<br>
<br>
-Tim
- Thread starter
- #3
Tim,<br>
<br>
We're looking at the possibility of having a table with about 2500 partions. Due to the business logic and how the partitioning is setup we know the partition to access therefore it seems quicker to specify it rather than have Oracle perform the partitioning pruning.<br>
<br>
Jules
<br>
We're looking at the possibility of having a table with about 2500 partions. Due to the business logic and how the partitioning is setup we know the partition to access therefore it seems quicker to specify it rather than have Oracle perform the partitioning pruning.<br>
<br>
Jules
Jules,<br>
<br>
Have you tested this? I've never witnessed any difference in performance between explicitly naming a partition in the FROM clause and implicitly specifying it via the WHERE clause. Verify it; it would be a real shame to hard-code something like this for no reason.<br>
<br>
One verification would be to enable SQL tracing for a test case session and TKPROF the result. This should tell definitively whether additional processing was necessary one way or the other. To enable SQL trace in the session, use:<br>
SQL> alter session set sql_trace = true;<br>
then just do your test cases. To TKPROF the resulting trace file, it would make sense to use the following:<br>
FOR UNIX:<br>
$ tkprof <trc-file> <out-file> sort=exeqry,fchqry,execu,fchcu explain=<username/password><br>
For a test like this, be aware that TKPROF summarizes at the bottom, so it would be best to do the two test cases in two separate sessions, and verify using the summarized statistics at the bottom of the TKPROF report.<br>
<br>
Another fast verification would be to use the SET AUTOTRACE ON facility in SQL*Plus. That should produce statistics for each statement which should prove it for you one way or another...<br>
<br>
Have you tested this? I've never witnessed any difference in performance between explicitly naming a partition in the FROM clause and implicitly specifying it via the WHERE clause. Verify it; it would be a real shame to hard-code something like this for no reason.<br>
<br>
One verification would be to enable SQL tracing for a test case session and TKPROF the result. This should tell definitively whether additional processing was necessary one way or the other. To enable SQL trace in the session, use:<br>
SQL> alter session set sql_trace = true;<br>
then just do your test cases. To TKPROF the resulting trace file, it would make sense to use the following:<br>
FOR UNIX:<br>
$ tkprof <trc-file> <out-file> sort=exeqry,fchqry,execu,fchcu explain=<username/password><br>
For a test like this, be aware that TKPROF summarizes at the bottom, so it would be best to do the two test cases in two separate sessions, and verify using the summarized statistics at the bottom of the TKPROF report.<br>
<br>
Another fast verification would be to use the SET AUTOTRACE ON facility in SQL*Plus. That should produce statistics for each statement which should prove it for you one way or another...<br>
- Status