Item Tracking query ready for testing

dmcmpbll commented 7 years ago

I think I have the Item Tracking query ready for testing (I'll be doing some testing of it too, but would appreciate a second set of eyes).

It's located in web reports under local_circulation_maintenance circulation_activity › item tracking.

The query reports on 14 transaction scenarios: UB Routing Shipped- Current UB Routing Shipped- Archived UB Routing Received- Archived (no need for UB Routing received, because these transactions are archived). Call slips- Current Call slips- Archived Charge transactions- Current Charge transactions- Archived Renew transactions- Current Renew transactions- Archived Discharge transactions- Archived (no need for discharge transactions- current, because these transactions are archived). Item statuses applied UB Browse discharges Local Browse discharges Item record creation dates

If for your tests you'd like the version of the Access queries that I modified to output results that don't rely on tables that are saved internally to the Microsoft Access database, I've uploaded a copy of the subquery to my Files directory named: ItemTrackingSubqueryReWrite.txt

dmcmpbll commented 7 years ago

I notice that in the .CSV file output, the date/time is formatted like: 2016-04-10 01:25:05+00:00 2016-04-09 12:27:24+00:00 2016-04-09 12:27:24+00:00 2016-04-06 18:28:29+00:00 2016-04-06 18:28:29+00:00 2016-03-08 19:02:16+00:00 2016-02-10 15:02:28+00:00 2016-01-13 14:35:22+00:00 Which is different than the display of the results on screen.

Excel doesn't recognize the .csv format of the date field as a "Date" field, so the formatting cannot be updated for different versions of display in Excel. Is there a way I can edit the SQL to make that happen?

dmcmpbll commented 7 years ago

I have checked the results of several items on NPU-devel that contain all 14 of the scenarios and have worked through the checklist in the wiki.

It did run a little slow on UIU; did someone want to check that portion before moving it over?

Here's a barcode to test with: 30112003872733

Otherwise, I think it is ready for Prod.

dmcmpbll commented 7 years ago

Sorry, I should add, it's ready for Prod unless there is a way to have the date formatting changed, if that change should be made to the SQL before you copy it over to Prod.

Here's also a second barcode for checking UIU's run time: 30112103801855 (returns 188 rows in the results).

gibsonjc commented 7 years ago

As discussed in today's meeting, I've assigned this to @csaundrs so he can check for slowness and load. If this report checks out ok, we'll move it to Production "as is" and have a new task/Issue to check into the data formatting differences between the web output and the csv output.

csaundrs commented 7 years ago

Despite the size of the query, it is quite efficient and does not pose much of a cost or resource load to the reporting database server. I think it's fine to move it to production.

188 rows selected.

Elapsed: 00:00:03.45

Execution Plan

Plan hash value: 2244094371

| 0 | SELECT STATEMENT | | 213 | 21726 | 15906 (1)| 00:00:01 |

| 1 | SORT ORDER BY | | 213 | 21726 | 15906 (1)| 00:00:01 |

| 2 | VIEW | | 213 | 21726 | 15905 (1)| 00:00:01 |

3	UNION-ALL

| 4 | NESTED LOOPS OUTER | | 1 | 76 | 56 (0)| 00:00:01 |

|* 5 | HASH JOIN OUTER | | 1 | 64 | 55 (0)| 00:00:01 |

|* 6 | HASH JOIN OUTER | | 1 | 56 | 52 (0)| 00:00:01 |

|* 7 | HASH JOIN | | 1 | 48 | 49 (0)| 00:00:01 |

| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| ITEM_BARCODE | 1 | 21 | 4 (0)| 00:00:01 |

|* 9 | INDEX RANGE SCAN | ITEM_BARCODE_BAR_IDX | 1 | | 3 (0)| 00:00:01 |

|* 10 | TABLE ACCESS FULL | UB_ROUTING | 2184 | 58968 | 45 (0)| 00:00:01 |

| 11 | TABLE ACCESS FULL | VOYAGER_DATABASES | 92 | 736 | 3 (0)| 00:00:01 |

| 12 | TABLE ACCESS FULL | VOYAGER_DATABASES | 92 | 736 | 3 (0)| 00:00:01 |

| 13 | TABLE ACCESS BY INDEX ROWID | LOCATION | 1 | 12 | 1 (0)| 00:00:01 |

|* 14 | INDEX UNIQUE SCAN | LOCATION_LOC_ID_IDX | 1 | | 0 (0)| 00:00:01 |

| 15 | NESTED LOOPS OUTER | | 2 | 152 | 4127 (1)| 00:00:01 |

|* 16 | HASH JOIN OUTER | | 2 | 128 | 4125 (1)| 00:00:01 |

|* 17 | HASH JOIN OUTER | | 2 | 112 | 4122 (1)| 00:00:01 |

|* 18 | HASH JOIN | | 2 | 96 | 4119 (1)| 00:00:01 |

| 19 | TABLE ACCESS BY INDEX ROWID BATCHED| ITEM_BARCODE | 1 | 21 | 4 (0)| 00:00:01 |

|* 20 | INDEX RANGE SCAN | ITEM_BARCODE_BAR_IDX | 1 | | 3 (0)| 00:00:01 |

|* 21 | TABLE ACCESS FULL | UB_ROUTING_ARCHIVE | 1375K| 35M| 4111 (1)| 00:00:01 |

| 22 | TABLE ACCESS FULL | VOYAGER_DATABASES | 92 | 736 | 3 (0)| 00:00:01 |

| 23 | TABLE ACCESS FULL | VOYAGER_DATABASES | 92 | 736 | 3 (0)| 00:00:01 |

| 24 | TABLE ACCESS BY INDEX ROWID | LOCATION | 1 | 12 | 1 (0)| 00:00:01 |

|* 25 | INDEX UNIQUE SCAN | LOCATION_LOC_ID_IDX | 1 | | 0 (0)| 00:00:01 |

|* 26 | HASH JOIN OUTER | | 2 | 104 | 4123 (1)| 00:00:01 |

|* 27 | HASH JOIN | | 2 | 88 | 4120 (1)| 00:00:01 |

| 28 | TABLE ACCESS BY INDEX ROWID BATCHED | ITEM_BARCODE | 1 | 21 | 4 (0)| 00:00:01 |

|* 29 | INDEX RANGE SCAN | ITEM_BARCODE_BAR_IDX | 1 | | 3 (0)| 00:00:01 |

|* 30 | TABLE ACCESS FULL | UB_ROUTING_ARCHIVE | 1375K| 30M| 4112 (1)| 00:00:01 |

| 31 | TABLE ACCESS FULL | VOYAGER_DATABASES | 92 | 736 | 3 (0)| 00:00:01 |

| 32 | NESTED LOOPS | | 1 | 48 | 51 (0)| 00:00:01 |

| 33 | NESTED LOOPS | | 1 | 48 | 51 (0)| 00:00:01 |

|* 34 | HASH JOIN | | 1 | 41 | 50 (0)| 00:00:01 |

| 35 | TABLE ACCESS BY INDEX ROWID BATCHED | ITEM_BARCODE | 1 | 21 | 4 (0)| 00:00:01 |

|* 36 | INDEX RANGE SCAN | ITEM_BARCODE_BAR_IDX | 1 | | 3 (0)| 00:00:01 |

| 37 | TABLE ACCESS FULL | CALL_SLIP | 9153 | 178K| 46 (0)| 00:00:01 |

|* 38 | INDEX UNIQUE SCAN | PATRON_GROUP_IDX | 1 | | 0 (0)| 00:00:01 |

| 39 | TABLE ACCESS BY INDEX ROWID | PATRON_GROUP | 1 | 7 | 1 (0)| 00:00:01 |

|* 40 | HASH JOIN | | 3 | 144 | 5535 (1)| 00:00:01 |

| 41 | MERGE JOIN CARTESIAN | | 32 | 896 | 6 (0)| 00:00:01 |

| 42 | TABLE ACCESS BY INDEX ROWID | ITEM_BARCODE | 1 | 21 | 4 (0)| 00:00:01 |

|* 43 | INDEX RANGE SCAN | ITEM_BARCODE_BAR_IDX | 1 | | 3 (0)| 00:00:01 |

| 44 | BUFFER SORT | | 32 | 224 | 2 (0)| 00:00:01 |

| 45 | TABLE ACCESS FULL | PATRON_GROUP | 32 | 224 | 2 (0)| 00:00:01 |

| 46 | TABLE ACCESS FULL | CALL_SLIP_ARCHIVE | 2188K| 41M| 5523 (1)| 00:00:01 |

|* 47 | HASH JOIN OUTER | | 67 | 6164 | 21 (0)| 00:00:01 |

|* 48 | HASH JOIN OUTER | | 67 | 4556 | 17 (0)| 00:00:01 |

| 49 | NESTED LOOPS | | 67 | 4020 | 14 (0)| 00:00:01 |

| 50 | NESTED LOOPS | | 67 | 4020 | 14 (0)| 00:00:01 |

| 51 | TABLE ACCESS BY INDEX ROWID BATCHED| ITEM_BARCODE | 1 | 21 | 4 (0)| 00:00:01 |

|* 52 | INDEX RANGE SCAN | ITEM_BARCODE_BAR_IDX | 1 | | 3 (0)| 00:00:01 |

|* 53 | INDEX RANGE SCAN | CIRC_TRANS_ARCH_ITM_IDX | 8 | | 2 (0)| 00:00:01 |

| 54 | TABLE ACCESS BY INDEX ROWID | CIRC_TRANS_ARCHIVE | 67 | 2613 | 10 (0)| 00:00:01 |

| 55 | TABLE ACCESS FULL | VOYAGER_DATABASES | 92 | 736 | 3 (0)| 00:00:01 |

| 56 | TABLE ACCESS FULL | LOCATION | 811 | 19464 | 4 (0)| 00:00:01 |

| 57 | NESTED LOOPS | | 58 | 3190 | 160 (0)| 00:00:01 |

| 58 | NESTED LOOPS | | 335 | 3190 | 160 (0)| 00:00:01 |

| 59 | NESTED LOOPS | | 67 | 2211 | 14 (0)| 00:00:01 |

| 60 | TABLE ACCESS BY INDEX ROWID BATCHED | ITEM_BARCODE | 1 | 21 | 4 (0)| 00:00:01 |

|* 61 | INDEX RANGE SCAN | ITEM_BARCODE_BAR_IDX | 1 | | 3 (0)| 00:00:01 |

| 62 | TABLE ACCESS BY INDEX ROWID BATCHED | CIRC_TRANS_ARCHIVE | 67 | 804 | 10 (0)| 00:00:01 |

|* 63 | INDEX RANGE SCAN | CIRC_TRANS_ARCH_ITM_IDX | 8 | | 2 (0)| 00:00:01 |

|* 64 | INDEX RANGE SCAN | RENEW_TRANS_ARCH_IDX | 5 | | 2 (0)| 00:00:01 |

| 65 | TABLE ACCESS BY INDEX ROWID | RENEW_TRANS_ARCHIVE | 1 | 22 | 3 (0)| 00:00:01 |

|* 66 | HASH JOIN OUTER | | 67 | 5159 | 18 (0)| 00:00:01 |

| 67 | NESTED LOOPS | | 67 | 3551 | 14 (0)| 00:00:01 |

| 68 | NESTED LOOPS | | 67 | 3551 | 14 (0)| 00:00:01 |

| 69 | TABLE ACCESS BY INDEX ROWID BATCHED | ITEM_BARCODE | 1 | 21 | 4 (0)| 00:00:01 |

|* 70 | INDEX RANGE SCAN | ITEM_BARCODE_BAR_IDX | 1 | | 3 (0)| 00:00:01 |

|* 71 | INDEX RANGE SCAN | CIRC_TRANS_ARCH_ITM_IDX | 8 | | 2 (0)| 00:00:01 |

| 72 | TABLE ACCESS BY INDEX ROWID | CIRC_TRANS_ARCHIVE | 67 | 2144 | 10 (0)| 00:00:01 |

| 73 | TABLE ACCESS FULL | LOCATION | 811 | 19464 | 4 (0)| 00:00:01 |

| 74 | NESTED LOOPS OUTER | | 1 | 92 | 9 (0)| 00:00:01 |

|* 75 | HASH JOIN OUTER | | 1 | 68 | 8 (0)| 00:00:01 |

| 76 | NESTED LOOPS | | 1 | 60 | 5 (0)| 00:00:01 |

| 77 | NESTED LOOPS | | 1 | 60 | 5 (0)| 00:00:01 |

| 78 | TABLE ACCESS BY INDEX ROWID BATCHED| ITEM_BARCODE | 1 | 21 | 4 (0)| 00:00:01 |

|* 79 | INDEX RANGE SCAN | ITEM_BARCODE_BAR_IDX | 1 | | 3 (0)| 00:00:01 |

|* 80 | INDEX UNIQUE SCAN | CIRC_TRANS_ITM_IDX | 1 | | 0 (0)| 00:00:01 |

| 81 | TABLE ACCESS BY INDEX ROWID | CIRC_TRANSACTIONS | 1 | 39 | 1 (0)| 00:00:01 |

| 82 | TABLE ACCESS FULL | VOYAGER_DATABASES | 92 | 736 | 3 (0)| 00:00:01 |

| 83 | TABLE ACCESS BY INDEX ROWID | LOCATION | 1 | 24 | 1 (0)| 00:00:01 |

|* 84 | INDEX UNIQUE SCAN | LOCATION_LOC_ID_IDX | 1 | | 0 (0)| 00:00:01 |

| 85 | NESTED LOOPS | | 3 | 165 | 13 (0)| 00:00:01 |

| 86 | NESTED LOOPS | | 6 | 165 | 13 (0)| 00:00:01 |

| 87 | NESTED LOOPS | | 1 | 33 | 5 (0)| 00:00:01 |

| 88 | TABLE ACCESS BY INDEX ROWID BATCHED | ITEM_BARCODE | 1 | 21 | 4 (0)| 00:00:01 |

|* 89 | INDEX RANGE SCAN | ITEM_BARCODE_BAR_IDX | 1 | | 3 (0)| 00:00:01 |

| 90 | TABLE ACCESS BY INDEX ROWID | CIRC_TRANSACTIONS | 1 | 12 | 1 (0)| 00:00:01 |

|* 91 | INDEX UNIQUE SCAN | CIRC_TRANS_ITM_IDX | 1 | | 0 (0)| 00:00:01 |

|* 92 | INDEX RANGE SCAN | RENEW_TRANS_IDX | 6 | | 2 (0)| 00:00:01 |

| 93 | TABLE ACCESS BY INDEX ROWID | RENEW_TRANSACTIONS | 3 | 66 | 8 (0)| 00:00:01 |

| 94 | NESTED LOOPS | | 1 | 55 | 8 (0)| 00:00:01 |

| 95 | NESTED LOOPS | | 1 | 38 | 7 (0)| 00:00:01 |

| 96 | TABLE ACCESS BY INDEX ROWID BATCHED | ITEM_BARCODE | 1 | 21 | 4 (0)| 00:00:01 |

|* 97 | INDEX RANGE SCAN | ITEM_BARCODE_BAR_IDX | 1 | | 3 (0)| 00:00:01 |

| 98 | TABLE ACCESS BY INDEX ROWID BATCHED | ITEM_STATUS | 1 | 17 | 3 (0)| 00:00:01 |

|* 99 | INDEX RANGE SCAN | ITEM_STATUS_ITEM_ID_IDX | 1 | | 2 (0)| 00:00:01 |

|*100 | INDEX RANGE SCAN | ITEM_STATUS_TYPE_PK | 1 | 17 | 1 (0)| 00:00:01 |

| 101 | NESTED LOOPS OUTER | | 2 | 172 | 888 (2)| 00:00:01 |

|*102 | HASH JOIN | | 2 | 124 | 886 (2)| 00:00:01 |

| 103 | MERGE JOIN CARTESIAN | | 1 | 32 | 4 (0)| 00:00:01 |

|*104 | INDEX SKIP SCAN | EVENT_TYPE_CODE_IDX | 1 | 11 | 1 (0)| 00:00:01 |

| 105 | BUFFER SORT | | 1 | 21 | 3 (0)| 00:00:01 |

| 106 | TABLE ACCESS BY INDEX ROWID BATCHED| ITEM_BARCODE | 1 | 21 | 3 (0)| 00:00:01 |

|*107 | INDEX RANGE SCAN | ITEM_BARCODE_BAR_IDX | 1 | | 2 (0)| 00:00:01 |

| 108 | TABLE ACCESS FULL | EVENT | 668K| 19M| 880 (1)| 00:00:01 |

| 109 | TABLE ACCESS BY INDEX ROWID | LOCATION | 1 | 24 | 1 (0)| 00:00:01 |

|*110 | INDEX UNIQUE SCAN | LOCATION_LOC_ID_IDX | 1 | | 0 (0)| 00:00:01 |

| 111 | NESTED LOOPS OUTER | | 2 | 172 | 888 (2)| 00:00:01 |

|*112 | HASH JOIN | | 2 | 124 | 886 (2)| 00:00:01 |

| 113 | MERGE JOIN CARTESIAN | | 1 | 32 | 4 (0)| 00:00:01 |

|*114 | INDEX SKIP SCAN | EVENT_TYPE_CODE_IDX | 1 | 11 | 1 (0)| 00:00:01 |

| 115 | BUFFER SORT | | 1 | 21 | 3 (0)| 00:00:01 |

| 116 | TABLE ACCESS BY INDEX ROWID BATCHED| ITEM_BARCODE | 1 | 21 | 3 (0)| 00:00:01 |

|*117 | INDEX RANGE SCAN | ITEM_BARCODE_BAR_IDX | 1 | | 2 (0)| 00:00:01 |

| 118 | TABLE ACCESS FULL | EVENT | 668K| 19M| 880 (1)| 00:00:01 |

| 119 | TABLE ACCESS BY INDEX ROWID | LOCATION | 1 | 24 | 1 (0)| 00:00:01 |

|*120 | INDEX UNIQUE SCAN | LOCATION_LOC_ID_IDX | 1 | | 0 (0)| 00:00:01 |

| 121 | NESTED LOOPS OUTER | | 3 | 195 | 9 (0)| 00:00:01 |

| 122 | NESTED LOOPS | | 3 | 123 | 6 (0)| 00:00:01 |

| 123 | TABLE ACCESS BY INDEX ROWID BATCHED | ITEM_BARCODE | 1 | 21 | 4 (0)| 00:00:01 |

|*124 | INDEX RANGE SCAN | ITEM_BARCODE_BAR_IDX | 1 | | 3 (0)| 00:00:01 |

| 125 | TABLE ACCESS BY INDEX ROWID | ITEM | 3 | 60 | 2 (0)| 00:00:01 |

|*126 | INDEX UNIQUE SCAN | ITEM_IDX | 1 | | 1 (0)| 00:00:01 |

| 127 | TABLE ACCESS BY INDEX ROWID | LOCATION | 1 | 24 | 1 (0)| 00:00:01 |

|*128 | INDEX UNIQUE SCAN | LOCATION_LOC_ID_IDX | 1 | | 0 (0)| 00:00:01 |

Predicate Information (identified by operation id):

5 - access("UBR"."DB_ID_TO"="VDT"."DB_ID"(+)) 6 - access("UBR"."DB_ID_FROM"="VDF"."DB_ID"(+)) 7 - access("IB"."ITEM_ID"="UBR"."ITEM_ID_UB") 9 - access("IB"."ITEM_BARCODE"='30112103801855') 10 - filter("UBR"."DB_ID_ITEM"=0) 14 - access("UBR"."LOCATION_ID_TO"="LOC"."LOCATION_ID"(+)) 16 - access("UBRA"."DB_ID_TO"="VDT"."DB_ID"(+)) 17 - access("UBRA"."DB_ID_FROM"="VDF"."DB_ID"(+)) 18 - access("IB"."ITEM_ID"="UBRA"."ITEM_ID_UB") 20 - access("IB"."ITEM_BARCODE"='30112103801855') 21 - filter("UBRA"."DB_ID_ITEM"=0) 25 - access("UBRA"."LOCATION_ID_TO"="LOC"."LOCATION_ID"(+)) 26 - access("UBRA"."DB_ID_TO"="VDT"."DB_ID"(+)) 27 - access("IB"."ITEM_ID"="UBRA"."ITEM_ID_UB") 29 - access("IB"."ITEM_BARCODE"='30112103801855') 30 - filter("UBRA"."DB_ID_ITEM"=0) 34 - access("IB"."ITEM_ID"="CS"."ITEM_ID") 36 - access("IB"."ITEM_BARCODE"='30112103801855') 38 - access("CS"."PATRON_GROUP_ID"="PG"."PATRON_GROUP_ID") 40 - access("IB"."ITEM_ID"="CSA"."ITEM_ID" AND "CSA"."PATRON_GROUP_ID"="PG"."P ATRON_GROUP_ID")

43 - access("IB"."ITEM_BARCODE"='30112103801855') 47 - access("CTA"."CHARGE_LOCATION"="LOC"."LOCATION_ID"(+)) 48 - access("CTA"."DB_ID"="VD"."DB_ID"(+)) 52 - access("IB"."ITEM_BARCODE"='30112103801855') 53 - access("IB"."ITEM_ID"="CTA"."ITEM_ID") 61 - access("IB"."ITEM_BARCODE"='30112103801855') 63 - access("IB"."ITEM_ID"="CTA"."ITEM_ID") 64 - access("CTA"."CIRC_TRANSACTION_ID"="RTA"."CIRC_TRANSACTION_ID") 66 - access("CTA"."CHARGE_LOCATION"="LOC"."LOCATION_ID"(+)) 70 - access("IB"."ITEM_BARCODE"='30112103801855') 71 - access("IB"."ITEM_ID"="CTA"."ITEM_ID") 75 - access("CT"."DB_ID"="VD"."DB_ID"(+)) 79 - access("IB"."ITEM_BARCODE"='30112103801855') 80 - access("IB"."ITEM_ID"="CT"."ITEM_ID") 84 - access("CT"."CHARGE_LOCATION"="LOC"."LOCATION_ID"(+)) 89 - access("IB"."ITEM_BARCODE"='30112103801855') 91 - access("IB"."ITEM_ID"="CT"."ITEM_ID") 92 - access("CT"."CIRC_TRANSACTION_ID"="RT"."CIRC_TRANSACTION_ID") 97 - access("IB"."ITEM_BARCODE"='30112103801855') 99 - access("IB"."ITEM_ID"="ITMS"."ITEM_ID") 100 - access("IST"."ITEM_STATUS_TYPE"="ITMS"."ITEM_STATUS") 102 - access("IB"."ITEM_ID"="EVNT"."ITEM_ID" AND "EVNT"."EVENT_TYPE_ID"="EVNTT" ."EVENT_TYPE_ID")

104 - access("EVNTT"."EVENT_TYPE_CODE"='UBBROWSE') filter("EVNTT"."EVENT_TYPE_CODE"='UBBROWSE') 107 - access("IB"."ITEM_BARCODE"='30112103801855') 110 - access("EVNT"."CIRC_LOCATION_ID"="LOC"."LOCATION_ID"(+)) 112 - access("IB"."ITEM_ID"="EVNT"."ITEM_ID" AND "EVNT"."EVENT_TYPE_ID"="EVNTT" ."EVENT_TYPE_ID")

114 - access("EVNTT"."EVENT_TYPE_CODE"='BROWSE') filter("EVNTT"."EVENT_TYPE_CODE"='BROWSE') 117 - access("IB"."ITEM_BARCODE"='30112103801855') 120 - access("EVNT"."CIRC_LOCATION_ID"="LOC"."LOCATION_ID"(+)) 124 - access("IB"."ITEM_BARCODE"='30112103801855') 126 - access("IB"."ITEM_ID"="ITM"."ITEM_ID") 128 - access("ITM"."CREATE_LOCATION_ID"="LOC"."LOCATION_ID"(+))

Note

dynamic statistics used: dynamic sampling (level=2)
this is an adaptive plan
3 Sql Plan Directives used for this statement

Statistics

    539  recursive calls
      0  db block gets
  91090  consistent gets
  89318  physical reads
      0  redo size
  19688  bytes sent via SQL*Net to client
    954  bytes received via SQL*Net from client
     14  SQL*Net roundtrips to/from client
     24  sorts (memory)
      0  sorts (disk)
    188  rows processed

patrickzurek commented 7 years ago

Report moved to prod. Runs ok there.

CARLI / web-reports