Index Performance Help Needed

We're using ORACLE v. 6.0.34, SQL*Plus v. 3.0.11, PL/SQL v. 1.0.34, and Forms v. 3.0.16 on a HP/UX 9000 Series 800.

I'm trying to tune our performance, so to speak! The problem is performance time with relation to indexes:

I'm using 3 tables:

Table		Index		Uniqueness
-----		-----		----------
Person		pers_nr		unique
		lastname	non-unique

Instit(ute)	inst_nr		unique

Userid		user_id		unique
		user_pnr	non-unique (same as pers_nr in table Person)
		inst_nr		non-unique

Then I have a view, combining these 3 tables (not all columns) for use in my (query-only) form:

View		Columns used in Where Clause
----		----------------------------
Person_view	userid.user_pnr
		person.pers_nr
		userid.inst_nr

If I do a query on Person_view.user_id (one of the columns in Person_view), I get a very quick response time (Userid is indexed on user_id). But if I do a query on Person_view.lastname (also index in it's original table-Person), then it takes much longer.

Is this because Person(lastname)-Index is non-unique or are my indexes inefficient. Maybe I should list the columns in my where-clause in a different order? (It's now:

			Userid.user_pnr = Person.pers_nr(+) and
			Userid.inst_nr = Instit.inst_nr          )

My Person table currently contains around 4500 records, Instit contains around 500 and Userid around 5200.

Anyone have a suggestion? I'd really appreciate it!!

Thanks in advance.

Karen Schillinger
rz34_at_hp850.rz.uni-karlsruhe.de Received on Thu Dec 03 1992 - 15:36:17 CET