SQL: ability to choose a specific index

Primary tabs

Indexing, SQL, Caché

I'm using Cache SQL and want the ability to choose a specific index.

I've boiled the problem down to one table and simplified the query down to

SELECT *
FROM Registration.PatResp
WHERE SchedApptNum=8450022

 

SchedApptNum is indexed, but instead of using that column, "Show Plan" indicates that it's looping through the entire Registration.PatResp table on Id (the primary key for the table).

I've done a tune-table with no change.

SQL provides %IGNOREINDEX, which allows me to ignore a specific index, but I don't need that.  I need the reverse - %CHOOSEINDEX so that I can force SQL to use a specific index.

 

Any ideas?

Replies

I have a whole slew of indices on that table.

And the strange thing is that it's not choosing the index I want, but it's walking through the whole table.

  • Read master map Registration.PatResp.DBMSMaster, looping on Id.

Can you include the class definition so we can take a look ?

The class definition is large, but here you go:

Class Reference
Cache for UNIX 2018.1.2
InterSystems: The power behind what matters   
 
Private  Storage  
 

persistent class Registration.PatResp extends %Persistent, %XML.Adaptor

Patient Responsibility table

Inventory
 
Summary
 
Properties
AdtVisPtr Assist Audits BudPlan
CaseNum Cin ColReason CurStmtBal
CustomCollection Dets EffDate EligLastReceived
EligReqNum EligReviewedBy EligStat Estimate
ExpDate Fsc Grp Id
InsComp InstanceDelFlag InstanceType LastStmtBal
LastStmtProducedDate Notes Patient RowInfo
SchedApptNum StmtAccountStat TotCollectedAmt TotPatResp
TotVerPatResp VisNum
 
Methods
%AddToSaveSet %AddToSyncSet %CheckConstraints %CheckConstraintsForExtent
%ClassIsLatestVersion %ClassName %ComposeOid %ConstructClone
%Delete %DeleteExtent %DeleteId %DispatchClassMethod
%DispatchGetModified %DispatchGetProperty %DispatchMethod %DispatchSetModified
%DispatchSetMultidimProperty %DispatchSetProperty %Exists %ExistsId
%Extends %GUID %GUIDSet %GetLock
%GetParameter %GetSwizzleObject %Id %IsA
%IsModified %IsNull %LockExtent %LockId
%New %NormalizeObject %ObjectIsNull %ObjectModified
%Oid %OnBeforeAddToSync %OnDetermineClass %Open
%OpenId %OriginalNamespace %PackageName %PurgeIndices
%Reload %RemoveFromSaveSet %ResolveConcurrencyConflict %RollBack
%Save %SaveDirect %SerializeObject %SetModified
%SortBegin %SortEnd %SyncObjectIn %SyncTransport
%UnlockExtent %UnlockId %ValidateIndices %ValidateObject
CaseNumExists CinExists ColReasonExists DeactbyCaseVisAppt
DeactivatebyPatId DeactivatebyPatRespId DeletebyCaseVisAppt DeletebyPatId
DeletebyPatRespId DelimiterCheck EffDateExists EligLastReceivedExists
EligReqNumExists EligReviewedByExists EligStatExists ExpDateExists
FscExists GetDisplayField GetInstances GetPRCaseVisAppt
GetPRRecCombo GetPatRespRec GetVersion GrpExists
InsCompExists InstanceDelFlagExists LastStmtProducedDateExists LoadCustom
LocalToObject LocalToObjectXml ObjectToLocal ObjectToLocalXml
OpenIdLocal OptimisticLockCheck ReactbyCaseVisAppt ReactivatebyPatId
ReactivatebyPatRespId SaveLocal SetSaveData StmtAccountStatExists
XMLDTD XMLExport XMLExportToStream XMLExportToString
XMLNew XMLSchema XMLSchemaNamespace XMLSchemaType

 
Parameters
 
• parameter DBMSTable = "REG_B_PAT_PT_RESPONSIBILITY";
 
