5
votes
1answer
529 views

Will a clustered index improve performance of updates performed sequentially on that clustered index?

I know indexes reduce data modification performance, but I have a task where (almost) all updates are done on items in a sequential order. Would a clustered index improve or reduce performance in ...
3
votes
3answers
182 views

Database-backed website configuration

I'm planning on adding storage for configuration options to the website I'm working on (php + MySQL). The problem is that I cannot decide between having a 2-column table (with key and value fields) ...
4
votes
1answer
2k views

Why doesn't INSERT AFTER Trigger add all results?

I'm working on the exercises for db-class.org where we have to write a trigger that makes new students named 'Friendly' automatically like everyone else in their grade. That is, after the trigger ...
3
votes
2answers
983 views

How to load data into Vertica from C?

I'm investigating column oriented databases and came across Vertica. My need is to feed the Vertica database from C code. I don't succeed in grabbing this information from Vertica: I'm told to use ...
1
vote
1answer
506 views

Storing Passport Numbers in a database

We're developing a travel/tourist application for a tours company who want to be able to input traveler information into an online database. Is there any security best practice for storing passport ...
3
votes
2answers
584 views

Tool to create a TSQL script from a SQL Server 2008 trace file

Are there any tools available to create an executable tsql script from a SQL Server trace file? Is there an easy manual way?
12
votes
4answers
8k views

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 ...
4
votes
2answers
925 views

Constraint many to many table between two child tables

New to this exchange site so I hope my question isn't inappropriate. Creating a somewhat normalized database I had one design problem I'm not sure how to handle efficiently. One table has a ...
0
votes
3answers
769 views

How using many tables and JOINS affect performance vs. using limited number of tables and lesser JOINS?

I want to design a db schema concerning a schedule. I. I can use all fields in the same table, for example: id | shop_id | mo (varchar) | tu (varchar) | we | th | fr | sa | su 1 | 1 | ...
1
vote
2answers
924 views

Can we decompose a schema into BCNF when it is not even in 1NF

Given a schema r(A,B,C,D,E,F) and a set of functional dependencies A --> BCD BC --> DE B --> D D --> A As we can see the schema is not even in 1NF as can be read here, now the question is can we ...
3
votes
1answer
7k views

SQL*Loader-605: Non-data dependent ORACLE error occurred

As I try to load data into Oracle, I've encountered SQL*Loader-605: Non-data dependent ORACLE error occurred error. Data is (are?) in .txt file and its size is 95GB. Here are some more details ...
7
votes
1answer
91 views

How can I see a query after rules have been applied?

