0
votes
1answer
61 views

Postgres Table Ownership

I'm new to PostgreSQL and I'm trying to change the table owner for a bunch of tables. I was able to change the ownership by logging in a the postgres user and executing alter table owner to user1, but ...
5
votes
1answer
4k views

Best way to create materialized view in mysql

I am using mysql 5.6, I am not being able to create materialized view here like oracle. I got one or two solution like flexview and all... Can anybody tell me best way to create materialized view in ...
2
votes
2answers
211 views

How to compare an Oracle database with a Postgresql database?

We are currently migrating an Oracle database 10gR2 to a Postgresql database 9.3. Thanks to Hibernate, all tables can be recreated seamlessly. However, the Oracle database is huge. We are going to ...
2
votes
1answer
1k views

Converting Heap to MyISAM issue

At work we have a problem that has now occurred more then once. Problem is that query is in state of converting HEAP to MyISAM and it paralyzes our server( query in this state is not able to kill ...
11
votes
2answers
4k views

Refresh materalized view incrementally in PostgreSQL

Is it possible to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed? Consider this table & materialized view: CREATE TABLE graph ( ...
2
votes
1answer
97 views

What does the Postgres <-> operator do?

I was reading through the Postgres documentation on materialized views and in one example they use the following: SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10; I tried to ...
1
vote
1answer
99 views

Deleting duplicate rows with condition [duplicate]

I have posted a similar question before, but this one is a little modified. I have a table with 4 columns as shown below. ITEMCHARGE CPT4 EFF_DATE CPT4MOD 326.8 29075 ...
0
votes
1answer
59 views

MongoDB Sharding : What should be the Sharding key if we want to find records with more than one field

I am having collection ("links") with listed fields: collection-name: "links" 1) url 2) title 3) score 4) description 5) ratings (embedded_many) and having index on "url", "title", "score" because ...
0
votes
1answer
64 views

Mysql Master-Master Replication Error

Configured Master-Master Replication But both servers slave shows the below error !! Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ...
1
vote
1answer
676 views

How to update a huge MySQL table from a text file

I have a huge MySQL table with 1.3 TB data with InnoDB engine format. Now I have a text file containing updated values of 2.2 GB of records in the 1.3 TB of data. How do I proceed ? I am a newbie ...
1
vote
1answer
274 views

Powershell Restore Azure SQL Database to local with Restore-Database

I have written a powershell script that uses Start-AzureSqlDatabaseExport & Get-AzureStorageBlob to export my Azure DB to a bacpac and download it locally, the next step is to restore the bacpac ...
0
votes
1answer
92 views

How to unify three separate tables having same data, same columns but tuples in different languages. Only one key(caption) is common to all

This needs to be done in order to complete the unification of many tables present in different databases with different languages. (Part of Dictionary Management, will have caption and messages where ...
2
votes
3answers
6k views

Unable to connect to Oracle from SQL Developer

below is the error Status : Failure -Io exception: The Network Adapter could not establish the connection It's connecting from my system but unable to connect from other systems on the ...
3
votes
2answers
788 views

use an Index correctly to optimize queries

I have an SQL database with millions of records and when I'm querying the data like select * from ActCosts where ScenarioID= 456 The tables has 1,323,718 rows and it gives me like 50,000+ rows ...
-2
votes
1answer
939 views

Is it possible to install SQL Server 2008 R2 on Windows Server 2012 R2? [closed]

Is it possible to install SQL Server 2008 R2 on Windows Server 2012 R2? I have the developer edition installation files.
5
votes
2answers
283 views

T-SQL brain teaser: find non-overlapping sets

I would like to ask for help coming up with a query, which can identify groups on non-overlapping records. Here is a sample scenario (admittedly contrived). Let's say I have employees who are ...
0
votes
1answer
48 views

optimizing a select query of 1.5 million entry

I have the following query: SELECT s0_.id, s0_.lowresimageurl FROM app_instagram_shop_picture s0_ FORCE INDEX ( idx_deleted_shopId ) LEFT JOIN app_instagram_shop s1_ ON s0_.shop_id = s1_.id AND ( ...
1
vote
2answers
255 views

Mongodb: Does not seem to respect capped size limit

Running mongodb on win2012 Mongo db version v2.6.4 Our collection is created with this param: capped:true, size:500000000000 // 500 gb But the data dir, that only holds this one collection, has ...
-1
votes
1answer
38 views

How to manage two tables in SQL Server [closed]

i have two tables offer1 and offer2 The two tables are Offer1 Offer2ID|OfferName|OfferExpiryDate Offer2 Offer2ID|OfferName|OfferExpiryDate now i have third table which keeps record ...
0
votes
1answer
34 views

tables relationships in database

Just a basic question here but it's been bothering me for a while. Do all tables have to be related to another? I'm trying to design a database so far I have 15 tables, 13 of them have relationships ...
7
votes
2answers
8k views

How to create a temporary table using VALUES in PostgreSQL

I am learning PostgreSQL and trying to figure out how to create a temporary table or a WITH declaration that can be used in place of regular table, for debugging purposes. I looked at the ...
1
vote
2answers
353 views

Deleting duplicate rows with condition(Using SQL and LINQ)

I have a table with 5 columnsID,CPT4,CPT4Mod,ItemCharge,Eff_Date. Deletion Condition: When rows exists with duplicate CPT4and CPT4Mod field values but different ItemCharge and Eff_Date values, ...
0
votes
1answer
552 views

Corrupt DB for a noob

Please help me! I am not a DB admin, and I have been thrust into the role after taking a new job. I need as much step by step as you can give me... I've already done the dbcc ...
0
votes
0answers
73 views

Possible to get log file with only statements sent to Postgres from my app (and not from psql prompt)?

I'm using an ORM in my app and would like to see all statements sent to Postgres so that I can figure out exactly what the ORM is doing. In the Postgres config file I added log_statement = 'all' to ...
2
votes
1answer
111 views

Sorting by two columns conditionally [closed]

Column a has category ids, column b has set orders: +-------------+-----------+ | category_id | set_order | +-------------+-----------+ | 80 | null | | 81 | null | | ...
1
vote
1answer
220 views

How to Select the First (or last) entry for a specific day

I have a table "AttendanceRecords" with entries like this EntryID UUID PRIMARY KEY, StafID UUID REFERENCES staffmembers(ID) NOT NULL, ArrivalTime TIMESTAMP NOT NULL, DepartureTime TIMESTAMP For one ...
0
votes
1answer
42 views

How can I aggregate multiple results from another table using something like BOOL_AND?

I'm using PostgreSQL as my database. CREATE TABLE users ( id SERIAL NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); CREATE TABLE groups ( id ...
1
vote
1answer
246 views

database design for simple comment

I have a following given scenario. A user can have zero or more item. A user can comment on the item. (Just simple comment listing with name of people who comment, no need hierarchical structure for ...
0
votes
2answers
114 views

SQL Query Help to get Quiz statistics

I'm coding a quiz web application and i need help. I've got three tables Nicknames : |USER_ID|NICKNAME| that stores USER_ID(auto increment) and NICKNAME registered to my system. Questions : ...
0
votes
1answer
224 views

Oracle Golden Gate error 00146

I am deploying Oracle Golden Gate to sync data from Sql Server 2008 to Sql Server 2008. One of the extract process have stopped.This is the error: 2014-12-21 17:22:09 ERROR OGG-00146 Call to ...
8
votes
2answers
507 views

Simple DELETE, but complicated execution plan

When I run this delete: DELETE FROM ETLHeaders WHERE ETLHeaderID < 32465870 ...it deletes 39,157 rows. It should be simple because it is deleting on ETLHeaderID which is the clustered index and ...
2
votes
1answer
62 views

MySQL column type to boost performance

I'll have a table where: userid (link to user's id in users table) foldername (a varchar, max. 260 char) entry (varchar, max. 1024) data (varchar, max. 20kb) Single user will have several ...
0
votes
2answers
817 views

insert inserted id to another table

Here's the scenario: create table a ( id serial primary key, val text ); create table b ( id serial primary key, a_id integer references a(id) ); create rule a_inserted as on insert to a do ...
3
votes
1answer
248 views

Are CTEs multithreaded?

Here and here seem to suggest that they are, but I've also been told (source unlocatable) that a multi-row single-statement insert will only use one thread for all writing, that to multithread it, the ...
1
vote
2answers
394 views

Performance of single vs multiple column unique constraint

I'm using PostgreSQL 9.3. I want to understand if I have an option to make a constraint unique across the entire table vs. unique across a subset of the table (i.e. by using 2 columns in the unique ...
5
votes
2answers
102 views

New Index on table cause table to be locked really often

Here are my tables : CREATE TABLE [dbo].[Trackers] ( [IdTracker] INT IDENTITY (1, 1) NOT NULL, [IMEI] NVARCHAR (16) NULL, [CreationDate] ...
0
votes
1answer
120 views

Need help in incrementing the date in oracle

Error thrown as "Invalid number" while trying to increment the date. Given below is the query executed through sqlplus. Please help me on this. select ''''||to_date(:To_date, 'dd-mon-yyyy')+1||'''' ...
1
vote
1answer
115 views

How to join fixed row table against mapping table and reveal unmapped records

I have two tables and I am having trouble figuring out how to join them to meet my projection needs. Here is a scenario that illustrates the problem 1. The first table has a fixed set of values/rows; ...
6
votes
2answers
438 views

Why doesn't a delete increase unused space in a table?

If I populate the following table: IF OBJECT_ID ('dbo.CleanTableTest', 'U') IS NOT NULL DROP TABLE dbo.CleanTableTest; GO CREATE TABLE dbo.CleanTableTest (FileName nvarchar(4000), ...
3
votes
1answer
874 views

How do I identify the program that called a stored procedure?

As part of an application database design spec, we've been asked to block execution of a set of stored procedures if they have been fired directly from SSMS / osql / sqlcmd and so on; that is, they ...
0
votes
0answers
401 views

MySQL dump - how to order tables?

It seems that mysqldump dumps tables in sequential order - using the same order tables are shown when running: show tables; I would like to reverse that order - is it possible to do without getting ...
0
votes
1answer
38 views

using foreign key as primary key

MySQL: If I have a user table and multiple other tables like seller, buyer etc, is it okay to use user_id foreign key as primary key inside tables like seller, buyer? That'll leave gaps in primary ...
1
vote
1answer
70 views

Table-based custom type does not verify check constraints

Is there any way for a table-based custom type to obey the table check constraints, other than creating my own type based on the table? Assume the following table that serves as a source for the ...
0
votes
1answer
36 views

Why storing product fees in another table is preferred?

MySql: What is the benefit of storing several types of fees in another table like Product Table -> (productid...), ProductFee Table -> (productid, feetypeid, feeamount), ProductFeeType -> ...
0
votes
1answer
201 views

Turning multiple fields in column to row

I have an issue that has really stumped me in SQL Server. I have recently had the need to insert a row in to a table, made up from a number of columns. I want to make the one row up from something ...
1
vote
2answers
3k views

When is it safe to disable InnoDB doublewrite buffering?

MySQL InnoDB allows us to disable doublewrite buffering by setting innodb_doublewrite = 0. Other databases doesn't seem to allow this setting to be tweaked. How could InnoDB still be able to maintain ...
1
vote
2answers
133 views

New to databases - does my ERD reflect good database design?

I'm making a database to hold data for a media player that I'm working on. Originally, I thought to use one table with the fields: {filePath,artist,title,album,track_number,length,date,artworkUrl} ...
2
votes
0answers
195 views

Exclude a User From an Audit

I need to set up a SQL Server audit to capture all database activity for all logins except one - the login our application runs under. In this case the login I want to exclude is CPSDOM\cpointedb. In ...
0
votes
0answers
32 views

Efficient settings for linked server to SSAS database

We are using OPENQUERY scripts to run MDX from sql-server. We're experiencing efficiency issues when we first run these queries each morning. The linked server objects which link to our SSAS ...
0
votes
0answers
351 views

Oracle - Slow queries without prepending schema name or providing hint

I am logged in as the schema owner in Oracle 11gR2 on Linux. I have 55 million rows in table. select max(col) from table; - takes 300 seconds (index full scan) select max(col) from schema.table; - ...

15 30 50 per page