• parameter DEFAULTCONCURRENCY = 0;
DEFAULTCONCURRENCY is the default value for the concurrency formal argument defined for %Open, %OpenId, %Delete and %DeleteId methods. It is not the default value of the %Concurrency property of persistent classes. If the value of the actual concurrency argument passed to any of the above methods is -1 then the formal argument value will be set to the value of the DEFAULTCONCURRENCY parameter value defined for the class.
• parameter NAMESPACE = "http://gehcit.ge.com/cb/Registration.PatResp";
NAMESPACE specifies the XML namespace to be used when projecting the class to XML. if NAMESPACE - "", the default namespace is used for the XML schema is used as the namespace for his class.
• parameter STORAGEDELIMITERCHECK = 1;
 
• parameter XMLFORMAT = "Literal";
The XMLFORMAT parameter controls the generation of the XMLExport and XMLImport methods for XML enabled classes to include code for only literal or only encoded format. This allows the generated routines to be significantly smaller since usually both formats are not needed.
If XMLFORMAT="Literal", then only support for literal format import and export is generated.
If XMLFORMAT="Encoded", then only support for SOAP encoded format import and export is generated.
The default is to generate support for both literal and encoded format.
• parameter XMLIGNORENULL = "INPUTONLY";
XMLIGNORENULL allows the programmer to override the default XML handling of empty strings for properties of type %String. By default (XMLIGNORENULL = 0), empty strings in the XML input are stored as $c(0) and $c(0) is written to XML as an empty tag. A missing tag in the XML input is always stored as "" and "" is always output to XML as no tag.

If XMLIGNORENULL is set = 1, then both missing tags in the XML and empty strings are input as "", and both "" and $c(0) are output as empty tags (i.e. <tag></tag>).

If XMLIGNORENULL is set = "inputonly", then both missing tags in the XML and empty strings are input as "". Output of "" and $c(0) are for XMLIGNORENULL = 0: $c(0) is output as an empty tag (i.e. <tag></tag>) and "" is output as no tag.

If XMLIGNORENULL = "runtime" (runtime is not case sensitive), then the behavior of XMLIGNORENULL is determined by the format parameter of XMLExport, XMLImport and %XML.Reader.OpenFile. The default behavior for XMLIGNORENULL="runtime is the same as XMLIGNORENULL=0. Adding "ignorenull" to the format argument changes the behavior to that of XMLIGNORENULL=1. "ignorenull" shoud be separated by a comma from literal/encoded part of the format. Example values for format are "", ",ignorenull", "literal,ignorenull" and "encoded,ignorenull". Note that "inputonly" is equivalent to using ,ignorenull for XMLExport and not for %XML.Reader.
Properties
 
• property AdtVisPtr as %String(CAPTION="ADT Visit Ptr",COLLATION="EXACT",TRUNCATE=1);
This is the internal pointer to the Hospital Visit = VGRP_VID_VIN_GRP_ARCHIVED_ID.
• property Assist as %String(COLLATION="EXACT",TRUNCATE=1);
Displays 'A'if there is an active approved FA application on file for the patient Displays 'Y' is there is a current FA application on file for the patient Displays 'N' if there is no current FA application on file for the patient 'Current' is defined as: the FA application/approval from-thru dates fall within the date of service of the appt/visit or today's date if only the patient registration is in context.
• relationship Audits as Registration.PatRespAudit(XMLPROJECTION="NONE") [ Inverse = PatResp,Cardinality = many ];
 
• property BudPlan as %String(CAPTION="Budget Plan",COLLATION="EXACT",TRUNCATE=1);
For HPA groups, displays Y/N indicating whether the patient's account has an active budget plan
• property CaseNum as CaseMgmt.Header(CAPTION="Case Number");
Case number this instance is linked to
• property Cin as %Numeric;
Enrollment contract linked to this instance
• property ColReason as Dict.ColVar(CAPTION="Collection Reason");
Reason why we did not collect the full amount from the patient
• property CurStmtBal as DBMS.Currency(CAPTION="CUR Stmt BAL");
Current self pay balance of all statement producing FSCs
• property CustomCollection as list of DBMS.NameValuePair [ Transient ];
 
