Using Analytics to select by priority and weight

This example is a bit contrived. I actually use this technique at work, but I don't want
to give away any company secrets, so I had to change the column names etc.
Here is the setting
You work at the corporate help-desk and it is your job to install new external disk drives on all of the computers that belong to bosses. You can't install the external drives on every computer because you have purchased one per boss for this month. Next month you are going to purchase another batch of hard drives and the month after that another batch and so on. You can only install the external drive on one computer per boss per month. But which computer? To decide this you let each boss prioritize his computers. Highest priority gets the new external drive. For example, Fred has 2 computers, #2000 and #4000. computer 2000 will always be selected and computer #4000 will never be selected given the data shown in the table below. Presumably, at some point, computer #2000 will have all of the external drives it can handle, and the record will be deleted from the table. This will allow Fred's #4000 to be selected. In Fred's case, COMP_WEIGHT is irrelevant. But what if the boss does not care exactly which of is computers gets the external drives first? For example, Mike has 4 computers, #1000,#2000,#3000 and #4000. #4000 is his desktop computer, so he wants to upgrade that one last. #1000,#2000, and #3000 are actually servers and he wants to make sure that they gets upgraded first. He does not care which of these servers get upgraded first, but he does care that they are finished being upgraded about the same time. This is where the COMP_WEIGHT comes in. Mike sets up the following weights for his servers: 29,44, and 72. This means that when a new external drive is available where is a 29 out of 145 chance that it will go to server #1000. There is a 44 out of 145 chance that it will go to server #2000 and there is a 72 out of 145 chance that it will go to #3000. So, weight determines the probability that a given computer will be selected for the current boss/priority.

CREATE TABLE BOSSTABLE
(
  BOSS_ID        VARCHAR2(10 BYTE),
  COMPUTER_ID    NUMBER(5),
  COMP_PRIORITY  NUMBER(5),
  COMP_WEIGHT    NUMBER(5)
)
;

SET DEFINE OFF;
Insert into BOSSTABLE
   (BOSS_ID, COMPUTER_ID, COMP_PRIORITY, COMP_WEIGHT)
 Values
   ('MIKE', 1000, 5, 29);
Insert into BOSSTABLE
   (BOSS_ID, COMPUTER_ID, COMP_PRIORITY, COMP_WEIGHT)
 Values
   ('FRED', 2000, 5, 61);
Insert into BOSSTABLE
   (BOSS_ID, COMPUTER_ID, COMP_PRIORITY, COMP_WEIGHT)
 Values
   ('MIKE', 2000, 5, 44);
Insert into BOSSTABLE
   (BOSS_ID, COMPUTER_ID, COMP_PRIORITY, COMP_WEIGHT)
 Values
   ('MIKE', 3000, 5, 72);
Insert into BOSSTABLE
   (BOSS_ID, COMPUTER_ID, COMP_PRIORITY, COMP_WEIGHT)
 Values
   ('MIKE', 4000, 4, 100);
Insert into BOSSTABLE
   (BOSS_ID, COMPUTER_ID, COMP_PRIORITY, COMP_WEIGHT)
 Values
   ('RAVI', 1000, 10, 40);
Insert into BOSSTABLE
   (BOSS_ID, COMPUTER_ID, COMP_PRIORITY, COMP_WEIGHT)
 Values
   ('RAVI', 2000, 10, 40);
Insert into BOSSTABLE
   (BOSS_ID, COMPUTER_ID, COMP_PRIORITY, COMP_WEIGHT)
 Values
   ('FRED', 4000, 2, 99);
COMMIT;




BOSS_ID
COMPUTER_ID
COMP_PRIORITY
COMP_WEIGHT


FRED
2000
5
61


FRED
4000
2
99


MIKE
1000
5
29


MIKE
2000
5
44


MIKE
3000
5
72


MIKE
4000
4
100


RAVI
1000
10
40


RAVI
2000
10
40







