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?
Whether the PPI is used or not does not require you to access the table directly.
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.
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.