Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Dec 2004
    Posts
    3

    Unanswered: REPLACE() function slooooows down the query

    I need to be able to use the functionality of REPLACE, however it causes my indexes to be skipped resulting in previously subsecond response to be increased to a very long time - hours even. Any ideas to alternatives or tweaks?

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Could you show the query, especialy the where clause. Only a replace in the where clause will cause the indexes to be skipped.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Dec 2004
    Posts
    3
    Select /*+ Index(cm Cm_idx) */ A.last_name
    , A.postal
    , A.address1
    , ' '
    , ' '
    , ' '
    , ' '
    From Myview A
    Where Replace(a.postal, ' ', '') Like '70002%' And A.last_name Like 'm%'

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by mtanq
    Select /*+ Index(cm Cm_idx) */ A.last_name
    , A.postal
    , A.address1
    , ' '
    , ' '
    , ' '
    , ' '
    From Myview A
    Where Replace(a.postal, ' ', '') Like '70002%' And A.last_name Like 'm%'
    On the base table of the view that a.postal comes from (for example, lets say its customer) create a function based index.

    create index customer_i1 on customer(Replace(postal, ' '));

    You do not need the third parameter in replace if you are replacing with null. What this will do is use the new function based index whenever you have the same function call in your where clause. The call must be the exact same, so your where clause would be.

    here Replace(a.postal, ' ') Like '70002%' And A.last_name Like
    Last edited by beilstwh; 12-09-04 at 17:48.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Dec 2004
    Posts
    3
    Thanks for the suggestion. I had the DBA add the index. I still get the same result. Here is the Explain Plan Without and With the REPLACE function in place. The only difference between the two plans is the use of REPLACE in the query, and a suggestion to use the new index. What I see is that the index we just added is not getting triggered. Is there some other tuning that I might have missed?


    WITHOUT REPLACE

    Operation Object Name Rows Bytes Cost

    SELECT STATEMENT Optimizer Mode=HINT: RULE 1 720
    TABLE ACCESS BY INDEX ROWID SYSADM.PS_BO_NAME 22 K 829 K 4
    NESTED LOOPS 1 195 720
    NESTED LOOPS 1 158 717
    NESTED LOOPS 97 11 K 521
    NESTED LOOPS 97 7 K 228
    TABLE ACCESS BY INDEX ROWID SYSADM.PS_CM 97 2 K 33
    INDEX RANGE SCAN SYSADM.PS_CM_SELENA 97 4
    INDEX RANGE SCAN SYSADM.WADBA_PS_BO_CM_IX2 1 55 3
    TABLE ACCESS BY INDEX ROWID SYSADM.PS_RD_PERSON 1 37 4
    INDEX RANGE SCAN SYSADM.PSDRD_PERSON 1 3
    INLIST ITERATOR
    INDEX RANGE SCAN SYSADM.WADBA_PS_BO_ROLE_IX1 15 K 563 K 3
    INDEX RANGE SCAN SYSADM.PS_BO_NAME 1 3



    WITH REPLACE

    Operation Object Name Rows Bytes Cost

    SELECT STATEMENT Optimizer Mode=HINT: RULE 1 12680
    NESTED LOOPS 1 195 12680
    NESTED LOOPS 1 167 12678
    HASH JOIN 31 3 K 12584
    INDEX FAST FULL SCAN SYSADM.WADBA_PS_BO_ROLE_IX1 15 K 563 K 6061
    HASH JOIN 7 K 674 K 6520
    TABLE ACCESS BY INDEX ROWID SYSADM.PS_BO_CM 7 K 402 K 455
    INDEX RANGE SCAN SYSADM.WADBA_PS_BO_CM_IX1 1 314
    TABLE ACCESS FULL SYSADM.PS_RD_PERSON 3 M 129 M 5757
    TABLE ACCESS BY INDEX ROWID SYSADM.PS_BO_NAME 1 37 4
    INDEX RANGE SCAN SYSADM.PS_BO_NAME 1 3
    TABLE ACCESS BY INDEX ROWID SYSADM.PS_CM 449 K 12 M 3
    INDEX UNIQUE SCAN SYSADM.PS_CM 20 2

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Your hint syntax is incorrect. It's an unfortunate (reliable?) situation with Oracle and hints.

    There are two problems....
    1: The index hint expect the table Alias as the first parameter
    2: The two parameters should be separated by a comma

    Your code...

    Quote Originally Posted by mtanq
    Select /*+ Index(cm Cm_idx) */ A.last_name
    From Myview A
    Where Replace(a.postal, ' ', '') Like '70002%' And A.last_name Like 'm%'
    Should be something like
    Code:
    select /*+ Index(a,Cm_idx) */
    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Avoid using such hints at all as a rule - they tie the optimizer's hands too much.

    To get the optimizer to use your new function-based index you also need to have some parameters set correctly:

    alter session set QUERY_REWRITE_ENABLED=TRUE;
    alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;

    You should also analyze your tables and use the CBO not the RBO.

  8. #8
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Tony, I have to disagree with you about avoiding "hints at all as a rule".

    That's too general as a statement. People will read that and start building theories around it.

    There are times when you will quite simply know far more about your data than Oracle ever will or possibly ever could. That isn't tieing the optimisers hands, it's freeing them.

    Regards
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by billm
    Hi,

    Tony, I have to disagree with you about avoiding "hints at all as a rule".

    That's too general as a statement. People will read that and start building theories around it.

    There are times when you will quite simply know far more about your data than Oracle ever will or possibly ever could. That isn't tieing the optimisers hands, it's freeing them.

    Regards
    Bill
    We'll have to agree to disagree! Rules, as they say, are for the guidance of wise men and the obedience of fools. I think more harm is done by beginners thinking tuning = hinting than by them not using hints. An experienced designer will know when to use them judiciously, and will ignore my "rule".

  10. #10
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Tony,

    Quote Originally Posted by andrewst
    <snip>I think more harm is done by beginners thinking tuning = hinting than by them not using hints. An experienced designer will know when to use them judiciously, and will ignore my "rule".
    On that we do agree, but that is not a problem of hints, that is a problem of people who don't understand the technology mis-using them. That could be said for just about any aspect of Oracle, or any other technology.

    There is a big difference between saying hints should be avoided which suggests there is a general problem with them, or saying if you don't understand the technology behind hints, you should avoid them.

    One of those statements is very wrong, the other very right.

    Regards
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    But actually, I think they should be avoided! Funnily enough, this old discussion of hints on Ask Tom has just come up again:

    Ask Tom.

  12. #12
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Tony,

    Thanks for the link, an interesting read.

    There is a lot to read in there, and you do have to read it all to get the full picture. His view appears to be that there are good and bad hints, and goes into explaining why he considers which hints to be of which type.

    He's not saying all should be avoided, he's saying there are good hints, bad hints and that bad hints should be a last resort. Ie, he's not making such an all encompassing statement as yours, which was my original point.

    On a side note, one of his arguments against hinting which I'm not sure I agree with is that where a new version comes out with better access paths (he quotes the index skip scan as an example), a bad hint would preclude the newer CBO from using its newer features - meaning not using the most optimal path. I am sure that is happening out there, but there is also code which ran fine on 8i and went like a dog on 9i specifically due to these new features. There have been a number of posts about it in this forum. In some cases, a hint could have saved the SQL from the new CBO and that rare occurrence when it gets it wrong. I'd say 50/50 about the new version argument.

    I've just looked over one of my apps, it's showing 3 per 1000 sql statements hinted - that's not because I avoid them, but because I only use them where I think appropriate. I think that's generally in line with Tom Kyte's position (apart from possibly the good/bad list, but I do understand his reasoning behind it).

    Regards
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Great - "3 per 1000" sounds fine to me. You have in fact avoided using hints "as a rule", only using them in exceptional cases. On that basis, it would be wrong to use hints 99.7% of the time - i.e. almost always!

    But for many newbies, the thinking goes: "hey, the optimizer didn't use my index! Better add a hint quick!" Never mind that they didn't gather stats, or that the optimizer was simply right not to use it, or whatever. They spend all their time patching bad database designs and bad queries with more and more hints, when instead they should be learning about how to design better databases and queries.

  14. #14
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    No, because I only used something 0.3% of the time doesn't mean I avoid it, it means I use it when appropriate, not necessarily as a last resort. There is quite a difference to the casual reader. Rhetorical - how many times do you use extproc? Is that because you actively avoid it?

    Your thoughts about newbies and poor design I've already agreed with. I've noticed that some newbies have a habit of creating excessive numbers of indexes on single tables, rather than work on solid designs. I don't think they understand the implications, I guess on that basis you would say "all indexes should be avoided as a rule".

    I make heavy use of DBMS_ALERT, does that mean I encourage it?, no, it means I use it when I think appropriate. I hope I have the right judgement to decide when something is appropriate without being judged under a generalised catch all statement (which it now transpires was only meant for newbies).

    It seems we have generally similiar views as to when and when they aren't appropriate. but "they should be avoided" implies there is some fundamental problem with them, which there isn't.

    Incidentally, I notice that in both 8i and 9i, Oracle make considerable use of hints for their data dictionary views.

    Anyway, at this point I doubt we're contributing anything to the forum, email/pm may be more appropriate.

    Regards
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Take the original problem in this post:
    Code:
    Select /*+ Index(cm Cm_idx) */ A.last_name
    , A.postal
    , A.address1
    , ' '
    , ' '
    , ' '
    , ' '
    From Myview A
    Where Replace(a.postal, ' ', '') Like '70002%' And A.last_name Like 'm%'
    Edit/Delete Message
    That hint is a futile attempt to tell the optimizer to use the index that it can't use because of the REPLACE function.
    Later on it was used, equally unsuccessfully, to get the optimizer to use a function based index - when in fact, the reason it wasn't being used was most likely because the wrong QUERY_REWRITE_ENABLED and QUERY_REWRITE_INTEGRITY parameter values were in place.

    Most of the time hints are not the solution to the problem (with the exception of a few hints like FIRST_ROWS*). Their over-use can lead to poor performance since the optimizer is prevented from doing its job properly. You know that, which is why you have only used hints 3 times in 1000 queries. Lots of people don't, and I would discourage them from using hints.

    * Yes, perhaps I should have made it clear that by "avoid using hints" I meant the "bad" prescriptive hints like the INDEX hint being used in the original post.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •