-
I understood it as:
Searching for the entire name with an equality operator
-
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...
-
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
-
Do you have a function index on UPPER() columns? That can really speed things up.
-
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.
-
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 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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|