PDA does not have index, so how can you index without being able to create index.
That is brilliance of PDA, able to use index without index. How? Materialized View.
I first came across it in the best practice material in PDA launch. Under materialized views,
Finally, for a single or small number of rows the mview can be used as an index
- it contains a pointer to the block that holds the base row in the original table.
I was not sure how materialized view can be use as an index. Having a pointer to
the block that hold the base row in the original table would make it possible. So I did some
testing and found from the query plan that PDA does use it to for lookup like an index, then
get the other columns from the base table.
If you are not familiar with Materialize View, according to the IBM Netezza Database User's Guide it is
A materialized view (sometimes called a sorted, projected, and materialized view or
SPM view) is a view whose columns have been sorted, projected, and materialized
(that is, stored physically in a unique table).
The beauty of Materialized view is that it create zone maps for all columns in the ORDER BY clause except
columns of numeric type greater than 8 bytes -- 19 decimal digits or more. This means you could have
zone map for character types, however, only the fist 8 bytes are used in the zone map, making lookups
on characters much faster, but it be in the ORDER BY clause.
Create Material views on columns you think that might benefit with indexing.
But remember to use the ORDER BY clause to get the zone map or the materialized view would be effectively
Likes before 03/04/2016 - 2
Views before 03/04/2016 - 552