Forget about adding the partition key to indexes that are currently unique
in order to make them local.
The uniqueness requirement is about the CONSTRAINT, not the index. If you
add the partition key to the unique constraing the original unique
condition is no longer unique so you could get duplicates spread across
different partitions.
e.g. if order_id is considered to be unique but order_date is the partition
key, then
a) the index on (order_id) can be unique, but has to be global (or globally
partitioned).
or
b) the index on (order_id, order_date) can be unique and local, but only if
you now allow duplicates of order_id.
or
c) the index on (order_id, order_data) can be non-unique and local, but if
you create a unique constraint on order_id Oracle will create a global
unique index on (order_id)
In this example (where you are not likely to execute a query "where
order_id between X and Y") a damage-limiting exercise is to create the
index as globally hash partitioned so that "order_id = X" requires only one
index partition to be visitied and the index partitions are reduced in size
hence easier to handle when doing house-keeping. (e.g. when rebuilding).
Regards
Jonathan Lewis
On Sat, 8 Jan 2022 at 06:52, Lok P <loknath.73@xxxxxxxxx> wrote:
Thank You So much Andy.
When you said "*And worthy of note (though kind of obvious), if the clean
jobs aren't able to complete then those segments are continuously growing*.",
Do you mean that if the delayed index maintenance/cleanup job is not
getting completed but still we keep on dropping partitions in subsequent
days. This can cause the global index to grow substantially and then it can
impact the performance of the queries those are using this index access
path in them?
As you mentioned , there are cases in which this index cleanup job can
block the other sessions , which looks scary on a 24/7 system. And even
for unique indexes we are being asked/forced to have the partition key
included in the index keys so as to make it unique and local. So ,overall
It means we are no better even on the higher/recent versions of Oracle for
considering creation of global indexes on partitioned tables. Correct me if
I'm wrong.
On Sat, Jan 8, 2022 at 2:38 AM Andy Klock <andy@xxxxxxxxxxxxx> wrote:
Hi Lok,
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, January 7th, 2022 at 3:35 PM, Lok P <loknath.73@xxxxxxxxx>
wrote:
So my question was , how feasible its is in reality , if its reliable one
and now we can think of creating global indexes without hesitation now on
12.1 onward where-ever needed?
Global indexes are still feasible and sometimes necessary (unique
constraints, for example), but because of the maintenance you speak of,
local indexes would be preferred where appropriate.
Is this 12.1 version, deferred global index maintenance going to take
same time as it used to happen during 11.2 version with immediate 'update
indexes' option or its faster? Or any other downside, say if during this
auto deferred maintenance it will block any DML/SELECT statements etc?
Partition DROPS and TRUNCATES are measurably faster now that Oracle is
deferring the cleaning up of orphaned entries, but your concerns are still
very valid. Depending on size, I've had cases where cleaning wasn't able to
complete within the maintenance window, even with parallelism enabled.
There were also cases where sessions were being blocked by the clean job
and those segments had to be deferred and index rebuilds had to be done
during downtime windows. And worthy of note (though kind of obvious), if
the clean jobs aren't able to complete then those segments are continuously
growing.
Andy K
