DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

4
votes
1answer
40 views

Verify Query Request Received

I have a software system which queries data via OLEDB from an external SQL Server 2005. The query request is sent every 15 minutes. I want to verify that the SQL Server is receiving the query ...
1
vote
2answers
2k views

Is there any way to force MySQL use Hash Join instead of Nested Loop Join?

According to the document MySQL Explain Output format, MySQL resolves all joins using a nested-loop join method. Is there any way to force MySQL to use a hash join?
0
votes
1answer
1k views

Changing the SGA and/or PGA in Oracle 11g affects the performance of the queries?

I am making a practice for school where one of the questions is if changing the SGA/PGA size would affect the queries performance and in what sense. I have been looking around for how to do that and ...
7
votes
1answer
3k views

Algorithm for finding the longest prefix

I have two tables. First one is a table with prefixes code name price 343 ek1 10 3435 nt 4 3432 ek2 2 Second is call records with phone numbers number time 834353212 10 ...
1
vote
2answers
534 views

Is there any manual for the visual explain in mysql? The output plan is unreadable

Just like this example, I cannot even tell the join ordering from it. And what does "ALL" and "ref" in the table mean? Is there any manual about the visual explain?
5
votes
1answer
690 views

Execute output from query

I've got this query that builds my drop login statements. As part two I want to execute this output. Ideally I want to do this in a sql job with two steps (generate and execute). Can this be done? ...
0
votes
1answer
53 views

Bit column vs multiple join tables

I asked a question earlier about how I could define a many to many relationship between users and FOSes (fields of study). My original question was how could we know whether the user was the ...
0
votes
2answers
557 views

Aggregate query on multiple values of same field

I am using SQL-Server 2012, I have a table of credit card transactions as follows: CardScheme RespCode ========================= Visa Declined MasterCard Approved MasterCard Approved ...
6
votes
2answers
2k views

Turning OFF CHECK_POLICY by default

We moved from SQL Server 2000 to SQL Server 2005. The client software, which I can not change, creates a user without option CHECK_POLICY = OFF; After creating a user, I have to run the command ...
-2
votes
1answer
101 views

Handle vast amount of data in DB [closed]

