RE: 10g ASM on RHEL 3 AS
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <FREEMANR@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 7 Oct 2004 20:00:32 +0200
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
Other related posts:
