To Index or not to Index (that is the question...)

I'd like to clear up an assumption I have.

If I have a Primary Key Constraint on a Table I assume that I will not need 
to create an Index on the field(s) of the constraint as the action of 
creating the primary key constraint produces what is the equivalent of an 
Index (be the constraint clustered or not).

Is this the case?

Also I'm looking to Pivot some data, are there any SQL tricks etc. I should 
be aware of to be able to do this (I've searched the Newsgroups for 'Pivot' 
but don't understand any of the answers posted - some SQL examples would be 
great.

Thanks

Pat Gandy
0
Pat_Gandy
12/4/2000 2:32:40 PM
sybase.sqlanywhere.general 32637 articles. 22 followers. Follow

3 Replies
688 Views

Similar Articles

[PageSpeed] 32

Please always post the version and build number you are using.

If you are at v6 or below, there are some circumstances may may benefit from
having a second index on the primary key.  Because at these versions, a
primary key also stores all the information about foriegn keys that
reference this primary key in the same index, searching for a particular
range in the primary key could be expensive, as all the foreign key
information that you are not interested in must be scanned by as well.  For
instance, a customer table that has 200 tables referencing it, with a rimary
key of cust_id, might have problems with the following query :

select * from customer where cust_id >100 and cust_id < 200;

It is up to the DBA to determine whether the increase in retrieval time for
this type of query warrants the overhead of maintaining a second index on
the same columns.

In v7 and above, primary and foreign keys are stored in sepearte indexes and
there is no need for this additional index.

Sorry, I'm not sure what you mean by pivoting data.

--
Reg Domaratzki
Sybase iAnywhere Solutions
Please reply only to the newsgroup

ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
   -> Choose SQL Anywhere Studio
   -> Set "Platform Preview" and "Time Frame" to ALL and click "GO"


<Pat_Gandy> wrote in message
news:FDFA6FC3B5DF3F60004FE53A852569AB.004FE554852569AB@webforums...
> I'd like to clear up an assumption I have.
>
> If I have a Primary Key Constraint on a Table I assume that I will not
need
> to create an Index on the field(s) of the constraint as the action of
> creating the primary key constraint produces what is the equivalent of an
> Index (be the constraint clustered or not).
>
> Is this the case?
>
> Also I'm looking to Pivot some data, are there any SQL tricks etc. I
should
> be aware of to be able to do this (I've searched the Newsgroups for
'Pivot'
> but don't understand any of the answers posted - some SQL examples would
be
> great.
>
> Thanks
>
> Pat Gandy


0
Reg
12/4/2000 3:08:57 PM
It's in your best interest to ask one question per posting. Some
people might know about pivot, but if they're busy they might not even
READ a message that has ANY replies (it's called Newsgroup Triage :).

Breck



On Mon, 4 Dec 2000 09:32:40 -0500, Pat_Gandy wrote:

>I'd like to clear up an assumption I have.
>
>If I have a Primary Key Constraint on a Table I assume that I will not need 
>to create an Index on the field(s) of the constraint as the action of 
>creating the primary key constraint produces what is the equivalent of an 
>Index (be the constraint clustered or not).
>
>Is this the case?
>
>Also I'm looking to Pivot some data, are there any SQL tricks etc. I should 
>be aware of to be able to do this (I've searched the Newsgroups for 'Pivot' 
>but don't understand any of the answers posted - some SQL examples would be 
>great.
>
>Thanks
>
>Pat Gandy

0
Breck
12/4/2000 5:24:51 PM
"Pivoting" a query is not difficult, but you should post the DDL and the
intended result set...

Here's a simple example:

Assume a table of transaction dates:
Create table Invoice(
Invoice_ID   integer   not null  default autoincrement,
Customer_ID  integer   not null,
Invoice_Date   date    not null,
Invoice_Amount   decimal(11,2)  not null,
primary key (Invoice_ID));

A straight select from this table shows invoice amounts "vertically".  Let's
say you wanted 12 monthly "buckets" across the page, grouped by Customer_ID.
That would look something like:

Select  Customer_ID,
Sum( If datepart(Month, invoice_date) = 1  then 1 else 0 endif) *
invoice_amount ) JAN_AMT,
Sum( If datepart(Month, invoice_date) = 2  then 1 else 0 endif) *
invoice_amount ) FEB_AMT,
Sum( If datepart(Month, invoice_date) = 3  then 1 else 0 endif) *
invoice_amount ) MAR_AMT,
..... /* and so on */
Sum( If datepart(Month, invoice_date) = 12  then 1 else 0 endif) *
invoice_amount ) DEC_AMT
From Invoice
WHERE Invoice_date between '1999-1-1' and '1999-12-31'
GROUP BY Customer_ID
ORDER BY Customer_ID ;

