Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

function based indexes

9 posts in Product Futures Discussion Last posting was on 2002-03-09 04:00:01.0Z
Pablo Sanchez Posted on 2002-01-12 22:57:17.0Z
From: "Pablo Sanchez" <pablo@dev.null>
Subject: function based indexes
Date: Sat, 12 Jan 2002 15:57:17 -0700
Lines: 18
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <9Ew4t07mBHA.297@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:941
Article PK: 95181

Howdy,

I would like to suggest that Sybase ASE support function based
indexes.

An example of such a beast would be to create an index where we
UPPER'd the 'last_name' column in an employee table. The data would
be entered in mixed case and querying would be supported provided that
the 'last_name' columnn was UPPER'd in the WHERE clause.

Thx!
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Independent Contractor, available for short-term and long-term
contracts


Yiwen Posted on 2002-03-08 15:06:55.0Z
From: "Yiwen" <huangy@sybase.com>
References: <9Ew4t07mBHA.297@forums.sybase.com>
Subject: Re: function based indexes
Date: Fri, 8 Mar 2002 10:06:55 -0500
Lines: 28
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
Message-ID: <0ES7b3sxBHA.304@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 10.22.91.149
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:721
Article PK: 94260

Hi Pablo,
We are actively working on this. We would like to
collect more inputs from the customers...especially with some real world
application examples.

Thanks

Yiwen

> I would like to suggest that Sybase ASE support function based
> indexes.
>
> An example of such a beast would be to create an index where we
> UPPER'd the 'last_name' column in an employee table. The data would
> be entered in mixed case and querying would be supported provided that
> the 'last_name' columnn was UPPER'd in the WHERE clause.
>
> Thx!
> --
> Pablo Sanchez, High-Performance Database Engineering
> www.hpdbe.com
> Independent Contractor, available for short-term and long-term
> contracts
>
>


Matt Rogish Posted on 2002-03-08 18:31:57.0Z
From: "Matt Rogish" <matt@fanhome.com>
References: <9Ew4t07mBHA.297@forums.sybase.com> <0ES7b3sxBHA.304@forums.sybase.com>
Subject: Re: function based indexes
Date: Fri, 8 Mar 2002 13:31:57 -0500
Lines: 48
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <1Ppp7#sxBHA.214@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: ip134-053-064-186.s64.muohio.edu 134.53.64.186
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:720
Article PK: 94247

Our application requires a username and password to log in (stored in a
'users' table as a varchar for each one). We have indexed username
obviously, but without changing the case-sensitivity of the whole server we
can't perform a case-insensitive search -- we have to continually remind the
users that 'BoB' is different than 'BOB' or 'bob'. We could always upon
insertion UPPER or LOWER the username except the username is used for
display purposes and having user-supplied 'Matt' turned into 'MATT' or
'matt' is poor from a display standpoint.

I'm considering creating another column for 'auth_name' which basically
stores the lower or upper'd name and is used for authentication only, but it
would be nice if we could create an index upon the UPPER or LOWER (as Pablo
said) so that it would be transparent -- even if I had to 'force' the
uppere'd index in the query it would be far preferable to maintaining a
redundant varchar column or messing up the display.

Thanks,

--
Matt

"Yiwen" <huangy@sybase.com> wrote in message
news:0ES7b3sxBHA.304@forums.sybase.com...
> Hi Pablo,
> We are actively working on this. We would like to
> collect more inputs from the customers...especially with some real world
> application examples.
>
> Thanks
>
> Yiwen
>
> > I would like to suggest that Sybase ASE support function based
> > indexes.
> >
> > An example of such a beast would be to create an index where we
> > UPPER'd the 'last_name' column in an employee table. The data would
> > be entered in mixed case and querying would be supported provided that
> > the 'last_name' columnn was UPPER'd in the WHERE clause.
> >
> > Thx!
> > --
> > Pablo Sanchez, High-Performance Database Engineering
> > www.hpdbe.com
> > Independent Contractor, available for short-term and long-term
> > contracts


