4
votes
2answers
2k views

Rebuild large Oracle 10g index with insufficient TEMP space?

One of my (Oracle 10g) tables has grown over time to about 840 million rows. An index (DATE, NUMBER(38)) on it occupies 38GB at present. I'd like to change the index (I think it could make good use of ...
2
votes
1answer
240 views

Can I use dbms_redefinition with bulk data transformations for better results?

Here is a situation for bulk loading in Oracle: Schema A { T1, T2, T3, T4} Schema B { TR1, TR2} Where schema B is re-factored from schema A. Both exist in same DB. Schema A has terabytes of ...
5
votes
3answers
370 views

MyISAM Performance: Join Decomposition

in High Performance MySQL on page 159 they talk about breaking up complex queries into simple ones: Converting SELECT * FROM tag JOIN tag_post ON tag_post.tag_id=tag.id JOIN post ON ...
2
votes
2answers
1k views

SQL Server DR Plan - Test Documentation

I have been tasked with creating a document for the testing of our DR/BC Plan for our SQL Servers. We are using DB Mirroring (tier-1) and transaction log shipping (tier-2) and for tier-3 databases we ...
3
votes
2answers
338 views

How to create a user with a particular user_id?

Is it possible to specify the user_id of a user while creating it? SQL> create user user1 identified by user1; User created. SQL> select user_id, username from all_users where ...
3
votes
1answer
749 views

Oracle query against SQL Server database giving inconsistent results

We have multiple Oracle 11.2.0.2 databases with og4odbc database links to a SQL Server 2008 database. A particular table in the SQL Server database has two Varchar(36) columns. The following ...
1
vote
1answer
210 views

Why am I not seeing any benefits of parallel execution when parallel clause is used?

I have these two methods of creating a table and populating with rows. The first one uses a parallel (degree 3) clause, so I expected the inserts to be much faster. But I timed the time taken for a) ...
1
vote
1answer
800 views

SQL Server 2008 R2 - how to repair System Views

I have SQL Server 2008 R2 database, which is corrupted. Dbcc checkdb command gives result: Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was detected while collecting facts. ...
1
vote
2answers
681 views

SQL 2008 - DBCHECK Failed

I am using SQL Server 2008 and have a Maintenance Plan setup that does a DBCHECK before the nightly backup. This has been working fine for months. It has started failing. If I run a DBCHECK on the ...
4
votes
3answers
51k views

SQL Server 2008 R2 (Suspect) mode - how to repair?

I have SQL Server 2008 R2 database in Suspect mode. I tried to fix it running this query: EXEC sp_resetstatus ‘yourDBname’; ALTER DATABASE yourDBname SET EMERGENCY DBCC checkdb(’yourDBname’) ALTER ...
2
votes
1answer
241 views

How do you convert IBM DB2's recursive increment to MySQL version?

