5
votes
1answer
357 views

Inline table-valued function vs inline sql

I'm seeing some odd behavior in trying to tune an inline table-valued function. Specifically, if I take the guts of the function and it with the same parameters as I'd have called the function with, I ...
5
votes
1answer
144 views

Practical application of EXISTS vs. LEFT JOIN to find addresses for delivery

Our end users have many ways of shipping product to our customers. They can use the customer address in one table ([customer]), a specific delivery address in another ([dropship]), or yet another ...
2
votes
1answer
2k views

Is there a progress indicator for OPTIMIZE TABLE progress?

MySQL 5.1.4x (Windows) | Innodb I recently purged data from a mySQL DB (a few hundred thousand rows) and I'm planning to use OPTIMIZE TABLE LOGTABLEFOO1,LOGTABLEFOO2,LOGTABLEFOO3; to reduce the ...
0
votes
3answers
719 views

Postgresql 9.2 won't start after rerunning installer

All, I made a fatal mistake and accidentally ran the core installer for postgresql rather than the stackinstaller. I didn't go all the way through the install process but it still set something that ...
2
votes
1answer
292 views

SQL Server table appears locked for no reason

My database was running just fine this morning, up until it wasn't. This query: SELECT TOP (1) * FROM tblTask; comes back in no time at all, while this query: SELECT * FROM tblTask this_ with ...
0
votes
1answer
251 views

Why doesn't more memory necessarily make Postgres more performant?

The Postgres resource consumption docs state, under the shared_buffers: ...it is unlikely that an allocation of more than 40% of RAM to shared_buffers will work better than a smaller amount. Why ...
3
votes
1answer
646 views

Normalized Table Structure for Logging

How can I normalize a table that has four columns for logging the latest activity for a single record: Created On Created By Modified On Modified By We have dozens of tables, all with these 4 ...
1
vote
3answers
2k views

Computer restarted and Postgres 9.2 service cannot be started?

My firewall is down and when I tried to start the Postgres service it gave me an error that said: the service started and then stopped. Some services stop automatically if they are not in use by other ...
1
vote
1answer
693 views

How do I use the unload command to export a table to a IXF file

For a table T in schema S, how do I use the unload command to export T data into a IXF file? The IBM DB2 documentation isn't very helpful for this simple case. I am trying to compare performance ...
1
vote
1answer
234 views

Set up replication and migrate to InnoDB with low downtime

Current situation is this: I have a large legacy DB using only MyISAM tables, the size of whole DB is 4GiB, one of the tables contains over 10 million rows, so it is pretty large. The database is ...
1
vote
0answers
145 views

Distribution Agent running but subscription does not exist?

I have an issue where in my replication topology, the distribution agent is running, and says there are no commands to distribute. However, checking the undistributed commands, it shows there are a ...
1
vote
3answers
374 views

MySQL replication - some values are not being replicated

MySQL Replication issues I might sound clue less here as i am :) , I am having problem with mysql replication, i.e. replication seems to be missing in some random occasion. My structure looks like: ...
1
vote
1answer
95 views

Monitor Sql Server 2000

I was wondering if there is a free tool to monitor state of SQL Server 2000. I would prefer a web tool. I would like to see if some database is accessible, or are there any problems on server, etc. ...
0
votes
2answers
581 views

How to mix pairs of rows without JOIN (in a temporary table)?

I have a TEMPORARY table containing pairs of key/value as id col1 col2 type pair 1 key1 key2 key 1 2 value1 value2 value 1 3 key3 ...
16
votes
5answers
2k views

Best cloud platform for PostgreSQL [closed]

Currently, in our project, we use dedicated servers for our PostgreSQL database. In theory, we can run anything on some cloud platforms. However, PostgreSQL configuration is strictly related to ...
0
votes
1answer
65 views

Oracle database migration or copy

I need to do migrate or copy the oracle 10g database from sun solaris to another sun solaris server (oracle 11g). I was tried with SQL Developer tool. But It is not able copy all the objects in the ...
0
votes
1answer
45 views

How to save mongodb data sub data more properly in this case?