Pablo Sanchez Posted on 2002-03-08 21:31:19.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <9Ew4t07mBHA.297@forums.sybase.com> <0ES7b3sxBHA.304@forums.sybase.com>
Subject: Re: function based indexes
Date: Fri, 8 Mar 2002 14:31:19 -0700
Lines: 20
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <IcUJSmuxBHA.318@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:717
Article PK: 94248


"Yiwen" <huangy@sybase.com> wrote in message
news:0ES7b3sxBHA.304@forums.sybase.com...
> Hi Pablo,
> We are actively working on this. We would like to
> collect more inputs from the customers...especially with some real
world
> application examples.

Hurrah! At the risk of biting the hand that feeds me, please take a
look at what Oracle offers. It's a good starting point.

We use a lot of UPPER function based indexes to facilitate formal name
searches as well as pull-down values.
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Available for short-term and long-term contracts


Sethu Posted on 2002-03-09 03:17:38.0Z
Message-ID: <3C897ED2.CD003013@sybase.com>
Date: Fri, 08 Mar 2002 19:17:38 -0800
From: Sethu <sethu@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: function based indexes
References: <9Ew4t07mBHA.297@forums.sybase.com> <0ES7b3sxBHA.304@forums.sybase.com> <IcUJSmuxBHA.318@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 27
NNTP-Posting-Host: 10.22.91.118
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:714
Article PK: 94240

We donot want to do what Oracle has done. I mean the way they have
implemented. We want to do better and make it useable. This feature
of Oracle is useable for that matter, function indexes orgeneric indexes
are useable in any applications.

Just curious, can you tell me what percentage of functionality that
is there in Oracle do you use in your company ?

Cheers,
Sethu

Pablo Sanchez wrote:
>
> "Yiwen" <huangy@sybase.com> wrote in message
> news:0ES7b3sxBHA.304@forums.sybase.com...
> > Hi Pablo,
> > We are actively working on this. We would like to
> > collect more inputs from the customers...especially with some real
> world
> > application examples.
>
> Hurrah! At the risk of biting the hand that feeds me, please take a
> look at what Oracle offers. It's a good starting point.
>
> We use a lot of UPPER function based indexes to facilitate formal name
> searches as well as pull-down values.
> --
> Pablo Sanchez, High-Performance Database Engineering
> www.hpdbe.com
> Available for short-term and long-term contracts


Pablo Sanchez Posted on 2002-03-09 04:00:01.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <9Ew4t07mBHA.297@forums.sybase.com> <0ES7b3sxBHA.304@forums.sybase.com> <IcUJSmuxBHA.318@forums.sybase.com> <3C897ED2.CD003013@sybase.com>
Subject: Re: function based indexes
Date: Fri, 8 Mar 2002 21:00:01 -0700
Lines: 35
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <9v2Ld$xxBHA.304@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:712
Article PK: 94238


"Sethu" <sethu@sybase.com> wrote in message
news:3C897ED2.CD003013@sybase.com...
> We do not want to do what Oracle has done. I mean the way they have
> implemented.

I agree. <g>

> We want to do better and make it useable.

Their implementation is very useable. I'm not trying to defend Oracle
however just stating a fact.

> This feature
> of Oracle is useable for that matter, function indexes orgeneric
indexes
> are useable in any applications.
>
> Just curious, can you tell me what percentage of functionality that
> is there in Oracle do you use in your company ?

Perhaps we should take this offline ... I'll send you a personal
email and we can go from there... or I can call you.

As a side note, HPDBE is DBMS agnostic and HPDBE is my company. The
'other' company that I work at uses Oracle. So many databases, so
little time! :)

Thx!
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Available for short-term and long-term contracts


