RE: Design question: too many SQLs differ by updated columns

Okay, first regarding the inserts, you'll want to be sure you're not overriding default values with specified nulls.  

Now if your updates are really only the permutations of the columns in order, I think the full enumerations is one less than 2 to the number of columns of each table per table. Oracle will process redo for columns changed to the current value, so that can be an expensive choice, plus any of them that are constrained by foreign keys will make that probe as well.  

There is some middle ground for groups of columns updated together, such that you might add in a few non-updated columns (avoiding foreign key constrained columns unless they are really updated) to get the total number of combinations down a bit. Understand from the git-go that this is a slippery slope and a possible win if the current number of parsed statements you have is actually causing slowness. You probably want to avoid prefetching the "not new" columns values, so the update statement would need to use nvl(:MRN,MRN) forms of the column names.  

I noticed you're using camel case, which I find error-prone and untypeable, but de gustibus non est disputandem.  

If the sheer size is a concern, and you're willing to comprehensively build a set of views and a tool to translate the queries written by developers and generated by programs into terse column names you can make things really small. It is pretty important that you have both gazintas and comzoutas tools (See Bodine, J.,CBS, 1964) so that people can write queries in user friendly names and query the terse names back out of v$sqltext in readible fashion. JL's chapter in Oak Tables tales has a seminal example of how silly it is to actually try to read queries written in machine generated table and column names (and they weren't even short!).  

It's not that hard to write such a tool, and if you're at least up to a release that supports updates through views you should be okay. Before you go to that work you should verify that the size is a problem.  

Regards,  

mwf  

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Yong Huang
Sent: Saturday, May 31, 2008 7:30 PM
To: oracle-l_at_freelists.org
Subject: Design question: too many SQLs differ by updated columns  

One schema in our 10.2.0.4 database have 40,000 SQLs in v$sql taking 1.5GB

sharable memory. A small number of them are due to not using bind variables.

But many are insert and update statements like this:  

UPDATE PATIENT SET MRN=:MRN,Last_Name=:Last_Name,First_Name=:First_Name,...

Occupation=:Occupation,Original_Reg_Date=:Original_Reg_Date,AD_Living_Will=: AD_Living_Will...  

Thousands of these SQLs differ by different columns in the SET clause. The

columns seem to be listed in their order in the patient table. Since one SQL

updates one set of columns, another updates another set, the permutation is

huge.  

I told the developer to consider changing the insert, listing all columns and

setting the inserted value to null if that column is missing. But for these

update statements we can't think of a good way to corect them. We either have

to redesign the data model, normalizing some columns into their own tables

(e.e. occupation shouldn't belong in patient table), or query the row first and

list all columns in the update set clause filling in the selected value for

those columns that don't need to be updated. The first method is not doable

financially and administratively. The second method raises buffer gets and

probably causes worse overall performance. I also thought of MERGE. But the app

already knows whether to insert or update rather than let the database decide.

Besides, it still has the column list problem in the update part of merge.  

Any comments are welcome.  

Yong Huang           

--

http://www.freelists.org/webpage/oracle-l    

--

http://www.freelists.org/webpage/oracle-l Received on Sun Jun 01 2008 - 20:06:41 CDT