• relationship Dets as Registration.PatRespDet [ Inverse = PatResp,Cardinality = many ];
 
• property EffDate as %Date(CAPTION="Effective Date");
The date this insurance became effective
• property EligLastReceived as %Date(CAPTION="Elig Last Received");
Date that an eligibility response was last received for this patient and insurance.
• property EligReqNum as %Numeric(CAPTION="Eligibility Request Number");
Eligibility request number made for this instance. This is used for the Pt Resp List action to view the EB segment for an elig response.
• property EligReviewedBy as %String(CAPTION="Elig Reviewed By",TRUNCATE=1);
Security plus user that last marked an eligibility response as reviewed
• property EligStat as Dict.EligStatus(CAPTION="Elig Status");
Status from the last eligibility check that occurred for this patient and insurance
• property Estimate as %String(COLLATION="EXACT",TRUNCATE=1);
Displays Y if there is a current estimate on file for the patient. Displays N if there is no PLE estimates in file for the patient.
• property ExpDate as %Date(CAPTION="Expiration Date");
The date this insurance expired
• property Fsc as Dict.FSC [ Required ];
FSC for Patient Responsibility data
• property Grp as Dict.GroupProfile(CAPTION="Group NUM");
BAR/HPA group in which the account summary came from for this instance
• property Id as %Integer [ Calculated,Transient,ReadOnly ];
Patient ID - a unique row identifier for the Patient Responsibility record
• property InsComp as Dict.CommInsComp(CAPTION="Insurance Company");
Insurance Company DE from Dict 120
• property InstanceDelFlag as %Numeric(CAPTION="Instance Deleted Flag");
Flag which signifies that instance was deleted. The way to delete the instance is to wipe out the fields in the instance. For those instances that were deleted, the selector will display "Deleted" next to the instance type.
• property InstanceType as %String(CAPTION="Instance Type",COLLATION="EXACT",TRUNCATE=1);
Type of instance: New, Verification, Payment, or Both
• property LastStmtBal as DBMS.Currency(CAPTION="Last Stmt BAL");
Balance of the last statement produced
• property LastStmtProducedDate as %Date(CAPTION="Last Stmt Produced Date");
Statement produced date
• relationship Notes as Registration.PatRespNote [ Inverse = PatResp,Cardinality = children ];
 
• property Patient as Registration.Patient [ Required ];
This field contains a reference to the patient object
• property RowInfo as DBMS.DataInfo(CAPTION="Rowinfo");
a reference to the rowinfo data
• property SchedApptNum as Sched.Appointment;
Sched appointment number
• property StmtAccountStat as Dict.AccountStatus(CAPTION="Stmt Account Status");
Account status at the time of stmt production
• property TotCollectedAmt as DBMS.Currency(CAPTION="TOT Collected Amount");
Total amount that could be collected from patient
• property TotPatResp as DBMS.Currency(CAPTION="Total PAT Resp");
Total amount patient could be responsible for
• property TotVerPatResp as DBMS.Currency(CAPTION="TOT VER PAT Resp");
Total verified amount that patient is responsible for
• property VisNum as %Numeric(CAPTION="Vis Num");
VM Visit Admission Number
Methods
 
• final classmethod %ExistsId(id As %String) as %Boolean
Checks to see if the object identified by the ID id exists in the extent.

Returns %Boolean TRUE is it exists, FALSE if it does not.

