On Thu, 1 Apr 1999, Tracy Felty wrote:

> Mr. Kaplan,
>    Your web page has a wealth of knowledge and has been very helpful.  I
> have a question concerning oracle indexes.  I am using oracal 7.3.  I have a
> table with a unique primary key that consists of 4 columns.  I have been
> analyzing my DB and have set the autotrace on so that I can see how selects
> are being processed.  The optimizer is set to 'CHOOSE' and I have analyzed 
> my entire schema, so all tables and Indexes have been analyzed.  When I    
> perform a select with a where condition using all 4 keys Oracle is doing a
> full table access instead of an index search and a table access by rowid. 
> It appears that most of my indexes on other tables are working as expected.
> Here is my Index, select statement and execution plan:
> 
>
> SQLWKS> select * from user_ind_columns where index_name = 'SYS_C004851'
>
> INDEX_NAME    TABLE_NAME          COLUMN_NAME   COLUMN_POS COLUMN_LEN
> ----------    ---------------     ------------  ---------- ----------
> SYS_C004851   APPRAISAL_EST_TAX   TAX_YEAR          1         22
> SYS_C004851   APPRAISAL_EST_TAX   ACCOUNT_NBR       2         22
> SYS_C004851   APPRAISAL_EST_TAX   DISTRICT_NBR      3          4
> SYS_C004851   APPRAISAL_EST_TAX   FUND_CD           4          4
> 4 rows selected.
> 
> 
> SQL> select * from appraisal_est_tax where tax_year = 1998 and account_nbr =
> 84 and  district_nbr =
>  'C001' and fund_cd = 'GEN';
> 
> Execution Plan
> ----------------------------------------------------------
>    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=31)   
>    1    0   TABLE ACCESS (FULL) OF 'APPRAISAL_EST_TAX' (Cost=1 Card=1
>           Bytes=31)
> 
> ALSO, on the execution plan what is 'COST' and 'CARD' ??  Thank you for
> whatever help and advice you can give.  I really appreciate your time in
> this matter.
> 
> Tracy
> TFelty@cpssys.com
> 
Based on your information, Oracle WILL use the primary key, with two
exceptions:
1) If there are only a few (usually < a few thousand) records, Oracle will
   do a full-table scan as opposed to using an index to retrieve a small
   number of records
2) If the cardinality ('CARD' per your question) is low, meaning that
   there are few distinct values of the index key versus the total number
   of records.

Since you are using a primary key, #2 would not be relevant, as a unique
index has the highest possible cardinality.

So, I am assuming that there are not many records in your table. If there
are, you will have to use a hint in your SQL and/or call Oracle support.

Best regards,
  
-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->   
<-> For 275+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->   

Back to Ari Kaplan's Home Page t="18"> Back to Ari Kaplan's Home Page