RE: 10g ASM on RHEL 3 AS
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: "Stephane Faroult" <sfaroult@xxxxxxxxxxxx>
- Date: Thu, 7 Oct 2004 21:49:41 +0200
yes, you are right -- I was too fast,
only thinking about the function-based index issue :-)
undskyld, undskyld ...
as a bonus, this is the ANSI/ISO way of stating the same:
... AND ACTN_LCL_TMSTMP >= DATE '2004-10-01'
AND ACTN_LCL_TMSTMP < DATE '2004-10-02'
Lex.
-------------------------------
visit http://www.naturaljoin.nl
-------------------------------
skype me <callto://lexdehaan>
-----Original Message-----
From: Stephane Faroult [mailto:sfaroult@xxxxxxxxxxxx]
Sent: Thursday, October 07, 2004 20:21
To: lex.de.haan@xxxxxxxxxxxxxx
Cc: FREEMANR@xxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: 10g ASM on RHEL 3 AS
Corrrection :
AND ACTN_LCL_TMSTPT >= TO_DATE('2004-10-01', 'YYYY-MM-DD')
AND ACTN_LCL_TMSTP < TO_DATE('2004-10-02', 'YYYY-MM-DD')
Lex de Haan wrote:
>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@xxxxxxxxxxxxx
>[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Freeman Robert - IL
>Sent: Thursday, October 07, 2004 18:22
>To: 'oracle-l@xxxxxxxxxxxxx '
>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
>
>
>--
>//www.freelists.org/webpage/oracle-l
>
>
>
>--
>//www.freelists.org/webpage/oracle-l
>
>
>
--
Regards,
Stephane Faroult
RoughSea Ltd
http://www.roughsea.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