SELECT   boss_id
       , computer_id
       , comp_priority
       , comp_weight
    FROM ( SELECT boss_id
                , computer_id
                , comp_priority
                , comp_weight
                ,              NVL
                                 ( SUM ( weight_total_per_rank ) OVER 
                                       ( PARTITION BY boss_id, comp_priority 
                                         ORDER BY comp_weight 
                                         ROWS BETWEEN 1 PRECEDING AND
                                                      1 PRECEDING )
                                 , 0 ) 
                  AS low_end_of_range
                , weight_total_per_rank
                ,                 FIRST_VALUE ( rand ) OVER 
                                  ( PARTITION BY boss_id, comp_priority )
                  AS the_random_num_we_will_use
            FROM ( SELECT boss_id
                        , computer_id
                        , comp_priority
                        , comp_weight
                        ,               DENSE_RANK ( ) OVER
                                        ( PARTITION BY boss_id 
                                          ORDER BY comp_priority DESC ) 
                          AS   priority_rank
                       ,                  SUM ( comp_weight ) OVER 
                                              ( PARTITION BY boss_id
                                                           , comp_priority 
                                                ORDER BY comp_weight 
                                                ROWS UNBOUNDED PRECEDING )
                          AS weight_total_per_rank
                                    , DBMS_RANDOM.VALUE
                                         ( 0
                                         , SUM ( comp_weight ) OVER 
                                               ( PARTITION BY boss_id
                                                            , comp_priority 
                                               ORDER BY comp_weight 
                                               RANGE BETWEEN UNBOUNDED PRECEDING 
                                                         AND UNBOUNDED FOLLOWING ) )
                         AS   rand
                    FROM bosstable )
           WHERE priority_rank = 1 )
   WHERE (     the_random_num_we_will_use >= low_end_of_range
           AND the_random_num_we_will_use 

First run

BOSS_ID	COMPUTER_ID	COMP_PRIORITY	COMP_WEIGHT
FRED	2000	5	61
MIKE	3000	5	72
RAVI	2000	10	40

Second run

BOSS_ID	COMPUTER_ID	COMP_PRIORITY	COMP_WEIGHT
FRED	2000	5	61
MIKE	2000	5	44
RAVI	2000	10	40

Using a Function Based Index to enforce complex unique constraints.

This article has moved to plsqlnotes.blogspot.com

XMLELEMENT performance problem (PL/SQL)

-- 22 seconds on 9i, 2 seconds on 10g
-- Also, a weird thing is that speed does not decrease in a linear  way
 with the number of --iterations. Instead, speed seems to decrease exponentially.
 If you increase the iterations from --200 to 400 ,
the timing goes from 2 seconds to 9.2 seconds.

SET timing on

DECLARE
   x            INTEGER           := 0;
   audit_info   VARCHAR2 ( 2000 );
BEGIN
   WHILE x < 300
       LOOP
             SELECT
                XMLELEMENT ( "DEALER_SERVICE_KEY", '234234' )
             || XMLELEMENT ( "PARENT_KEY", '234234' )
             || XMLELEMENT ( "CAP_TOTAL_KEY", '234234' )
             || XMLELEMENT ( "CAP_ENTITY", '234234' )
             || XMLELEMENT ( "CAP_LAST_RESET_DATE", '234234' )
             || XMLELEMENT ( "CAP_LIMIT", '234234' )
             || XMLELEMENT ( "CURRENT_CNT", '234234' )
             || XMLELEMENT ( "DATE", sysdate)
             || XMLELEMENT ( "CAP_PROFILE2TX_CAP_PROFILE", '234234' )
             || XMLELEMENT ( "WHERE_CLAUSE", '234234' )
             || XMLELEMENT ( "PRODUCT2TX_PRODUCT", '234234' )
             || XMLELEMENT ( "PRODUCT_KEY", '234234' )
             || XMLELEMENT ( "PRODUCT_NAME", '234234' )
             || XMLELEMENT ( "RATE", '234234' )
             || XMLELEMENT ( "GL_NUMBER", '234234' )
             || XMLELEMENT ( "AP_NUMBER", '234234' )
             || XMLELEMENT ( "COMMISSIONS_NUMBER", '234234' )
             || XMLELEMENT ( "INVOICE_STATUS", 'NOTPROCESSED' )
             || XMLELEMENT ( "INVOICE_STATUS_DATE", sysdate)
        INTO audit_info
        FROM DUAL;

      x := x + 1;
   END LOOP;

END;

Using LEAD to determine if number is between rows

You can adapt this SQL to use any numeric colum from any table you have.

Notice the NVL funtion. We need this because there is no 'NEXT' number after
 the highest number, so a NULL is returned in that position.

here we are saying, "substitute the highest possible value if null is returned here".
This will make our BETWEEN logic work correctly.

SELECT portal_key,
nvl(lead (portal_key) over ( order by portal_key),99999999) as nextkey
FROM tx_portal
WHERE 4000 between portal_key and nextkey;

portal_key is any numeric

Single table row to XML CLOB using DBURITYPE

set long 2200

-- PORTAL_NAME is a unique column of table TX_PORTAL in the QSN_APP schema

SQL> select (dburitype.createuri('/QSN_APP/TX_PORTAL/ROW[PORTAL_NAME="MORTGAGE"]')).getclob() mydata from dual;

MYDATA
--------------------------------------------------------------------------------
<?xml version="1.0"?>
 <ROW>
  <PORTAL_KEY>1000</PORTAL_KEY>
  <PORTAL_NAME>MORTGAGE</PORTAL_NAME>
  <DESCRIPTION>Mortgage Portal</DESCRIPTION>
  <CREATEDDATE>17-NOV-04</CREATEDDATE>
  <CREATEDBY2TUSER>1250</CREATEDBY2TUSER>
  <UPDATEDDATE>20-OCT-05</UPDATEDDATE>
  <UPDATEDBY2TUSER>1250</UPDATEDBY2TUSER>
  <SUPPRESS_COMM_WHEN_NO_VENDORS>N</SUPPRESS_COMM_WHEN_NO_VENDORS>
  <TRACE_MATCH_PROCESS>Y</TRACE_MATCH_PROCESS>
  <TRACE_TABLE_MAX_ROWS>10000</TRACE_TABLE_MAX_ROWS>
  <EMAIL_ERRORS_TO>somebody@somewhere.com</EMAIL_ERRORS_TO>
  <FRONT_END_SPLIT>N</FRONT_END_SPLIT>
  <CATEGORY2TCODE>9261</CATEGORY2TCODE>
 </ROW>

CSV to XMLTYPE to TABLE

Take a CSV string and use it as SELECT criteria.

DECLARE
mystr             VARCHAR2 ( 2000 );
mycomma_list      VARCHAR2 ( 1000 ) := '1000,999,0980,49494';

BEGIN
mystr := '<list><x>' || REPLACE ( mycomma_list, ',', '</x><x>' ) || '</x></list>';

FOR cur2 IN
  ( SELECT portal_name
  FROM tx_portal
  WHERE portal_key IN (
        SELECT TO_NUMBER ( EXTRACTVALUE ( COLUMN_VALUE, '/x' ) )
        FROM TABLE ( SELECT XMLSEQUENCE ( EXTRACT ( XMLTYPE.createxml ( mystr )
        , '/list//x' ) )
       FROM DUAL ) ) )
LOOP
DBMS_OUTPUT.put_line ( 'Got ' || cur2.portal_name );
END LOOP;
END;