• classmethod %OnDetermineClass(oid As %ObjectIdentity, ByRef class As %String) as %Status
[Previously private]
• final classmethod CaseNumExists(Value, Id) as %Integer
Does the value exist in a CaseNum index
• final classmethod CinExists(Value, Id) as %Integer
Does the value exist in a Cin index
• final classmethod ColReasonExists(Value, Id) as %Integer
Does the value exist in a ColReason index
• classmethod DeactbyCaseVisAppt(Pid As %Integer = "", Fsc As %Integer = "", Case As %Integer = "", Appt As %Integer = "", Vis As %String = "") as %Status
Deactivate Patient Responsibility records (Header, Detail, and Notes) for a given Patient ID, Fsc, Case/Appt/Visit.
• classmethod DeactivatebyPatId(Patient As %Integer = "") as %Status
Deactivate all Patient Responsibility records (Header, Detail, and Notes) for a given Patient ID.
• classmethod DeactivatebyPatRespId(PatRespId As %Integer = "") as %Status
Deactivate a Patient Responsibility record for a given Patient Responsibility Id. After executing this code, we call trigger DeactReact to deactivate the Detail and Notes records.
• classmethod DeletebyCaseVisAppt(Pid As %Integer = "", Fsc As %Integer = "", Case As %Integer = "", Appt As %Integer = "", Vis As %String = "") as %Status
Delete Patient Responsibility records (Header, Detail, and Notes) for a given Patient ID, Fsc, Case/Appt/Visit.
• classmethod DeletebyPatId(Patient As %Integer = "") as %Status
Delete all Patient Responsibility records (header, detail, and Notes) for a given Patient ID.
• classmethod DeletebyPatRespId(PatRespId As %Integer = "") as %Status
Delete a Patient Responsibility record for a given Patient Responsibility Id. After executing this code, we call trigger Delete to deactivate the Detail and Notes records.
• method DelimiterCheck() as %String
 
• final classmethod EffDateExists(Value, Id) as %Integer
Does the value exist in a EffDate index
• final classmethod EligLastReceivedExists(Value, Id) as %Integer
Does the value exist in a EligLastReceived index
• final classmethod EligReqNumExists(Value, Id) as %Integer
Does the value exist in a EligReqNum index
• final classmethod EligReviewedByExists(Value, Id) as %Integer
Does the value exist in a EligReviewedBy index
• final classmethod EligStatExists(Value, Id) as %Integer
Does the value exist in a EligStat index
• final classmethod ExpDateExists(Value, Id) as %Integer
Does the value exist in a ExpDate index
• final classmethod FscExists(Value, Id) as %Integer
Does the value exist in a Fsc index
• final classmethod GetDisplayField(Id As %Integer) as %String
Return the value of the entry to be displayed when only supplied an Id
• classmethod GetInstances(Pid As %String = "", Fsc As %String = "", Case As %String = "", Appt As %String = "", Vis As %String = "") as %String
Return all valid Patient Responsibility records for a given ID/FSC/Case/SchedAppt/VisNum in a List format.
• classmethod GetPRCaseVisAppt(Case As %String = "", Appt As %String = "", Vis As %String = "") as %String
Return all valid Patient Responsibility records for a given Case/Visit/Appt in a List format. Do not search on Patient Id or Fsc.
• classmethod GetPRRecCombo(Pid As %String = "", Fsc As %String = "", Case As %String = "", Appt As %String = "", Vis As %String = "") as %String
Return all valid Patient Responsibility records for a given Patient ID/FSC/Case/SchedAppt/VisNum in a List format.
• classmethod GetPatRespRec(PatientId As %String) as %String
Return all valid Patient Responsibility records for a given Patient ID in a List format.
• classmethod GetVersion(Rowid As %String)
 
• final classmethod GrpExists(Value, Id) as %Integer
Does the value exist in a Grp index
• final classmethod InsCompExists(Value, Id) as %Integer
Does the value exist in a InsComp index
• final classmethod InstanceDelFlagExists(Value, Id) as %Integer
Does the value exist in a InstanceDelFlag index
• final classmethod LastStmtProducedDateExists(Value, Id) as %Integer
Does the value exist in a LastStmtProducedDate index
• method LoadCustom()
 
• method LocalToObject(RegPatRespD As %String)
 
• method LocalToObjectXml(RegPatRespD As %String)
 
