Tools for Oracle database - TO_DATE function causes table scan

Author

TO_DATE function causes table scan

dhart@t2systems.com

2006-03-30, 8:23 pm

Hi,
I have a query that has to retrieve all the rows that occur after a
specific date/time. This is an ad hoc query where the date/time is
generated on the fly. It is '03-19-2006 06:26:45' in the example query
below. The problem is since I have to use the TO_DATE function to
filter by a time the index on the ALM_DATE column doesn't get used and
a table scan is used instead.

I have already tried using Function Based Indexes and query hints but I
guess my approach is flawed. Any help would be greatly appreciated.

Thanks,
Dave

SELECT
ALM.ALM_UID AS ZAJ_UID,
ALM.ALM_DATE AS ZAJ_MODIFY_DATE,
ITL.ITL_DESCRIPTION,
ITC.ICL_UID_LEVEL,
FAC.FAC_DESCRIPTION,
FGL.FGL_DESCRIPTION,
LAN.LAN_DESCRIPTION,
AAK.AAK_DATE AS ZAJ_RESPONSE_DATE_TI
ME,
USR.USR_NAME AS ZAJ_RESPONDER_NAME,
(SELECT MAX(ACS_UID_STATUS) FROM ALARM_ACKNOWLEDGEMEN
T WHERE
ALM_UID_ALARM = ALM_UID) AS ACS_UID_STATUS,
LCT.LCT_LANE_NUMBER,
USR.USR_UID AS USR_UID_RESPONDENT
FROM ALARM ALM
INNER JOIN INCIDENT INC
ON ALM.INC_UID_INCIDENT = INC.INC_UID
INNER JOIN INCIDENT_TYPE_MLKP ITL
ON INC. ITL_UID_INCIDENT_TYP
E = ITL.ITL_UID
INNER JOIN INCIDENT_TYPE_CATEGO
RY_LKP ITC
ON ITL.ITC_UID_CATEGORY = ITC.ITC_UID
LEFT OUTER JOIN FACILITY FAC
ON INC.FAC_UID_FACILITY = FAC.FAC_UID
LEFT OUTER JOIN FACILITY_GROUP_LKP FGL
ON INC. FGL_UID_FACILITY_GRO
UP = FGL.FGL_UID
LEFT OUTER JOIN LANE LAN
ON INC.LAN_UID_LANE = LAN.LAN_UID
LEFT OUTER JOIN LANE_CONTROLLER LCT
ON LAN. LCT_UID_LANE_CONTROL
LER = LCT.LCT_UID
LEFT OUTER JOIN ALARM_ACKNOWLEDGEMEN
T AAK
ON ALM.ALM_UID = AAK.ALM_UID_ALARM AND AAK.AAK_HAS_RESPONDED = 1
LEFT OUTER JOIN USER_ACCOUNT USR
ON AAK.USR_UID_RESPONDENT = USR.USR_UID
where ALM_DATE >= TO_DATE('03-19-2006 06:26:45', 'MM/DD/YYYY
HH24:MI:SS')

Mark C. Stock

2006-03-30, 8:23 pm

<dhart@t2systems.com> wrote in message
news:1143747215.798872.213180@i39g2000cwa.googlegroups.com...
: Hi,
: I have a query that has to retrieve all the rows that occur after a
: specific date/time. This is an ad hoc query where the date/time is
: generated on the fly. It is '03-19-2006 06:26:45' in the example query
: below. The problem is since I have to use the TO_DATE function to
: filter by a time the index on the ALM_DATE column doesn't get used and
: a table scan is used instead.
:
: I have already tried using Function Based Indexes and query hints but I
: guess my approach is flawed. Any help would be greatly appreciated.
:
: Thanks,
: Dave
:
: SELECT
: ALM.ALM_UID AS ZAJ_UID,
: ALM.ALM_DATE AS ZAJ_MODIFY_DATE,
: ITL.ITL_DESCRIPTION,
: ITC.ICL_UID_LEVEL,
: FAC.FAC_DESCRIPTION,
: FGL.FGL_DESCRIPTION,
: LAN.LAN_DESCRIPTION,
: AAK.AAK_DATE AS ZAJ_RESPONSE_DATE_TI
ME,
: USR.USR_NAME AS ZAJ_RESPONDER_NAME,
: (SELECT MAX(ACS_UID_STATUS) FROM ALARM_ACKNOWLEDGEMEN
T WHERE
: ALM_UID_ALARM = ALM_UID) AS ACS_UID_STATUS,
: LCT.LCT_LANE_NUMBER,
: USR.USR_UID AS USR_UID_RESPONDENT
: FROM ALARM ALM
: INNER JOIN INCIDENT INC
: ON ALM.INC_UID_INCIDENT = INC.INC_UID
: INNER JOIN INCIDENT_TYPE_MLKP ITL
: ON INC. ITL_UID_INCIDENT_TYP
E = ITL.ITL_UID
: INNER JOIN INCIDENT_TYPE_CATEGO
RY_LKP ITC
: ON ITL.ITC_UID_CATEGORY = ITC.ITC_UID
: LEFT OUTER JOIN FACILITY FAC
: ON INC.FAC_UID_FACILITY = FAC.FAC_UID
: LEFT OUTER JOIN FACILITY_GROUP_LKP FGL
: ON INC. FGL_UID_FACILITY_GRO
UP = FGL.FGL_UID
: LEFT OUTER JOIN LANE LAN
: ON INC.LAN_UID_LANE = LAN.LAN_UID
: LEFT OUTER JOIN LANE_CONTROLLER LCT
: ON LAN. LCT_UID_LANE_CONTROL
LER = LCT.LCT_UID
: LEFT OUTER JOIN ALARM_ACKNOWLEDGEMEN
T AAK
: ON ALM.ALM_UID = AAK.ALM_UID_ALARM AND AAK.AAK_HAS_RESPONDED = 1
: LEFT OUTER JOIN USER_ACCOUNT USR
: ON AAK.USR_UID_RESPONDENT = USR.USR_UID
: where ALM_DATE >= TO_DATE('03-19-2006 06:26:45', 'MM/DD/YYYY
: HH24:MI:SS')
:

1) post the entire explain plan
2) post the version
3) how are you maintining statistics?
4) (maybe actually #1) to test your theory that the TO_DATE is the issue
(likely not) try running the query which pl/sql using a DATE datatype
variable instead of the TO_DATE expression -- if you get a different plan,
then TO_DATE is the issue, if you get the same plan, then it's some other
factor affecting the optimizer's decision

++ mcs

DA Morgan

2006-03-30, 8:23 pm

dhart@t2systems.com wrote:
> Hi,
> I have a query that has to retrieve all the rows that occur after a
> specific date/time. This is an ad hoc query where the date/time is
> generated on the fly. It is '03-19-2006 06:26:45' in the example query
> below. The problem is since I have to use the TO_DATE function to
> filter by a time the index on the ALM_DATE column doesn't get used and
> a table scan is used instead.
>
> I have already tried using Function Based Indexes and query hints but I
> guess my approach is flawed. Any help would be greatly appreciated.
>
> Thanks,
> Dave
>
> SELECT
> ALM.ALM_UID AS ZAJ_UID,
> ALM.ALM_DATE AS ZAJ_MODIFY_DATE,
> ITL.ITL_DESCRIPTION,
> ITC.ICL_UID_LEVEL,
> FAC.FAC_DESCRIPTION,
> FGL.FGL_DESCRIPTION,
> LAN.LAN_DESCRIPTION,
> AAK.AAK_DATE AS ZAJ_RESPONSE_DATE_TI
ME,
> USR.USR_NAME AS ZAJ_RESPONDER_NAME,
> (SELECT MAX(ACS_UID_STATUS) FROM ALARM_ACKNOWLEDGEMEN
T WHERE
> ALM_UID_ALARM = ALM_UID) AS ACS_UID_STATUS,
> LCT.LCT_LANE_NUMBER,
> USR.USR_UID AS USR_UID_RESPONDENT
> FROM ALARM ALM
> INNER JOIN INCIDENT INC
> ON ALM.INC_UID_INCIDENT = INC.INC_UID
> INNER JOIN INCIDENT_TYPE_MLKP ITL
> ON INC. ITL_UID_INCIDENT_TYP
E = ITL.ITL_UID
> INNER JOIN INCIDENT_TYPE_CATEGO
RY_LKP ITC
> ON ITL.ITC_UID_CATEGORY = ITC.ITC_UID
> LEFT OUTER JOIN FACILITY FAC
> ON INC.FAC_UID_FACILITY = FAC.FAC_UID
> LEFT OUTER JOIN FACILITY_GROUP_LKP FGL
> ON INC. FGL_UID_FACILITY_GRO
UP = FGL.FGL_UID
> LEFT OUTER JOIN LANE LAN
> ON INC.LAN_UID_LANE = LAN.LAN_UID
> LEFT OUTER JOIN LANE_CONTROLLER LCT
> ON LAN. LCT_UID_LANE_CONTROL
LER = LCT.LCT_UID
> LEFT OUTER JOIN ALARM_ACKNOWLEDGEMEN
T AAK
> ON ALM.ALM_UID = AAK.ALM_UID_ALARM AND AAK.AAK_HAS_RESPONDED = 1
> LEFT OUTER JOIN USER_ACCOUNT USR
> ON AAK.USR_UID_RESPONDENT = USR.USR_UID
> where ALM_DATE >= TO_DATE('03-19-2006 06:26:45', 'MM/DD/YYYY
> HH24:MI:SS')

What version of Oracle?

Whether Oracle chooses to use an index is not so simple. What
percentage of the data meets this criterion? How stale are the
statistics? Have you run an Explain Plan? What is the clustering
factor?

Oracle may be telling you that your index is not valuable for
your query. And it is likely that >= is more critical to what
is happening than TO_DATE.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

dhart@t2systems.com

2006-03-30, 8:23 pm

First of all my background is in SQL Server and this is my first time
optimizing an Oracle query.

1. The Entire explain plan is very large... What's parts of it do you
want?
2. Version 10.1.0
3. This is a development and I don't think we are. What's the best way
to refresh them.
4. I tried this the explain plans are the same -- At the moment there
are 3500 rows in the alarm table, how can I force Oracle to use the
index?

Thanks,
Dave

frank.van.bortel@gmail.com

2006-03-31, 3:24 am

Don't be obsessed with the use of an index; how did you come to the
conclusion your query will be faster when it uses the index?
3500 rows is nothing, and tells nothing; what could tell something is
which portion of the 3500 rows meets your criteria; CBO used to switch
to FTS when more than 5-30% of the total records would be fetched.
Also, values of optimizer_index_cost
_adjust, optimizer_index_cach
ing
and db_file_multiblock_r
ead_count are of importance.

A good document on that would be "The Search for Intelligent Life in
the Cost-Based Optimizer" on
http://www.evdbt.com/ SearchIntelligenceCB
O.doc

dhart@t2systems.com

2006-03-31, 9:23 am

Thanks very much for the link, I'll give it a read!

I'm definitely not obessed with the index, and I know 3500 rows is
nothing. I should have been more specific in my last post on why I need
the index to work: When in production this table will grow at the rate
of about 10,000 rows a day so I need to know if the query is going to
use the index or not.

After some initial research I was worried that using the TO_DATE in the
where would cause Oracle not to use the index on the ALM_DATE column.
Could someone tell me definititively that this is or is not the case?
Function Based Indexes don't work with TO_DATE because it's not
deterministic, so using FBI's won't solve this particular problem --
unless I've done it wrong -- suggestions are welcomed :)

One other thing to note is that I am always going to grabbing the
latest records out of this table (RE: where ALM_DATE >=
TO_DATE('03-19-2006 06:26:45', 'MM/DD/YYYY HH24:MI:SS'). In SQL Server
I would create a clustered index (leaf node of the b-tree points
directly to the data) on the ALM_DATE field and this would improve
performance dramatically. Does Oracle have a silver bullet like this?
Or will a regular index work fine?

Thanks again,
Dave

Mark C. Stock

2006-03-31, 9:23 am

<dhart@t2systems.com> wrote in message
news:1143812753.260934.116020@g10g2000cwb.googlegroups.com...
: Thanks very much for the link, I'll give it a read!
:
:
: After some initial research I was worried that using the TO_DATE in the
: where would cause Oracle not to use the index on the ALM_DATE column.
: Could someone tell me definititively that this is or is not the case?
: Function Based Indexes don't work with TO_DATE because it's not
: deterministic, so using FBI's won't solve this particular problem --
: unless I've done it wrong -- suggestions are welcomed :)
:

that's not the case, and AFAIR, you already proved it by testing with a date
variable and getting the same execution plan (which you have not yet posted)

also, FBIs are for when you use an expresson on the column being searched,
not for the search expression

++ mcs

Frank van Bortel

2006-03-31, 9:23 am

dhart@t2systems.com wrote:
> Thanks very much for the link, I'll give it a read!
>
> I'm definitely not obessed with the index, and I know 3500 rows is
> nothing. I should have been more specific in my last post on why I need
> the index to work: When in production this table will grow at the rate
> of about 10,000 rows a day so I need to know if the query is going to
> use the index or not.
>

No, you do not.

Let the database do what databases do best.
If Oracle finds that an index will be better, it will use
it. If it finds it is a waste of time, it will not use it.
If there is an index, updates will be slower (table + index
to update).

When you can *prove* Oracle wrong, start thinking what you
missed; one option has been mentioned: statistics.

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

DA Morgan

2006-03-31, 11:24 am

frank.van.bortel@gmail.com wrote:
> Don't be obsessed with the use of an index; how did you come to the
> conclusion your query will be faster when it uses the index?
> 3500 rows is nothing, and tells nothing; what could tell something is
> which portion of the 3500 rows meets your criteria; CBO used to switch
> to FTS when more than 5-30% of the total records would be fetched.
> Also, values of optimizer_index_cost
_adjust, optimizer_index_cach
ing
> and db_file_multiblock_r
ead_count are of importance.
>
> A good document on that would be "The Search for Intelligent Life in
> the Cost-Based Optimizer" on
> http://www.evdbt.com/ SearchIntelligenceCB
O.doc

I hadn't noticed the 3500 row figure before. But given that this
likely fits into a very small number of blocks it is quite
probable that the index is a waste of a perfectly good 4K block.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

DA Morgan

2006-03-31, 11:24 am

dhart@t2systems.com wrote:
> Thanks very much for the link, I'll give it a read!
>
> I'm definitely not obessed with the index, and I know 3500 rows is
> nothing. I should have been more specific in my last post on why I need
> the index to work: When in production this table will grow at the rate
> of about 10,000 rows a day so I need to know if the query is going to
> use the index or not.

Then you are going about this in entirely the wrong way. Oracle may
well make different decisions about that index over time depending
on the number of rows, cardinality, clustering factor, etc.

And you do seem a bit obsessed in that there are plenty of queries
that run slower using an index than without ... even as tables grow.

But given your desire to see this thing through ... you need to
create enough dummy data to simulate the table at 3 months, 6 months,
1 year, 2 years, and try your query at each level. Testing with 3500
rows is a total waste of time as it doesn't even correspond to one
day's worth of data.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

dhart@t2systems.com

2006-03-31, 1:23 pm

Daniel thanks for your reply, I realize that tuning queries depends on
multiple factors and the row count is just one of them. I was just
starting to test this query with the 3500 row as I would rather start
testing with a small amount of data and then slowly add data and see
how the query responds.

I would be very surprise if this query would run faster without the use
of an index :) I'm not obsessing, just being practical.

After viewing the Explain Plan I had some question and was trying to
get some expert support as I'm new to Oracle but not new to databases.
I appreciate all the input, it has answered my all questions :)

Cheers!
Dave

Jim Smith

2006-04-02, 3:23 am

In message <1143812753.260934.116020@g10g2000cwb.googlegroups.com>,
dhart@t2systems.com writes
>One other thing to note is that I am always going to grabbing the
>latest records out of this table (RE: where ALM_DATE >=
>TO_DATE('03-19-2006 06:26:45', 'MM/DD/YYYY HH24:MI:SS'). In SQL Server
>I would create a clustered index (leaf node of the b-tree points
>directly to the data) on the ALM_DATE field and this would improve
>performance dramatically. Does Oracle have a silver bullet like this?
>Or will a regular index work fine?

The equivalent is an index-organised table.
If the table gets very large (unlikely at 10000 rows per day),
partitioning on ALM_DATE might also help.
--
Jim Smith
I'm afraid you've mistaken me for someone who gives a damn.

DA Morgan

2006-04-03, 11:23 am

dhart@t2systems.com wrote:

> I would be very surprise if this query would run faster without the use
> of an index :) I'm not obsessing, just being practical.

I wouldn't. Many queries run faster without an index. But here's
something to consider:

CREATE TABLE t (
doc_id VARCHAR2(10),
person_id NUMBER(7),
doc_name VARCHAR2(30));

CREATE SEQUENCE seq_t;

DECLARE
t_docname dbms_sql.VARCHAR2_TABLE;

CURSOR c IS
SELECT object_name
FROM dba_objects;

BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO t_docname LIMIT 250;

FORALL i IN 1..t_docname.COUNT
INSERT INTO t
(doc_id, person_id, doc_name)
VALUES
(seq_t.NEXTVAL, MOD(seq_t.CURRVAL, 233), t_docname(i));

EXIT WHEN c%NOTFOUND;
END LOOP;
COMMIT;
CLOSE c;
END;
/

set autotrace traceonly

SELECT person_id, count(*)
FROM t
GROUP BY person_id
ORDER BY 1;

CREATE INDEX ix_t_person_id
ON t(person_id);

exec dbms_stats. gather_schema_stats(
'<your_schema_name>', CASCADE=>TRUE);

SELECT doc_name
FROM t
WHERE person_id = 221;

Note that the index is not used even after running DBMS_STATS.\

Now force index usage:

SELECT /*+ INDEX(t ix_t_person_id) */ doc_name
FROM t
WHERE person_id = 221;

Did it run faster? It sure doesn't on my system with 10gR2.

Before force with hint:

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 50885 | 64 (4)| 00:00:01 |
-------------------------------------------------------------------

After force with hint:

Execution Plan
----------------------------------------------------------
Plan hash value: 1539598453

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 186 | 5394 |
186 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 186 | 5394 |
186 (0)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | IX_T_PERSON_ID | 186 | |
1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

This demo can be found in Morgan's Library under Clustering Factor at
www.psoug.org. You might want to run the full demo on your system as
your mileage may vary.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Sponsored Links