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.

Index only access

11 posts in Product Futures Discussion Last posting was on 2003-04-21 15:20:42.0Z
putnamr Posted on 2003-03-04 14:24:48.0Z
From: putnamr@river.it.gvsu.edu
Date: Tue, 4 Mar 2003 09:24:48 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Index only access
Message-ID: <1EA6C6E5BD913E44004F2CC285256CDF.004F2CCF85256CDF@webforums>
Lines: 17
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:985
Article PK: 95224

Hello,

Will Sybase investigate having a set option and/or forceable option at the
table level to specify that a table can only be accessed via a qualifying
index?

Example)
SET IXONLY ON
or
SELECT *
FROM A (IXONLY)

I think this could be a huge improvement on the optimization of some
queries such as temp tables and other tables that change from a small
number of rows to a large number quite frequently.

Thanks


Pablo Sanchez Posted on 2003-03-04 16:12:37.0Z
Subject: Re: Index only access
From: Pablo Sanchez <pablo@dev.null>
References: <1EA6C6E5BD913E44004F2CC285256CDF.004F2CCF85256CDF@webforums>
Organization: High-Performance Database Engineering
Message-ID: <Xns93345A48248A0pingottpingottbah@199.93.177.77>
User-Agent: Xnews/06.01.10
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Tue, 04 Mar 2003 11:12:37 -0500
Lines: 12
NNTP-Posting-Host: host-4-069.ionsky.com 66.35.44.69
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:984
Article PK: 95223

putnamr@river.it.gvsu.edu wrote in
news:1EA6C6E5BD913E44004F2CC285256CDF.004F2CCF85256CDF@webforums:

> Hello,
>
> Will Sybase investigate having a set option and/or forceable option
> at the table level to specify that a table can only be accessed via
> a qualifying index?

I'm not sure I follow you, why wouldn't you force the index in these
situations?
--
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com


putnamr Posted on 2003-03-08 17:18:52.0Z
From: putnamr@river.it.gvsu.edu
Date: Sat, 8 Mar 2003 12:18:52 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Index only access
Message-ID: <0891469D505645F2005F1CA985256CE3.005C463E85256CDF@webforums>
References: <1EA6C6E5BD913E44004F2CC285256CDF.004F2CCF85256CDF@webforums> <Xns93345A48248A0pingottpingottbah@199.93.177.77>
Lines: 9
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:989
Article PK: 95228

Hello,

Forcing an index is far worse than doing specifying that a table use an
index. Index names and ids can change throughout the life cycle of an
application. As well as, the most efficient index could change as well.

A table scan could also be more the best access in this case, but it could
also in a case where I force an index. Specifying IXONLY would only tell
the optimizer that it must use best qualifying index to access the table.


Eric Miner Posted on 2003-03-04 17:21:55.0Z
Message-ID: <3E64E0B3.BC8253A5@sybase.com>
Date: Tue, 04 Mar 2003 09:21:55 -0800
From: Eric Miner <eminer@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
To: putnamr@river.it.gvsu.edu
Subject: Re: Index only access
References: <1EA6C6E5BD913E44004F2CC285256CDF.004F2CCF85256CDF@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 22
NNTP-Posting-Host: 10.22.91.122
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:987
Article PK: 95226

Well, you can do this with an AP or a partial AP (via the 'plan' command).

Are you asking for a feature that allow a table to be accessed only via an
index, any index, or a specified index?

As you know there are times when accessing a table via any index could be less
efficient than a table scan.

Post some more details

Later,

Eric
Optimizer Group

putnamr@river.it.gvsu.edu wrote:

> Hello,
>
> Will Sybase investigate having a set option and/or forceable option at the
> table level to specify that a table can only be accessed via a qualifying
> index?
>
> Example)
> SET IXONLY ON
> or
> SELECT *
> FROM A (IXONLY)
>
> I think this could be a huge improvement on the optimization of some
> queries such as temp tables and other tables that change from a small
> number of rows to a large number quite frequently.
>
> Thanks