For more info on this technique (Conditional Function Expressions) get a
copy of David Rozenshtein's book, Advanced SQL Techniques, available on most
bookstore websites.

Paul Horan [TeamSybase]
Buffalo, NY

<Pat_Gandy> wrote in message
news:FDFA6FC3B5DF3F60004FE53A852569AB.004FE554852569AB@webforums...

[snipped]

> Also I'm looking to Pivot some data, are there any SQL tricks etc. I
should
> be aware of to be able to do this (I've searched the Newsgroups for
'Pivot'
> but don't understand any of the answers posted - some SQL examples would
be
> great.
>
> Thanks
>
> Pat Gandy


0
Paul
12/4/2000 6:50:17 PM
Reply:

Similar Artilces:

To Index or Not To Index or How To Index
Hello, I was wondering if someone knows a better way to explain how different indexes work. Such as, is it better to have an index with int type or char or varchar etc? Is it better to have composite indexes or indexes with individual columns? How does optimizer handle different indexes and what can we do help it? Indexes are very efficient most of the time but they can be quite detrimental to server's performance, if abused. Perhaps you know of a good article about indexing that you could share with us. Or, perhaps Mr. Rob Verschoor would like to issue an article in his very usefu...

Basic Index question :- nonclusted indexes which are a subset of a clustered index
Can anyone suggest why I would have the following set of indices upon a table: index1 clustered, unique field1,field,field3,fiel4 index2 nonclustered field2,field3, field4 index3 nonclustered field,field2,field4 The table is one into which a lot of inserts are done, so I would have thought that index2 & 3 would be unncessary since they're (1) already in the clustered index and (2) any inserts would triple the amount of index updates that are required... Are these assumptions correct ? Thanks in advance, Steve Hi, if you have a...

Questions about Indexing and Using an Indexing POA
Although I have only about 50 users, at least 15 of them have in excess of 100,000 messages in their accounts and the POA (version 7.0.2) is regularly slowing to a crawl. (I just know that plans for revolution are fomenting!) I have embarked on a campaign to reduce these accounts by archiving everything off to get mail accounts down to 3000 or fewer pieces. I have achieved user buy-in, but have worked on only a few users so far. In another closely related thread, it was suggested to me that the PO speed issues relate to broken indexes. And I suspect that given so many messages...

index server not indexing
Ok, so I want to add a catalog to the index server. I can add the new catalog, I then add a directory to this catalog. I restart the index server, and merge the index. If I click the index server, in mmc, it shows 0 documents for everything. Everything meaning documents to index, total docs, docs defered for indexing and so on.  I've tried this on vista as well as on win2003 and the same result is obtained, no documents are indexed. In win2003 I can index document, but ONLY the ones in the default catalog, if I paste some documents there they get indexed. So, what have I don...

A question about clustered indexes forcing rebuild of non-clustered indexes.
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this: When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004 Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if...

Index Questions
Hi, I was wondering if the order in which indexes are created, as well as, the position of the column is located in table. I have a table with 40+ fields. Over time, I've just been appending fields to the end of the table and then indexing them. There are approx. 30 indexes. Thanks, Shew Shew wrote: > Hi, > > I was wondering if the order in which indexes are created, as well as, the > position of the column is located in table. > > I have a table with 40+ fields. Over time, I've just been appending fields > to the end of the table ...

Index Name from Index ID
Hi there, Have an error reported in log and I want to find the index name, the error has provided the database name and table name but only the index id. Is there any easy way of doing this ? Thanks, Mully "mully" <niallpmullan@yahoo.co.uk> wrote in message news:1193139772.411602.109710@z24g2000prh.googlegroups.com... > Hi there, > > Have an error reported in log and I want to find the index name, the > error has provided the database name and table name but only the index > id. Is there any easy way of doing this ? > > Thanks, >...

MS Index Server, Web Page Searches
Greetings: I have the service set up and running and I'm able to do searches on my website and return the results to a search page. The basics are fine and up and running. Question: I find when I publish the website if the '[x] Make site updateable' checkbox isn't checked the .aspx pages do not show up in a search. Is it true that the site must be published as updateable in order to make the MS Indexer work?  Am I sacrificing security in order to get the pages indexed?  Is there a way to publish the site as not updateable and still have the indexer work?  I...

Index question
If I have a table with a foreign key into another table, will the optimizer use that as an index for other purposes as well, such as joins to other tables (not the one the foreignkey is referring to) or 'where' clauses. Or do I need to put in an explicit index for those purposes? The reason I'm asking is that a query which uses a join into this table was giving me a sequential scan of this table even though there was an index available in the form of the foreign key. I can't tell if that's just because the optimizer thought this would be faster (since it's ...

Crawler Index and FIle Index
I have setup the Apache web services as well as Quickfinder for my Netware 6.5 server. The Crawler works fine, finds all the hits I am looking for and opens the file on my data volume. However, when I setup a File System Index it does not open anything. I have double checked my paths and syntex. The thing that I have noticed is that when I search for a file with the Crawler index and highlight the file, the path at the bottom of my browser is like the following example. http://myserver/comp/docs/myfile. showing the server name not an IP address, which is fine and works! Howev...

indexing question
I'm a bit unsure about a topic after reading the ASA 9 documentation: automatically generated indexes with respect to primary keys and foreign keys constraints. The documentation suggests that ASA 9 engine automatically indexes both primary and foreign key columns upon creation of the constraints. In the past we have practiced creation of foreign key column indexes manually, but now I am thinking this practice may now be counter-productive in terms of now forcing the database to maintain two indexes on the same set of 'foreign key' columns. Am I correct to assume...

Placing indexes in index table
I have an Oracle 8.1.7 database and right now when I have Power Designer 9.5 create the script to create the database, the indexes have no tablespace attached to them, so they are placed in the USERS tablespace. Can someone explain to me the steps that need to be performed to have the indexes to into the INDX tablespace? Thanks. STeve Look at the Index properties dialog box, and select the Options tab. From there you should be able to specify everything that you need for the index. If you want to change all existing indexes at once, click on Database -> Default Physical Op...

Index
Hi everybody How can I know when I have to recreate the index of the table or execute the update statistics. Thanks Adriana Check with Mercury Interactive. http://www-svca.mercuryinteractive.com/products/ -- Jim Egan [TeamSybase] Senior Consultant Sybase Professional Services Get your free subscription to PowerTimes at http://www.powertimes.com I rarely recreate indexes. The exception is clustered indexes which I may occasionally rebuild. In both cases I would rebuild to get the organization of the index or data more clustered (get the values/rows closer togeth...

Index
As I make an index? Please explain what you are trying to do. In the database painter you can select a table and then use the 'create index' icon to create unique / duplicate indexes. -- Terry Dykstra [TeamPS] Canadian Forest Oil Ltd. Eduardo Garc�a Jim�nez (TINSA) wrote in message <362CB372.6C4F78A7@mad.servicom.es>... >As I make an index? Hello Terry; I want to make an index as in the books Terry Dykstra [TeamPS] escribi=F3: > = > Please explain what you are trying to do. > In the database painter you can select a table and then use ...

Web resources about - To Index or not to Index (that is the question...) - sybase.sqlanywhere.general

German question - Wikipedia, the free encyclopedia
"Kleindeutschland" redirects here. For the neighborhood in New York City, see Little Germany, Manhattan . 1820 map of Central Europe showing ...

Media Search of Suspects’ Apartment Raises Questions
“There is something so strange about all of this,” said MSNBC’s Andrea Mitchell , as correspondent Kerry Sanders picked up baby’s toys, sifted ...

Question answered
The San Bernardine massacre was caused by workplace stresses combined with climate change.

Question of the Day: Would you choose smog-reducing gear?
Filed under: Etc. , Green , Emissions Governments now enforce emissions laws. What if you could choose to comply or not comply? Continue reading ...

Some Questions
1.) Yesterday’s shooter was American born, and arguably more American than Ted Cruz. Why isn’t the fact that he was legally able to obtain that ...

The Question People Need to Stop Asking Women
Lately, I'm getting it a lot. The dreaded question. The one that each and every woman in has to hear in her life, probably more than once. I ...

Hillary was asked the blunt question: "You say that all rape victims should be believed, but would you ...
Clearly prepared for that question, she answered: "I would say that everybody should be believed at first until they are disbelieved based on ...

3 big questions about human gene editing
Global summit wrestles with the promise, risks and ethics of editing human genes

Questions raised on ex-Trump adviser
Trump tapped a man to be a senior business adviser after the man's involvement in a fraud became publicly known, the AP reports.

Will The TPP Increase Trade? That’s The Wrong Question
One of the selling points for the Trans-Pacific Partnership (TPP) agreement is that it will " increase trade ." Here's the thing. If you close ...

Resources last updated: 12/5/2015 4:32:37 AM