Index on This

[scohan]

I'm looking into where we can add indexes. I have 2 or 3 queries similar to the one below. What would be an appropriate index for it, if any? Thanks.


select id from os_currentstep where (entry_id = " + wfId +
" and id in (select wf_step_id from workflow_activity_attribute where name = 'step_name'" +
" and value = '" + activityCode + "')" +
" and id in (select wf_step_id from workflow_activity_attribute where name = 'business_id'" +
" and value = '" + businessIdStr + "'))";

 

[Turkbear]

Hi,
It will actually depend, but as a start:
Index entry_id and id and value in os_current_step
and
name in wf_step_id

You may want to look into compound indexes as well, but be aware of their limitations ...

 

[scohan]

Thanks. I was thinking of putting one on name and value in workflow_activity_attribute. They are both varchr2(50).

 

[Turkbear]

Hi,
Yep, I missed the table name in the subqueries..
so,

Table
os_currentstep
Indexes
entry_id
id


Table
workflow_activity_attribute
Indexes
name
value
(and maybe wf_step_id so only the index is needed to get the value)

 

[scohan]

Good idea.

 

[scohan]

Now you have me thinking as to when it makes sense to add an index and what to put in it. If I have the following table:

CREATE TABLE workitem_party
(wi_prty_id NUMBER (15) NOT NULL,
party_role NUMBER (2) NOT NULL,
parent_id NUMBER (15) NOT NULL,
pt_prty_id NUMBER (15) NOT NULL,
CONSTRAINT wi_prty_id PRIMARY KEY (wi_prty_id)
USING INDEX TABLESPACE indx
);

and I create the following index:

CREATE INDEX wi_party_idx1 ON workitem_party (party_role, parent_id, pt_prty_id) TABLESPACE indx;

for queries like the following:

select pt_prty_id from workitem_party where parent_id = 11 and role = 1;

does it make sense to have the index since an index scan will take place to get the pt_prty_id vs. a table scan w/out the index?

Does the index scan perform better or are we just creating more storage requirements and processing (for the index) with out and increased speed in pt_prty_id retrieval?

What about if the query is 'select * from workitem_party where parent_id = 11 and role = 1', would the table row need to be accessed to get wi_prty_id since it's not in the index (it is the pk though)?

Thanks for the education.

 

[scohan]

To clarify, the second to last question in the previous message should have been:

Does the index scan perform better or are we just creating more storage requirements and processing (for the index) without increased speed in pt_prty_id retrieval?