Re: Experts plese help me..

Darren,

I suppose the real question is what Norazman means by "huge", but it sounds to me like it's soooooo big (or his processor is sooooooo slow) that he doesn't want to do it iteratively. If I were doing this I would at least try it the way you describe (using the linear search with a cursor) because it just may be fast enough. This would also give me something to which I can compare my other solution. From your response I'm getting the impression that you're thinking of processing all the rows in a single function call, and then stowing them in another table. At that point the job would be over. I, on the other hand, was envisioning a function which returns TRUE (found) or FALSE (not found), which would be called for some given "capital part number" each time a person looks up a part in an interactive fashion. I have worked on a couple of systems where one database contains most of the objects, but a good portion is contained in another database, so you need this kind of lookup scheme on a per-object basis. Maybe this isn't how it will be used, but if it is then he could code something like this:

FUNCTION IsItThere (cpNum IN VARCHAR2)
RETURN BOOLEAN
IS

    prefix VARCHAR(32) := 'CP#' || TO_CHAR(cpNum) || '%'; BEGIN
    select count(*) from the_table where the_column like prefix; EXCEPTION
    WHEN NODATAFOUND THEN
        return FALSE;
    END;
    return TRUE;
END IsItThere;

In such a situation I might just opt to normalize the database a bit.

Paul Cluiss
Dallas, Texas

Darren Brock wrote:

> Paul,
>
> select * from the_table where the_column like '%CP%';
> minus the first % is what I first considered but as you pointed out he
> needs a NOTFOUND flag which the above query does not return without then
> performing another action to determine which rows were not returned in
> the first query. In my response I did say that within the cursor he
> would have to check for the existence of CP# which you are doing with
> your query but based on whether or not CP# is found he could either
> return the row_ID or whatever he needs, or, a NOTFOUND flag as he sees
> fit. Agreed, if he is dealling with 50,000,000 rows a cursor may prove
> difficult but then again I got the impression that it was a lot but only
> by the standards that it was too much to do by hand and still be time
> efficient. Just by the fact he even thought about doing it by hand
> implies, to me anyway, that the number is not even close to 50,000,000
> but probably relativly small in terms of a database (i.e. < 100,000)
> which should not be much of a problem for a cursor.
>
> What do you think?
> Darren
>
> paul cluiss wrote:
> >
> > Darren,
> >
> > I think Norazman is saying that his table contains too many records for a
> > linear search to be practical, and wants instead a presumably faster SQL
> > search. I am guessing that he needs to perform this search interactively, so
> > searching all 50,000,000 (or however many) records might take too long. I
> > further speculate that if the string is not found in any record, then he needs
> > to return some sort of NOTFOUND flag, whereupon some alternative action is
> > taken.
> >
> > If these assumptions are correct, I propose this SQL statement:
> >
> > select * from the_table where the_column like '%CP%';
> >
> > Well, Norazman, what do you think?
> >
> > Paul Cluiss
> > Dallas, Texas
> >
> > Darren Brock wrote:
> >
> > > One way that you can do this is with a cursor. Loop through all the
> > > records and for each one, check to see if it contains CP# and if it
> > > does, perform a select and substring out the part that you want stuffing
> > > appropriate info into a temp table.
> > >
> > > Darren
> > >
> > > Norazman wrote:
> > > >
> > > > Hi all,
> > > > I have a description field which user use to enter some description.
> > > > However due to bad design (from the earlier designer), I need a way of
> > > > how I can figure this out.
> > > >
> > > > This field can contains any words but usually it was also use to key in
> > > > some Capital number.
> > > > It might be something like this:
> > > >
> > > > CP#4599 ACERPOWER 4100, 64MB SDRAM DIMM(upgradable to 384MB)
> > > >
> > > > However, I'm only interested on the Capital number not the nonsense
> > > > word.
> > > > Normally the format (the way usr keyed) in is 'CP% 999999'.
> > > >
> > > > The problem is, the numbers of record is so huge that I don't want to go
> > > > through it 1 by 1.
> > > > How can I do a SQL to return only whatever from this field that sounds
> > > > like 'CP% 999999' and if nothing match just
> > > > return N/A or whatever to indicate the search string is not exist.
> > > >
> > > > So far I can say around 65-70% of the field have this string.
> > > >
> > > > Thanks for your help.
> > > >
> > > > Azman
> > >
> > > --
> > > ***********************************************
> > > Darren Brock Oracle Developer/DBA
> > > Governet
> > > 208-522-1225
> > > brock_at_governet.net
> > > ***********************************************
>
> --
> ***********************************************
> Darren Brock Oracle Developer/DBA
> Governet
> 208-522-1225
> brock_at_governet.net
> ***********************************************
Received on Fri Nov 19 1999 - 11:28:15 CST