February 7th, 2011 Matt
It should be a fairly simple task – count the number of rows in a table. I recently had the “pleasure” of discussing this with a colleague who was adamant that the best way to count the number of rows in a table was to use something explicit, e.g. COUNT(1) or “even better” COUNT(ROWID).
The basis for his thinking seemed to be OCP Introduction to Oracle 9i: SQL Exam Guide which apparently says something like
Do not use count(*) to determine the number of rows in a table. Use count(1) or count(ROWID) instead. These options are faster because they bypass some unnecessary operations in Oracle’s SQL pocessing mechanism.
Now, I’ve been told both things in the past – I was always taught to use COUNT(*), but some people that I’ve worked with have always used COUNT(0) or similar. So, as with all these kinds of questions, I turned to Tom Kyte for more information…
Looking at a number of questions and answers on the AskTom site, this seems to have been done to death, so I’ll just post a link to what appears to be a definitive couple of answers: here and here.
As ever, there are a number of neat points made in the threads, but for those that don’t want to read through them all, here’s my concise summary:
- COUNT(1) is optimised to COUNT(*) in the database.
- COUNT(*) is meaningful (it shows we are counting everything), whereas COUNT(rowid) means nothing.
- COUNT(*) is the right way to go.
- This remains true for any version of the database greater than 8.
- This remains true regardless of the column types in the table
- Matt was right.
Posted in Oracle, Technical | No Comments »
February 1st, 2011 Matt
Within Oracle Email Center, there is the facility to associate an incoming email to either a new or existing service request. When you are manually creating the association between the two, the standard Email Center screens (via Email Center Agent Console) allow the user to create new notes against the service request. By default, however, the list of available notes types probably doesn’t match the ones that you really want the agents to use.
I was working recently with a client on an upgrade from 11.5.9 to Release 12, which also throws up an anomaly – there are some notes types defined in an AOL lookup where the application ID is set to CRM Foundation. This means that the list of notes types available now includes some which you cannot query in any other form in the eBusiness Suite.
So, firstly, we need to make those obsolete, incorrect notes types visible to the lookup screen, so that at least we are aware that there are additional notes types that are in the system – if we can’t find them, we can’t exclude them!
The way that this needs to be done is documented in Metalink note 813677.1. The note instructs you to delete the notes from the system, but I am always somewhat wary of doing this, in case at some stage they were available and have been used. Before doing anything with the values, I would recommend that they are backed up into a custom table, just in case we need to revert them at any stage:
CREATE TABLE xx_obsolete_lookups_backup AS
SELECT *
FROM FND_LOOKUP_VALUES
WHERE lookup_type = 'JTF_NOTE_TYPE'
AND view_application_id != 0
/
Instead of removing them completely, run the following SQL which makes them visible but are clearly obsolete:
UPDATE FND_LOOKUP_VALUES
SET view_application_id = 0
, meaning = 'OBSOLETE - '||meaning
WHERE lookup_type = 'JTF_NOTE_TYPE'
AND view_application_id != 0
/
Now, if you navigate to the AOL lookups for the JTF_NOTE_TYPE lookup, the list is now at least complete. Ideally, you would then just be able to end-date or disable the lookups, but those fields are protected against update. So, the next step is to create an exclusion list of notes types that we aren’t interested in.
Using the CRM Administrator responsibility, navigate to Task and Escalation Manager > Setup > Object Meta-data and create a new Object Type which will store the list of notes that we aren’t interested in.
For example, I use a name “Unwanted Notes Bin” and code “XX_UNWANTED_NOTES”. Set the Application to “CRM Foundation” and (this is the important bit), on the Usages tab, ensure that there is a record for object user is “NOTES”, as per the following example:
At this point, depending on how many notes types you want / need to exclude, we hit the really dull part of the configuration – map the notes you want to exclude to the new object type. Again using the CRM Administrator responsibility, navigate to Notes Setup > Source and Note Type Mapping. Create new records for each note type you wish to exclude – the Source Object should be the name of the object type you have just created, and then include the note type you wish to exclude. The screenshot below shows my example exclusion list:
If you are doing an upgrade and had to run the SQL at the start of this post, ensure that you add the notes which begin OBSOLETE to the list as well.
Once you have completed the list and saved it, when you next try to add a note to a service request from an email, the list of note types should be what you want to see. The two screens below show how the list now changes from this:
to this:
The list of note types available is now governed in two ways. Firstly, all note types which exist in the custom object type we created here are now excluded. Secondly, all note types which have been mapped to the Service Request object type will be enabled. If there are conflicts between the two of these, then the second takes priority over the first.
Posted in Functional, Oracle | No Comments »