Roger Broadbent Posted on 2002-01-15 11:11:09.0Z
From: "Roger Broadbent" <RBroadbent@wilco-int.com>
References: <9Ew4t07mBHA.297@forums.sybase.com>
Subject: Re: function based indexes
Date: Tue, 15 Jan 2002 11:11:09 -0000
Lines: 34
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <XlWsZYbnBHA.266@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: wilcohost-180.wilco-int.com 212.36.174.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:931
Article PK: 95171

If you needed to, you could already do this, simply by adding a column that
was updated to the value you require by a trigger on the table, then
indexing the column. Granted there is the overhead of storing and
maintaining this extra column, but given this, is it really worthwhile for
Sybase to provide another way of achieving the same end?

--
Roger Broadbent
Technical Consultant
Wilco International Ltd

Pablo Sanchez <pablo@dev.null> wrote in message
news:9Ew4t07mBHA.297@forums.sybase.com...
> Howdy,
>
> I would like to suggest that Sybase ASE support function based
> indexes.
>
> An example of such a beast would be to create an index where we
> UPPER'd the 'last_name' column in an employee table. The data would
> be entered in mixed case and querying would be supported provided that
> the 'last_name' columnn was UPPER'd in the WHERE clause.
>
> Thx!
> --
> Pablo Sanchez, High-Performance Database Engineering
> www.hpdbe.com
> Independent Contractor, available for short-term and long-term
> contracts
>
>


Pablo Sanchez Posted on 2002-01-15 18:12:55.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <9Ew4t07mBHA.297@forums.sybase.com> <XlWsZYbnBHA.266@forums.sybase.com>
Subject: Re: function based indexes
Date: Tue, 15 Jan 2002 11:12:55 -0700
Lines: 36
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <8nbE3DfnBHA.190@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:926
Article PK: 94452

[ Reposted - original seems to have not made it?? ]

"Roger Broadbent" <RBroadbent@wilco-int.com> wrote in message
news:XlWsZYbnBHA.266@forums.sybase.com...
> If you needed to, you could already do this, simply by adding a
column that
> was updated to the value you require by a trigger on the table, then
> indexing the column. Granted there is the overhead of storing and
> maintaining this extra column, but given this, is it really
worthwhile for
> Sybase to provide another way of achieving the same end?

The above solution is the 'old' method to solve the problem function
based problem. The issue though, as you start to point out, is that
it requires a lot more extra work to support and run it. Minimally,
we're looking at:

* Additional code at the PL/SQL and trigger level
* Maintenance support
* Storage space
* Additional log data (bad in a high-performance situation)

Looking at the cost-analysis, I'd rather invest my developer budget
on:

* create index

Nice and clean.
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Independent Contractor, available for short-term and long-term
contracts


Pablo Sanchez Posted on 2002-01-15 16:17:57.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <9Ew4t07mBHA.297@forums.sybase.com> <XlWsZYbnBHA.266@forums.sybase.com>
Subject: Re: function based indexes
Date: Tue, 15 Jan 2002 09:17:57 -0700
Lines: 36
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <6NWwnDenBHA.317@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:928
Article PK: 95170


"Roger Broadbent" <RBroadbent@wilco-int.com> wrote in message
news:XlWsZYbnBHA.266@forums.sybase.com...
> If you needed to, you could already do this, simply by adding a
column that
> was updated to the value you require by a trigger on the table, then
> indexing the column. Granted there is the overhead of storing and
> maintaining this extra column, but given this, is it really
worthwhile for
> Sybase to provide another way of achieving the same end?

The above solution is the 'old' method to solve the problem function
based problem. The issue though, as you start to point out, is that
it requires a lot more extra work to support and run it. Minimally,
we're looking at:

* Additional code at the PL/SQL and trigger level
* Maintenance support
* Storage space
* Additional log data (bad in a high-performance situation)

Looking at the cost-analysis, I'd rather invest my developer budget
on:

* create index

Nice and clean.
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Independent Contractor, available for short-term and long-term
contracts