if you are interested into the internal differences of the use of unique and non-unique indexes (latches, LIOs, reuse of index space etc.) you can take a look at Richard Foote's articles: http://richardfoote.wordpress.com/2009/03/25/differences-between-unique-and-non-unique-indexes-part-iv-take-it-back/. But looking at your example my main concern would be the use of row movement and a changeable partition key (row_purge_date): if you have a lot of migration between the partitions this could make DML quite expensive.
If I run the below two queries, how will be the expected performance and table/partition scans:
I assume, the following counts records from all partitions, so it does an index fast full scan as expected:
SELECT COUNT(*) FROM DOCA;
How about the performance of the below query? I assume this one does a full table scan rather than a full scan of one index partition.
SELECT COUNT(*) FROM DOCA WHERE DOCA_BKG_PAX_ID = 1234;
Field DOCA_BKG_PAX_ID is not a column in the index and not even the primary key, but it is the partition key. Of course adding a local index with the partition key in it does the trick. But why should I need to do this?
Please let me know if any more info required from my side. I would like to understand how useful is an index which uses key of a non-unique and non-partitioned one. Thanks for help.
Thanks for the link. It was helpful.
So please help me to understand what happens if we are creating a non-unique index on a non-partition & non-unique key like below and try to use it in a select query? Is this idea completely useless to be implemented.
CREATE INDEX XFKDOCA_DOCA_BKG_PAX_ID ON DOCA
if your query is: SELECT COUNT(*) FROM DOCA WHERE DOCA_BKG_PAX_ID = 1234; and the definitions of your initial post are correct and complete (and I did not miss something important) then the local index on DOCA_BKG_PAX_ID could be very useful and avoid a full table scan for all table partitions: if the CBO calculates that the value 1234 has a sufficient selectivity tomake an index access more efficient than the FTS it will use an index range scan on all index partitions (since the query does not contain a restriction on the partition key allowing partition pruning).
Thanks for your inputs.
Can u please share more insights into if we perform lot of migration between the partitions (use of row movement and a changeable partition key (row_purge_date) this could make DML quite expensive? I would like to understand more on this. Thanks in advacne for your help.