index selection problem (10053 event)

From: Milen Kulev <makulev_at_gmx.net>
Date: Thu, 18 May 2006 12:57:51 +0200 (MEST)
Message-ID: <14633.1147949871@www057.gmx.net>

Hello listers,

I have the following situation. A problematic SQL statement (see the end of the posting) is not using the right index The index details (an excerpt from 10053 event trace file ) are below

Existing indexes (note the order in which the indexes are presented)

.....
Access path: index (equal)

      Index: TD_SST_HO_ADDR_SADLR_IDX
  TABLE: TABLE_X_SST_HO_ADDR
      RSC_CPU: 0 RSC_IO: 4
  IX_SEL: 0.0000e+00 TB_SEL: 3.5365e-08   Skip scan: ss-sel 0 andv 2380
    ss cost 2380
    index io scan cost 0
.....
  Access path: index (scan)

      Index: X_SST_HO_ADDR_IN_DT_TDNUM_IDX   TABLE: TABLE_X_SST_HO_ADDR
      RSC_CPU: 0 RSC_IO: 4
  IX_SEL: 6.9354e-09 TB_SEL: 6.9354e-09 ....
BEST_CST: 4.00 PATH: 4 Degree: 1

The cost of TD_SST_HO_ADDR_SADLR_IDX and X_SST_HO_ADDR_IN_DT_TDNUM_IDX is the same (Cost = 4).
CBO prefers usage of TD_SST_HO_ADDR_SADLR_IDX index and this is not what I want (the other index is really better, I have tried it!)

The execution plan is:

| Id  | Operation                    |  Name                     | Rows  |

Bytes | Cost
|

|   0 | SELECT STATEMENT             |                           |     1 |  
261 |    51 |
|   1 |  SORT ORDER BY               |                           |     1 |  
261 |    51 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TABLE_X_SST_HO_ADDR       |     1 |  
261 |     4 |
|*  3 |    INDEX RANGE SCAN          | TD_SST_HO_ADDR_SADLR_IDX  |     1 |  
    |     3

Predicate Information (identified by operation id):

2 - filter("TABLE_X_SST_HO_ADDR"."X_IN_DT"=TO_DATE(:Z,'MM/DD/YYYY HH24:MI:SS') AND

              "TABLE_X_SST_HO_ADDR"."X_ERR_NBR"=:Z AND
("TABLE_X_SST_HO_ADDR"."X_ACT_TYP"=:Z OR
              "TABLE_X_SST_HO_ADDR"."X_ACT_TYP"=:Z) AND
"TABLE_X_SST_HO_ADDR"."X_TDNUMBER"<TO_NUMBER(:Z)
              )

3 - access("TABLE_X_SST_HO_ADDR"."X_DLR_ID"=:Z AND "TABLE_X_SST_HO_ADDR"."X_SA_ID"=:Z) Workaround:

I have dropped the index X_SST_HO_ADDR_IN_DT_TDNUM_IDX and just re-created it with another name (IDX_SST_HO_ADDR_IN_DT_TDNUM) so that the new name is alphabetically "lower" than than the name of the "wrong" index(TD_SST_HO_ADDR_SADLR_IDX):

create unique index sa.IDX_SST_HO_ADDR_IN_DT_TDNUM on SA.TABLE_X_SST_HO_ADDR (X_IN_DT, X_TDNUMBER) compute statistics

Now the execution plan looks like(And this is what I want ):

| Id  | Operation                    |  Name                        | Rows

| Bytes | Cost
|

|   0 | SELECT STATEMENT             |                              |     1
|   261 |     4 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | TABLE_X_SST_HO_ADDR          |     1
|   261 |     4 |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_SST_HO_ADDR_IN_DT_TDNUM  |     1
|       |     3

Predicate Information (identified by operation id):

