A database structure that can improve the speed of queries at the cost of disk space and slower inserts/updates. It stores a copy of one or more columns but structures the data differently to allow for faster access.

learn more… | top users | synonyms (2)

1
vote
1answer
46 views

Get the DDL for all indexes - Oracle

I have about 5 schemas in my oracle 12c database for which I need the DDL for all the indexes for each of the schemas. DDL I have checked it on how to get the code from it, but for all the indexes and ...
11
votes
4answers
433 views

Unable to create a Filtered Index on a Computed Column

In a previous question of mine, Is it a good idea to disable lock escalation while adding new calculated columns to a table?, I am creating a computed column: ALTER TABLE dbo.tblBGiftVoucherItem ADD ...
0
votes
0answers
45 views

Is it a good idea to disable lock escalation while adding new calculated columns to a table?

Having in mind that I can add computed columns to the subscription tables according to this link: SQL Server : Transactional Replication Computed Column Is it a good idea to disable lock escalation ...
1
vote
2answers
58 views

How do you achieve a physically sorted table file organization?

Do I just declare an index on a column in the schema like ? ALTER TABLE `table` ADD INDEX `index_name` (`colDate`) I want the table records to be physically sorted not just when doing an ORDER BY ...
6
votes
2answers
217 views

Index Fragmentation - Am I interpreting the results correctly?

I'm not a DBA, but I'm responsible for a database that currently has hundreds of tables and ~5TB of data. I recently ran the following query, in hopes of determining index fragmentation: Declare ...
1
vote
3answers
114 views

Get count estimates from pg_class.reltuples for given conditions

Is it possible to query the reltuples column for a given table with additional conditions like table.name LIKE 'hello%'? Currently on my bigger tables the SELECT count(*) query takes long and I ...
1
vote
1answer
87 views

Using index on datetime field not working really

Here is my query select msisdn, sum(score) as score from contest_player_update_history_ideacontest6_35 where request_date >='2015-01-14 00:00:00' and request_date <= '2015-02-03 23:59:59' ...
2
votes
1answer
63 views

Why is my query not using “Missing index tip”? ( SQL Server 2008 R2 )

I know there's a lot of questions like this one, but I think this is different, and none of them could help me. When I run a query with execution plan, I get this: But, even with the index ...
3
votes
2answers
119 views

When is the _id index used?

When is the _id index used by MongoDB? I could not find anything specific about it in Index Introduction, Analyze Query Performance, and Query Plans. When sorting by _id at the end of the query via ...
1
vote
0answers
51 views

Fragmented clustered primary key (sequential GUID) index after processing - SQL Server

I'm in the process of evaluating a schema change on the primary keys of several of our biggest tables in our software. Till now we used varchar(36) for all of our PKs and FKs and Hibernate generated ...
1
vote
0answers
27 views

DMV Index proposals still reported as missing

We have recently converted a big client running our patient/journal application from ORACLE to MSSQL, and after the conversion we have had quite a few performance issues. After running the DMV query: ...
10
votes
1answer
388 views

Creating an index on a really busy table

We have a table with a lot of insert\select every time ( like 100 machines inserting\updating every second). What is the best way to create an index on a table that can't be locked not even for a ...
2
votes
1answer
352 views

What is the best index implementation for a really large databse

I have a really large SQL SERVER table with only non clustered indices for several columns , while querying the table the execution plan shows RID Lookups , i expected to see this kind of lookup ...
0
votes
1answer
165 views

What privileges are needed to run EXPLAIN PLAN FOR CREATE INDEX?

My user can EXPLAIN PLAN FOR any query I like, but not for CREATE INDEX. I need to do that in order to estimate a suggested index's size, and using sysdba is out of question. What privileges should I ...
11
votes
2answers
441 views

What is the optimal data type for an MD5 field?

We are designing a system that is known to be read-heavy (on the order of tens of thousands of reads per minute). There is a table names that serves as a sort of central registry. Each row has a ...
1
vote
3answers
99 views

What indexes should one use for this two-table setup?

