Force an index retrieval

[Fry, Mark]

I had said:

<blah blah blah>

WHERE 0 <> 0

and then realized my omission just after pressing send.  What I should have
said was:

WHERE <some other predicate you are currently using> OR 0 <> 0

might cause the optimizer not to use the index it was using prior to adding
OR 0 <> 0

I could use jetlag as an excuse, but in fact I was just testing to see if
you were all awake :-)

Regards,

Mark Fry
Cognos Limited
email: Mark.Fry@Cognos.com

 -----Original Message-----
From: 	Fry, Mark  
Sent:	17 March 2000 10:43
To:	Paul Howard; powerh-l@lists.swau.edu
Subject:	RE: Force an index retrieval

Hi Paul,

First the bad news.  It's Sybase's query optimizer that is deciding how to
read the table.  I'm not aware of any *direct* syntax supported by
PowerHouse that will allow you to override the optimizer.

Now for the slight ray of hope.  I remember seeing (ages ago, from a DB2
magazine of all places) some SQL techniques to manipulate the optimizer's
selectivity of indexes.  Simply by adding predicates to the SQL, you can
"guide" the optimizer towards or away from an index.

WHERE customers.debtors_month = customers.debtors_month

might increase the selectivity of an index with debtors_month defined as the
first segment, whereas

WHERE 0 <> 0

might decrease the selectivity of what would otherwise be the optimizer's
preferred index, perhaps introducing a natural scan.

Do these work with Sybase?  I've no idea, but if you have some time, you
might try them out.  It does mean, however, that you will have to change
your code to use cursors and SQL instead of file statements.

Off the top of my head, another track you might explore is coding the SQL
into a stored procedure (with the appropriate Sybase-specific syntax to
force the index), and then call the stored procedure from your screen.
Again, it would mean writing a cursor in the screen to call the stored
procedure, so some pretty major changes to your screen would be required.

I'd be interested to hear if these work...

Good luck.

Mark Fry
Cognos Limited
email: Mark.Fry@Cognos.com

 -----Original Message-----
From: 	Paul Howard [mailto:paul@synergy.co.za] 
Sent:	16 March 2000 15:07
To:	powerh-l@lists.swau.edu
Subject:	Force an index retrieval

Hi Listers,
One of my customers is using PH 733d3 on hpux accessing a sybase database.

Here is a sample of the code

> procedure FIND
> ;-----------------
> begin
>   for missing CUSTOMERS
>     begin
>       get CUSTOMERS &
>           viaindex idx02 &
>       using &
>           T_DEBTORS_MONTH, &
>           T_SURNAME
>     end
> end 

He finds that this does a sequential table scan instead of using an indexed
read.  In ISQL you can force it to use an index with special syntax.  Does
anyone know how to do this from within PowerHouse?

Regards
Paul Howard
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.