• method ObjectToLocal(RegPatRespD As %String, ByRef RowInfo As %String, Error As %String)
 
• method ObjectToLocalXml(RegPatRespD As %String, ByRef RowInfo As %String, Error As %String)
 
• classmethod OpenIdLocal(Id, Concurrency=0, ByRef Array As %String, ByRef RowInfo As %String) as Registration.PatResp
 
• method OptimisticLockCheck(Id As %String, ErrMsg As %String)
 
• classmethod ReactbyCaseVisAppt(Pid As %Integer = "", Fsc As %Integer = "", Case As %Integer = "", Appt As %Integer = "", Vis As %String = "") as %Status
Reactivate Patient Responsibility records (Header, Detail, and Notes) for a given Patient ID, Fsc, Case/Appt/Visit.
• classmethod ReactivatebyPatId(Patient As %Integer = "") as %Status
Reactivate all Patient Responsibility records (Header, Detail, and Notes) for a given Patient ID.
• classmethod ReactivatebyPatRespId(PatRespId As %Integer = "") as %Status
Reactivate a Patient Responsibility record for a given Patient Responsibility Id. After executing this code, we call trigger DeactReact to reactivate the Detail and Notes records.
• classmethod SaveLocal(Array, oRef="")
 
• method SetSaveData()
 
• final classmethod StmtAccountStatExists(Value, Id) as %Integer
Does the value exist in a StmtAccountStat index
Queries
 
• query CombinedAuditTrail(Entry As %Integer)
Selects Lvl As %String, Identifier As %String, Dt As %Date, Tm As %Time, Ini As %String, Property As %String, OldVal As %String, NewVal As %String, Id As %String
SQL Query :
SELECT
'Header' As Lvl,
'' As Identifier,
P.PatRespAudit->Event_Dt As Dt,
P.PatRespAudit->Event_Tm as Tm,
P.PatRespAudit->Event_Ini as Ini,
DBMS.AuditTxn_Caption('Registration.PatResp',Data_Property) as Property,
DBMS.AuditTxn_ClassLogicalToDisplay('Registration.PatResp',Data_Property,
Data_OldVal) as OldVal,
DBMS.AuditTxn_ClassLogicalToDisplay('Registration.PatResp',Data_Property,
Data_NewVal) as NewVal,
P.%ID
FROM
Registration.PatRespAuditTxn P
WHERE
P.PatRespAudit->PatResp = :Entry
UNION
SELECT
'Detail' As Lvl,
C.PatRespDetAudit->PatRespDet->PatResponsibilityType->Name As Identifier,

C.PatRespDetAudit->Event_Dt As Dt,
C.PatRespDetAudit->Event_Tm as Tm,
C.PatRespDetAudit->Event_Ini as Ini,
DBMS.AuditTxn_Caption('Registration.PatRespDet',Data_Property) as
Property,
DBMS.AuditTxn_ClassLogicalToDisplay('Registration.PatRespDet',
Data_Property,Data_OldVal) as OldVal,
DBMS.AuditTxn_ClassLogicalToDisplay('Registration.PatRespDet',
Data_Property,Data_NewVal) as NewVal,
C.%ID
FROM
Registration.PatRespDetAuditTxn C
WHERE
C.PatRespDetAudit->PatRespDet->PatResp = :Entry
ORDER BY
Dt Desc,
Tm Desc,
Lvl Desc

 
	CALL Registration.PatResp_CombinedAuditTrail(Entry)
Indices
 
• index (ZAdtVisPtr on AdtVisPtr) [Type = index];
 
• index (ZCaseNum on CaseNum) [Type = bitmap];
 
• index (ZCin on Cin) [Type = bitmap];
 
• index (ZColReason on ColReason) [Type = bitmap];
 
• index (ZEffDate on EffDate) [Type = bitmap];
 
• index (ZEligLastReceived on EligLastReceived) [Type = bitmap];
 
• index (ZEligReqNum on EligReqNum) [Type = bitmap];
 
