Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Unanswered: Between Dates slow!!!

    Hi guys,

    On Oracle 8. This query is so simple but it takes about 5 minutes to run and this is way to slow. My table has about five millions of records so I don't think this should be a problem for Oracle.

    SELECT COUNT(*)
    FROM cicdospa
    WHERE (co_contr = 'E660' OR co_contr = '0000E660')
    AND da_paimn BETWEEN TO_DATE('4/30/2002','MM/DD/YYYY') AND TO_DATE('5/1/2003','MM/DD/YYYY')

    by the way the following takes 3-4 seconds so I really don't understand

    SELECT COUNT(*)
    FROM cicdospa
    WHERE (co_contr = 'E660' OR co_contr = '0000E660')
    AND da_paimn >= TO_DATE('4/30/2002','MM/DD/YYYY')

    Anybody can help?

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296

    Re: Between Dates slow!!!

    Originally posted by harvma
    Hi guys,

    On Oracle 8. This query is so simple but it takes about 5 minutes to run and this is way to slow. My table has about five millions of records so I don't think this should be a problem for Oracle.

    SELECT COUNT(*)
    FROM cicdospa
    WHERE (co_contr = 'E660' OR co_contr = '0000E660')
    AND da_paimn BETWEEN TO_DATE('4/30/2002','MM/DD/YYYY') AND TO_DATE('5/1/2003','MM/DD/YYYY')

    by the way the following takes 3-4 seconds so I really don't understand

    SELECT COUNT(*)
    FROM cicdospa
    WHERE (co_contr = 'E660' OR co_contr = '0000E660')
    AND da_paimn >= TO_DATE('4/30/2002','MM/DD/YYYY')

    Anybody can help?
    5 Million rows! ouch!
    you need an index on the where clause columns (if you do not already have one).

    3-4 secs is not terrible.

    try this:
    create index TEMP_DATE_INDEX
    on cicdospa
    (co_contr, da_paimn)
    tablespace INDEX_01
    storage (initial 25M next 15M pctincrease 0 maxextents 200)
    nologging;

    analyze table cicdospa compute statistics;

    ^ This will take a while but you need to do it
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66

    Re: Between Dates slow!!!

    Harvma,

    I suspect your optimiser works in RULE-based mode, either because cost-based is not turn on for your session, or the table hasn't been analysed.

    There is a simple explanation ( and a solution ) for your problem. The RULE-based optimiser assumes that a BETWEEN clause on an indexed colum is very selective (i.e. it returns few rows). So, it prefers a BETWEEN over an inequality condition.

    To be sure, the rule-based optimiser does not really know how the data looks. So, with "between" it has probably picked the index on the payment date column. But that could have still returned all the rows for 12 month. It had to examine all of them, until finding the few which refered to the particular contract you were looking at.

    When you were running your second query, with ">=" the optimiser has probably avoided the index on the date. It has picked instead an index on "co_contr". In this case, it has quickly found all records for the contract in question, and then eliminated the payments which were made outside your date range. I assume it only had to work on a small proportion of the data, since "co_contr" can have many other values.

    Normally, the optimiser should have chosen the index on "co_contr" in both your queries, since it has an equality condition, which is deemed to be more selective. But it could happen that "co_contr" is just the inital field in a composite index, which makes it a less prefered canditate.

    The solution for the Between case is to force the optimiser to ignore the index on the date. Try:

    AND TRUNC( da_paimn) BETWEEN ...

    The trunc() function chops off hh:mi:ss, but you probably don't care. More importantly, it makes it impossible for the optimiser to use the index on the date. It leaves no other choice than to use the other indeces. Now your query should run just as fast as with ">="

    Turning off indeces is quite frequent with rule-based systems.

    Andrew Schonberger

  4. #4
    Join Date
    Jul 2003
    Posts
    2
    Thank you guys,

    It did was much faster with the trunc(date); It took less than one second. I also have been able to make this query fast by forcing an index.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •