Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
In Oracle, can one calculate statistics on non indexed columns
24 views
Skip to first unread message
zigz...@yahoo.com
Jun 24, 2012, 10:37:57 PM6/24/12
to
1. I am learning SQL Server 2008. I see that sql server calculates statistics on non indexed columns as well and makes use of such statistics in determining best query plan. I also see something like this in Teradata. Does Oracle do anything like that, if not why not?
2. Another thing I noticed that SQL Server calculates string statistics, i.e., for a varcahr column statistics on all substrings in that column. This statistics is useful when where clause uses LIKE:
name like ‘%Christ%’. Is there anything like in Oracle?
2. Another thing I noticed that SQL Server calculates string statistics, i.e., for a varcahr column statistics on all substrings in that column. This statistics is useful when where clause uses LIKE:
name like ‘%Christ%’. Is there anything like in Oracle?
zigz...@yahoo.com
Jun 24, 2012, 10:54:09 PM6/24/12
to
https://forums.oracle.com/forums/thread.jspa?threadID=2268501
dombrooks
Jun 25, 2012, 4:22:30 AM6/25/12
to
1. Yes - see user_tab_col_statistics and if using histograms then user_histograms. To what extent histograms are calculated will depend on how you gather statistics.
2. The best performing solution for searching for substrings with both leading and trailing wildcards will usually be an Oracle Text index otherwise a search on something like ‘%Christ%’ cannot efficiently use a index to identify such rows.
See http://docs.oracle.com/cd/E11882_01/text.112/e24435/toc.htm
2. The best performing solution for searching for substrings with both leading and trailing wildcards will usually be an Oracle Text index otherwise a search on something like ‘%Christ%’ cannot efficiently use a index to identify such rows.
See http://docs.oracle.com/cd/E11882_01/text.112/e24435/toc.htm
zigz...@yahoo.com
Jun 25, 2012, 7:03:20 AM6/25/12
to
On Sunday, June 24, 2012 10:37:57 PM UTC-4, (unknown) wrote:
Micsrosoft sql server also has text indexes. But string statistics is for normal indexs, as for as I know.
Mladen Gogala
Jun 25, 2012, 9:07:27 AM6/25/12
to
On Mon, 25 Jun 2012 04:03:20 -0700, zigzagdna wrote:
> Oracle text indexes are different than normalOracle indexes. One does
> not really use LIKE instead uses contains.Also syntax for setting text
> indexes is different from normal index.
>
> Micsrosoft sql server also has text indexes. But string statistics is
> for normal indexs, as for as I know.
You can also use open source text indexing software like Lucene or
> Oracle text indexes are different than normalOracle indexes. One does
> not really use LIKE instead uses contains.Also syntax for setting text
> indexes is different from normal index.
>
> Micsrosoft sql server also has text indexes. But string statistics is
> for normal indexs, as for as I know.
Sphinx, which have a myriad of options, work with any ODBC compliant
database and are, generally speaking, much more advanced than the
database specific text indexing software.
Sub-string analysis looks like a brain-dead feature which would slow down
statistics gathering, without a real reason, because neither B*Tree
indexes nor has indexes can be used for resolution of LIKE '%COND%'
condition.
--
http://mgogala.byethost5.com
0 new messages