can any one tell me that how can we find the tables which are having PPI'S ?
sel databasename, tablename from dbc.indices where indextype ='Q';
thanks for the update, and i have another doubt like "what are the key factors that we need to observe, if we are planing to implement PPI?"
thanks in advance...
database administration physical implementation documentation is what you need to browse..
All physical implementation are centered around even distribution of data and providing best access path for the most frequently asked questions!
If the data retrieval is based on specific range for example eventhough the table has 5 years of data but the most queries submitted against the table pulls only last two quaters. Then definig PPI will help in improving the performance by partition elimination.
Make sure all the queries submitted against PPI tables using the PPI columns as filter, else may impact the performance.
Collect stats on PARTITION.