Paritition Elimination using Substitution

Database
Enthusiast

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/jklee
Password:

*** 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:

explain
using (d1 date)
select *
from staging.global_omniture_hit
where 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:
------------------------------------------------
explain
select *
from staging.global_omniture_hit
where cast(omniture_date_time as date ) = '2006-12-06'
;

explain
select *
from staging.global_omniture_hit
where 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?
3 REPLIES
Teradata Employee

Re: Paritition Elimination using Substitution

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:

explain
using (d1 date)
select *
from staging.global_omniture_hit, (select :d1 as TheDate) x
where cast(omniture_date_time as date) = x.TheDate
;
Enthusiast

Re: Paritition Elimination using Substitution

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....
Enthusiast

Re: Paritition Elimination using Substitution

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!