RE: Oracle's use of Indexes

> From: DENNIS WILLIAMS
> I believe that all the statistics that CBO uses to make a
> decision are in
> USER_TABLES and USER_INDEXES. You might compare the values
> for both tables
> to see if there is a difference that might cause the CBO to
> make a different
> decision.

I'll look into that, thanks.

>Are you using different usernames? Any chance one
> session is doing
> an ALTER SESSION?

Yes, different usernames, but neither are doing an ALTER SESSION.

Wolfgang, yes, it's doing a FTS instead of using the index. I'll look at doing a trace on it tomorrow.

Daniel:
Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7060 Card=338 Bytes=52728)

   1 0 SORT (ORDER BY) (Cost=7060 Card=338 Bytes=52728)    2 1 TABLE ACCESS (FULL) OF 'TBL_CUST_MAST' (Cost=7050 Card=338 Bytes=52728)

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12711 Card=338 Bytes=52728)

   1 0 SORT (ORDER BY) (Cost=12711 Card=338 Bytes=52728)    2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_CUST_MAST' (Cost=12701 Card=338 Bytes=52728)

   3 2 INDEX (RANGE SCAN) OF 'IDX_CUST_MAST_EMAIL1' (NON-UNIQUE) (Cost=887 Card=338)

Top plan takes about 20 seconds, the lower one less than 1 second.

• DBA_TABLES.NUM_ROWS = 3,161,764
• DBA_TABLES.BLOCKS = 73,294
• DBA_INDEXES.CLUSTERING_FACTOR = 118,131
• DBA_INDEXES.AVG_LEAF_BLOCKS_PER_KEY = 1
• DBA_INDEXES.AVG_DATA_BLOCKS_PER_KEY = 6
• DBA_INDEXES.DISTINCT_KEYS = 18,767
• DBA_INDEXEX.BLEVEL = 2
• DBA_INDEXES.LEAF_BLOCKS = 8850 index is on CLI_CD, CUST_EMAIL1, CUST_STATUS, densities are CLI_CD = 0.1 CUST_EMAIL1 = 0.00006 CUST_STATUS = 0.5 db_file_multiblock_read_count = 16

Craig Healey

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company.   

If you have received this email in error please notify system.administrator_at_hhsuk.com  

This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Craig Healey
  INET: c.healey_at_hhsuk.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).