DB2 Queries: WITH TABLE1 (YEAR_END) as( VALUES (2011)) , TABLE2 (YEAR_END) AS ( VALUES(2011)) , TABLE3 (YEARS) AS ( SELECT 0+2008 FROM TABLE1 T1, TABLE2 T2 union ...
7
votes
1answer
8k views

Breaking and Restoring a mirror

I have to do some maintenance on a secondary node of a mirror. Since this is the result of the site becoming slow while the mirror re-established after a brief network outage I want to be safe and ...
4
votes
1answer
2k views

nest SELECT in INSERT

I'm trying to do something like: INSERT INTO stories (vid, ip, body, timestamp) VALUES ( (SELECT vid+1 FROM stories ORDER BY vid DESC LIMIT 1), INET_ATON('127.0.0.1'), 'test', ...
3
votes
3answers
792 views

ms sql differential backup size jumps

We are doing a full backup once a week and a daily diff between that. The total db is 80Gb, the diffs start out under 1Gb, then gradually grow to approximately 3-5Gb which is ok, but then all of a ...
4
votes
3answers
2k views

Port number change

We are planning on setting a static port for SQL Server 2005/2008 failover clustering. Any guide on what port to choose/select for 4 nodes of each cluster?(Active/Active) I am also thinking some ...
13
votes
3answers
9k views

What are reasons *not* to use the MEMORY storage engine in MySQL?

I recently discovered that MySQL has a "memory" engine that I wasn't aware of (most of my database work is for hobby projects so I learn what I need as I go). It seems like this option should give me ...
10
votes
3answers
676 views

Executing same request from C# VS SSMS give different execution time

I have a request like this one SELECT [EstimateId], [CreationUserId], [EstimateStatusValueId], [LanguageId], [LocationId], [EstimatorUserId], [FilterUnitSystemTypeId], [EstimateNumber], ...
2
votes
1answer
2k views

Migration from DB2 to MySQL

Does any one have experience on migration from DB2 to MySQL ? Please share with me. I tried googling, but I have not received much information on this.
14
votes
5answers
7k views

In MySQL, does the order of the columns in a WHERE clause affect query performance?

I am having performance issues on certain database queries that have large possible result sets. The query in question, I have three ANDs in the WHERE clause Does the order of the clauses matter? ...
3
votes
1answer
6k views

How do I calculate values based on the previous row after skipping the first 12 rows?

I'm looking for help to write this query. I have included some sample data below. My initial premise is that all the values from the 13th row onward will be dynamic and the first 12 rows will be ...
2
votes
2answers
1k views

SQL 2008 R2 3rd Party Peer-to-Peer Replication, Global Site Distribution

We are looking at hosting 3 globally distributed SQL Server installations at different data centers. The intent is that Site A will serve web traffic and data for a specific region, same with Site B ...
9
votes
1answer
3k views

How to find corrupt page in SQL Server 2008

I know I can execute a DBCC CHECKDB and get a status of my database. Questions How can I find if there is some corrupt data page into my database? If an error is throwndue to page corruption, where ...
7
votes
5answers
30k views

Matching single column against multiple values without self-joining table in MySQL

We have a table that we use to store answers to questions. We need to be able to find users that have certain answers to particular questions. So, if our table consists of the following data: user_id ...
5
votes
3answers
2k views

Which problems arise, declaring the size of all varchar parameters as max in stored proc?

Looking at this question, it seems, that other sites too have the problem of increasing column sizes in the process of time. Increasing just the size of a column in a table is a rather simple task. ...
19
votes
8answers
8k views

How do long columns impact performance and disk usage?

In our current project it just happens too often, that we need to extend columns by a couple of characters. From varchar(20) to varchar(30) and so on. In reality, how much does it really matter? How ...
6
votes
2answers
1k views

Displaying query plans in Server Management Studio

Another SQL server question: I have a simple query that gives me the most CPU intensive SQL since the counters were reset: select top 10 sum(qs.total_worker_time) as total_cpu_time, ...
2
votes
2answers
2k views

An inconsistency was detected

Am running asp.net application.Am searching records.if i click the view button i got this error, an inconsistency was detected during an internal operation in database(database name) on ...
6
votes
2answers
3k views

Non-unique multicolumn foreign key

I have a "comments" table that models a conversation on a topic, like this: id serial topic_id integer parent_comment_id integer body text So, every comment has a reference to its topic AND ...
6
votes
3answers
800 views

Multidimensional indices in databases

Which databases use real multidimensional indices? Is oracle ever using several indices to get data from tables, or will it always take the one that seems to have the highest selectivity? How about ...
9
votes
4answers
12k views

Methods of speeding up a huge DELETE FROM <table> with no clauses

Using SQL Server 2005. I am performing a huge DELETE FROM with no where clauses. It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE. The problem is ...
3
votes
2answers
2k views

Select XML values when attribute involved

I have a table that stores XML values in field: OtherData. An XML value from one row: < file path="C:\WINDOWS\system32\svchost.exe" /> I would like to select all rows that have an ...
7
votes
1answer
206 views

Are relations slower than a big, inefficient table?

I've been asked in my job to violate the first normal form (repeating groups across columns, using empty / null values) several times, "for the sake of computer processing power". In a nutshell, a ...
11
votes
4answers
31k views

How to insert (file) data into a PostgreSQL bytea column

This question is not about bytea v. oid v. blobs v. large objects, etc. I have a table containing a primary key integer field and a bytea field. I'd like to enter data into the bytea field. This ...
4
votes
2answers
3k views

Determine Reason for Deadlock in MySQL

We've been seeing some deadlocks occur but have not been able to reproduce. Is there any way to determine, after the fact, what queries were running, etc to figure out why deadlock occurred?
10
votes
3answers
4k views

How do indices impact query performance?

Obviously keeping several different indices has a negative impact on insert and delete performance. How about query performance: Does it make sense at all keeping too many indices on a table? Will the ...
11
votes
2answers
20k views

Database model with users, roles and rights

I have a database model with a user table and role table. I want to control the access (rights) to up to 10 different elements. The access can be granted to either a role or a single user. Below is ...
5
votes
2answers
2k views

How to wrap long lines when SELECTing SQL text columns?

I'm selecting from a table with long text columns. I'd like to wrap long lines to a maximum line length. From: SELECT * FROM test; test_id | text ...
9
votes
3answers
9k views

Tracing, debugging and fixing Row Lock Contentions

Off late, I've been facing a lot of row lock contentions. The table in contention seems to be a particular table. This is generally what happens - Developer 1 starts a transaction from Oracle ...
2
votes
1answer
255 views

Are there other sites still using SQL Server with codepage 850 or codepage 437 for their sql scripts?

Using one of these codepages is a disaster when scripts are edited by Visual Studio or TFS, but sometimes it is difficult to change old standards. What must be observed, when changing to the current ...
8
votes
2answers
2k views

How to migrate SQL Server Stored Procedures using temporary tables or table variables to Oracle?

C# developer encouraged by management to write SQL Server stored procedures often produce procedures like this create table #t1 (...); insert into #t1 Select ... from table_a where ...; insert into ...
10
votes
1answer
785 views

SQL Server 2008 R2 Dirty reads - how non-atomic?

I am wondering "how dirty" dirty reads can get under a read-uncommited isolation level. I understand that rows that have been updated but not yet committed are visible, but: Can a row appear as ...
3
votes
1answer
191 views

Can I determine if a stored procedure uses dynamic sql without parsing the definition?

Since there seems to be no way to strip the procedure definition from comments without parsing cf. this, there seem to be no reliably T-SQL query to answer this question. Or can I get this ...
10
votes
3answers
4k views

SQL Server performance sudden degradation

I have a SQL Server 2005 that has become unpredictable of late, and I'm scratching my head as to why. Queries that executed in seconds are changing plans and taking minutes (taking the time in full ...
4
votes
2answers
232 views

Are there other sites forbidding SQL developer to use c style comments?

My personal opinion is that C style comments should not be used by sql developers. The problem with C Style comments is, that they do not nest. C Style comments are one of the lifesavers for the ...
4
votes
1answer
392 views

convert legacy showplan text into graphical format

Does anyone know of a way to convert legacy showplan text into a graphical format? In other words, can I take the StmtText returned from SET SHOWPLAN_TEXT ON and convert that to show me a graphical ...
5
votes
1answer
2k views

DBMS_Resource_Manager.Calibrate_IO Usage

DBMS_Resource_Manager has a procedure called Calibrate_IO with a Physical Disks input parameter. The documentation says this value should be set to the... Approximate number of physical disks in ...
6
votes
4answers
919 views

When to use MySQL query_cache?

Up until recently, I have viewed the query cache as a very important tool to improve query performance. Today, I was listening to a podcast that discussed tuning the query cache to 0, and using a ...
13
votes
2answers
2k views

Index vs. partition

Why is it not possible to achieve performance improvements with indexes only, so that other techniques like table partitioning becomes necessary? The question relates only to performance, of course ...
15
votes
7answers
7k views

Storing IP address

I have to store the IP address of all registered users in the database. I am wondering, how many characters should I declare for such a column? Should I support IPv6 as well? If so, what is the ...
4
votes
2answers
935 views

Should we use SQL Server partitions for a multi-tenant database design?

My organisation is creating a multi-tenant data architecture to support analysis of data uploaded by multiple different clients. The database is Microsoft SQL Server. It has been suggested that ...

15 30 50 per page