1

Topic: Composite

Good afternoon!
Came across on such in a label.

---------
alter table BAL_ARCHIVE
add constraint PK_BAL primary key (ARCHIVEDATE, ACCOUNTNO, STATE)
using index local;
alter index PK_BAL nologging;
----------------
create index X_BAL on BAL_ARCHIVE (ACCOUNTNO, ARCHIVEDATE, STATE)
compress 1 nologging local;
-------------

Prompt, whether there is any sense in index X_BAL if we have on the same fields unique index PK_BAL?
Label partition by range (ARCHIVEDATE).

2

Re: Composite

Tried to consider DBMS_SPACE.create_index_cost differences in size is not present.
Or it is impossible to consider through DBMS_SPACE.create_index_cost when indexes are already created?

3

Re: Composite

Moss, the order of fields matters. These are different indexes for the different purposes. For example for request on "to all accounts for yesterday" the first index will be used. For requests of a type "to look that there under the specific account for the last month" it was more effective there will be the second.
PS time this field makes  the second index could be declared and unique