how to use partition on the following sql?

[bunwong]

We are using the following query to get data from a table which is paritioned by "call_start_date"

Select field1, field2
From huge_tbl
Where Call_Start_Date >= current_date - 60

We found that we are not able to use the partition because of the "current_date" function and the ">=", this causes a performance problem in the query.

However, the above query is generated by Cognos ReportNet, we are not able to subsitute the "current_date" function with a constant date.

In this case, any (innovative) idea to make use of partition?

Bun

 

[RogerCruse]

Hi Bun,
You haven't stated what the Primary Index is.

Also, ">=" will mean that Teradata has to search every row to find matching records.

I suggest you -
Use the PI (let's assume it's Customer_Number & Calendar_Date) and include the reference tables for these.
Change the ">=" to a between -

e.g.
select field1, field2
from hugh_tbl xxx
join ref_customer yyy
on xxx.customer_number=yyy.customer_number
join ref_calenday zzz
on zzz.calendar_date=xxx.Call_Start_Date
where xxx.calendar_date between current_date - 60 and current_date;

Now, Teradata will should be able to use your PI to identify the rows you want.

Of course, if you don't have STATS collected or your still asking for more than 15% or so of your large table then Teradata will still have to plough through every record.

The "trick" with Teradata is choosing the right Primary Index and using it.

Check the Primary Index (using SHOW before the select), check that you've got STATS ( help stats hugh_tbl) and also check the access path (put EXPLAIN before the select or hitF6 if your using QueryMan).

Roger...

Roger...

 

[bunwong]

Dear Roger,

Thanks, for your input. However I still do not understand...

My problem is that the sql won't use partition, how does the PI affact the use of partition?

also, if i just want to have a simple select on the table (i.e. i don't have ref_customer) , i.e

select field1, field2
from hugh_tbl xxx
join ref_calenday zzz
on zzz.calendar_date=xxx.Call_Start_Date
where xxx.calendar_date between current_date - 60 and current_date;

how does it work?

Merry Christmas and thank you!

Bun

 

[RogerCruse]

Hi Bun,
Try this simple test to help you understand what I mean.

Put the word SHOW before your SQL - e.g.

SHOW SELECT * FROM ...

Hit F5 button (or the green feet). This should show the underlying views / tables in your SQL. Find your "hugh_tbl" and see what the Primary Index is.

Now add additional conditions to your SQL so that each column of the Primary Index is used, don't worry about what values you use at this stage -

SELECT * FROM ...
WHERE Column1 = 1234567
AND Column2= 'ABC'

Now hit F5 button and see how long Teradata takes to return a result.

Was that quick?

OK, most likely, there wasn't any data returned, but this gives you an idea how fast Teradata can respond when you use the Primary Index. This is because Teradata uses the Primary Index to store the data and can use this to find the data you want.

Try agains but this time use the F6 button (or put the word EXPLAIN before your SQL).

This produces a step-by-step guide to how Teradata will resolve your SQL with estimates for time & rows returned plus levels of confidence for each step.

If you now include the reference tables for each column in your Primary Index and then apply any conditions you have then Teradata will use these reference tables to get your data.

Check the EXPLAIN using the F6 button

Even if you want every value for one of the reference tables include it anyway, so that Teradata can use the Primary Index.

If you need every value of every reference table or your Primary Index is made up of columns without reference tables then maybe your need to review your SQL or the Prmary Index.

You may want to check that the STATS are up to date for your tables by putting the words HELP STATS before the table name and hittinf the F5 button for this piuece of code only -

HELP STATS hugh_tbl;

There's an option within QueryMan that makes this easier.

If you results are quite old or there are no results! then check this with your DBA, etc.

Roger...