Questions tagged [database-indexes]

An index contains keys built from one or more columns in the table, or view, and pointers that map to the storage location of the specified data.

An index contains keys built from one or more columns in the table, or view, and pointers that map to the storage location of the specified data.

References:

Top Questions related to Database Indexing

571 questions
23
votes
1 answer

SQL Server Web vs Standard edition

I have found out that there's two versions of SQL Server types that are very different in terms of pricing... The Web version from my host provider costs about 13$ per 2 core packs, whereas the Standard edition is right around 200$. From my…
User987
  • 3,663
  • 15
  • 54
  • 115
21
votes
1 answer

How to implement composite primary keys in rails

I have a User model that looks like this: class User < ApplicationRecord belongs_to :organization belongs_to :department end The users table in the database has the two foreign keys organization_id and department_id. How can I make these two…
21
votes
1 answer

Deferrable, case-insensitive unique constraint

Is it possible in PostgreSQL to create a deferrable unique constraint on a character column, but case-insensitive? Let's assume the following basic table: CREATE TABLE sample_table ( my_column VARCHAR(100) ); If deferrable constraint is not…
Code Painters
  • 7,175
  • 2
  • 31
  • 47
19
votes
4 answers

Creating Indexes on DB with Hibernate @Index Annotation

I have annotation-driven hibernate capabilies on my project. Now I want to create an index over a column. My current column definition is @NotNull @Column(name = "hash") private String hash; and I add @Index annotation here. @NotNull @Column(name =…
13
votes
1 answer

Indexes on join tables

When searching on Google for join table indexes, I got this question. Now, I believe that it is giving some false information in the accepted answer, or I do not understand how everything works. Given the following tables (running on PostGreSQL…
Florin Asăvoaie
  • 849
  • 1
  • 6
  • 20
12
votes
1 answer

Row level security(RLS) performance is significantly slower in postgres.

Description : Here is the sample demonstration of the performance issue. We first created two tables , enabled row level security and created policy as well . Table definition: create table sample_schema.sample_table1(ID numeric(38) PRIMARY KEY NOT…
11
votes
4 answers

Different types of database indexes?

I am trying to compile a list of non-system-specific database indexes. I've looked at Oracle, DB2, MySQL, Postgres and Sybase, and almost every resource has a different list. So far I have seen: clustered, multi-dimensional clustered, unclustered,…
Nathron
  • 1,639
  • 3
  • 18
  • 25
10
votes
2 answers

Why MongoDB doesn't use Index Intersection?

I am trying to reproduce the first example of index intersection instruction (http://docs.mongodb.org/manual/core/index-intersection/) but facing a problem: mongo doesn't uses both indexes My steps: Download mongo (3.0.3) and install it Run mongod:…
Alexander
  • 560
  • 3
  • 14
10
votes
1 answer

How do I change a column from null to not null when an index depends on that column without recreating the index?

I have a column Column which is declared to be NULL DEFAULT(GETUTCDATE()) and there's a non-clustered index that includes this column. I want to change this column to be NOT NULL DEFAULT(GETUTCDATE()) and when I run ALTER TABLE ALTER COLUMN…
sharptooth
  • 167,383
  • 100
  • 513
  • 979
10
votes
5 answers

SQL Server - INSERT failed because of 'ARITHABORT'

I use NHibernate and SQL Server 2005 and I have an index on a computed column in one of my tables. My problem is that when I insert a record to that table I get the following error: INSERT failed because the following SET options have incorrect…
9
votes
3 answers

How binary search is used in database indexing

I know how binary search works but I wanted to know practical uses for binary search... I searched through the internet and I found that the main use is data base indexing, but I couldn't understand how binary search could help in data base…
9
votes
2 answers

How to delete composite indexes in Cloud Firestore?

The documentation says : https://firebase.google.com/docs/firestore/query-data/indexing To delete an index: Go to the Cloud Firestore section of the Firebase console. Click the Indexes tab. Hover over the index you want to delete and select Delete…
9
votes
3 answers

Oracle - Create an index only if not exists

Is there any way to create indexes in oracle only if they don't exists ? Something like CREATE INDEX IF NOT EXISTS ord_customer_ix ON orders (customer_id);
Adelin
  • 18,144
  • 26
  • 115
  • 175
8
votes
1 answer

restoring table with pg_restore does not include primary key or indexes

So I made a backup of a table using pg_dump: pg_dump -U bob -F c -d commerce -t orders > orders.dump This table had several listed indexes such as a primary key However when I restore this table into a development database on another system using…
8
votes
1 answer

How to show all invalid objects in PostgresQL

Looking for the view I can list up all 'invalid' objects in PostgreSQL. In Oracle, we can use dab_objects.status column but I'm not sure if there is a simple way to do such a thing in PostgreSQL. Maybe, I can check the invalid indexes with below…
Sigularity
  • 917
  • 2
  • 12
  • 28
1
2
3
38 39