• index (ZEligReviewedBy on EligReviewedBy) [Type = bitmap];
 
• index (ZEligStat on EligStat) [Type = bitmap];
 
• index (ZExpDate on ExpDate) [Type = bitmap];
 
• index (ZFsc on Fsc) [Type = bitmap];
 
• index (ZGrp on Grp) [Type = bitmap];
 
• index (ZInsComp on InsComp) [Type = bitmap];
 
• index (ZInstanceDelFlag on InstanceDelFlag) [Type = bitmap];
 
• index (ZLastStmtProducedDate on LastStmtProducedDate) [Type = bitmap];
 
• index (ZPatient on Patient) [Type = index]; 
• index (ZSchedApptNum on SchedApptNum) [Type = bitmap];
• index (ZStmtAccountStat on StmtAccountStat) [Type = bitmap];
 
ForeignKeys
 
• foreignkey CaseNum (CaseNum) references CaseMgmt.Header (DBMSKeyIndex) OnDelete: noaction; OnUpdate: noaction
 
• foreignkey ColReason (ColReason) references Dict.ColVar () OnDelete: noaction; OnUpdate: noaction
 
• foreignkey EligStat (EligStat) references Dict.EligStatus () OnDelete: noaction; OnUpdate: noaction
 
• foreignkey Fsc (Fsc) references Dict.FSC () OnDelete: noaction; OnUpdate: noaction
 
• foreignkey Grp (Grp) references Dict.GroupProfile () OnDelete: noaction; OnUpdate: noaction
 
• foreignkey InsComp (InsComp) references Dict.CommInsComp () OnDelete: noaction; OnUpdate: noaction
 
• foreignkey Patient (Patient) references Registration.Patient (DBMSKeyIndex) OnDelete: noaction; OnUpdate: noaction
 
• foreignkey RowInfoCreUsername (RowInfo.CreUsername) references SecurityPlus.Usr (DBMSKeyIndex) OnDelete: noaction; OnUpdate: noaction
 
• foreignkey RowInfoDeactUsername (RowInfo.DeactUsername) references SecurityPlus.Usr (DBMSKeyIndex) OnDelete: noaction; OnUpdate: noaction
 
• foreignkey RowInfoDeleteBy (RowInfo.DeleteBy) references SecurityPlus.Usr (DBMSKeyIndex) OnDelete: noaction; OnUpdate: noaction
 
• foreignkey RowInfoOrg (RowInfo.Org) references Dict.Organization () OnDelete: noaction; OnUpdate: noaction
 
• foreignkey RowInfoUpdUsername (RowInfo.UpdUsername) references SecurityPlus.Usr (DBMSKeyIndex) OnDelete: noaction; OnUpdate: noaction
 
• foreignkey SchedApptNum (SchedApptNum) references Sched.Appointment (DBMSKeyIndex) OnDelete: noaction; OnUpdate: noaction
 
• foreignkey StmtAccountStat (StmtAccountStat) references Dict.AccountStatus () OnDelete: noaction; OnUpdate: noaction
 
Triggers
 
• trigger AllowInsert (BEFORE event INSERT)
 
• trigger AllowUpdate (BEFORE event UPDATE)
 
• trigger Audit (AFTER event INSERT/UPDATE)
 
• trigger DeactCheck (BEFORE event INSERT/UPDATE)
 
• trigger DeactReact (AFTER event INSERT/UPDATE)
 
• trigger Delete (BEFORE event DELETE)
 
• trigger DeleteAudit (BEFORE event DELETE)
 

 
Copyright © 1997-2020, InterSystems Corporation

Your index is a bitmap index, have you tried changing it to a regular index just for testing if the behavior changes? There are restrictions on when to use a bitmap index. Wondering if the engine is considering the bitmap to be slower than full table scan in this situation.

I came to the root of the issue - the system wasn't properly indexing this table.  The rebuild-index command (we have the core cache methods wrapped in an application) were crashing and not properly creating the index.

Thanks to everyone for your help/advice!