how to tell Oracle the expected return rows of a join

Hi,

we have two tables, SECURITY table stores relationship between person and organization. The relationship is represented as a number. CONTACT table stores contact's information, each row has a column ORG store which organization is the owner of this contact and another column PRIVILEGE which specify other organization's privilege to view this record, there is a match between relationship column in security table and privilege column in contact table.

For example, a person with id=1111 is the application user, if security.person_id=1111 and secrutity.org=contact.org and relationship<=contact.privilege, then the person which id=1111 has the right to read this record. Also query criteria may includes something else like first_name of contact, the query could be:

select contact.* from contact,security
security.person_id=1111 and
security.org=contact.org and
secrity.relationship<=contact.privilege and contact.last_name like 'S%' order by constact.last_name

this query will return all the contacts person 1111 has right to see and
with last name like 'S%'.

we have index on contact(last_name). also have index on constact(org,privilege),security(person_id,relationship). Both table are big table. The problem is sometimes I want Oracle to use index on contact(last_name) at first because I can use use /*+ first_rows */ to full scan the index and avoid the sort. By the way, the application is a GUI, so response time is more important than throughput. But sometimes when the current user only has right to see very few rows in contact and the predicate "contact.last_name like 'S%'" will return millions of rows, We want to Oracle to apply the security rule first and don't even use index on constact(last_name), otherwise it'll never return. The hard part is Oracle knows the selectivity of "last name like ..." by histogram or data density, he can guess how many rows will be filtered by this condition, whilst he has no idea how many rows will be get rid of by security rule, without this information, Oracle can't decide which way to go. And I can build statistic on how many rows will return by securtity rule for each person, my question is how I can tell oracle the estimated return rows. I can make up statistics using dbms_stats.set_stats to fool Oracle, the problem is the users in top hierarchy and in bottom need different fake stats to make Oracle choose right plan. And also, I can write my own optimizer which will add different hint for queries of differrent users based on the knowledge of how many rows this user can see. But, I regard this as last resort.

Is there any way to tell Oracle the expected return rows of a join?

Thanks,

Ling Received on Wed Feb 26 2003 - 18:26:17 CET