putnamr Posted on 2003-03-10 00:22:15.0Z
From: putnamr@river.it.gvsu.edu
Date: Sun, 9 Mar 2003 19:22:15 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Index only access
Message-ID: <EB033F32E61F09550002096A85256CE5.00669E5385256CDF@webforums>
References: <1EA6C6E5BD913E44004F2CC285256CDF.004F2CCF85256CDF@webforums> <3E64E0B3.BC8253A5@sybase.com>
Lines: 26
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:993
Article PK: 95234

Hello,

You bet...

Rather than forcing a particular index on a table which is dangerous as
well, I would like the ability to tell the optimizer that a index must be
chosen. The optimizer would not consider a table scan as part of the
costing of the table. It would merely determine the cheapest index and use
it. If an index does not exist on a table specified for IXONLY an error
message would be generated similar to if you actually forced an index which
does not exist.

This feature would be more useful than specifying a plan as index names and
ids and columns, etc., all change or have the potential to change during
the life cycle of an application. This should be less dangerous than using
forceplan or forcing an index.

Would be beneficial in cases where the optimizer is not choosing the most
efficient path, on tables that change in size from say 0 rows to x number
of rows. In these cases we might not need to have a plan, a forced index,
or a forced query plan. We just specify that the query must use an index
on whatever table/tables that we have specified to be IXONLY accessible.

This option still allows for nearly full optimization of queries. Only
gets rid of a table scan as a viable option on a table marked for IXONLY
access. Again this is a huge benefit on any system that undergoes changes
during its life cycle.


Sherlock, Kevin Posted on 2003-03-28 16:26:37.0Z
Message-ID: <3E8477B6.72250836@qwest.com.nospam>
Date: Fri, 28 Mar 2003 10:26:37 -0600
From: "Sherlock, Kevin" <ksherlo@qwest.com.nospam>
Reply-To: ksherlo@qwest.com.nospam
Organization: QWEST Wireless
X-Mailer: Mozilla 4.79 (Macintosh; U; PPC)
X-Accept-Language: en,pdf,ko
MIME-Version: 1.0
Subject: Re: Index only access
References: <1EA6C6E5BD913E44004F2CC285256CDF.004F2CCF85256CDF@webforums> <3E64E0B3.BC8253A5@sybase.com> <EB033F32E61F09550002096A85256CE5.00669E5385256CDF@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: np45.qwest.com 155.70.39.45
Lines: 49
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1074
Article PK: 95313

There is some current functionality to persuade the optimizer in this way.

sp_chgattribute <tablename>, "concurrency_opt_threshold",<value>

A <value> of "-1" tells the optimizer to "never table scan if avoidable".

This procedure was meant to adjust the threshold (in pages) in which the
optimizer chooses index scans vs table scans. I haven't personally
tested this, so you might want to see if it gives you what you want in
your case.

putnamr@river.it.gvsu.edu wrote:
>
> Hello,
>
> You bet...
>
> Rather than forcing a particular index on a table which is dangerous as
> well, I would like the ability to tell the optimizer that a index must be
> chosen. The optimizer would not consider a table scan as part of the
> costing of the table. It would merely determine the cheapest index and use
> it. If an index does not exist on a table specified for IXONLY an error
> message would be generated similar to if you actually forced an index which
> does not exist.
>
> This feature would be more useful than specifying a plan as index names and
> ids and columns, etc., all change or have the potential to change during
> the life cycle of an application. This should be less dangerous than using
> forceplan or forcing an index.
>
> Would be beneficial in cases where the optimizer is not choosing the most
> efficient path, on tables that change in size from say 0 rows to x number
> of rows. In these cases we might not need to have a plan, a forced index,
> or a forced query plan. We just specify that the query must use an index
> on whatever table/tables that we have specified to be IXONLY accessible.
>
> This option still allows for nearly full optimization of queries. Only
> gets rid of a table scan as a viable option on a table marked for IXONLY
> access. Again this is a huge benefit on any system that undergoes changes
> during its life cycle.


