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: Single Join Query vs Individual Table Queries
Next Topic: data guard query
Goto Forum:

Current Time: Tue Jun 27 21:11:32 CDT 2017

Total time taken to generate the page: 0.13473 seconds