Home » RDBMS Server » Performance Tuning » Index based on subtype
Index based on subtype [message #218206] Wed, 07 February 2007 05:36
Messages: 20
Registered: November 2006
Junior Member
Hello -

I have a table (person) that contains rows of person subtypes (employee, student, instructor, professor ...).
I have queries that use only one subtype. For example:

SELECT s.id, s.name, s.state,
        TREAT(Value(s) AS InstructorType).worksin.name deptname
From PersonTab s
WHERE VALUE(s) IS OF (InstructorType) AND
        TREAT(REF(s) AS REF InstructorType).worksin.dno = 7;

In this query, the system performs a full table scan on the person table, which takes a very long time. Is there a way to build an index on the person table based on type, so that the system would not have to perform a full table scan for a query like this?
Any ideas other than an index that might enhance the performance would also be appreciated.
Previous Topic: query fast in one session slow in another
Next Topic: how to tune query
Goto Forum:

Current Time: Sun Aug 20 14:22:43 CDT 2017

Total time taken to generate the page: 0.06744 seconds