putnamr Posted on 2003-03-28 17:32:51.0Z
From: putnamr@river.it.gvsu.edu
Date: Fri, 28 Mar 2003 12:32:51 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Index only access
Message-ID: <150547F195E8ABE80060641D85256CF7.005F33C585256CF7@webforums>
References: <1EA6C6E5BD913E44004F2CC285256CDF.004F2CCF85256CDF@webforums> <3E64E0B3.BC8253A5@sybase.com> <EB033F32E61F09550002096A85256CE5.00669E5385256CDF@webforums> <3E8477B6.72250836@qwest.com.nospam>
Lines: 6
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1075
Article PK: 95314

Hello,

I will test and post my results. Thanks for reply.

Thanks,
Ryan Putnam


putnamr Posted on 2003-03-31 00:50:36.0Z
From: putnamr@river.it.gvsu.edu
Date: Sun, 30 Mar 2003 20:50:36 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Index only access
Message-ID: <ADA18BEBAF8DFEAA0004A23185256CFA.005F33C585256CF7@webforums>
References: <1EA6C6E5BD913E44004F2CC285256CDF.004F2CCF85256CDF@webforums> <3E64E0B3.BC8253A5@sybase.com> <EB033F32E61F09550002096A85256CE5.00669E5385256CDF@webforums> <3E8477B6.72250836@qwest.com.nospam>
Lines: 7
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1086
Article PK: 95326

Hello,

This option only works for tables that use a DOL scheme. In addition,
tables with little to no data still table scan instead of using an index.

Thank You,
Ryan Putnam


putnamr Posted on 2003-03-31 00:54:45.0Z
From: putnamr@river.it.gvsu.edu
Date: Sun, 30 Mar 2003 20:54:45 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Index only access
Message-ID: <85357D77408F0FB30005036285256CFA.005F33C585256CF7@webforums>
References: <1EA6C6E5BD913E44004F2CC285256CDF.004F2CCF85256CDF@webforums> <3E64E0B3.BC8253A5@sybase.com> <EB033F32E61F09550002096A85256CE5.00669E5385256CDF@webforums> <3E8477B6.72250836@qwest.com.nospam>
Lines: 8
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1087
Article PK: 95327

Hello,

Ignore my previous message. This option works very well for tables that
use a dol scheme (thank you). Does Sybase plan on incorporating this
option to the allpages tables?

Thanks,
Ryan Putnam


Ilya Zvyagin Posted on 2003-04-18 11:58:36.0Z
Reply-To: "Ilya Zvyagin" <masterziv@mail.ru>
From: "Ilya Zvyagin" <masterziv@mail.ru>
References: <1EA6C6E5BD913E44004F2CC285256CDF.004F2CCF85256CDF@webforums>
Subject: Re: Index only access
Date: Fri, 18 Apr 2003 15:58:36 +0400
Lines: 16
Organization: FCT
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
FL-Build: Fidolook Express 2001 UIExt. BuildID: 3BC00FAD (7/10/2001 12:17:49).
X-Comment-To: putnamr@river.it.gvsu.edu
Message-ID: <1050667116.358031@gatekeeper.fct.ru>
Cache-Post-Path: gatekeeper.fct.ru!unknown@dream.int.fct.ru
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: gatekeeper.fct.ru 212.113.103.2
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1151
Article PK: 95390

Hello, putnamr@river.it.gvsu.edu!
You wrote on Tue, 4 Mar 2003 09:24:48 -0500:

p> Example)
p> SET IXONLY ON or
p> SELECT *
p> FROM A (IXONLY)

You can do the latter by using an fbstract query plan with i_scan clause without an index.

--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port
E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
ICQ UID: 29427861(MasterZIV)


putnamr Posted on 2003-04-21 15:20:42.0Z
From: putnamr@river.it.gvsu.edu
Date: Mon, 21 Apr 2003 11:20:42 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Index only access
Message-ID: <3846B742E00EBC6600544AFA85256D0F.004795B285256D0C@webforums>
References: <1EA6C6E5BD913E44004F2CC285256CDF.004F2CCF85256CDF@webforums> <1050667116.358031@gatekeeper.fct.ru>
Lines: 7
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1168
Article PK: 95408

Hello,

Thank you for your response. If what you posted works, then I think this
is the best way to do this, currently. I will give this a try.

Thanks,
Ryan x13646