COUNT(*) or COUNT(1) or COUNT(anything else)…

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.

🙂

How To Remove Unnecessary Notes Types From Email Center

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:

Define new object type

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:

Define note type mapping

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:

Long list of notes types in Email Center

to this:

Short list of notes types in Email Center

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.

By continuing to use the site, you agree to the use of cookies. more information

In common with almost all professionally run websites, this website logs the IP address of each visitor in order to keep it running reliably. This is also essential for protecting the website and its visitors from malicious attacks, including infection with malware.

This website provides information as a service to visitors such as yourself, and to do this reliably and efficiently, it sometimes places small amounts of information on your computer or device (e.g. mobile phone). This includes small files known as cookies. The cookies stored by this website cannot be used to identify you personally.

We use cookies to understand what pages and information visitors find useful, and to detect problems such as broken links, or pages which are taking a long time to load.

We sometimes use cookies to remember a choice you make on one page, when you have moved to another page if that information can be used to make the website work better. For example:
- avoiding the need to ask for the same information several times during a session (e.g. when filling in forms), or
- remembering that you have logged in, so that you don’t have to re-enter your username and password on every page.

You can prevent the setting of cookies by adjusting the settings on your browser (see your browser Help for how to do this). Be aware that disabling cookies will affect the functionality of this and many other websites that you visit.

Close