4
votes
2answers
535 views

What is the best practice for mapping from natural keys to integer-based keys? (ETL)

This question originates more from an "enterprise architecture" point of view. (Reason being, our company is just starting to get into data management, and actually having DBA's -- I know, horrific ...
4
votes
4answers
1k views

Is tuning the innodb_buffer_pool_size important on Solaris ZFS?

We're running a moderate size (350GB) database with some fairly large tables (a few hundred million rows, 50GB) on a reasonably large server (2 x quad-core Xeons, 24GB RAM, 2.5" 10k disks in RAID10), ...
5
votes
1answer
7k views

MySQL high availability, failover and replication with Latency

We are in the process of implementing a new CMS (Drupal 6.x) that runs on MySQL. We have two data centers -- primary and secondary -- with known latency between them. We're unsure which version of ...
1
vote
1answer
194 views

In a recruiting website, should I force a choice between one table for jobseekers and employers or 2 separate tables?

I'm working on a recruiting web site and I was wondering what would be best between: 1) A User table, the employer accounts are linked to a company (therefore when companyID is null then it's a job ...
3
votes
2answers
314 views

Comparing two XML query plans

Hopefully a simple one, if I have two (fairly complex, 30-40 node) .sqlplan files (from the same SQL text) what is a good way to compare them side by side? Or just have it tell me what it did ...
11
votes
2answers
5k views

SQL Server 2008 - Partitioning and Clustered Indexes

So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario. Comments about how we ...
31
votes
7answers
21k views

SQL: SELECT All columns except some

Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table. Something like: SELECT * ...
3
votes
1answer
669 views

SQL Server 2008 R2 Express for a single website?

Someone recently told me I could use SQL Server 2008 R2 Express edition for a single website. But don't you need the Server Agent for connections (I think. I'm trying to remember things from over ...
3
votes
2answers
3k views

How to check that stored procedure is valid

As we know, it is possible to compile and to have so stored procedures: CREATE PROC dbo.MyProc1 AS SELECT * FROM Not_Exists_Or_Removed_Table or CREATE PROC dbo.MyProc2 AS SELECT Id ...
3
votes
4answers
4k views

SQL Server 2008 R2 Ent trial expired move to express?

So someone let our SQL server 2008 R2 enterprise trial expire on a web server. It was part of a plan to restart a project and move everything we had over to MySql instead. We don't need anything ...
1
vote
1answer
143 views

MyISAM table row size is bigger than the sum of all the columns size?

I have a table: CREATE TABLE `playlist` ( `pid` int(11) unsigned NOT NULL, `mid` bigint(21) unsigned NOT NULL, `status` tinyint(1) NOT NULL, `add_time` int(11) unsigned NOT NULL, ...
4
votes
1answer
399 views

Oracle AWR shows missing stats for SQL ID as top elapsed statement

Why SQL ID has null number of executions and elapsed time / execution in AWR report. SQL ID is among top elapsed command on AWR but no details stats whoing time / execution. Would it be because shared ...
1
vote
1answer
3k views

One MySQL user from 2 hosts not on same subnet

I've googled around for this with no luck. I have a master database with one slave. The perms for the slave are set and it runs fine. I'd like to add another slave using a clone of the original ...
5
votes
1answer
2k views

Print Parameters in Dynamic SQL

I've used dynamic SQL for many tasks and continuously run into the same problem: Printing values of variables used inside the Dynamic T-SQL statement. EG: Declare @SQL nvarchar(max), @Params ...
3
votes
2answers
826 views

DMV Queries as Perfmon Counters

Certain DMV queries that return various statistics that increment since boot would be useful as perfmon counters. For example, take: SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats; Most ...
8
votes
2answers
9k views

How do I switch off SCHEMABINDING for a view without recreating it?

How do I switch off SCHEMABINDING for a view without recreating it?
8
votes
2answers
6k views

Is REINDEX dangerous?

I've been trying to COUNT(*) a table with 150,000 rows that has a Primary key. It tool about 5 minutes, so I figured out this is an indexing problem. Citing the PostgreSQL manual: REINDEX is ...
9
votes
2answers
16k views

How to use prepared statements within MySQL stored procedures?

I'm using mysql and I need to somehow use the column curid returned by the prepared statement in the later query. I use prepared statements because as I've read, it is the only way to pass a variable ...
3
votes
2answers
3k views

tsql: table data size won't shrink after large row deletion

i have a table with 12 months+ data in it. I backup the table then delete all rows keeping only rows inserted in the last month. But the table size won't shrink, nor the underlying physical file. ...
7
votes
4answers
5k views

How do I do a complex GROUP BY in MySQL?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to be able to group rows together that have an equal key, but I don't want ...
4
votes
1answer
4k views

How do I make MySQL auto increment in random order?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to create a new column for each key that would be an integer such that ...
6
votes
1answer
1k views

SQL Server nonclustered index with INCLUDED columns and their order

I have a nonclustered index (called NCIDX1) on col1, col2 of a table. I included col3, col4, col5, col6 as included columns in this order. If I run the following query: select col1,col2 from ...
7
votes
4answers
19k views

Why does MySQL say I'm out of memory?

I was trying to execute a fairly large INSERT...SELECT in MySQL with JDBC, and I got the following exception: Exception in thread "main" java.sql.SQLException: Out of memory (Needed 1073741824 bytes) ...
4
votes
1answer
607 views

Impact of an Asynchronous Mirror / Full Recovery Model on Performance

For an asynchronous mirror in SQL Server 2008 R2, using the full recovery model is required. Assuming that the network and disk IO on both sides of the Mirror can keep up with the transaction logs ...
6
votes
1answer
2k views

sp_WhoIsActive and get_plans — Actual Plan?

Does Adam Machanic's sp_WhoIsActive return the actual query plan or the estimated plan when run with @get_plans=1 ?
4
votes
1answer
216 views

When refreshing materialised views does the index automatically refresh

When refreshing materialised views does the index automatically refresh
18
votes
5answers
4k views

Why use an int as a lookup table's primary key?

I want to know why I should use an int as a lookup table's primary key instead of just using the lookup value as the primary key (which in most cases would be a string). I understand that using a ...
3
votes
1answer
425 views

Error moving SQL Server 2000 to SQL Server 2005 NOLOCK Error

I need to move a database from the SQL Server 2000 (32 bit) to SQL Server 2005.But,When I attacted my database to SQL Server 2005,it's gives me error.(NOLOCK error) Also , After I running dbcc check ...
3
votes
1answer
7k views

Referencing PL/SQL variables in FOR loop

I have written a PL/SQL script to find the size of a long column in a table. Just to make the script generic I am passing the table Name and column name as variables , but I am getting an error saying ...
12
votes
5answers
4k views

Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

I've got about a billion rows of data in a table with a name and an integer in the range 1-288. For a given name, every int is unique, and not every possible integer in the range is present--so there ...
3
votes
7answers
17k views

Can't Login to SQL Server 2008 when running single user mode

I'm following the MS documentation here to migrate logins from one instance of SQL Server to another as part of the process described here. This involves putting the server into single user mode ...
7
votes
1answer
918 views

Materialized views in oracle

How are materialized views implemented in oracle? I've created a materialized view in oracle. What I now see is a quite normal table with a job. Are materialized views translated into tables & ...
7
votes
1answer
3k views

Oracle materialized view refresh schedule

Since I see that it is possible to specify an expression like sysdate + 1, here are my questions: Is it possible to specify the exact time of the refresh for materialized views in oracle? Is it ...
16
votes
2answers
21k views

How do I install pgcrypto for PostgreSQL in Ubuntu Server?

I'm using Ubuntu Server 10.10 and I have installed PostgreSQL 8.4 using apt-get install postgresql. I would like to use the built-in sha1() function, but it seems that I have to install pgcrypto ...
4
votes
1answer
3k views

Using a Parameter Query to generate Charts in MS Access Reports

I am using Microsoft Access 2007 and I have a query which looks something like this; PARAMETERS Identifier TEXT(255); SELECT DateDiff('h', (SELECT Min(SampleDateTime) FROM Specimen WHERE ...
9
votes
2answers
834 views

A Non-DBA Asks: How to Painlessly Copy/Move a SQL Server instance to another Server via Backup/Restore?

We've just got a new server so I need to copy the existing SQL Server 2008 instance over from the old db box. I normally do this by copying .mdf and log files over and attaching them but I'm not able ...
16
votes
3answers
22k views

Execution Plan Basics — Hash Match Confusion

I am starting to learn execution plans and am confused about how exactly a hash match works and why it would be used in a simple join: select Posts.Title, Users.DisplayName From Posts JOIN Users on ...
13
votes
1answer
1k views

Sql Anywhere 11: Restoring incremental backup failure

We want to create remote incremental backups after a full backup. This will allow us to restore in the event of a failure and bring up another machine with as close to real time backups as possible ...
6
votes
5answers
10k views

User Permissions Messed Up following a Backup -> Restore Operation

I had to move several SQL Server 2008 databases to our new db server so I backed them all up (to .bak files), copied these files over to the new box and restored them (all done using SQL Management ...
1
vote
0answers
719 views

An advice to design a fact table with a scd as a dimension

I have a problem of conception and I would like to get your ideas on how I'll be able to do what I am aiming. My goal is to create a datawarehouse, with three dimensions and one fact table so far. ...
3
votes
3answers
1k views

How does NHibernate handle execution plan?

I heard from a podcast that there are no ORMs that have a good solution for execution plan reuse. It will lead to increased execution plan cache which affects the performance. How does NHibernate ...
3
votes
1answer
2k views

When should I use Full-Text Search?

When should I use Full-Text Search? Instead of using SELECT * FROM myTABLE WHERE myCOLUMN LIKE '*something'
4
votes
1answer
7k views

Running a stored procedure across db Link

I have a script whose sole propose is just to transfer data from one schema to another via a db link. Part of the process however is that after the data has been loaded, a stored procedure needs to be ...
6
votes
3answers
3k views

Specify Connection in Management Studio T-SQL Query

When adding users are roles to DB servers I often use the "Script this action" function from the GUI. I then just go to "Connection :: Change Connection" to do the same on my other servers. Is there ...
6
votes
2answers
703 views

Resources for SQL Server developer migrating to Oracle

What resources can you recommend for a SQL Server developer wanting to learn Oracle basics? I am looking for a comprehensive whitepaper or blog post describing differences between these systems and ...
1
vote
4answers
617 views

Best way to automate running several MySQL commands

We have several core db tables on our system and allow our employees access to these tables via views that just show them the data they need to see. We created these views in a new db specific to ...
2
votes
2answers
3k views

Database mirroring - Witness only

In SQL Server 2008 R2 Express the only mirroring alternative is "Witness only", however I can't find a good explenation to what this is. I would like to have a database on a virtual server and then ...
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
246 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
395 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 ...

15 30 50 per page