All Forums Database
jklee 40 posts Joined 07/06
07 Dec 2006
Paritition Elimination using Substitution

I read a post on another forum that indicates you can get parition elimination when uwing an import file in bteq (http://www.teradataforum.com/teradata/ 20061013_083308.htm).But I can't seem to get it to work.In the following example, table global_omniture_hit is partitioned by day on cast(omniture_date_time as date).In any case, what I need to do is to dynamically determine the date to select from the table at run time. There is only one date in the table at a time. The reason it is paritioned is becuase it improves the insert performance into another table that is partitioned by day as well. Here's what I tried:------------------------------------------------ ------------- Teradata BTEQ 08.02.03.03 for WIN32. Copyright 1984-2006, NCR Corporation. ALL RIGHTS RESERVED. Enter your logon or BTEQ command:.logon 192.168.10.240/jklee.logon 192.168.10.240/jkleePassword: *** Logon successfully completed. *** Teradata Database Release is V2R.06.01.00.02 *** Teradata Database Version is 06.01.00.39 *** Transaction Semantics are BTET. *** Character Set Name is 'ASCII'. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:.export data file="test.dat".export data file="test.dat" *** To reset export, type .EXPORT RESET BTEQ -- Enter your DBC/SQL request or BTEQ command:select current_date;select current_date; *** Success, Stmt# 1 ActivityCount = 1 *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:.export reset;.export reset; *** Output returned to console. BTEQ -- Enter your DBC/SQL request or BTEQ command:.import data file="test.dat";.import data file="test.dat"; BTEQ -- Enter your DBC/SQL request or BTEQ command:explainusing (d1 date)select *from staging.global_omniture_hitwhere cast(omniture_date_time as date) = :d1; *** Help information returned. 17 rows. *** Total elapsed time was 1 second.Explanation------------------------------------ --------------------------------------- 1) First, we lock a distinct staging."pseudo table" for read on a RowHash to prevent global deadlock for staging.global_omniture_hit. 2) Next, we lock staging.global_omniture_hit for read. 3) We do an all-AMPs RETRIEVE step from staging.global_omniture_hit by way of an all-rows scan with a condition of ( "(CAST((staging.global_omniture_hit.omniture_date_time ) AS DATE))= :d1") into Spool 1 (group_amps), which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The result spool file will not be cached in memory. The size of Spool 1 is estimated with no confidence to be 1,368,069 rows. The estimated time for this step is 25 minutes and 16 seconds. 4) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 25 minutes and 16 seconds. BTEQ -- Enter your DBC/SQL request or BTEQ command:-----------------If I hardcode the date, I get the parition eliminition:------------------------------------------ ------explainselect *from staging.global_omniture_hitwhere cast(omniture_date_time as date ) = '2006-12-06';explainselect *from staging.global_omniture_hitwhere cast(omniture_date_time as date ) = '2006-12-06'; *** Help information returned. 16 rows. *** Total elapsed time was 1 second.Explanation------------------------------------ --------------------------------------- 1) First, we lock a distinct staging."pseudo table" for read on a RowHash to prevent global deadlock for staging.global_omniture_hit. 2) Next, we lock staging.global_omniture_hit for read. 3) We do an all-AMPs RETRIEVE step from a single partition of staging.global_omniture_hit with a condition of ( "(CAST((staging.global_omniture_hit.omniture_date_time ) AS DATE))= DATE '2006-12-06'") into Spool 1 (group_amps), which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The result spool file will not be cached in memory. The size of Spool 1 is estimated with no confidence to be 1,368,069 rows. The estimated time for this step is 4 minutes and 34 seconds. 4) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 4 minutes and 34 seconds. BTEQ -- Enter your DBC/SQL request or BTEQ command:--------------Any tips on what is going on and how I can achieve what I want to do?

Fred 1096 posts Joined 08/04
07 Dec 2006

Unlike "static" partition elimination, Dynamic Partition Elimination happens during a join.It may not work with the CAST in the expression anyway, but try this:explainusing (d1 date)select *from staging.global_omniture_hit, (select :d1 as TheDate) xwhere cast(omniture_date_time as date) = x.TheDate;

jklee 40 posts Joined 07/06
07 Dec 2006

Well, in general the cast doesn't cause a problem. If I hard code the date, it works fine.Thanks for the idea, but alas, it didn't work either.So, still looking....

VandeBergB 182 posts Joined 09/06
08 Dec 2006

You're really not getting partition elimination with the hard-coded date. What your getting is the result of providing better information to the optimizer, it see's a date, Oh! gee I can use the partition. Per your post, you've only got one partition on the table, there aren't any partitions to eliminate. You should still see the same number of logical IO's in any case, the query still pushes the entire table into the target.Hope this helps...-Out!

Some drink from the fountain of knowledge, others just gargle.

You must sign in to leave a comment.