1 - filter("TABLE_X_SST_HO_ADDR"."X_DLR_ID"=:Z AND "TABLE_X_SST_HO_ADDR"."X_SA_ID"=:Z AND

              "TABLE_X_SST_HO_ADDR"."X_ERR_NBR"=:Z AND
("TABLE_X_SST_HO_ADDR"."X_ACT_TYP"=:Z OR
              "TABLE_X_SST_HO_ADDR"."X_ACT_TYP"=:Z))
   2 - access("TABLE_X_SST_HO_ADDR"."X_IN_DT"=TO_DATE(:Z,'MM/DD/YYYY
HH24:MI:SS') AND
              "TABLE_X_SST_HO_ADDR"."X_TDNUMBER"<TO_NUMBER(:Z))

Excerpt from the new 10053 event file (note the order in which the indexes are presented):

...
Access path: index (scan)

      Index: IDX_SST_HO_ADDR_IN_DT_TDNUM   TABLE: TABLE_X_SST_HO_ADDR
      RSC_CPU: 0 RSC_IO: 4
  IX_SEL: 6.9354e-09 TB_SEL: 6.9354e-09   Skip scan: ss-sel 0 andv 368
    ss cost 1472
    index io scan cost 0
....
  Access path: index (equal)

      Index: TD_SST_HO_ADDR_SADLR_IDX
  TABLE: TABLE_X_SST_HO_ADDR
      RSC_CPU: 0 RSC_IO: 4
  IX_SEL: 0.0000e+00 TB_SEL: 3.5365e-08 .....
BEST_CST: 4.00 PATH: 4 Degree: 1

The question :

Why CBO is using the first index (TD_SST_HO_ADDR_SADLR_IDX) and not the (better) second one (X_SST_HO_ADDR_IN_DT_TDNUM_IDX)? Suggestions:

Because by equal cost of many indexes the CBO is using the first one in alphabetital order
Because CBO thinks that "index (equal)" access is always better than "index (scan)" ? (some kind of internal CBO rule)

It ***seems*** to me that the right answer is 1)

Any opinions are highly appreciated.
Many thanks in advance.

Regards. Milen

P.S. Note that I don't want to use hints and gathering system statistics is not an option (another looong story ;( )

The SQL statement:

select
objid, dev, x_dva_id,

TO_CHAR(x_in_dt, 'MM/DD/YYYY HH24:MI:SS'), 
TO_CHAR(x_chg_dt, 'MM/DD/YYYY HH24:MI:SS'), 
x_exclude_flag, x_act_typ, x_salutation,

x_f_nam, x_l_nam, x_firm1, x_firm2,
x_title, x_prefix, x_suffix,
TO_CHAR(x_birth_dt, 'MM/DD/YYYY HH24:MI:SS'),

x_prof, x_dva_branch, x_bus_typ, x_grp_mkt_nbr, 
x_country, x_pbox, x_addr, x_house_nbr, x_zip, x_city, 
x_phon, x_bus_phon, x_fax, x_dlr_id, x_sls_nbr, 
x_ho_stat, x_vup_id, TO_CHAR(x_first_ctc_dt, 'MM/DD/YYYY HH24:MI:SS'),

TO_CHAR(x_cust_dt, 'MM/DD/YYYY HH24:MI:SS'), TO_CHAR(x_check_dt, 'MM/DD/YYYY HH24:MI:SS'), x_err_nbr, x_err_txt, x_use_con_nbr, x_ibp_seq, x_sa_id, x_correct, x_src, x_fmly_stat, x_sex_code, x_addr_typ,

TO_CHAR(x_start_dt, 'MM/DD/YYYY HH24:MI:SS'), x_con_nbr, 
x_ho_info_flag, x_force_act, x_nopost, x_fullupd, 
TO_CHAR(x_out_dt, 'MM/DD/YYYY HH24:MI:SS'), x_src2, x_tdnumber, 
x_bad_quality, x_mob_phon, x_bus_mob_phon, x_email,

x_bus_email, x_addr_class, x_sa_cust_typ, x_sales_rep, x_sa_src, x_bus_fax
FROM
SA.table_x_sst_ho_addr
WHERE
( x_dlr_id = :B0 AND x_sa_id = :B1

AND  x_in_dt =  TO_DATE( :B2, 'MM/DD/YYYY HH24:MI:SS')   
AND  x_err_nbr = :B3 AND  (x_act_typ IN ( :B4,:B5))  
AND  x_tdnumber < :B6 )

order by x_tdnumber desc ;

-- 
GMX Produkte empfehlen und ganz einfach Geld verdienen!
Satte Provisionen für GMX Partner: http://www.gmx.net/de/go/partner
--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 18 2006 - 05:57:51 CDT