Now my current database is working in SQL Server 2008 R2 and we are uploaded its only in 1 server. How can I migrate my existing database in to Hyper Table ? (My current database has huge amounts of ...
-2
votes
3answers
193 views

Load Balancing in SQL

We are implementing new database applications with large users and the developer thinks we need to look into doing some kind of load balanced SQL database.our server is slow due to vast amount of ...
24
votes
2answers
13k views

Is it a bad practice to always create a transaction?

Is it a bad practice to always create a transaction? For example, it is a good practice to create a transaction for nothing but one simple SELECT? What is the cost of creating a transaction when it ...
0
votes
1answer
1k views

Restoring database backup file gives access error

I am running SQL server 2008 and trying to restore from a backup file. Note: the backup file and folder and read/write access to "everyone". Running the command as "master" Running RESTORE ...
0
votes
2answers
158 views

How do you store tabular data in an RDBMS?

First: I really haven't got any idea how to google for this. If you got one, leave a comment. If I want to store arbitrarily large tables in a database, how should I set up my database tables? ...
2
votes
1answer
112 views

How to select nodes where all children is satisfied?

I have a tree structure of light bulbs. I want to turn on all the light bulbs starting from the leafs of the tree. A light bulb cannot be turned on unless all its immediate children are turned on. ...
2
votes
4answers
979 views

Shell: How to time a script running in SQLPlus and kill it after x amount of minutes?

I have a little tool which automatically runs a series of SQL scripts when and outputs to .XLS when an appropriate request is made. However, some SQL scripts need to be ran on a live database, and I ...
-1
votes
1answer
71 views

validate where each parent intermediary is also a parent to itself [duplicate]

I want to validate where each parent intermediary is also a parent to itself. So this query should return invalid parents in SELECT If table has any. I need to do is first of all get all parents ...
0
votes
1answer
3k views

pgAdmin3 can't connect properly to Postgres 9.2

I have installed pgadmin3 version 1.10.2, however when it connects to PostgreSQL server version 9.2, it says: Warning: This version of pgAdmin has only been tested with PostgreSQL version ...
0
votes
1answer
301 views

Multiple junction tables?

I am working on a system where you have users, and you have Fields of Study or 'fos' for short. Each user can have many fos and each fos can have many users. So we are dealing with a many to many ...
1
vote
1answer
2k views

Mysql query grouping by two columns for max date takes too long

I have table that looks like this: l_p l_a l_timestamp l_n l_act l_name 123 321 2011-1-1T01:00:00Z B q das 21 23 2012-1-1T01:00:00Z C q sd 123 ...
1
vote
1answer
2k views

Remove specific duplicates (all but latest)

When removing duplicate rows, using the below query from this tutorial, how would I go about forcing which of the found duplicates to remove? DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN ...
11
votes
3answers
2k views

Updating a table with millions of records, its been 4 days

I am currently updating a table with millions of records, its been 4 days and query is still executing. I checked the activity monitor its shows that query is running. In event log there is no ...
11
votes
3answers
11k views

Are individual queries faster than joins?

Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient? ...
2
votes
1answer
446 views

Longest prefix search in Oracle

I have a list of phone number prefixes defined for large number of zones (in query defined by gvcode and cgi). I need to efficiently find a longest prefix that matches given number PHONE_NR. I use ...
1
vote
0answers
58 views

Which database could handle storage of billions/trillions of records in sql? [duplicate]

We are looking at developing a tool to capture and analyze netflow data, of which we gather tremendous amounts of. Each day we capture about ~1.4 billion flow records which would look like this in ...
1
vote
2answers
1k views

Join on different types

In a database I have two tables: The first has a field named taxonomy_id that is an integer The latter has a field named ID that is a character varying The two tables are related: if it exists a ...
1
vote
2answers
865 views

Remove scientific notation from an XML column

I have a XML column as shown below: <Root> <Row> <Rowid>1</Rowid> <date>2013-05-06</date> <Balance>1.0002E7</Balance> </Row> ...
1
vote
0answers
315 views

Search for multiple keywords related to separate columns on a single table

Imagine I have a table with the schema shown. Each of the columns represent a searchable collection that I would like to be picked up in the search. I am looking for a method that would derive the ...
2
votes
4answers
163 views

When should I use the shrink option

I have seen a lot of blogs stating that shrinking is not a good habit as it will reduce the performance of the system. I agree with all those things it will lead to side effects like fragmentation, ...
0
votes
2answers
4k views

How to forcibly flush the undo log/tablespace of UNDOTBS1

I'm looking for a way to flush the undo logs so that the UNDOTBS1 doesn't give me an error saying that my tablespace isn't large enough. I tried using a commit, but without success, it still gives me ...
0
votes
0answers
21 views

Where condition execution sequence in MySQL [duplicate]

I am using MySQL 5.0.88. I have a query like select DISTINCT party.­id FROM party­ LEFT join party_­identifier AS pi ON pi.­party_­id = party.­id­ WHERE ­(pi.­source_­note=? AND first_­name LIKE ? OR ...
2
votes
2answers
2k views

Postgres 9.2 select multiple specific rows in one query

I have a table with three columns, Id Serial. Value real. timein Timestamp. I want to select the values based on a range of days ie. from two days ago until now. The table may contain one or two ...
4
votes
1answer
277 views

How to get a row_number to have the behavior of dense_rank

I have a stored procedure will be used to page through data. One of the requirements of the procedure is to have a parameter that will be used to sort two columns of data, the sorting should be used ...
2
votes
1answer
50 views

How can I include more query in one query?

These are my tables: tbl_answers => aid qid answer uid dateposted emailnotify namedisplay status isbestanswer tbl_questions => qid question ...
0
votes
1answer
31 views

Separate table by often changed / not often changed data?

I have a application which querys different gameservers, and stores for example if the server is online, and how many players are connected. But of course there is also data which is changed rarely. ...
2
votes
2answers
360 views

MySQL many to many relation

Sorry is this seems a stupid question, but I have a table of server addresses and a second table with a list of hardware devices which use the addresses, it is possible for multiple devices to use the ...
4
votes
1answer
6k views

Convert SQL Server 2012 SSIS package to 2008

I've created a few SSIS packages in SQL Server 2012 that I need to run on SQL Server 2008. I've been told that it is impossible in their current state. Is there a way to convert the 2012 packages to ...
5
votes
6answers
2k views

Backup very large table

I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We ...
1
vote
6answers
1k views

Best practices with large amount of data

I'm building a solution and I have a question. All my data is currently stored in a unique table. Each row of data is associated with a type. My question is, what's the best practice for defining a ...
6
votes
2answers
296 views

Is there any way to trace optimizer's work in MySQL?

Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query. Is there anything in MySQL through which I can get the information ...
5
votes
2answers
617 views

Should I use a composite or single-column index?

I have the following columns in my database table (Medicines). ID bigint, MedicineName nvarchar(50), BrandName nvarchar(50), MedicineCode nvarchar(20), and price,quantity. I am making a stored ...
-1
votes
1answer
641 views

Adding a column at a specific position using DBMS_REDEFINITION

Can anybody tell me how I can add a column to table at a specific position using the DBMS_REDEFINITION package from Oracle?
3
votes
1answer
5k views

Moving postgresql data directory

I moved postgresql's data directory by following the following steps: Stop postgres cp -a source_data_directory destination_data_directory export PGDATA=destination_data_directory Changing data ...
-4
votes
0answers
46 views

oracle database query [closed]

I have a problem in writing a Database Query Problem: I have the below fields in my table Fields: Primary_ID(PK) ,E_ID, Bank, REQUEST_STATUS, START_DATE, STATUS Data: REQUEST_STATUS may contains ...
3
votes
1answer
3k views

Insert Row With Foreign Key Equal to ID - Circular Reference

In MySQL database I have an events table which has a circular reference to itself with the following columns. id (PK) | user_id (FK) | event_id (FK) | title Each new row inserted has a circular ...
1
vote
1answer
752 views

“relation does not exist” trying to import mysql dump into postgres

environment: ubuntu 10.04 mysql server 5.1.69 postgres 9.2 Here's the sequence of steps: created a new postgres database, myDatabase executed this command: mysqldump -u root -p ...
1
vote
2answers
7k views

Query WHERE clause and JOIN

I'm having an issue using a WHERE clause and JOIN. SELECT * FROM `CallDetailRecord` WHERE `StartTime` >=1357102799000 AND `StartTime` <=1357880399000 JOIN `CallEvent` ON `EventID` = ...
0
votes
2answers
793 views

copy package from schema B to schema C

I am in the next situation: I am using oracle 11g. I am connected to an user, lets say schema1, where are a lot of permissions. I want to create a script which will copy the Package1 (and its body) ...
4
votes
1answer
905 views

CTE memory space [closed]

How do CTE memory space work? When you have complex SQL statements executing with the CTE and you get unusually large result set, can it increase the logical reads of the query? I have a CTE that is ...
0
votes
1answer
73 views

Single quote issue

I have to update a column where I am giving query which is Update Table Set col = '[189] IS NOT NULL and [189] <> ''' Where colid= 198 But it's giving output:- [189] IS NOT NULL and ...