Re: SELECT statement efficiency question

On Sat, 07 Apr 2007 22:52:08 -0500, Dereck L. Dietz wrote:

> I have the following two select statements in some code I've been
> looking at. I don't have
> access to a computer with Oracle on it currently so I can't determine
> this myself.

Interesting situation.

>
> Both selects do the same thing. The function in the second version
> simply checks the
> condition what is in the first and send back a 'Y' or 'N' whereas the
> first has the
> check in the where clause itself.
>
> My question is this: is any one of the two selects more efficient (or
> less efficient)
> than the other?
>
>
> SELECT a.mbr_dob,
> a.mbr_first_nm,

.....
The question here is what is more efficient: a "not in" expression that clearly cannot use an index or the same thing wrapped up in a function, with a possible use of the functional bitmap index. This question deserves a closer look. First, if the table is a target for frequent DML statements, bitmap indexes are out of the question. In this case, the second method is much less efficient because it cannot use an index and it has to execute PL/SQL code, something that the first method doesn't have to do. If it's a DW database and you can create bitmap indexes at will, then the second method will be faster. You can, however, create a hash cluster based on that function and speed the retrieval. What the closer look at the query reveals is a missing object. I don't know many people named "DATA" (even the Startrek NG character is a machine which doesn't run Windows) or "CORRECTION" and the columns like the first and last name are usually reserved for humans. Your data structure is trying to make distinctly inhuman entities look human. What you're probably looking for is another column. The real answer how to speed up this query is to index the remaining columns and make sure that the optimizer uses available indexes. Then, it will filter out the non-conforming results in a snap, if the result set is small enough.

-- 
http://www.mladen-gogala.com