Re: How to minimise sorting time in a query

>> But any way, as you suggested , having a global index created on this
table to avoid sorting overhead, so we can have the global index created on three columns (c1, c2, c3) to help the query and map the primary key to this new global index. And drop the existing local pk index which is on (c1, c2, date_created).

From what you've told us so far this seems to be the case. In your position I would be a little worried that you might already have duplicates of (c1, c2) with different values of date_created - you can't trust client code to enforce database uniqueness - so when you design your implementation plan include the assumption that there will be duplicates ("hope for the best, plan for the worst").

Regards
Jonathan Lewis

Regards
Jonathan Lewis

On Tue, 8 Nov 2022 at 09:51, Lok P <loknath.73_at_gmail.com> wrote:

> Thank you so much Jonathan. Got your point. And thanks for the clever
> technique you suggested to refer a part of the index I. E first two keys of
> index to enforce unique constraints.
>
> So now we don't have any existing constraints defined on (c1, c2), however
> as per the business logic, data will be unique on these composite two
> columns. The date_created was added just because a local unique index would
> need partition key to be part of the primary key and it has been done like
> that in past to avoid global index maintenance issues.
>
> But any way, as you suggested , having a global index created on this
> table to avoid sorting overhead, so we can have the global index created on
> three columns (c1, c2, c3) to help the query and map the primary key to
> this new global index. And drop the existing local pk index which is on
> (c1, c2, date_created).
>
>
> On Tue, 8 Nov, 2022, 1:27 am Jonathan Lewis, <jlewisoracle_at_gmail.com>
> wrote:
>
>> I missed the detail that it was (c1, c2, create_dt) that was the primar
>> key, and slipped to the end of the sentence where you had (c1, c2) is
>> unique.
>>
>> Is the combination (c1, c2) declared as unique, or are you hoping it will
>> stay unique without an enforcing constraint. If you've got a declared
>> constraint is it safe to assume it's enforce by a global index on (c1,c2)?
>>
>> A globally partitioned index has to be prefixed by the column that's used
>> to partition it, not by the table's partitioning column(s) so you can
>> create a globally partitioned index on (c1, c2, c3) which is partitioned by
>> C1 viz:
>>
>> create index t1_i1 on t1(c1,c2,c3) global partition by range (c1) (
>> partition p1 values less than ('31'),
>> partition p2 values less than ('32'),
>> partition pm values less than (maxvalue)
>> )
>> /
>>
>> Because t1_i1 is not a (non-unique) globally partitioned index starting
>> with (c1, c2), you can also enforce uniqueness on (c1, c2) by executing:
>>
>> alter table t1 add constraint t1_uk unique (c1, c2) using index t1_i1;
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l