Oracle 8i Date Function Slow

Question

I am trying to run the following PL / SQL on an Oracle 8i server (old, I know):

select
    -- stuff --
from
    s_doc_quote d,
    s_quote_item i,
    s_contact c,
    s_addr_per a,
    cx_meter_info m
where
    d.row_id = i.sd_id
    and d.con_per_id = c.row_id
    and i.ship_per_addr_id = a.row_id(+)
    and i.x_meter_info_id = m.row_id(+)
    and d.x_move_type in ('Move In','Move Out','Move Out / Move In')
    and i.prod_id in ('1-QH6','1-QH8')
    and d.created between add_months(trunc(sysdate,'MM'), -1) and sysdate
;

The execution is incredibly slow. Since the server is removed at midnight every night, it often does not have time to complete.

The implementation plan is as follows:

SELECT STATEMENT   1179377
 NESTED LOOPS   1179377
  NESTED LOOPS OUTER  959695
   NESTED LOOPS OUTER  740014
    NESTED LOOPS   520332
     INLIST ITERATOR
      TABLE ACCESS BY INDEX ROWID S_QUOTE_ITEM 157132
       INDEX RANGE SCAN S_QUOTE_ITEM_IDX8 8917
     TABLE ACCESS BY INDEX ROWID S_DOC_QUOTE 1
      INDEX UNIQUE SCAN S_DOC_QUOTE_P1 1
    TABLE ACCESS BY INDEX ROWID S_ADDR_PER 1
     INDEX UNIQUE SCAN S_ADDR_PER_P1 1
   TABLE ACCESS BY INDEX ROWID CX_METER_INFO 1
    INDEX UNIQUE SCAN CX_METER_INFO_P1 1
  TABLE ACCESS BY INDEX ROWID S_CONTACT 1
   INDEX UNIQUE SCAN S_CONTACT_P1 1

If I change the following, where is the condition:

and d.created between add_months(trunc(sysdate,'MM'), -1) and sysdate

To a static value, for example:

and d.created between to_date('20110101','yyyymmdd') and sysdate

execution plan:

SELECT STATEMENT   5
 NESTED LOOPS   5
  NESTED LOOPS OUTER  4
   NESTED LOOPS OUTER  3
    NESTED LOOPS   2
     TABLE ACCESS BY INDEX ROWID S_DOC_QUOTE 1
      INDEX RANGE SCAN S_DOC_QUOTE_IDX1 3
     INLIST ITERATOR
      TABLE ACCESS BY INDEX ROWID S_QUOTE_ITEM 1
       INDEX RANGE SCAN S_QUOTE_ITEM_IDX4 4
    TABLE ACCESS BY INDEX ROWID S_ADDR_PER 1
     INDEX UNIQUE SCAN S_ADDR_PER_P1 1
   TABLE ACCESS BY INDEX ROWID CX_METER_INFO 1
    INDEX UNIQUE SCAN CX_METER_INFO_P1 1
  TABLE ACCESS BY INDEX ROWID S_CONTACT 1
   INDEX UNIQUE SCAN S_CONTACT_P1 1

which starts returning strings almost instantly.

So far, I have been trying to replace the dynamic date condition with bind variables, and also use a subquery that selects the dynamic date from a double table. None of these methods have helped improve performance.

Since I am relatively new to PL / SQL, I cannot understand the reasons for the significant differences in execution plans.

SAS, SQL * Plus.

EDIT:

:

var start_date varchar2(8);
exec :start_date := to_char(add_months(trunc(sysdate,'MM'), -1),'yyyymmdd')

where:

and d.created between to_date(:start_date,'yyyymmdd') and sysdate

1179377.

, , , SAS- ( ).

Answer 1

, ADD_MONTHS. , , . , , , , . , , , .

S_DOC_QUOTE_IDX1. , , CREATED. , SYSDATE. , , , . , .

, , , 8i, , , . Oracle , bind .

, , , , , () () , .

, - .

Answer 2

, , , , Oracle . SQL, (, , ), , /.

, , , Oracle . . " add_months ?" :

Oracle , ?

, , . , ( , ) , , . , .

, , , / , ( ).

. (add_months(trunc(sysdate,'MM'), -1) and sysdate), .

, , : ( , 8i) . , . , , .

, , , .

,

, , . , ( ). , :

• , ​​. last_analyzed ALL_TABLES ALL_INDEXES , . , () .
• (dbms_stats package)
• , , , .
• , ( ).
• , , - , :
  
  • rownum ( , ).
  • hints, , . , LEADING, ( ).
• , , , , . 8i 12+ , , :)

---

. ( ), , . , Oracle:

Answer 3

, .

:

and d.created 
    between (select add_months(trunc(sysdate,'MM'), -1) from dual) 
    and sysdate

:

select
    -- stuff --
from
    s_doc_quote d,
    s_quote_item i,
    s_contact c,
    s_addr_per a,
    cx_meter_info m,
    (select add_months(trunc(sysdate,'MM'), -1) as startdate from dual) sd
where
    d.row_id = i.sd_id
    and d.con_per_id = c.row_id
    and i.ship_per_addr_id = a.row_id(+)
    and i.x_meter_info_id = m.row_id(+)
    and d.x_move_type in ('Move In','Move Out','Move Out / Move In')
    and i.prod_id in ('1-QH6','1-QH8')
    and d.created between sd.startdate and sysdate

: :

and d.created between :startdate and sysdate

[] , , . . , , add_months .

Answer 4

SQL. , PL/SQL add_months (trunc (sysdate, 'MM'), -1) , .

In addition, I saw that SAS computing takes a lot of time due to pulling data over the network and doing additional work on each line it processes. Depending on your environment, you might consider creating a temp table to save the results of these joins first, and then click on the temp table (try CTAS).