Monitoring Progress

[acct098]

I need an sql script to manage progress when thing are constantly changing.
Below is a layout of the tmp_susf table and count of the records by date.
Inside of this table are records from each day--some leaving and
new ones added. Does anyone have an idea as to how to show progress or a lack of progress by user_id or t_num?



SQL> desc tmp_susf
Name Null? Type
----------------------------------------- -------- ------------
EDATE DATE
USERID_2 VARCHAR2(8)
B_C VARCHAR2(2)
B_AO VARCHAR2(2)
B_NUM VARCHAR2(6)
T_C VARCHAR2(2)
AO VARCHAR2(4)
T_NUM VARCHAR2(12)



EDATE COUNT(*)
--------- ----------
03-NOV-06 9029
07-NOV-06 8510
09-NOV-06 8976
14-NOV-06 9273
15-NOV-06 8810
16-NOV-06 9336
21-NOV-06 8180
22-NOV-06 8421
27-NOV-06 8619
28-NOV-06 8420
29-NOV-06 8403

 

[Dagon]

I don't really understand what your application is trying to do, but the obvious thing that springs to mind is adding user_id and t_num into the query as "group by" elements.

 

[acct098]

I looking for ideas an easy to show progress.

Example:

Date Record USER
01/01/2006 A CAT
01/01/2006 B DOG
01/02/2006 A CAT
01/02/2006 C CAT
01/03/2006 A CAT
01/03/2006 C CAT
01/03/2006 D MOUSE

In the example above I can say that 50% of the records have been fixed (this is because it does not show up
on 01/02/2006).

 

[acct098]

Im taking a different approach. Does anyone know how to speed this query up?

SELECT EDATE, USERID_2||B_C||B_AO||B_NUM||T_C||AO||T_NUM FROM SUSF
WHERE SUBSTR(USERID_2,1,1) IN ('R' , 'S') AND EDATE + 1 IN (SELECT EDATE FROM SUSF)
AND USERID_2||B_C||B_AO||B_NUM||T_C||AO||T_NUM IN (SELECT
USERID_2||B_C||B_AO||B_NUM||T_C||AO||T_NUM FROM SUSF
) AND EDATE IN ('01-DEC-06' , '30-NOV-06');



SQL> desc susf
Name Null? Type
----------------------------------------- -------- ----------------------------
EDATE DATE
TYPE VARCHAR2(9)
POC VARCHAR2(30)
STATION VARCHAR2(4)
USERID_2 VARCHAR2(8)
B_C VARCHAR2(2)
B_AO VARCHAR2(4)
B_NUM VARCHAR2(6)
T_C VARCHAR2(2)
AO VARCHAR2(4)
T_NUM VARCHAR2(12)
ERR_LVL VARCHAR2(3)
INITDATE DATE
STATUS VARCHAR2(5)
DOC_IND VARCHAR2(1)
DO_LT_30 VARCHAR2(1)
DO_30_60 VARCHAR2(1)
DO_61_90 VARCHAR2(1)
DO_91_120 VARCHAR2(1)
DO_GT_120 VARCHAR2(1)
CLEARED VARCHAR2(1)

 

[SantaMufasa]

Yes, acct98, I believe that the following alternate coding achieves (faster) your same results:

SELECT EDATE, USERID_2||B_C||B_AO||B_NUM||T_C||AO||T_NUM
  FROM SUSF x
 WHERE (USERID_2 like ('R%') OR USERID_2 like ('S%'))
   and exists (select 'foo' from SUSF
                where edate trunc(x.edate) = trunc(x.edate)+1)
   AND EDATE IN ('30-NOV-06','01-DEC-06');

Here is why I believe this code produces results identical to your code:
[tt]
1) Your code: "WHERE SUBSTR(USERID_2,1,1) IN ('R' , 'S')"
My code : "WHERE (USERID_2 like ('R%') OR USERID_2 like ('S%'))"

2) Your code: "...AND EDATE IN ('01-DEC-06' , '30-NOV-06')
My code : "...and exists (select 'foo' from SUSF
where edate trunc(x.edate) = trunc(x.edate)+1)

The "exists" operator is ALWAYS more efficient than the "IN" operator when comparing against results of a subquery.

3) Your code: "...AND USERID_2||B_C||B_AO||B_NUM||T_C||AO||T_NUM IN (SELECT
USERID_2||B_C||B_AO||B_NUM||T_C||AO||T_NUM FROM SUSF
)
[/tt]
Certainly, if you find a string in a table, then you will find that same string in that table...that is what this code is checking for, therefore the condition is extraneous.

Let us know how much of a performance improvement you encounter.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[carp]

Depending on the size of the table and your indexing, the following might be faster since it uses a single subquery and sort rather than a subquery for each row returned:

SELECT x.EDATE, x.USERID_2||x.B_C||x.B_AO||x.B_NUM||x.T_C||x.AO||x.T_NUM
  FROM SUSF x, 
       (SELECT DISTINCT TRUNC(edate - 1) ldate
          FROM susf
         WHERE edate IN ('1-DEC-06', '2-DEC-06')) y
WHERE TRUNC(x.edate) IN ('30-NOV-06', '1-DEC-06') 
  AND TRUNC(x.edate) = y.ldate
  AND (USERID_2 like ('R%') OR USERID_2 like ('S%'))
;

A more generalized approach that would allow you to plug in different dates with fewer code changes would be

SELECT x.EDATE, x.USERID_2||x.B_C||x.B_AO||x.B_NUM||x.T_C||x.AO||x.T_NUM
  FROM SUSF x, 
       (SELECT DISTINCT TRUNC(edate - 1) ldate
          FROM susf) y
WHERE TRUNC(edate) IN ('30-NOV-06', '1-DEC-06') 
  AND TRUNC(x.edate) = y.ldate;

Also, if you are sure that all of your edate values are set to midnight, then you can dispense with the TRUNC function. However, it would be good to double-check this:

SELECT count(*) FROM susf WHERE TRUNC(edate) != edate;

As long as you get a response of zero, then you don't need to TRUNC the dates.