Re: Queries returning large number of rows
Is your "like 'whatever%'" clause large enough? Using too few characters may make the query engine decide to ignore the index.
For instance, 'abc%' is more generic the 'abcdefgh%'. If your data has many rows beginning with 'abc', the optimizer may not choose the index over a table scan.
Also, you may see better performance for a minimally represented prefix, and poorer performance for a more common prefix.
This is a common issue that arises with the "like" clause.
Any index you create (even one with maximum cardinality) won't help much if you ask it to process millions of records. If all of your data is available in your index, the scan takes place against the index instead of the table itself, but it's still a scan.
With that in mind, you might try reducing the prefixes in your index to increase the key density. (Do you really need 62 bytes if you use fewer than that?) This may yield a modest performance gain. But again, counting many rows takes time.
I hope this helps.
Subject
Views
Written By
Posted
5500
May 22, 2006 06:21AM
1892
May 22, 2006 09:04AM
1726
May 22, 2006 10:28AM
Re: Queries returning large number of rows
1747
May 23, 2006 07:13AM
1775
May 23, 2006 01:02PM
1558
May 24, 2006 07:08AM
1500
May 24, 2006 09:19AM
1645
May 24, 2006 10:18AM
1715
May 24, 2006 11:53AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.