I have a business collection. The businesses are reviewed by users. Each user can only review a business once. Future reviews will simply replace the former one. This is how we store it: ...
2
votes
1answer
384 views

SQLite or CouchDB for Locational Data Mining Project?

I am currently designing a Data Mining Project where I am going to harvest rather large volumes of Twitter data in order to analyse locational data (geocoded tweets) and do some machine learning with ...
3
votes
2answers
636 views

Cannot generate reports from SQL Management Data Warehouse

I'm running SQL Server 2008 R2 and have installed the MDW on one server and have a Data Collector collecting and uploading the server activity, query results, and Disk activity data to the MDW. When I ...
1
vote
2answers
326 views

Size of TempDB Microsoft SQL Server 2005

Are there drawbacks to having a large tempdb in a SQL Server database? Assuming the disk is not full. I am creating large temporary tables in a couple of queries. Once I am finished running queries, ...
2
votes
2answers
465 views

How well, if at all, does Oracle NoSQL database integrate with Oracle RDBMS?

I currently have an application which runs on Oracle RDBMS and an area of new functionality which would be better supported by a NoSQL store. I am wondering if Oracle NoSQL Database may be a good ...
16
votes
2answers
2k views

Optimising plans with XML readers

Executing the query from here to pull the deadlock events out of the default extended events session SELECT CAST ( REPLACE ( REPLACE ( XEventData.XEvent.value ...
1
vote
2answers
6k views

Connect to Postgresql 9.2 from Eclipse Data Tools Platform

GOAL: My goal is to write sql code within Eclipse to query a Postgresql database. The reason I want to write my queries within Eclipse is to then write R or Python code to perform statistical analysis ...
3
votes
1answer
6k views

MySQL stored procedure: loop through table, delete rows. Logic problem: won't exit loop because of LIMIT option in query

I've created a procedure to loop through large InnoDB tables and delete a chunk of rows, then pause and repeat until max field value allowed is reached. Binary logging is enabled, but I'm leaving it ...
1
vote
1answer
260 views

MySQL Stalls and stops all queries

We occassionally have our servers stall and back up queres for 3-5 minutes. then back to normal but locks up all users. We made a new server with binlogs and log fiels on seperate disk from data. ...
1
vote
3answers
414 views

moving a 13 gb table to amazon rds

I am moving our current database server from a regular EC2 instance running mysql to a RDS instance. I moved all the databases using the amazon-suggested method, simply piping the mysqldump output to ...
9
votes
1answer
553 views

query optimization: time intervals

In the main, I've got two kinds of time intervals: presence time and absence time absence time can be of different types (eg breaks, absences, special day and so on) and time intervals may overlap ...
0
votes
1answer
404 views

Optimising MySQL query with lots of self-joins

I have an entity_relationship table that describes relationships between entities. Each relationship will have 2+ entities involved. In some cases it can be said that one entity is a constituent of a ...
5
votes
3answers
342 views

Reducing the size of a database

SQL Server newbie here. I'm a MySQL guy. I'm having a look at something for a client in their 2008 SQL Server, and need some advice. Whoever designed the database chose to log insane amounts of stuff ...
0
votes
2answers
535 views

how to select * from XML where id=1?

declare @xml xml, declare @num int set @num = 1 set @xml = ' <row> <Id>1</Id> <name>reza</name> </row> <row> <Id>2</Id> ...
2
votes
1answer
193 views

Generating an arithmetic sequence

I would like to generate a named table in a WITH clause which will contain this arithmetic progression: ╔══════════╗ ║ mynumber ║ ╠══════════╣ ║ 0 ║ ║ 30 ║ ║ 60 ║ ║ 90 ║ ║ ...
0
votes
1answer
476 views

MySQL Workbench table organisation

Well, I'm interested is there a way to (someway)automatically organize database tables when you make EER Model from existing database. In my schema I have 30(and something) tables and 50(and ...
2
votes
2answers
151 views

Query taking too much time

My query is taking too much time to execute. Please help me here. mysql> explain select pcm.catalog_id from cat_produ_catal_map_defer pcm, cat_catal_catal_map_defer ccm, cat_catal_defer c ...
2
votes
3answers
631 views

Backup a sample of a PostgreSQL database

Is it possible to back up a PostgreSQL database with only a fraction of the data (1000 Rows from each table)? I am able to back up that database using pg_dump. In some case I need db with minimum ...
2
votes
0answers
105 views

Riak Key spaces and Indexes

I wanted to know how riak handles two queries when the data set starts getting really large. Since riak buckets are just key space, everything seems like it lives in one massive index well bucket. I ...
5
votes
2answers
329 views

Index fragmentation and SSD disks

I'm new to performance problems, but I saw some discussion that SSD disks solve performance problems caused by index fragmentation. Has anyone experienced this? I'm thinking of changing my hardware ...
0
votes
1answer
104 views

Storing user search keywords

I need to store search keywords in a database to analyse user needs. How efficient should my schema be? I'm thinking like this: CREATE TABLE SearchKeyword ( ID INTEGER PRIMARY KEY ...
0
votes
1answer
81 views

Long running query

This is my long running query: select * from t_keyword where id in ( select rel.element_id from t_event event join t_event_element_rel rel on event.id = rel.event_id where ...
1
vote
2answers
134 views

How to optimize this query?

Query: Select * from `t_event` where `create_user_id`=7 and (`event_create_date`)=('2012-12-18 00:00:00') and `event_type_cd`=11 and `domain_id` =602 and `job_id` =1 limit 1 Table structure: ...
1
vote
1answer
139 views

Transposing data

I have a table with 31 columns which contain values for each day in a month and I want to create a tranpose table like the example below. Real data in the database is really big so I don't expect to ...
0
votes
2answers
197 views

Best Way to Partition By City / State in MySQL 5.5

I have a table with ~20M rows and every query against it will include state or city or a combination so I was thinking of partitioning the table using a combo of the two fields like so: ALTER TABLE ...
3
votes
1answer
160 views

Bayesian ranking

I have the following table in my database: ╔════════╦═════════════╦═══════════════╗ ║ Name ║ total_stars ║ total_reviews ║ ╠════════╬═════════════╬═══════════════╣ ║ Item A ║ 27 ║ ...
6
votes
0answers
148 views

9i streams capture doesn't evaluate rules

I have a streams replication set up between two oracle 9.2.0.8 instances as sources and an 11g instance as a destination, processing a small subset of data from each database (let it be one table ...
2
votes
1answer
1k views

How to re-assign two variables in a conditional UPDATE

Consider a simple update as SET @var1 = 20; SET @var2 = 26; UPDATE table1 SET col1=IF(@var1>50, @var1 := @var1-col4, @var1 := @var1+col5), col2=IF(@var2>50, @var2 := @var2-col6, @var2); ...
4
votes
2answers
183 views

Finding longest matching prefix

I have two tables: Sources: (SName) (SId) Apple1 100 Apple2 200 Banks 300 BanksB 400 Bankerly 500 Prefixes: (PName) (PId) App 1 Bank 2 Banker 3 My goal: To find the longest ...
5
votes
3answers
607 views

“AT TIME ZONE” with zone name PostgreSQL bug?

I was answering this stackoverflow question and found strange result: select * from pg_timezone_names where name = 'Europe/Berlin' ; name | abbrev | utc_offset | is_dst ...
2
votes
1answer
373 views

Error deleting more than 1 row

I have a stored procedure that is supposed to delete several rows from a table. It does not work and gives me the error: The transaction ended in the trigger. The batch has been aborted I tried ...
2
votes
1answer
1k views

Update MySQL slave when changing the hostname/IP of master

I have one master and one slave replicating great, but the master is about to have both it's hostname and IP changed. In master.info on the slave server I see the current hostname for the master, if I ...
1
vote
1answer
271 views

How to re-arrange rows to best fit within groups?

I categorized a table with the method introduced here to categorize my items to folders with a limit size of 100. mixing_order id num_items cat order_in_cat cumulative_sum folder 1 ...
0
votes
2answers
1k views

Create a “barrier” (prevent parallel execution) in Oracle PL/SQL?

I'm maintaining some stored procedures in an Oracle 11g RAC environment that are part of the pipeline of steps for data-interchange with other systems. One in particular has been producing duplicate ...

15 30 50 per page