
BEGIN;

DROP INDEX IF EXISTS index_gtcMaterial_Content;
DROP INDEX IF EXISTS index_gtcMaterial_SearchContent;
DROP INDEX IF EXISTS index_gtcMaterial_FieldId;
DROP INDEX IF EXISTS index_gtcMaterial_SubfieldId;
DROP INDEX IF EXISTS index_gtcMaterial_FieldSubField;
DROP INDEX IF EXISTS index_gtcMaterial_ControlNumberSearch;

CREATE INDEX index_gtcMaterial_Content                  ON gtcmaterial Using BTREE (controlNumber, content);
CREATE INDEX index_gtcMaterial_SearchContent            ON gtcmaterial Using BTREE (controlNumber, searchContent);
CREATE INDEX index_gtcMaterial_FieldId                  ON gtcmaterial Using BTREE (controlNumber, fieldId);
CREATE INDEX index_gtcMaterial_SubfieldId               ON gtcmaterial Using BTREE (controlNumber, subFieldId);
CREATE INDEX index_gtcMaterial_FieldSubField            ON gtcmaterial Using BTREE (controlNumber, fieldId, subFieldId);
CREATE INDEX index_gtcMaterial_ControlNumberSearch      ON gtcmaterial Using BTREE (controlNumber, fieldId, subFieldId, searchContent);

DROP INDEX IF EXISTS index_gtcMaterialControl_controlNumber;
DROP INDEX IF EXISTS index_gtcMaterialControl_entranceDate;
DROP INDEX IF EXISTS index_gtcMaterialControl_category;
DROP INDEX IF EXISTS index_gtcMaterialControl_level;
DROP INDEX IF EXISTS index_gtcMaterialControl_gender;
DROP INDEX IF EXISTS index_gtcMaterialControl_type;
DROP INDEX IF EXISTS index_gtcMaterialControl_physical;

CREATE INDEX index_gtcMaterialControl_controlNumber     ON gtcmaterialcontrol Using BTREE (controlNumber, category, level);
CREATE INDEX index_gtcMaterialControl_entranceDate      ON gtcmaterialcontrol Using BTREE (controlNumber, entrancedate);
CREATE INDEX index_gtcMaterialControl_category          ON gtcmaterialcontrol Using BTREE (controlNumber, category);
CREATE INDEX index_gtcMaterialControl_level             ON gtcmaterialcontrol Using BTREE (controlNumber, level);
CREATE INDEX index_gtcMaterialControl_gender            ON gtcmaterialcontrol Using BTREE (controlNumber, materialgenderid);
CREATE INDEX index_gtcMaterialControl_type              ON gtcmaterialcontrol Using BTREE (controlNumber, materialtypeid);
CREATE INDEX index_gtcMaterialControl_physical          ON gtcmaterialcontrol Using BTREE (controlNumber, materialphysicaltypeid);

DROP INDEX IF EXISTS index_gtcMaterialControl_father;
DROP INDEX IF EXISTS index_gtcMaterialControl_fatherCatLev;

CREATE INDEX index_gtcMaterialControl_father            ON gtcmaterialcontrol Using BTREE (controlnumberfather);
CREATE INDEX index_gtcMaterialControl_fatherCatLev      ON gtcmaterialcontrol Using BTREE (controlnumberfather, category, level);

DROP INDEX IF EXISTS index_gtcExemplaryControl_library;
DROP INDEX IF EXISTS index_gtcExemplaryControl_status;

CREATE INDEX index_gtcExemplaryControl_library          ON gtcExemplarycontrol Using BTREE (controlNumber, libraryunitid);
CREATE INDEX index_gtcExemplaryControl_status           ON gtcExemplarycontrol Using BTREE (controlNumber, exemplarystatusid);

DROP INDEX IF EXISTS index_gtcSearchMaterialView_controlNumber;
DROP INDEX IF EXISTS index_gtcSearchMaterialView_Category;
DROP INDEX IF EXISTS index_gtcSearchMaterialView_CategoryLevel;
DROP INDEX IF EXISTS index_gtcSearchMaterialView_itemNumber;
DROP INDEX IF EXISTS index_gtcSearchMaterialView_libraruUnit;
DROP INDEX IF EXISTS index_gtcSearchMaterialView_itemNumberL;
DROP INDEX IF EXISTS index_gtcSearchMaterialView_itemNUmberLO;

CREATE INDEX index_gtcSearchMaterialView_controlNumber   ON gtcSearchMaterialView Using BTREE (controlNumber);

--Aconselhados pelo Xande:
DROP INDEX IF EXISTS index_gtcLoan_personId;
DROP INDEX IF EXISTS index_gtcReserveComposition_itemNumber;
DROP INDEX IF EXISTS index_gtcLoan_itemNumber_returnDate;
DROP INDEX IF EXISTS index_gtcFine_loanId;
DROP INDEX IF EXISTS index_gtcReserve_personId;
DROP INDEX IF EXISTS index_gtcPenalty_personId;
DROP INDEX IF EXISTS index_gtcMarcTagListingOption_marcTagListingId;
DROP INDEX IF EXISTS index_gtcOperatorLibraryUnit_operator;
DROP INDEX IF EXISTS index_gtcControlFieldDetail_1;
DROP INDEX IF EXISTS index_gtcMaterial_indice4;
DROP INDEX IF EXISTS index_gtcLoan_libraryUnitId;
DROP INDEX IF EXISTS index_gtcFineStatusHistory;

CREATE INDEX index_gtcLoan_personId on gtcloan (personid);
CREATE INDEX index_gtcReserveComposition_itemNumber on gtcreservecomposition(itemnumber);
CREATE INDEX index_gtcLoan_itemNumber_returnDate on gtcloan (itemnumber) where returndate is null;
CREATE INDEX index_gtcFine_loanId on gtcfine (loanid);
CREATE INDEX index_gtcReserve_personId ON gtcReserve (personid);
CREATE INDEX index_gtcPenalty_personId ON gtcpenalty (personid);
CREATE INDEX index_gtcMarcTagListingOption_marcTagListingId ON gtcMarcTagListingOption (marcTagListingId);
CREATE INDEX index_gtcOperatorLibraryUnit_operator ON gtcOperatorLibraryUnit(operator);
CREATE INDEX index_gtcControlFieldDetail_1 ON gtcControlFieldDetail(fieldId, subfieldId, categoryId, isActive);
CREATE INDEX index_gtcMaterial_indice4 ON gtcmaterial(fieldid, subfieldid, searchcontent varchar_pattern_ops);
CREATE INDEX index_gtcLoan_libraryUnitId on gtcLoan (libraryunitid) where returndate is null;
CREATE INDEX index_gtcFineStatusHistory  ON gtcfinestatushistory (fineid);

COMMIT;