I have the following two-table setup: CREATE TABLE relationships ( id bigint(20) NOT NULL AUTO_INCREMENT, type varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (id), ...
2
votes
1answer
69 views

Why would CREATE INDEX with “online” mode suddenly slow my database down?

Earlier I used CREATE INDEX ... WITH(ONLINE=ON) without any problems - the statement would run in parallel with user queries and not slow them down at any time. Then suddenly I see that once I start ...
1
vote
1answer
55 views

Oracle: How do I add users to the HTTP ACL in Oracle 11?

How do I add a user to the network ACL (access control list)? When running this code declare req utl_http.req; begin req := utl_http.begin_request('http://example.com'); end; I receive ...
2
votes
1answer
30 views

PostgreSQL indices on multi fields queries

I have the following table: id message_read notification_sent send_date text version recipient sender -- ------------ ----------------- ...
2
votes
0answers
39 views

SQL Server 2012 duplicate key error with Spatial index that shouldn't be unique

We have a table that has a geometry column that is filled by a stored procedure from two x and y values whenever they're changed ( SW_GEOMETRY = GEOMETRY::STGeomFromText('POINT('+ ...
1
vote
1answer
68 views

Multi-Column PK or Auto-Increment PK?

In another question I mentioned that I have a table where, in order to ensure uniqueness, I need to use multiple columns as the PK. However, lookups are usually only done using the first of those ...
0
votes
1answer
31 views

Necessary to have separate index for lookups against one of a multi-column PK?

Sorry if this is a FAQ, but I'm having problems turning up exactly what I'm asking: I have a table (mysql) with a primary key which is composed of multiple columns only because this is necessary to ...
1
vote
1answer
52 views

how to get the missing indexes that are “filtered indexes”

I have a procedure that shows me the missing indexes. It has been very good to me, but it does not show me the missing FILTERED INDEXES. I would like my procedure to be able to catch and suggest ...
1
vote
1answer
187 views

Oracle: How can I track the progress of an index rebuild?

How can I track the progress of a command such as this? alter index myindex rebuild reverse online;
2
votes
1answer
96 views

Oracle: How to create a “reverse” index primary key?

I can create a primary key for a table and then reverse it with the command alter index T_PK rebuild reverse online; How can I create it reversed?
1
vote
1answer
70 views

DISABLE / DROP INDEX causing deadlock

Rather urgent one. Have created an index on one of our tables and for some reason all data going into that table has stopped. Can't see any blocking, but when I'm trying to drop or disable the ...
3
votes
1answer
104 views

Function Based Index with XPATH extractValue

// Oracle Version 11.2.0.4 Standard Edition // OS RedHat I am trying to create a function index on a large table, based on using an XML object. So I have done stuff such as: CREATE INDEX ...
1
vote
0answers
14 views

Query INFORMATION_SCHEMA for list of indexes [duplicate]

How does one query INFORMATION_SCHEMA in a standards-compliant database such as Postgres to obtain a list of all indexes in one database/catalog?
4
votes
1answer
108 views

missing indexes on distributor database

Looking for missing indexes on my distributor databases, I surprisingly find too many. the tables that need to be indexed are generally 3: MSrepl_commands MSlogreader_history ...
1
vote
2answers
47 views

DB select query performance when queried by partial key of primary key [closed]

My primary key is composite - {a,b}. Will there be any performance gain if I query only {a} or DB is going to scan the whole table as there is no index only on {a}. How much storage overhead would be, ...
1
vote
2answers
69 views

MySQL - Small table popping up in slowlog, refuses to use index

So I have two very very small tables (~20 rows) that are hit very frequently and pop up in the slowlog (I'm also logging tables not using indexes). I tested using a large multipart index (that's just ...
3
votes
2answers
149 views

GIN index not used when adding order clause

I'm trying to speed up a query that executes three ILIKE queries and reduces these with or (returning the overall count and 10 entries) SELECT *, count(*) OVER() as filtered_count FROM "users" ...
5
votes
1answer
194 views

Slow fulltext search for terms with high occurence

I have a table that contains data that is extracted from text documents. The data is stored in a column called "CONTENT" for which I have created this index using GIN: CREATE INDEX ...
1
vote
2answers
76 views

Partition of a table - how to minimize the time offline? How to check if all the statistics were updated?

As you ca see on the picture, I have a table called "tblsapdispatch" that has 55,707,259 records. Last sunday 30-aug-2015 they run a big update on this table, and I can see that the statistics were ...
7
votes
2answers
230 views

Should I mark a composite index as unique if it contains the primary key?

Given some table with a primary key, e.g.: CREATE TABLE Customers ( CustomerID int NOT NULL PRIMARY KEY, FirstName nvarchar(50), LastName nvarchar(50), Address nvarchar(200), Email ...
3
votes
2answers
95 views

Can I improve the performance of this MySQL query by adding an index?

I ran SET GLOBAL slow_query_log = 'ON'; in MySQL and this appeared in the log: SELECT U.*, DATE_FORMAT(U.TIMESTAMP_X, '%d.%m.%Y %H:%i:%s') TIMESTAMP_X, IF(U.LAST_ACTIVITY_DATE > DATE_SUB(NOW(), ...
1
vote
2answers
63 views

Index planning for many columns with different usage

I've a table with 80 columns and that is a base table for most of the application. Daily load inserts almost 8,000 records and update upto 2,000 records. This table is now having more than 5 million ...
10
votes
1answer
210 views

How to set up indexed view when SELECTing TOP 1 with ORDER BY from different tables

I am struggling to setup an indexed view in the following scenario so that the following query performs without two clustered index scans. Whenever I create an index view for this query and then use ...
0
votes
1answer
40 views

Redundant to have index + full text on same column?

If I have a full text index is it redundant to have a regular index on the same column? I am asking this because I need to support 5.5 and 5.6 at the same time and am using a stored procedure to ...
1
vote
1answer
134 views

Should I add a clustered Index on a foreign key that is not unique?

I have access to a table that has a primary key unique clustered index on an nvarchar(36). I need to create another table that hangs our own data off that unique clustered index in another table and ...
1
vote
0answers
139 views

Script to find unused indexes over a period of time doesn't work in Oracle 11g

The statspack_unused_indexes.sql script described in this page would be incredibly useful to me, but it has been coded for an earlier version of Oracle. When I try to run it on 11g, I get the ...
2
votes
2answers
36 views

Grouping in long tables

My application is running over Postgresql 9.4 and I found a problem and I don't know how can I fix it. I'm runing VoIp application, and we have a few tables that are too long (>60M of rows) with all ...
0
votes
1answer
53 views

MongoDB 3.0 Indexes maximum limitation

I have a mongo DB that store user's public messages. Each message should be categorized by at least one TAG (3 max in an array) The TAG can be anything up to 32 characters. ([0..9a..z]) the Tag ...
2
votes
2answers
134 views

avg_page_space_used_in_percent and fill factor

sys.dm_db_index_physical_stats contains column avg_page_space_used_in_percent. How does this fit in with the fill factor of an index? For example: I have index idx_HeaderInfo I create the index ...
0
votes
1answer
69 views

Oracle: How to create secondary index based on every word in a CLOB

I have a Table where each record is made up of a VARCHAR2 and a CLOB. The VARCHAR2 is the primary key. There are currently 1.5 million records and growing. The table is only ever used for REGEX ...
8
votes
3answers
311 views

Index tuning question

I am tuning some indexes and seeing some issues would like to take your advice On 1 table there are 3 indexes dbo.Address.IX_Address_ProfileId [1 KEY] ProfileId {int 4} Reads: 0 Writes:10,519 ...
1
vote
1answer
155 views

How does Postgres make its B-tree index?

I want to estimate how many reads the Partial index of PostgreSQL with the method B-tree requires, since I have not been able directly to alter the block size. PostgreSQL manual about the index here ...
0
votes
1answer
57 views

Creating index for large table is taking forever

I am trying to create Index for a large table with row count of 496,000,000 and the database is on SQL Server 2012 Enterprise edition. I know how to create index but its taking forever and would like ...
11
votes
2answers
788 views

Why does my WHERE clause benefit from an “included” column?

According to this answer, unless an index is built over the columns which are used to restrict, the query will not benefit from an index. I have this definition: CREATE TABLE [dbo].[JobItems] ( ...
2
votes
1answer
51 views

postgresql join - too long when no results found

I have two tables: controller_monitor and controller_monitor_reading. controller_monitor Column | Type | Modifiers ...