DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Optimizing for unpredictable selectivity

  1. #11
    Join Date
    Mar 2002
    Posts
    534
    I understood it as:
    Searching for the entire name with an equality operator

  2. #12
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Quite possibly. I just wondered after his comment about Oracle Text and the fact we can't see the query, whether we were missing something and just making assumptions as a result. Thats why I asked.
    Assistance is Futile...

  3. #13
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Thank you all for your replies. Great discussion.

    Here's the idea:
    All of these tables are BIG. The smallest are names and clients. The really big tables are sandwiched beteen these and join by a single key. The criteria is generally an UPPER() and a LIKE() search on one of 3 name columns and a key id search on clients.

    I think I've tweaked the queries every way possible.

    The row stitching seems to be the real problem here. There's just so many rows to put together to end up with so few.

    I'm thinking this is the right time to migrate to a denormalized and pre-aggregated environment. So, yesterday/last night I created a de-normalized table. Seems too simple and wasteful, but queries are so much more efficient.

    Ken
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  4. #14
    Do you have a function index on UPPER() columns? That can really speed things up.

  5. #15
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    There is not much you can do, I was in a Peoplesoft CRM implementation a couple of years ago, the users were allowed to search by Name and Surname using LIKE, when they hit those popular Surnames (quite often since they are so popular) which returnes 250000 customers the response time was simply awful. Was full scan a better approach? Well no because the tables the query accesed had over 20 million rows.

    The fix? More criteria were forced and LIKE was got rid of. Entire Name or Surname must be entered.

  6. #16
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Pando, sounds like you and I have walked in the same mire...

    The query the app is running is limited to returning 1000 rows (rownum < 1001). That forces them to use client ID or some other criteria. The users quickly learn that 1000 Smiths does them no good.

    Yes, we did try Function Based Indexes. Some help, but not really the magic bullet in this case.

    Another DBA is actually implementing the denormalization solution with a materialized view. An absolutely great idea in this case. It refreshes from the mv logs and .. on demand, so we can regulate refreshes during peak usage. And, we can create all kinds of crazy indexes on the MV.

    This was a good exercise for all of us. Thank you all for your interest and input.

    Ken
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  7. #17
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    i still wish you share your query....just change the table name to EMP
    or DEPT or SAL. you made us all curious and thinking of it so much
    that we cannot sleep at night.
    Behind The Success And Failure Of A Man Is A Woman

Posting Permissions

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


Click Here to Expand Forum to Full Width