RE: 10g ASM on RHEL 3 AS

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Thu, 7 Oct 2004 20:00:32 +0200
Message-ID: <JFEEIGBIDOCCDALDIPLNMEMHCJAA.lex.de.haan@naturaljoin.nl>

just out of curiosity,
why don't you rewrite the WHERE clause into:

... AND ACTN_LCL_TMSTMP = TO_DATE('2004-10-01','yyyy-mm-dd') in that case, you don't need a function-based index ...

Kind regards,
Lex.

visit http://www.naturaljoin.nl <http://www.naturaljoin.nl>

skype me <callto://lexdehaan>

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Freeman Robert - IL Sent: Thursday, October 07, 2004 18:22
To: 'oracle-l_at_freelists.org '
Subject: RE: 10g ASM on RHEL 3 AS

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



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

Received on Thu Oct 07 2004 - 12:56:08 CDT