asked on

Clustered and Non-clustered Index

Hi,

I have one table having more than 3 millions rows with following columns:

[SITE_ID]

[USER_NAME]

[ACTIVITY_NAME]

[PAGE_TYPE]

[ACTION]

[DOC_ID]

[FROM_PAGE]

[TO_PAGE]

[ORG_NAME]

[LOG_DATE_TIME]

[SESSION_ID]

[IP]

[MACHINE_ID]

I am using 8 different queries, all thru stored procedure, to fetch data.

SITE_ID, USER_NAME, ORG_NAME and ACTION are in all queries WHERE clause.

I have created SITE_ID as non-unique clustered index and USER_NAME, ORG_NAME and ACTION as non-unique non-clustered index.

Problem is that data fetching without indexs is faster than when creating idex.

Is there any problem in index columns.

Can you please suggest me a better index plan.

I have attached the queries / column table image file.

Images file described columns called details
<br /><img src='http://xs434.xs.to/xs434/08513/queries-columns997.jpg'><br>
(http://xs434.xs.to/xs434/08513/queries-columns997.jpg or
http://xs.to/xs.php?h=xs434&d=08513&f=queries-columns997.jpg)

Please help..

Thanks

Sharma

Queries-Columns.jpg

Guy Hengel [angelIII / a3]

can you explain a bit how you are measuring?

Mark Geerlings

Is this a SQL Server question or an Oracle question? Somehow this shows up in the (Oracle) PL\SQL zone, but the terms you use in your question title (Clustered and Non-clustered Index) are SQL Server-only terms.

tigin44

if your table has high data insert delete operations then non-clustered indexes need to be reorginized and clustered indexes to be rebuild. If not they may slow down the performance. How often do you do this operations or did you do?

ASKER CERTIFIED SOLUTION

ValentinoV

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer