Most Popular
1500 questions
33
votes
6 answers
Combining separate ranges into largest possible contiguous ranges
I'm trying to combine multiple date ranges (my load is about max 500, most cases 10) that may or may not overlap into the largest possible contiguous date ranges. For example:
Data:
CREATE TABLE test (
id SERIAL PRIMARY KEY NOT NULL,
range…
Villiers Strauss
- 617
- 2
- 6
- 9
32
votes
2 answers
How to make a transition from SQL Server DBA to Oracle?
As an interest, if I would to transition from a SQL Server DBA to Oracle what will be the major learning or unlearning that I would have to do?
I would assume the concepts are the same and the difference is merely the programming language but I have…
darwindeeds
- 560
- 4
- 7
32
votes
1 answer
Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used
I have two tables employee and phones. An employee can have 0 to n phone numbers. I want to list the employee names with their phone numbers. I am using the below query which runs fine.
SELECT empname,array_agg(phonenumber) AS phonenumbers
FROM…
Programmer
- 423
- 2
- 5
- 6
32
votes
5 answers
The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values
One of INSERT INTO script is written as following.
INSERT INTO tableName (Column1, Column2,....) VALUES (value1, Value2,...), (value1, Value2,...),....
Following is the error we are facing on parsing above insert statement
Msg 10738, Level 15,…
Aasim Abdullah
- 2,915
- 4
- 23
- 40
32
votes
4 answers
What's better for large changes to a table: DELETE and INSERT every time or UPDATE existing?
I am making a project where I need to change around 36K records in one table daily. I'm wondering what will perform better:
delete rows and insert new ones, or
update already existing rows
For me it is easier to just delete all the rows and insert…
adopilot
- 2,423
- 8
- 31
- 46
32
votes
5 answers
Get column names and data types of a query, table or view
Is there a PostgreSQL query or command that returns the field names and field types of a query, table or view?
E.g., a solution if applied to simple SELECT query like SELECT * from person should return a list like:
Column Name | Column…
vfclists
- 1,053
- 4
- 13
- 21
32
votes
8 answers
Security implications of restoring a backup from an unknown source?
Scenario: You're handed a database backup and told to restore it to a server (that's already hosting other databases), but are given no useful information about what the backup contains or whether the source should be trusted.
Question 1: What are…
Simon Righarts
- 4,733
- 27
- 31
32
votes
3 answers
How Does Table Partitioning Help?
I am having difficulty to grab the idea of pros and cons of table partitioning. I am about to start work on a project which would have 8 tables and one of them will be the main data table which will hold 180-260 million records. As it will be…
Rick James
- 1,271
- 3
- 15
- 19
32
votes
2 answers
Where can I find database tuning exercises for learning?
As a developer, often DBAs take responsibility for resolving performance issues at the database level so we don't get that much experience diagnosing, tuning, refactoring queries etc.
I'm looking for a database with a bunch of tables, data,…
flesh
- 421
- 4
- 3
32
votes
2 answers
What is the fastest way to insert large numbers of rows?
I have a database where I load files into a staging table, from this staging table i have 1-2 joins to resolve some foreign keys and then insert this rows into the final table (which has one partition per month). I have around 3.4 billion rows for…
nojetlag
- 2,877
- 9
- 33
- 42
32
votes
1 answer
How to uninstall SQL Server Management Studio 2012?
How to uninstall SQL Server Management Studio 2012?
ControlPanel's "Uninstall or change programs" (in Windows 7 Prof) gives a row of entries:
while internet search results always tell: "click Microsoft SQL Server 2005, and then click Change"…
Fulproof
- 1,382
- 2
- 26
- 35
32
votes
1 answer
Why would you index text_pattern_ops on a text column?
Today Seven Databases in Seven Weeks introduced me to per-operator indexes.
You can index strings for pattern matching the previous queries by creating a text_pattern_ops operator class index, as long as the values are indexed in lowercase.
CREATE…
Iain Samuel McLean Elder
- 2,328
- 4
- 25
- 39
32
votes
3 answers
MySQL creates temporary tables on disk. How do I stop it?
We are running a site (Moodle) that the users currently find slow. I think I have tracked down the problem to MySQL creating temporary tables on disk. I watch the variable created_tmp_disk_tables in Mysql Workbench server administration and the…
user30431
- 453
- 1
- 5
- 11
32
votes
1 answer
"ORA-03113: end-of-file on communication channel" on startup
I have been reading posts here, on Oracle support, and anywhere else I can find for the last three days and I've given up on this problem...
An Oracle database hung. Shutdown of the database sat for a few hours and then it quit. It wouldn't restart.…
kainaw
- 1,362
- 3
- 14
- 26
32
votes
5 answers
Downgrading from SQL Server 2008 to 2005
Database files that are built using SQL 2008 are not compatable with 2005. Is there a work around?
Sevki
- 533
- 1
- 7
- 11