Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec

Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec

From: Mark Townsend <markbtownsend_at_comcast.net>
Date: Sun, 12 Feb 2006 18:50:03 -0800
Message-ID: <43EFF3DB.8030701@comcast.net>


Tony Rogerson wrote:

>>Can you show me an example of this ?

>
>
> This gives an explanation and example
> http://blogs.msdn.com/sqlclr/archive/2005/10/03/476661.aspx entitled
> 'Tutorial on SqlClr Computed Columns and Indexability'
>
> Make sure you read to the end, but for convience i will post the example
> here...
>
> using System;
> using Microsoft.SqlServer.Server;
>
> public class testclass
> {
> [SqlFunction(IsDeterministic=true, IsPrecise=true)]
> public static int clr_func(int i)
> {
> return i*i;
> }
> }
>
>
> create table clr_persisted_table
> (
> col int,
> comp_col as dbo.clr_udf(col) persisted
> ) go
> -- create clustered and nonclustered indexes on col and comp_col
> from clr_table
> create clustered index pri_idx on clr_persisted_table(col)
> go
> create nonclustered index sec_idx on clr_persisted_table(comp_col)
> go

This example is a little trite, in that the function used returns a known data type - int. So presumably the optimizer can gather stats on this column (and index) as it would any other int column in the database - there are a known range of values, after all. Also, the column (in this example at least), has been presisted in the table.

My question was a little more complicated than that - an index on a column that is computed using a regex expression. It's still deterministic, and can still be persisted. But now the optimizer has a much harder job to gather and evaluate a range of stats. Received on Sun Feb 12 2006 - 20:50:03 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US