what is the disadvantage of PPIs?

Well , if we are retrieving data based on a column which is not
part of PPI column, then AMP has to search every partions.

AMPs cant apply Binary search on ROWID. Because rows are ordered
using partition column

Ex: Assume employee table where primary index is empno and partion is created using
deptno..

select * from employee where eno=1000;

The above query goes to one AMP. But with in that AMP it can't Apply
Binary search(on Row HASH) to find row quickly. because data is ordered
using dept no.

So we have to be careful


To handle this situavations,

1. we can create a secondary index on that column (if seems to be good)

2. Include dept no also in the query

Ex: select * from employee where eno=1000 and deptno=20;