RE: 10g ASM on RHEL 3 AS

From: Freeman Robert - IL <FREEMANR_at_tusc.com>
Date: Thu, 7 Oct 2004 12:21:55 -0500
Message-ID: <F5E885BEF9540D47A7BDC03CF16880870BC177FF@tuscil_ex1>

Is it me, or has it been really quiet here in the last couple of days? Question:

Given this index:

SQL> create unique index ix_test_psgr_hist on psgr_hist (to_char(actn_lcl_tmstmp, 'yyyy-mm-dd'), psgr_id, psgr_hist_sqnr, actn_stn_cd, agnt_id, psgr_actn_cd)
online compute statistics nologging;

And this query:
SELECT /*+ INDEX(psgr_hist, ix_test_psgr_hist) */ COUNT(DISTINCT psgr_hist.psgr_id)
FROM Psgr_Hist, Psgr_Flt_Leg_Hist
WHERE Psgr_Hist.Psgr_Id=Psgr_Flt_Leg_Hist.Psgr_Id AND Psgr_Hist.Psgr_Hist_Sqnr=Psgr_Flt_Leg_Hist.Psgr_Hist_Sqnr AND TO_CHAR(ACTN_LCL_TMSTMP, 'yyyy-mm-dd') = '2004-10-01' AND ACTN_STN_CD IN ('CHI','ORD')
AND ORIG_ARPT_CD = 'ORD'
AND dep_rte_type_cd = 'ORIG'

AND PSGR_ACTN_CD IN ('PSCK','PSSC')
AND PSGR_STAT_CD = 'CHK'
AND AGNT_ID='SLFSERV'

Anyone have an idea why I can't get Oracle to do a range scan on the index instead of a full scan? Here is the execution plan:

| Id  | Operation                      |  Name
--------------------------------------------------------------------------|
0 | SELECT STATEMENT               | 
|   1 |  SORT GROUP BY                 |
|*  2 |   TABLE ACCESS BY INDEX ROWID  | PSGR_FLT_LEG_HIST      
|   3 |    NESTED LOOPS                |                        
|*  4 |     TABLE ACCESS BY INDEX ROWID| PSGR_HIST              
|*  5 |      INDEX FULL SCAN           | IX_TEST_PSGR_HIST      
|*  6 |     INDEX RANGE SCAN           | XIF1PSGR_FLT_LEG_HIST  
--------------------------------------------------------------------------

Looking at the autotrace output, it appears that the function still isnt' being processed as I'd expect:

2 - filter("PSGR_FLT_LEG_HIST"."ORIG_ARPT_CD"='ORD' AND
"PSGR_FLT_LEG_HIST"."DEP_RTE_TYPE_CD"='ORIG' AND
"PSGR_FLT_LEG_HIST"."PSGR_STAT_CD"='CHK')

   4 - filter(TO_CHAR("PSGR_HIST"."ACTN_LCL_TMSTMP",:B1)='2004-10-01')
   5 - access("PSGR_HIST"."AGNT_ID"='SLFSERV')
       filter(("PSGR_HIST"."ACTN_STN_CD"='CHI' OR


"PSGR_HIST"."ACTN_STN_CD"='ORD') AND

               ("PSGR_HIST"."PSGR_ACTN_CD"='PSCK' OR


"PSGR_HIST"."PSGR_ACTN_CD"='PSSC'

              ) AND "PSGR_HIST"."AGNT_ID"='SLFSERV')

6 - access("PSGR_HIST"."PSGR_ID"="PSGR_FLT_LEG_HIST"."PSGR_ID" AND
"PSGR_HIST"."PSGR_HIST_SQNR"="PSGR_FLT_LEG_HIST"."PSGR_HIST_SQNR"
I disabled cursor sharing for the session, but that didn't help.

Ideas?

Robert

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 07 2004 - 12:17:27 CDT