All Questions

Tagged with
Filter by
Sorted by
Tagged with
1
vote
2answers
46 views

Index on a partitioned key

we have a list partitioned table. Let partitioned column be c1. We have an index on that column(c1). We are planning to drop the index. Our doubt is that if we drop the index, does it affect the ...
1
vote
1answer
76 views

Performance Recommendations for Large Volume of Data

Oracle Table: Fields: 55 Roughly 10 are Used for Querying Single Record Length: 512 bytes Records: 3-5 million records per day Period of Time: 13 months to be available, > 13 months to be purged ...
0
votes
0answers
20 views

Global nonpartitioned index vs local partitioned on for a partitioned table

We have a select query which is really slow. We are using views in the from clause. Global nonpartitioned index is used for accessing the rows of a partitioned table. My doubt is that if Global index ...
2
votes
1answer
401 views

Index partitioned table to prevent Postgres from doing a sequential scan

I have a Postgres database with a partitioned table that will contain ~2,000,000,000 entries. I have defined a partitioned database based on the first letter of the "identifier" - this is split into ...
3
votes
1answer
30 views

Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant?

I have a big table (30 millions rows) defined like this: CREATE TABLE `drawing` ( `id` int unsigned NOT NULL, `artist` int unsigned NOT NULL, `imageUrl` text NOT NULL, `uploadDate` timestamp NOT ...
1
vote
2answers
55 views

Index options and applying partitioning to an index

I have following two questions: I have a SQL Server index with the following options: PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF SORT_IN_TEMPDB = OFF DROP_EXISTING = OFF ONLINE = OFF ...
2
votes
1answer
70 views

Is it possible to add a spatial index to a partition with Postgres 10?

I am creating declarative partitions like: CREATE TABLE mydata.my_features ( id bigint, my_feature1 character(1), my_feature2 integer, ... my_geom geometry ) PARTITION BY ...
0
votes
1answer
46 views

Questions on database table and index partitioning in SQL Server

I have developed Transact-SQL script to partition the table. To apply the partition, I am creating one non-clustered index for my partition key column and associating it to partition scheme. I have ...
8
votes
1answer
2k views

TOP(1) BY GROUP of very huge (100,000,000+) table

Setup I have a huge table of ~115,382,254 rows. The table is relatively simple and logs application process operations. CREATE TABLE [data].[OperationData]( [SourceDeciveID] [bigint] NOT NULL, ...
0
votes
1answer
120 views

Internal error code, kksgaItLvl_Setup [closed]

A simple query fails with the error. Has anyone faced this issue or has any solutions? SELECT MAX(some_value) FROM some_table WHERE some_id = 62328188199 AND some_name = '...
1
vote
3answers
167 views

Partial local index on partitioned Oracle table

We have a big transactional table, partitioned on transaction date (day). Each day, several million records are added. We need to do selects on this table, over a period of the last five days, and ...
-4
votes
2answers
934 views

Can data size be less than index size in sql database?

Can the indexes on a SQL Server table consume more disk space than the table itself? I have gone through several articles and studied this and a few expert recommended table partitioning, for ...
1
vote
2answers
60 views

Will table partitions help me?

I have 10 tables. dbo.Table2008 dbo.Table2009 ... dbo.Table2018 Each table holds approximately 500,000,000 records which are 20 columns wide (if that matters). And each table has a clustered ...
3
votes
1answer
746 views

Creating additional unique keys on a partitioned table

I am utilizing partition schemes in order to logically isolate data based on a TenantId column. TenantId is the partition column and clustering index across all tables that require tenant isolation. I ...
0
votes
1answer
367 views

MySQL Partitioning on event tables with start and end datetime columns

I am curious as to what solutions are out there for partitioning tables that log events, that allow both efficient querying and the advantages of pruning when truncating data. Suppose I have a simple ...
0
votes
1answer
58 views

MySQL - Indexing Partitioned Column

I have a table with no primary key defined, partitioned on a column, using List Partitioning method. It has huge data in one partition. Though partitioned column is used in WHERE clause while querying ...
4
votes
1answer
101 views

Defining a physical strategy for a table with ~110M rows

I am tasked with designing a table in PostgreSQL (never used it) to load data from flat files. This table will contain ~110M rows. Each row will have a certain range for IP Addresses (e.g.: 0.0.1.0 - ...
0
votes
2answers
798 views

How does MySQL decide what partitions to load into memory?

I have a server where the MySQL indexes won't fit into memory (2TB total index, 512GB RAM on machine), and am wondering how MySQL decides what parts to put into memory and what not. The relevant ...
1
vote
0answers
155 views

Indexing for Partition Elimination

I have an event table that is partitioned by month on EventCreatedDateTime and contains 1bn+ rows. CREATE TABLE [dbo].[Events]( [EventtId] [bigint] IDENTITY(1,1) NOT NULL, [...
0
votes
2answers
652 views

Partition an unpartitioned primary key index to become locally partitioned by primary key

I tried: alter index clave_primaria_de_b local; and I read: ALTER INDEX But I don't know how to do it.
6
votes
1answer
4k views

Table Partition Switch out problem

Please help! I am having error switching out from the partition i set up. I have the scripts and error information below : I created separate file groups for each range --Create Partition Function ...
4
votes
2answers
1k views

Partitioning a large table has not improved performance, why?

In SQL Server 2014 I have partitioned one of my large tables weekly and defined a sliding window scenario to switch the oldest week's data to the archive DB and create a new partition for the next ...
2
votes
2answers
2k views

Index not used on partitioned table

We have a table similar to this one mysql> SHOW CREATE TABLE my_requests\G *************************** 1. row *************************** Table: my_requests Create Table: CREATE TABLE `...
5
votes
2answers
1k views

Total billions of rows and daily millions of row insert: What database system suits best [closed]

We developed a system with very high volume of data but very simplified data structure. We had only cellX, cellY, timeStamp and value columns. Operations are: Insert 455+ thousand rows per day Query ...
1
vote
0answers
595 views

PostgreSQL doesn't use index on inherited tables

I have two tables in PostgreSQL 9.4.5 deployed on Amazon RDS: an append only, weekly partitioned, All Positions table. These store all position readings from about 100,000 sensors. There are about 50 ...
0
votes
1answer
47 views

How do I create a table best suited for storing address information

I have to create an address table in Oracle. Table is expected to hold millions of row. I created a table looking like this Table Address ID | Address Line1 | Address Line2 | Address Line3| City ...
1
vote
1answer
620 views

how to check whether or not queries are doing partition elimination when accessing my partitioned table?

I am considering partitioning a table. I am trying to think in terms of how we query that table so that I achieve partition elimination. Suppose I have a table that has a partition by month, what ...
2
votes
2answers
790 views

Partitioned Tables and Indexes - what are the downsides?

When talking about Partitioned Tables and Indexes for tables with less than 100 partitions, no nonaligned indexes: with that I mean: Nonaligned index An index partitioned ...
1
vote
1answer
656 views

Aligned index column order

Situation: There is one SQL Server (2014) database that has multiple partitioned tables, that all share the same partitioning strategy. Tables have a partition per unique [PartitionKey]. Every [Id] is ...
6
votes
1answer
2k views

Index is not used with table inheritance

I have a PostgreSQL 9.0.12 database with a master table and 2 child tables. My tables: CREATE TABLE test2 ( id serial PRIMARY KEY, coll character varying(15), ts timestamp without time ...
6
votes
1answer
1k views

Managing and speeding up queries on PostgreSQL table with over 3 trillion rows

I have time series data which spans over 10 years and has over 3 trillion rows and 10 columns. At the moment I use a PCIe SSD with 128GB of RAM and I am finding that querying takes a significant ...
7
votes
1answer
3k views

Performance issues with inherited tables and indices

I have a PostgreSQL database with a master table and 2 child tables. My master table: CREATE TABLE test ( id serial PRIMARY KEY, date timestamp without time zone ); CREATE INDEX ON test(...
4
votes
1answer
3k views

How to maintain a global index on a large table undergoing regular partition purges?

Problem I've got giant table haystack weighing in at nearly 3 TB, partitioned by month (date) and subpartitioned by client. I need to create a global index that spans all clients to facilitate ...
9
votes
3answers
3k views

Is there an advantage to not partition aligning an index?

I have the privilege of managing a large partitioned OLAP table. While reviewing this table I noticed one of the indexes is not aligned with the partitioning scheme. Since the author is not ...
1
vote
2answers
5k views

Compressing partition - what about index?

To save space on disk I want to compress some partitions of my tables: alter table TEST_TAB move partition TEST_TAB_2014 compress for all operations; I have also indexes on this partition. Should I ...
1
vote
0answers
159 views

schema for mysql database with 30million rows

We am building a SaaS backend for restaurants. We integrate directly with POS, so each POS keeps sending customer orders that we store in a following table in mysql. CREATE TABLE name ( `id` int(11)...
1
vote
0answers
166 views

MySQL table partition strange behavior (slow query suddenly)

(I asked this question in "stack overflow" and was recommended to ask in here). (MySQL version: 5.6.15) I have a huge table (Table_A) with 10M rows, in entity-attribute-value model. It has a ...
2
votes
1answer
5k views

Adding Indexes To a Partitioned table in SQL Server 2012

I have recently converted our one large table to use table partitioning in SQL Server 2012. It's a table that stores logs based on a time. So I created a partition function to split the table on my [...
2
votes
2answers
896 views

Update on view over partitioned tables updating all indexes

We have a table, which is partitioned through a date field into separate years. There is a view over all of these tables (Call) Schema is as follows: CREATE TABLE [dbo].[Call_2015]( [calID] [...
1
vote
1answer
350 views

Is searching an alternate index on a partitioned myISAM table concurrent or serial?

I have a myISAM table with 30 partitions. The primary key is a SHA1 hash value, which is also used in the partition selection function. I also have an alternate key - an autoincrement integer. Since ...
0
votes
1answer
220 views

Constraint exclusion to fetch latest N rows

I'm managing a table with several million records which are being inserted in real time. A part of my app needs to display the last N inserted rows, so at the beginning I just queried for: select id, ...
1
vote
1answer
2k views

How to partition an Oracle table with 65 million rows?

The table that needs partitioning contains a list of Components and their Values. So one table is tblComponents. Another is tblFields. Then this table, tblValues, contains the values of the ...
4
votes
1answer
8k views

Partitioning vs Indexes

I'm very new to databases, so I apologize if my question is very basic... Anyways, I'm creating a table with what seems like alot of data (500 million rows right now, and potentially twice as much in ...
0
votes
1answer
117 views

Will Partitions and Indexes on the same table help in performace of Inserts and Selects?

I have a table containing the list of visitors and this table has the following information. Visitor Browser Information Visitor Location Information Visitor Time Information No of Visits I have a ...
0
votes
2answers
1k views

Sync Indexes between two tables

I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database. I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync ...
6
votes
2answers
2k views

Recreate Indexes on 1 billion record table

I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partition this table on a new Clustered index with date column. I have just enough space (...
1
vote
1answer
8k views

Does 'update global indexes' not work if the index is already unusable?

I have a partitioned table in Oracle 11g with a PK (global index). If I truncate one of the partitions with ALTER TABLE tbl1 TRUNCATE PARTITION p1; the global index has the status UNUSABLE. If I ...
5
votes
1answer
17k views

After truncating a single partition its Primary key's index becomes unusable and all inserts/updates into that partition fail

I have a partitioned table: SAMPLE_PARTITIONED_TBL with 60 partitions (no sub-paritions) based on the PERIOD_ID numeric field (Data set: 201001...201212.. and so on). This table has several local ...
2
votes
1answer
120 views

Partitions and Indexes, in which cases should be used

I know some knowledges: Partitions used to achieve better performance (like described in this article) And indexes used to increase performance in selection operations. And as result my question: in ...
27
votes
1answer
38k views

Index optimization with dates

I have a large table of objects (15M+ row) in PostgreSQL 9.0.8, for which I want to query for outdated field. I want to divide the query by millions, for scalability & concurrency purposes, and I ...