Oracle Query Date Range

Question

This query takes 16 minutes. Is there a faster way?

select INIT.MPI_MEMHEAD.MEMIDNUM 
from INIT.MPI_MEMHEAD 
inner join INIT.MPI_AUDHEAD 
on INIT.MPI_AUDHEAD.audrecno = INIT.MPI_MEMHEAD.CAUDRECNO 
where(INIT.MPI_AUDHEAD.audctime+0)  >= to_date('01-JAN-14') AND (INIT.MPI_AUDHEAD.audctime+0) <=  to_date('01-APR-14')

What is the query plan? How big are the tables? How selective are the conditions? What indexes are available? Why are you, apparently, explicitly adding 0 to `audctime` in order to, presumably, prevent an index on that column from being used? Are your statistics up to date? — Justin Cave, Apr 23 '14 at 22:50
There is not enough information about your data, your statistics, no explain plan. Nothing to go on. However, if `INIT.MPI_AUDHEAD.audctime` has an index on that column, by adding a 0 to the column value you are purposely telling the optimizer to **not** use that index. — WoMo, Apr 23 '14 at 22:50
Here is the explain plan: [http://i.imgur.com/uWOAwoX.png](http://i.imgur.com/uWOAwoX.png) Also, the tables are quite large. Millions of records. — You, Apr 24 '14 at 13:55

score 0 · Answer 1 · answered Apr 24 '14 at 00:44

0

First, you can revise "where(INIT.MPI_AUDHEAD.audctime+0) >= to_date('01-JAN-14') AND (INIT.MPI_AUDHEAD.audctime+0) <= to_date('01-APR-14')" to "where INIT.MPI_AUDHEAD.audctime between date '01-JAN-14' and date '01-APR-14'".

Second, if you are always to select this table in one quarter by INIT.MPI_AUDHEAD.audctime, you can alter this table to a partition table by audctime. As below:

CREATE TABLE MPI_AUDHEAD(
      audctime DATE
) PARTITION BY RANGE(audctime)
(
PARTITION part01 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs01,
PARTITION part02 VALUES LESS THAN (TO_DATE('2014-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs02,
);

)

Third, add an index on audctime.

For your reference.

answered Apr 24 '14 at 00:44

VikiYang

233
1
10

For one more question: Is that OLAP or OLTP? – VikiYang Apr 24 '14 at 00:48
Love the partitioning advice, but the first paragraph prevents a +1 because it contains syntax errors – Rob van Wijk Apr 24 '14 at 07:57
Thanks for the post, I will look into this. I have never worked with partitions. – You Apr 24 '14 at 14:08

Gordon Linoff · Answer 2 · 2014-04-24T14:34:37.867

0

Your query formatted so it is a bit more understandable:

select mh.MEMIDNUM 
from INIT.MPI_MEMHEAD mh inner join
     INIT.MPI_AUDHEAD ah
     on ah.audrecno = mh.CAUDRECNO 
where (au.audctime+0)  >= to_date('01-JAN-14') AND (au.audctime+0) <=  to_date('01-APR-14')

I don't fully understand the where clause. The + 0 isn't doing anything. So, you should convert it to:

where au.audctime >= to_date('01-JAN-14') AND au.audctime <= to_date('01-APR-14')

Assuming that audctime is in a date/time format, then the query engine can take advantage of an index. My recommendation would be to add the following two indexes:

INIT.MPI_AUDHEAD(audctime, audrecno)
INIT.MPI_MEMHEAD(CAUDRECNO, MEMIDNUM)

These are covering indexes, so all columns mentioned in the query are in the indexes. In addition, they should speed up the where clause and the join.

edited Apr 24 '14 at 14:34

answered Apr 24 '14 at 02:09

Gordon Linoff

1,242,037
58
646
786

I added the +0 when I was googling different ways to run the query. If it helps, my old query took exactly the same time. (Without the 0" – You Apr 24 '14 at 14:07
@You . . . Seems strange. It will often prevent the use of an index. – Gordon Linoff Apr 24 '14 at 14:24
I am still new to querying databases. It is just inconvenient to search this much data and have it take this long. – You Apr 24 '14 at 14:31
How large are the tables? And do `audrecno` and `caudrecno` have exactly the same type? – Gordon Linoff Apr 24 '14 at 14:36

Joseph B · Answer 3 · 2014-04-24T14:16:37.927

0

You could try the following modified query:

SELECT mm.MEMIDNUM 
FROM INIT.MPI_MEMHEAD mm
INNER JOIN
    (SELECT ma.audrecno as audrecno
     FROM INIT.MPI_AUDHEAD ma
    WHERE ma.audctime >= to_date('01-JAN-14', 'DD-MON-YY') AND ma.audctime <=  to_date('01-APR-14', 'DD-MON-YY')
    ) filtered_ma
ON filtered_ma.audrecno = mm.CAUDRECNO;

The objective of re-writing the query this way is to join with a smaller subset of the INIT.MPI_AUDHEAD table data.

As others have recommended, it would useful to create indexes on the columns that are used for the join and the filter conditions.

edited Apr 24 '14 at 14:16

answered Apr 24 '14 at 02:40

Joseph B

5,519
1
15
19

Getting the error: From keyword not found where expected. line 5 column 8. – You Apr 24 '14 at 14:07
@You I have added the missing FROM in the inline view. Thank you for pointing out. – Joseph B Apr 24 '14 at 14:17
I have ran your query and it now takes 7.5 minutes (thank you). I am wondering if indexing will speed it up even more but I do not know how to index a column nor what repercussions it will have. I don't want to screw anything up in the database. – You Apr 24 '14 at 14:28
@You I think it is very prudent on your part to weigh the effects of creating indexes. Indexes can speed up your SELECT query, but they might also slow down your DML (INSERT, UPDATE, DELETE) operations. – Joseph B Apr 24 '14 at 14:36
Here's an SO question about the creation of indexes: http://stackoverflow.com/questions/212264/how-to-choose-and-optimize-oracle-indexes I would point out "Do not index columns that are modified frequently." and "Do not index keys that appear only in WHERE clauses with functions or operators." – Joseph B Apr 24 '14 at 14:38

Oracle Query Date Range

3 Answers3