Could you show an example of creating an FBI with MONTHS_BETWEEN a sysdate and a date passed in?

Thanks!

We have a table t038_fi that has 5172129 rows.

It's analyzed and indexed on first_use_dt and t040_id.
Obviously with the functions, the first_use_dt index is useless for this query.

This query is taking about 1 minute, and I need to tune it.

I tried creating a function based index on the months_between, but got the "pure" error.

And you're saying it must be a deterministic function, which as you have pointed out... it is not.

So how can I tune this guy?

Thanks much,

Justin

wic_owner5> SELECT a.last_name
2 , a.first_name
3 , a.fi_seq_nr
4 , b.create_dt
5 , b.first_use_dt
6 , b.last_use_dt
7 , b.fi_nr
8 , b.fi_picked_up_in
9 , b.void_reason_cd
10 , ' ' FLAG
11 FROM t038_fi b JOIN tt_tmp5 a ON a.t040_id = b.t040_id
12 WHERE ABS(MONTHS_BETWEEN (SYSDATE, b.first_use_dt)) <= 12
13 ORDER BY a.last_name
14 , a.first_name
15 , b.first_use_dt DESC
16 , b.create_dt DESC
17 , a.fi_seq_nr DESC;

no rows selected

Elapsed: 00:00:40.02

Execution Plan
==========================================================
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=944 Card=1470 Bytes=148470)
1 0 SORT (ORDER BY) (Cost=944 Card=1470 Bytes=148470)
2 1 MERGE JOIN (Cost=920 Card=1470 Bytes=148470)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T038_FI' (Cost=826 Card=258606 Bytes=10602846)
4 3 INDEX (FULL SCAN) OF 'X038FK03' (NON-UNIQUE) (Cost=26 Card=5172129)
5 2 SORT (JOIN) (Cost=94 Card=8168 Bytes=490080)
6 5 TABLE ACCESS (FULL) OF 'TT_TMP5' (Cost=11 Card=8168 Bytes=490080)




Statistics
==========================================================
26 recursive calls
0 db block gets
27184 consistent gets
9754 physical reads
0 redo size
746 bytes sent via SQL*Net to client
368 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
0 rows processed

the tt_tmp5 table only has 6 records in it.

What is the reason for recommending dbms_stats.set_table_stats instead of dbms_stats.gather_table_stats for the above table with 6 records?

worked supa fast.

So why is months_between slow would be the next obvious question?

Also, the number of records in tt_tmp5 will change, but will always be small. So would specifying stats for say 20 rows be a good idea, even if it is less, but never more?

Thanks!

I think you meant that:

where b.first_user_dt between add_months(sysdate,-12) and sysdate

is a duplicate for:

ABS(MONTHS_BETWEEN (SYSDATE, b.first_use_dt)) <= 12

because ADD_MONTHS(sysdate,12) would make the time difference 24 months?

No, i very specifically meant +-12 months

Say first_use_dt is in the past:


ops$tkyte@ORA10G> select months_between( sysdate, to_date('01-aug-2004'))
  2  from dual;
 
MONTHS_BETWEEN(SYSDATE,TO_DATE('01-AUG-2004'))
----------------------------------------------
                                    1.75294616

<b>months between is +1.7, say it is in the future:</b>
 

ops$tkyte@ORA10G> 1
  1* select months_between( sysdate, to_date('01-aug-2004'))
ops$tkyte@ORA10G> c/aug/nov
  1* select months_between( sysdate, to_date('01-nov-2004'))
ops$tkyte@ORA10G> /
 
MONTHS_BETWEEN(SYSDATE,TO_DATE('01-NOV-2004'))
----------------------------------------------
                                    -1.2470482
 
<b>it is negative == but you have ABS() wrapped about it -- so it is +1.2</b>


your query asked for things +/- 12 months from sysdate, i was just duplicating that. 

The answer refers to a bug that's still open with 8.1.7.
I created a more convoluted index in 11.2 and 12.1 perfectly fine, but when trying to import that index into 12.2, I got this "pure function" error...

The index was on:

"SOMEBIGTABLE" (UPPER(RTRIM( REGEXP_REPLACE (SUBSTR("VARCHAR2_700_COL",NVL("NUMBER_SINGLE_DIGIT_COL",0)+1),'[[:punct:]]',''))))

SOMEBIGTABLE has 373 columns and (being a dev system) only 3,321 rows.

(Yes, I know that looks really odd: the VARCHAR2_700_COL contains book titles, e.g. "The Hobbit"; the NUMBER_SINGLE_DIGIT_COL contains a number of letters to ignore when sorting so that "The Hobbit" sorts with the Hs, not with the Ts. Sometimes the title is, e.g., 'The "Hobbit"', hence the ignoring of punctuation)