The process of determining which indexes are useful and which are not.
7
votes
2answers
348 views
PostgreSQL Index Caching
I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions:
PostgreSQL indexes, like rows, live on disk ...
1
vote
0answers
284 views
SQL Server Database Engine Tuning Advisor vs DMV queries
What is the best way to tune the indexes on my production database? I normally use the following query :
SELECT
user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage],
...
3
votes
3answers
128 views
What is a good guideline for sampling for statistics updates
Scope: A billion record table that is indexed in 10 ways. Rate of change per day is 1/2 a percent.
Some of the indexes are monotonously increasing (datetime/timestamp), yet the common queries most ...
5
votes
2answers
643 views
Viable strategies for rebuilding indexes online in SQL Server 2008 R2 Standard edition?
We use SQL Server 2008 R2 Standard edition (we can't afford Enterprise edition on all our production database servers) and have to periodically maintain the critical indexes (rebuilding/reorganizing) ...
4
votes
3answers
367 views
Lookup table for a one-to-many relationship
I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source.
tickets
- id
- sutff
lookup
- ...
1
vote
1answer
90 views
What is the best relationship model for high performance?
I want to model a relationship between Customer and Transaction.
However, because Customer is only described by a unique string and no extra info, I have two possibilities to implement it in the ...
1
vote
1answer
121 views
INDEX Creation Advice Oracle
I have a table that has time data of 2 billion records obtained from the clients. The data has two main columns, date and entity_id.
The attributes date, and entity_id would make a unique pair.
...
0
votes
0answers
148 views
Why this query takes 500 seconds to complete even though I got all the compound index right?
I tried this query
db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { ...
5
votes
2answers
535 views
How can I improve the speed of a query on a 20 million+ row table?
I have a query that is used for getting internet traffic statistics of certain IP addresses.
There are separate IP address fields for hosts and blocks of IPs called assignments. The data is stored ...
7
votes
2answers
354 views
Filtering data ordered by rowversion
I have a SQL table of data with the following structure :
CREATE TABLE Data(
Id uniqueidentifier NOT NULL,
Date datetime NOT NULL,
Value decimal(20, 10) NULL,
RV timestamp NOT NULL,
...
2
votes
1answer
142 views
Oracle function based index is not used in query
I cannot figure out why the following is not working as I expect it :
CREATE TABLE table1(table1_id INT NOT NULL PRIMARY KEY,
col1 INT NOT NULL,
val1 VARCHAR(50));
CREATE INDEX ...
2
votes
1answer
100 views
Where to place non clustered index
Execution plan clearly showing that one of my table used in query is useing Clustered Index Scan. From this node, how can i guess that which columns should be part of my non clustered index key and ...
3
votes
1answer
201 views
Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaning
I'm working on an inventory database that uses supertype/subtype model. My question regards creating multiple relationships with the same tables using the primary key and composite indexes that also ...
4
votes
2answers
887 views
How I should fix the following update statement to make it run faster?
I have the following DML:
UPDATE rc_usutb011 u
SET cod_ant=(
SELECT cod_hst
FROM rc_pdptb101 p
WHERE ocip_hst=ocip_ant
AND p.sector=u.sector
);
The explain plan ...
2
votes
2answers
348 views
Reorganize Indexes T-SQL
I'm moving a database to SQL Azure and with that I lose the maintenance plans so I am going to use a Worker Role to schedule them. When I open the properties of the Rebuild Index Task and click on ...
5
votes
3answers
966 views
SQL server indexing foreign keys, covering indexes included columns
I have pulled a list of FK's that do not have indexes on them.
If a FK does not have a dedicated index on them but are part of wider indexes used for covering queries, Should they have a dedicated ...
4
votes
2answers
2k views
Primary key vs. unique index: performance difference with foreign keys?
Will there be any difference in performance if you compare a query fetching data via a join between two tables where there's a relationship against either of the following:
A primary key
A unique ...
2
votes
1answer
137 views
Indexing or changing one-to-many relationship
I have these two Microsoft SQL Server 2005 tables:
ObjectLicenses (ObjectLicenseID [PK]; ObjectID; LicenseType; LicenseNumber)
1|1|A|000001
2|1|A|000002
3|1|B|000003
4|2|C|000004
Objects (ObjectID ...
2
votes
1answer
95 views
Are two indexes needed?
Our MySQL database will contain an encyclopedia. The encyclopedia will be shown in pages where every page contains entries starting from a letter.
Which indexes should I use? Should I have two ...
3
votes
2answers
91 views
MySQL using a index referencing a different column
I have a large table which uses a versioning system in the table. When a record is updated a new record is inserted with the old data and the original record is updated ( so to maintain the same ...
0
votes
1answer
70 views
Can you search using an index while indexes are being updated in oracle11g?
I am using oracle 11g. I noticed some performance problems that causes hanging on some search operations.
Can I still search based on an index while that index is being updated in the database?
0
votes
3answers
1k views
Will more indexes on a table affect performance? [duplicate]
Possible Duplicate:
How do indices impact query performance?
How to know when/if I have too many indexes?
Say ‘X’ report using some "where" condition in the statement so we are creating ...
1
vote
1answer
500 views
Index Strategies on Text or NVARCHAR(MAX) Fields
I have the following query (simplified for question) I'm trying to speed up for a read only DB ...
SELECT
[sysid]
,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101)
,[From]=[from_addr] ...
0
votes
1answer
98 views
Creating Indexes to optimize query
I have this query and I want to increase its performance by adding appropriate Indexes.
DELETE
FROM MYTAB1
WHERE MYID1 IN
( SELECT MYID2 FROM MYTAB2 );
I am not familiar with the ...
3
votes
2answers
248 views
Why is a secondary index chosen over a clustered index for SELECT COUNT(*) …?
In this query:
select count(*) from largetable;
a secondary index is chosen:
mysql> explain select count(*) from largetable;
...
1
vote
1answer
185 views
Analyzing TCB Index Status for DB2
The Following is the TCB index that I could get using a command for my DB2 database. I don't know how to analyze it. Which columns are important? What do they mean?
TCB Index Stats:
Address ...
4
votes
3answers
142 views
Most efficient (or meaningful) way to index grid-based data
I have a MySQL table which I expect to be in surplus of a few million rows and 99% select statements. The problem I am having is coming up with a meaningful way to determine the Primary Key. (I have ...
5
votes
4answers
708 views
How will large index INCLUDE fields effect system performance?
This question is about SQL Server index performance with a varchar(2000) as an INCLUDE in a covering index.
I’m trying to improve performance in an slow and unstable database application. In some ...
7
votes
1answer
634 views
Should I use many single field indexes, instead of specific multi column indexes?
This question is about the effectiveness of a SQL Server indexing technique. I think it is known as "index intersection".
I'm working with an existing SQL Server (2008) application that has a number ...
5
votes
2answers
2k views
What is the difference between ALTER INDEX and DBCC DBREINDEX?
Is the only difference between
ALTER INDEX [index_name] on [object_name] REBUILD with (ONLINE=OFF, FILLFACTOR=90)
and
DBCC DBREINDEX([dbname], 90)
just that the DBCC command will reindex all ...
3
votes
1answer
123 views
Filtered index statistics refresh threshold
We have filtered indexes in our production environment. While doing some research about them, I came across this article "Filtered indexes and filtered stats might become seriously out-of-date"
...
2
votes
1answer
206 views
Is staggering reindexing jobs a good strategy?
I have inherited a server with 35 databases and a job that reindexes all tables on all databases (using sp_MSForEachTable). It's taking over 14 hours to complete and is now blocking other processes.
...
3
votes
1answer
760 views
Oracle not using index when > used in where clause
We have a relatively classic scenario where the date clause of the query generated by the application uses a "greater than" for a given date.
Because there is no end clause to the date range, oracle ...
2
votes
1answer
177 views
In DB2, what are the alternative ways of evaluating an index's (or several indexes) benefit?
I'm trying to evaluate different approaches to analyzing the benefit of changing the set of indexes that are available. If I pick some representative queries from my workload, I can run EXPLAIN and ...
1
vote
2answers
493 views
How can an identity primary key index become fragmented?
From what I understand about index fragmentation, this should not be possible. The cases I have found in my databases are non-clustered.
Example:
ALTER TABLE [dbo].[ClaimLineInstitutional] ADD ...
7
votes
4answers
248 views
What are the performance considerations between using a broad PK vs a separate synthetic key and UQ?
I have several tables where records can be uniquely identified with several broad business fields. In the past, I've used these fields as a PK, with these benefits in mind:
Simplicity; there are no ...
5
votes
1answer
403 views
SQL Server 2008 Maintenance Plan Failure
Instead of using my old school index rebuild script I had decided to use 2008's built-in Maintenance Plan to do a simple Rebuild Index task.
The details are:
Connection = Local server connection
...
0
votes
1answer
1k views
Reindexing Partitioned Table Indexes
Historic Reporting Server running SQL 2008 R2.
I'm trying to tweak the fill factor. I have a job that records the fragmentation when the jobs runs in the early hours.
For every table that had high ...
8
votes
2answers
2k views
How to know when/if I have too many indexes?
Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement...").
From my understanding every added ...
4
votes
3answers
270 views
Using DTA vs. evaluating DMVs?
Currently I am confronted with a production SQL Server database where someone had added almost all missing index proposals from DTA.
Form How to determine if an Index is required or necessary I have ...
