Will PPI work if table is accessed through a view?

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Will PPI work if table is accessed through a view?

Hi All,

 

If suppose i have a QWE.table_A which is partitioned on its primary index. Now if a create a simple view like 

REPLACE VIEW QWE.table_A_v AS LOCKING ROW FOR ACCESS SELECT * FROM QEW.table_A;

Now if  i join this table QWE.table_A thorugh this view, will PPI work while accessing data from this table or not? if not is there any way to achieve this?

3 REPLIES
Apprentice

Re: Will PPI work if table is accessed through a view?

Hi,

Yes.

Whether the PPI is used or not does not require you to access the table directly.

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Will PPI work if table is accessed through a view?

You could also check the explian of the query to see if PPI is used or not ? You would see "elimination" keyword in it. Also, as replied it dosn't matter if you use view/base table for PPI usage.

----samir

Apprentice

Re: Will PPI work if table is accessed through a view?

Hi Samir,

 

Good point about using the Explain feature, often useful.

 

One minor correction though, you won't always see the word "elimination" in the plan. That only shows up if the optimiser can use "dynamic partition elimination" (DPE). If the plan uses "static partition elimination" then the word "elimination" is not shown in the plan. Static elimination is when the selection criteria in the sql code contains hard-coded values for the PPI column(s). An example is:

create table t1
(col1 integer
,txn_dt date
)
primary index(col1)
partition by range_n(txn_dt between date '2010-01-01' and date '2018-12-31' each interval '1' day);

If you explain the following sql:

select *
from t1
where txn_dt between date '2012-01-01' and date '2012-01-20';

The explain plan shows:

   1) First, we lock WARD.t1 for read on a reserved RowHash in all      
      partitions to prevent global deadlock.                            
   2) Next, we lock WARD.t1 for read.                                   
   3) We do an all-AMPs RETRIEVE step from 20 partitions of WARD.t1 with
      a condition of ("(WARD.t1.txn_dt <= DATE '2012-01-20') AND        
      (WARD.t1.txn_dt >= DATE '2012-01-01')") into Spool 1 (group_amps),
      which is built locally on the AMPs.  The size of Spool 1 is       
      estimated with no confidence to be 15 rows (435 bytes).  The      
      estimated time for this step is 0.01 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 0.01 seconds.           

Clearly the PPI is being used  and partitions are being eliminated, but the word "elimination" does not appear in the plan.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com