PL/SQL

September 22, 2007

I Give Up! Goodbye WordPress hello Blogger.

Filed under: PL/SQL — Michael Moore @ 7:35 pm

After countless hours of trying to get code samples from TOAD to WordPress without corruption, I give up!

I will be migrating my articles from WordPress to http://plsqlnotes.blogspot.com

If you are interested in my TOAD to Blogspot solution,  give a comment at Blogspot.
Thanks for all the visits.

Mike

Advertisements

August 20, 2007

PL/SQL Package Structures

Filed under: example, package, package structure, PL/SQL — Michael Moore @ 12:35 am
I'VE MOVED to BLOGSPOT. This link will take you directly to the article on PL/SQL Package Structures

http://plsqlnotes.blogspot.com


	

June 5, 2007

Application Cache Refresh Call-back Trigger

Filed under: HTTPURITYPE, PL/SQL, trigger, UTL_HTTP, UTL_HTTP.set_transfer_timeout — Michael Moore @ 10:41 pm

This article has move to

http://plsqlnotes.blogspot.com/

May 30, 2007

Using Model for generating CSV by Partition

Filed under: CSV, example, MODEL, PL/SQL, SQL — Michael Moore @ 5:48 pm

This article has moved to

plsqlnotes.blogspot.com

May 15, 2007

Incremential elimination using predicate negation

Filed under: PL/SQL — Michael Moore @ 4:28 am

This article has moved to

Mike’s PL/SQL Notes

May 7, 2007

Which where-clause predicates were false?

Filed under: PL/SQL — Michael Moore @ 1:53 am

This may be obvious to most people who read this blog but I’m going to post it anyway.

Let’s suppose you have a requirement to match organ donors to potential recipients. Each recipient has a complex set of requirements. For recipient 555 it has to be a liver, and the blood type must be ABNeg and the donor must be less than 60 years old or could be less than 70 if he was a non drinker. You decide to express the criteria as a table of predicates and operators which can be constructed into a WHERE clause. For example, WHERE organ = ‘liver’ and blood_type = ‘AB-” and (age < 60 or (age < 70 and drinker = ‘No’ ))

When a new organ comes in, an organ record is written to the t_organ table and assigned a unique sequential numeric key.

Your program iterates through the the recipients table and tests each recipient record against the organ record to see if there is a match. The recipient record has 2 columns, recipient_id and criteria_nested_table. The criteria_nested_table column contains Boolean criteria and operators that will allow you to construct a where clause at run time.
Your pseudo code looks something like:

  • Get new donor record
  • For each recipient record
  • construct the where_clause using the recipient’s criteria records
  • Using dynamic SQL, execute recipient.where_clause against donor record.
  • If matched, celebrate else try next recipient

A month later, your program is working perfectly and donors are being matched with recipients. Then, one day a doctor asks you, “Why didn’t recipient 123 match with donor record number 789”? You don’t know exactly which criteria cause the no-match situation. So, now you need to enhance the system.

You think about it for a while and conclude that, if you need to determine which criteria did not match, you will need to test each criteria independently. That means executing a where clause for each predicate.

Instead of SELECT ‘MATCHED’ from t_organ where key_id = 8373 and organ = ‘liver’ and blood_type = ‘AB-‘;

You will now need to generate and execute

SELECT ‘MATCHED’ from t_organ where key_id = 8373 and organ = ‘liver’;

SELECT ‘MATCHED’ from t_organ where key_id = 8373 and blood_type = ‘AB-‘;

If all of the generated SELECT statements match, then the recipient matches the donor and you will be able to record which criteria, if any, failed.

But, the criteria is actually much more complex and sometimes involves 20 predicates. That would mean 20 SELECT statements to get the job done. Performance is already a problem, so clearly, this is never going to work. Then it hits you like a ton of lead bricks …. the CASE statement! What if I move my where_clause into a CASE statement.

The following example shows where I am going with this.

drop table t;
create table t (thekey integer,a integer,b integer,c integer, d varchar2(20));
insert into t values (10,1,2,3,’this’);
insert into t values (20,1,2,4,’that’);
insert into t values (30,1,3,5,’same’);
insert into t values (40,2,2,3,’other’);
commit;

— THE OLD WAY (do the same for thekey =20,30,40 also)

select ‘matched’

from t where thekey = 10 and a = 1 and b = 2 and (c = 3 or d = ‘same’);

if rec_not_found then no match

— THE NEW WAY (do the same for thekey =20,30,40 also)

select thekey,
case when a = 1 then ‘TRUE’ else ‘FALSE’ end a,
case when b = 2 then ‘TRUE’ else ‘FALSE’ end b,
case when c = 3 or d = ‘same’ then ‘TRUE’ else ‘FALSE’ end c_or_d,
case when a = 1 and b = 2 and (c = 3 or d = ‘same’) then ‘TRUE’ else ‘FALSE’ end overall
from t

where thekey = 10;

If overall = ‘FALSE’ then no match.

So, now I can easily record which predicates were true and which were false without making multiple trips to the database. Note that I don’t try to split out the OR’d criteria, but treat it as a single predicate. Fortunately, this is good enough.

April 16, 2007

Analytics Performance Comparison

Filed under: ANALYTICS, example, execution plan, explain plan, partitioned by, performance, PL/SQL, row_number, SQL — Michael Moore @ 9:01 pm

In this case Analytic SQL ran 99% faster than a more traditional approach.
The SQL problem being solved in the examples is getting the last 3 of each group.

This is my first attempt at using images for this blog. If you come to the web site, the images appear
somewhat reasonable sized. If you are viewing this through a feed reader, the images may appear
to be gigantic. I am thinking I will avoid images in the future.
I don’t know if this example really proves anything except that under these circumstances,
the analytics query is much faster than the traditional query.
If you think these results are atypical, or have any comments on this example, please leave a comment.
Also, ignore the /*xyzzy03*/ in the example; it is just a comment that I forgot to strip out.

page1

page2

page3

page6

April 3, 2007

Using MODEL to generate a sequence of numbers

Filed under: example, PL/SQL, pl/sql code — Michael Moore @ 9:12 pm

This article has moved to

Mike’s PL/SQL Notes

April 2, 2007

Using Analytics to select by priority and weight

Filed under: example, PL/SQL, pl/sql code, SQL — Michael Moore @ 7:28 pm

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

March 25, 2007

Using a Function Based Index to enforce complex unique constraints.

Filed under: CONSTRAINT, example, FBI, PL/SQL, pl/sql code — Michael Moore @ 8:20 pm

This article has moved to plsqlnotes.blogspot.com

Older Posts »

Create a free website or blog at WordPress.com.