From docs - 37.3.1.1. "A First Rule Step by Step" CREATE TABLE shoelace_log ( sl_name text, -- shoelace changed sl_avail integer, -- new available value log_who ...
10
votes
3answers
20k views

Any better way out of MySQL InnoDB log “in the future”?

I've got this InnoDB error in MySQL 5.0. Mysqld was stopped cleanly, but I managed to lose ib_logfile0 & ib_logfile1 afterward. Now after a clean startup, InnoDB has done its "crash recovery". ...
5
votes
1answer
427 views

Is a Named Instance more Secure than the Default Instance?

I realize that you can change the default instance from the default port of 1433, and equally you can make named instances use static ports as opposed to dynamic ports, but in general are named ...
0
votes
1answer
68 views

How to select rate for all selected modules and all selected years no matter they got rate for that year or not

How to write a SQL to achieve the "goal" section? Select rate for all selected modules and selected years no matter they got rate for that year or not. The years and modules will be input by users, ...
2
votes
1answer
3k views

What's the differences between utf8_general_ci and utf8_unicode_ci and utf8_binary collation in MySQL?

I'm can't find the documentation of MySQL on this topic. Anyone can give some explanations please?
1
vote
3answers
2k views

Why would recompile query hint result in different plan for same adhoc statement after freeproccache?

This is on SQL 2005 SP2, but I suspect this is something that applies to all query hints in general. I've got an adhoc sql statement that gets a different query plan solely because of ...
6
votes
2answers
301 views

What is the most efficient way of inserting 199 millon rows of data?

I have a 2.2 GB text file in the format of: 1234567890 1234567890 etc....... I need to insert each line into a table (field 1) such as: CREATE TABLE [dbo].[table]( [field1] [varchar](10) NOT ...
2
votes
2answers
2k views

SQL Server 2008 R2 SQL Server has encountered x occurrence(s) of I/O requests taking longer than 15 seconds to complete on file '

When I alter or create a stored procedure directly on production or QA database, after a few seconds I start experience timeouts and application becomes unavailable. Log files shows this error: ...
1
vote
2answers
2k views

Is there a way to find out how many times a stored procedure was invoked since last DB start

I am currently in a fairly mismanaged environment, where stored procs were introduced to the database over a few years and some versions became obsolete. Is there an easy way to figure out (preferably ...
4
votes
3answers
1k views

Log MySQL DB changing queries and users

For security reasons I need a server-side (running Debian 6.0 Squeeze) logging of all queries that may have changed the content of a MySQL DB (v. 5.1) and the user who issued it. I had to rule out ...
13
votes
3answers
6k views

Does “WHERE 1=1” usually have an impact on query performance?

I recently saw the question "where 1=1 statement"; a SQL construct I have used often in constructing dynamic SQL in an effort to write cleaner code (from the perspective of the host language). ...
1
vote
1answer
2k views

Creating an index in a temporary tablespace

I am trying to create an index for a relatively large table (80M rows). First I got an ORA-01652 error, then I searched in SO and found a related question. However, trying to implement the steps ...
3
votes
1answer
934 views

Programmatically copy index from one table to another, via a stored procedure?

I use a stored procedure to copy the tables from one database to the other database. Right now it does insert into copy select * from original I would like to copy the index from the original ...
1
vote
1answer
270 views

Design Tagging module in the database

I've a database system which we want to define tags for many tables into it. For example I've videos, photos, ... different entities that we wish to make them taggable. The tags could be anything. By ...
2
votes
2answers
3k views

“ORA-00942: table or view does not exist” when deleting from a very real table in the user's schema

I have a table that definitely exists in the logged-in user's schema (therefore I own the table) and is not a view/synonym/anything wacky. I can select from it, but when I try and either insert, ...
3
votes
2answers
34k views

How to include a datetime parameter within a stored procedure along with string query?

My stored procedure is as follows, -- Add the parameters for the stored procedure here @FromDate datetime, @ToDate datetime --Select query DECLARE @query nvarchar(max) ...
2
votes
1answer
12k views

Oracle 11g Listener not working

The listener on my Oracle 11g instance is no longer working. I tried to start it but that doesn't work. There are no Linux processes for the listener: > lsnrctl start LSNRCTL for Linux: Version ...
8
votes
4answers
442 views

How can I know which store procedure or trigger is using a table on SQL Server 2008 R2?

This is the case that in the DB I'm checking, there is an archive table which keeps the user history, and there is a trigger or store procedure that after some time delete rows from this table, in ...
4
votes
1answer
330 views

Speed inserts in database with a table with +47 million data per each year, containing more than 10 years?

I'm moving my database to mysql but there is one table that is pretty big (more than 47 million rows for each year) and it takes years to insert all the data. I already tried everything I found in ...
1
vote
2answers
807 views

Oracle Linked Server query error

We have a problem with a query from a SQL Server 2008R2 - 64bit to an Oracle Linked Server. We use the Oracle 64-bit driver for Windows. When we query a certain table we get the following error: ...
4
votes
1answer
1k views

What is Sql Server Clustered Index in Oracle terminology

A clustered index in sql server like a dictionary or telephone directory. A,B,C,D it goes on. If you look up with a name Ozgur you start O then z etc. I would like a simple explaination for this ...
1
vote
4answers
5k views

Can't create user in SQL Server

Hi guys when I try to create user for my database I always encounter this kind of error. This is the sequence of task I done. I manage to create new login using windows authentication of SQL then ...
5
votes
1answer
207 views

How can I efficiently represent inheritance down a tree?

I have a database with three "levels" of objects: A, B, and C. Table A has around 100000 rows, B has 500000, and C has 2 million. In a fit of stupidity, I designed the database to use joining tables ...
2
votes
3answers
511 views

RAM Impact on Oracle database operations

We are seeing drastic change in the amount of time taken for db operations depending on ram size. The same content when inserted into a db running on a 4gb ram machine takes about 600% more than into ...
1
vote
1answer
75 views

RAM Impact on database operations [duplicate]

Possible Duplicate: RAM Impact on Oracle database operations We are seeing drastic change in the amount of time taken for db operations depending on ram size. The same content when ...
3
votes
1answer
573 views

What is object Server?

As I was reading article I came across IBM's site about object Server. What actually is Object Server and what are it's uses? Can this be explained for a beginner?
2
votes
1answer
423 views

Return Output from SQL Function using command line SQLCMD

I have a SQL Function that returns an Integer as the Output. I need to execute it via sqlcmd and I only want to get it's integer output. However right now I'm getting back this type of output: ...
1
vote
2answers
790 views

MySQL UPDATE Performance Issue

I'm running out of ideas on a performance bottleneck I'm seeing on one of my projects. The table in question is pretty small, with only 5396 records. However, I'm seeing queries like this show up in ...
2
votes
2answers
98 views

Extra tables are added when I create a new Database

This is a really weird problem. When I create a new DB from a script I'm getting extra tables added. These aren't system type tables but regular tables that look like they may have come from a past ...
8
votes
3answers
6k views

One Big Database vs. Several Smaller Ones

We have a situation were we can (A) deploy instances of an applications in one MySQL database using table prefixing or (B) use different MySQL databases for each instance of the application, for e.g., ...
12
votes
5answers
5k views

SQL Server 2008 Full Text Index Never Seems to Complete

Our website has a SQL Server 2008 R2 Express Edition database with full-text indexing for our website search. Each time a new record is added or updated in one of the indexed tables, the indexing ...
8
votes
3answers
2k views

How recording of every change of a row in a database is generaly stored?

In a project I'm working on, every change to the rows in some tables of the database must be tracked for further audit or rollback. It must be easy to find who modified the row, from which IP address ...
1
vote
0answers
3k views

Transactional replication subscription not showing in SQL Server Management Studio [closed]

I tried to set up 3 SQL instances, one publisher 2 subscribers, to replicate data between them. I did it using the GUI wizard and all went fine in the Publisher: my publication appears under "Local ...
3
votes
1answer
38k views

How to determine used/free space within SQL database files?

I normally use the following method to determine the free/used space within each file of a database: Select *, fileproperty(name, 'SpaceUsed') as Used From dbo.sysfiles This returns total and used ...
0
votes
1answer
488 views

Oracle: Extract Graphical Database Representation [duplicate]

Possible Duplicate: Oracle Creating ER Diagrams and Data Dictionaries How can I extract a graphical (schema) representation of an Oracle database (tables and their relationships; with ...
1
vote
1answer
3k views

High PageIOLatch_SH Waits with High Drive Idle times

We are experiencing high volume of PageIOLatch_SH waits on our database (row counts in the Billions). However it seems that our drive Idle time Percentage hovers around 50-60 percent. CPU usage is ...
3
votes
2answers
6k views

Why Isn't the SQL Server Agent Service Set to Automatic Startup by Default?

After a default install of SQL Server 2008 R2, the SQL Server Agent process seems to be set to manual startup. What is the reason for this? Is setting to automatic a bad idea or pointless for some ...
10
votes
4answers
382 views

Determining that Indexes on a table are unused

I’ve been running this script to try to find extraneous indexes select o.name as TableName, i.name as IndexName, p.reserved_page_count * 8.0 / 1024 as SpaceInMB, s.* from ...
2
votes
5answers
689 views

Pros and cons of having one table per entity vs. having a shared table with an extra column indicating the entity type

I am working on a database design and am wondering about the pros and cons of two setups. I am going to collect data about two types of entities, lets say cars and boats. The structure of the data ...

15 30 50 per page