23 lines
1.5 KiB
SQL
23 lines
1.5 KiB
SQL
-- تقرير أوزان التحميل (loading-weights) and the other reports that filter EVENTS by type and date time out on QA:
|
|
-- a ONE-DAY range already exceeds the 30 s command timeout, so the cost does not depend on the date range. That points to
|
|
-- a full scan of EVENTS because no index serves "EVENTTYPE IN (...) AND EVENTDATE >= :from AND EVENTDATE < :to + 1".
|
|
-- NOT executed by the application and NOT verified: the DBA decides after looking at the existing indexes and the plan.
|
|
--
|
|
-- Step 1 - what indexes EVENTS already has, and how big it is
|
|
-- SELECT index_name, column_position, column_name FROM all_ind_columns WHERE table_name = 'EVENTS' ORDER BY index_name, column_position;
|
|
-- SELECT num_rows, last_analyzed FROM all_tables WHERE table_name = 'EVENTS';
|
|
--
|
|
-- Step 2 - plan of the report query (replace the dates)
|
|
-- EXPLAIN PLAN FOR
|
|
-- select p.pn, max(decode(e.eventtype, 'LTW', to_number(e.weight))), min(e.eventdate)
|
|
-- from permits p, events e
|
|
-- where p.domain = 'GN' and e.permitid = p.id and e.eventtype in ('LTW', 'LGW', 'LNW')
|
|
-- and e.eventdate >= DATE '2026-11-10' and e.eventdate < DATE '2026-11-10' + 1
|
|
-- group by p.id, p.pn;
|
|
-- SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
|
|
--
|
|
-- Step 3 - if EVENTS has no index starting with EVENTDATE / EVENTTYPE, this one is the candidate
|
|
-- (EVENTDATE second keeps date-range scans cheap for each of the few event types):
|
|
-- CREATE INDEX EVENTS_TYPE_DATE_IX ON EVENTS (EVENTTYPE, EVENTDATE);
|
|
-- EXEC DBMS_STATS.GATHER_INDEX_STATS(USER, 'EVENTS_TYPE_DATE_IX');
|