Question on SQL SELECT QUERY using an index

[Melissa Laurel]

In normal SQL using Oracle and other databases you can do the following:

SELECT *

FROM Table WITH(INDEX(Index_Name))

I was wondering if CACHE can do the same thing.

So I went to the Management Portal,(we are on version 2010.2.7); Data Management/SQL. Selected my namespace, then under SQL Operations, selected Execute SQL Statement.

I typed in the following statement:

SELECT * 

FROM tUser.tEpisodeTests

WITH INDEX(IndxEpisodeNo)

I get the following error:

ERROR #5540: SQLCODE: -25 Message: Input (WITH) encountered after end of query^ SELECT * FROM tUser . tEpisodeTests WITH

I also tried by calling the column of the table itself:

SELECT EpisodeNo

FROM tUser.tEpisodeTests

WITH (INDEX(IndxEpisodeNo))

I get the same error:

ERROR #5540: SQLCODE: -25 Message: Input (WITH) encountered after end of query^ SELECT EpisodeNo FROM tUser . tEpisodeTests WITH

What am I missing? Can this functionality be done in Management Portal?

Thank You,

Melissa

[Brendan Bannon]

Hi Melissa

 

Cache does not support this syntax. We do the opposite letting you tell us where we should NOT use an index.

 

For example

 

SELECT *
FROM Table

WHERE Age > 0

 

Will return all the people in the data base so if there is an index on Age it would not be helpful here.  You can tell us to avoid that index by adding the hint %NOINDEX before the condition

 

SELECT *
FROM Table

WHERE %NOINDEX Age > 0

 

 

 

Hope this helps

 

Brendan

--
--
IRIS, Caché, Ensemble
---
You received this message because you are subscribed to the Google Groups "IRIS, Caché, Ensemble" group.
To unsubscribe from this group and stop receiving emails from it, send an email to intersystems-publi...@googlegroups.com.
To post to this group, send email to intersystems...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Andrew Makinson]

Is this the functionality you require to create and INDEX in a cacheSQL table? Or am I not fully understanding the issue ? 

https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_createindex

Sent from my iPhone

[shrinivas rao]

Not sure if it can be used.

--