What are efficient ways to deal with database schemas that are shared between code branches? Working on a project with multiple branches, where each branch is eventually merged back to the main branch, and in essence is isolated in order to develop a new feature.

The database, which is MS SQL Server, has a shared schema, however each branch makes changes to the schema as it progresses.

My primary inquiry is what are good ways to deal with sharing the schema from the main branch down to the derived branch, such that changes that are made to the main branch are easily merged into the derived branch, without stepping on new changes in the derived branch?

I have successfully used the following methodology, elaborated in Version Control and your Database:

maintain a version number in metadata (I use a database extended property)
any schema change is coded as a script that updates from current version to next version
application ships with all the scripts to upgrade from version 0 (initial deployment) all the way to current version
Every change is done through a script. Including 'system' data changes like dictionaries and lookup table entries.
when deployed, the application checks the on-disk schema version, then runs all the upgrade steps to bring the schema to current required version

I often hear the opinion of 'how is this different from just keeping the object definition scripts under source control?'. The difference is huge, because when you deploy a new version of your app you're not going to simply create a new database. Most times your app will have to upgrade the existing database, including the existing data. This is a crucial difference, your upgrade steps need to ensure the integrity and consistency of existing data during the upgrade. Some operations are trivial in code (add a non-nullable column with default value to the table object definition script, done), but they are in fact hugely painful at actual deployment (the table has 1.5 Billion rows, the add column would run out of log space if done the 'simpleton' way).

How does this works with branching:

when the branch is created, it snaps the current schema version, say version 1.6
as the team start working on the branch, it adds a new version, 1.7, and then it starts coding the upgrade step from 1.6 to 1.7
the upgrade step gets changed as modifications are done in the branch. It always runs the script that upgrade from v 1.6 to 1.7, but what exactly those scripts do, is subject to the normal code iterations and check-ins in the branch
branch finishes development, it prepares for the reverse integration (to be merged back into baseline)

it does a new forward integration from the baseline to the branch. If the integration does not bring any changes to the schema version, all things are good, the branch can reverse integrate as-is. version 1.7 becomes the new baseline version.
the interesting stuff is when another branch has reverse integrated into base in the meantime and now the base schema version has changed to, say, 1.7. In this case our branch has to bump it's deployment target schema version to 1.8 and do a review of the upgrade step that was formerly from 1.6 to 1.7 to see how it operates in the new environment, upgrading from 1.7 to 1.8. Logical schema conflicts have to be resolved, script may require changes, testing has to be done. Once completed, the branch can reverse integrate into base. The deployed target version of the product now becomes 1.8.
when another branch that has forked at schema version 1.6 wants to reverse-integrate, it needs to it will have to bump it's schema version to 1.9, test the upgrade script from 1.8 to 1.9, then it can integrate back into the base.

Notice that there is no tool involved, no magic schema diff scripting, no wizards and no right-button-click-generate-script involved. This is a 100% developer driven process, based on source (scripts). Many find this whole process elaborate, but it works. In fact, as a SQL Server user, you have already leveraged the results of this process in your daily use of SQL Server: SQL Server itself uses a very similar database upgrade process and, as you probably expect, the product development process makes extensive use of branching and the problem you mentioned is a very real problem that has to be solved.

BTW, how the branching/integration actually occurs differs between source control products, I'm using the terms familiar from the perforce integrate mode of operation.


How to clone a table schema (including indices) in SQL Server? Is there a command that will completely clone the schema of a table?  This would include all constraints, foreign keys, and indices on the table.

In SSMS the Script Table as | Create To menu command does not include indices defined on the table.

To change the behavior of Script Table As, use Tools | Options | SQL Server Object Explorer | Scripting | Table and view options as Scott Hodgin originally suggested in a comment.

Alternatively, if you want to script out multiple objects at the same time, you want to use the Generate Scripts functionality.  Right click on the database in question, select Tasks --> Generate Scripts.  Then the wizard will walk you through further options.  You can even have it script out the data.

You can select all objects of a type, or specific objects to be scripted. By default, some parts of the database aren't scripted, including some things you might expect - like statistics contents.


Loading XML documents to Oracle 11g DB with control file I am using Oracle 11g XML database and trying to load XML documents to this DB with a control file and the sqlldr utility.  All these XML files have an element that contains a date string with time stamp (and the letter T in the middle of it).  Oracle rejects this date string because of T in it and thus the XML file is not loaded to the DB.

I want to use the Oracle function TO_TIMESTAMP_TZ on the date string during the data load, but I do not know how to do it.  That's where I need help.  If there is any other way to import the XML (with date string with timestamp), I will try that also.

Here is the date entry in XML file:

<ns3:EntryDateTime cls="U">2013-04-20T21:02:52.468-04:00</ns3:EntryDateTime>

And here is entire code the control file:

load data infile 'filelist.dat'
  append into table STXP xmltype(XMLDATA)
  ( filename filler char(120), XMLDATA lobfile(filename) terminated by eof )

I believe that I can execute the above control file with the sqlldr utility on SQL*Plus command line also, but not sure about this option.  If this is possible, I guess I can ALTER SESSION (to somehow format date string) on command line before executing the control file.

The filelist.dat mentioned above contains entries for input XML file, with one line listing one XML file.  The above date entry is required in each XML file.  Each XML file has about 50 different elements, some required and some optional.  I would greatly appreciate your help.

UPDATE:
I successfully registered the schema, which contains definition for the date string, and 100 other schema, with a script.  Since this script is very large, I am posting only 2 registration portions of it:

DECLARE
SCHEMAURL VARCHAR2( 100 );
SCHEMADOC VARCHAR2( 100 );
BEGIN
SCHEMAURL := 'http://www.some.org/stxp/DataTypes.xsd';
SCHEMADOC := 'DataTypes.xsd';
DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL,
    BFILENAME( 'XSD_DIR', SCHEMADOC ),
    LOCAL => TRUE, -- local
    GENTYPES => TRUE,  -- generate object types
    GENBEAN => FALSE, -- no java beans
    GENTABLES => TRUE,  -- generate object tables
    OWNER => USER );
    SCHEMAURL := 'http://www.some.org/stxp/STXP.xsd';
    SCHEMADOC := 'STXP.xsd';
    DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL,
    BFILENAME( 'XSD_DIR', SCHEMADOC ),
    LOCAL => TRUE, -- local
    GENTYPES => TRUE,  -- generate object types
    GENBEAN => FALSE, -- no java beans
    GENTABLES => TRUE,  -- generate object tables
    OWNER => USER );
  END;
  /

The 2nd registration above is the last in the script, and this creates the table STXP, in which I am trying to load about 800 XML files.  Each XML file has a root element called stxp.

This is the relevant definition of date string:

 <xsd:simpleType name="DT" xdb:SQLType="TIMESTAMP WITH TIME ZONE">
    <xsd:restriction base="xsd:dateTime"/>
</xsd:simpleType>

And this is how I am using the above definition:

<element name="EntryDateTime" type="oth:DT"/>

When I make the above element optional (for testing purpose) and remove the date string entry (mentioned near the top of this question) from my XML file, the XML file is loaded successfully to Oracle XML database.  When I put this entry back to XML file (because it is required), Oracle rejects it.

Because I let Oracle take care of population of STXP table with data from XML files, I am not sure if I can set a trigger to pre-process the date string from the input XML file before saving it in database.  i think there is a way to do it in the control file.

I think the way you'll have to do it is a Jokke suggest: instead of defining the DT datatype as TIMESTAMP WITH TIME ZONE, rather define it as VARCHAR2(29) where I think that 29 characters should be enough but you could define it wider.

Then when you extract data from the XML you'll have to convert it to TIMESTAMP WITH TIME ZONE.

However, you could also define the column as an "xsd:dateTime". See http://www.adp-gmbh.ch/xml/schema.html and http://www.w3.org/TR/xmlschema-2/#dateTime


How to update a multiple rows all at once with a procedure.? Im trying to do a procedure to update an (for now) empty column recently added.

In this new column, i will put the values of the result obtained by a join query.
To explain it better, i have this

delimiter //
create procedure update_amount_products (in id int)
begin
update categories set products_amount=
           (SELECT DISTINCT COUNT( products_to_categories.products_id )
FROM categories_description
INNER JOIN products_to_categories ON products_to_categories.categories_id = categories_description.categories_id
INNER JOIN products_description ON products_description.products_id = products_to_categories.products_id
WHERE categories_description.categories_id =id)
WHERE categories_id = id;
end
//
call update_amount_products(id);

So what i want, is to not calling the procedure everytime with diferents "ids", i want to update all at once (because there are over 1500 rows with diferents ids). What can i do?

I have been thinking of some kind of "for" to get all the ids and saving them on a variable so later used on my update procedure, but i've seen that there isn't any array possibilty in sql nor foreach.

Any help is highly appreciate it.

Finally i got it, after several tries i manage to get what i needed, so i'll share my answer.

First i made one procedure to update one single row in the column:

delimiter //
create procedure update_amount_products (in id int)
begin
update categories set products_amount=
           (SELECT DISTINCT COUNT( products_to_categories.products_id )
FROM categories_description
INNER JOIN products_to_categories ON products_to_categories.categories_id = categories_description.categories_id
INNER JOIN products_description ON products_description.products_id = products_to_categories.products_id
WHERE categories_description.categories_id =id)
WHERE categories_id = id;
end
//

DELIMITER ;

Then i realized i could handle the whole column by calling the procedure with another procedure with a loop on it

DELIMITER $$
CREATE PROCEDURE update_products_amount()

   BEGIN
      DECLARE a INT Default 1 ;
      DECLARE category_length int;

      select max(categories_id) into category_length from categories_description;

      simple_loop: LOOP
         SET a=a+1;

         call update_amount_products(a);

         IF a >= category_length THEN
            LEAVE simple_loop;
         END IF;
   END LOOP simple_loop;
END $$
DELIMITER ;

commit;

Finally, all i have to do now, is to call my last procedure and voilá, all my column will be automatically updated.

call update_products_amount();


How to write double Natural Join in SQL? Let be the following database :

  Product(Constructor, Model, Type)
  PC(Model, Speed, RAM, HD, CD, Price)
  Laptop(Model, Speed, RAM, HD, Screen, Price)
  Printer(Model, Color, Type, Price)

  Who are the constructors giving printers and PC ?

I think that the answer in relational algebra is :

ΠConstructor(Product ⋈  Laptop ⋈  Printer)

Yet I don't know how to write it in SQL, would it be :

SELECT Constructor
FROM Product NATURAL JOIN Laptop NATURAL JOIN Printer;

Your question is a bit unclear - does the 'Type' field
specify 'PC', 'Laptop' or 'Printer' - if so, it's relatively
easy - no need to join at all - just

SELECT * -- or whatever
FROM Product
WHERE Type = 'PC' OR Type = 'Printer'

If 'Type' is not the above, then it's trickier, but
not too difficult. BTW, 'Type' is a keyword in some
RDBMS products - I'd advise changing it to 'p_Type' or
similar.

To solve this, I did the following (SQLFiddle here):

I didn't put in referential constraints - I leave that as an
exercise for yourself :-) (especially since you haven't provided
your particular RDBMS). I also excluded the non-joining fields
(less typing for me :-) )

CREATE TABLE Product
(
  Constructor INT NOT NULL,
  Model VARCHAR(5) NOT NULL,
  Type VARCHAR(5) NOT NULL
);

CREATE TABLE PC
(
  Model VARCHAR(5) NOT NULL
);

CREATE TABLE Laptop
(
  Model VARCHAR(5) NOT NULL
);

CREATE TABLE Printer
(
  Model VARCHAR(5) NOT NULL
);

INSERT INTO Product VALUES( 1, 'm_1', 't_1');
INSERT INTO Product VALUES( 2, 'm_2', 't_1');
INSERT INTO Product VALUES( 3, 'm_3', 't_2');
INSERT INTO Product VALUES( 4, 'm_4', 't_3');

INSERT INTO PC VALUES('m_1');
INSERT INTO PC VALUES('m_2');

INSERT INTO Laptop VALUES ( 'm_3');

INSERT INTO Printer VALUES ('m_4');

The query:

SELECT * -- or whatever
FROM Product
  JOIN PC ON PC.Model = Product.Model
UNION
SELECT * -- or whatever
FROM Product
  JOIN Printer ON Printer.Model = Product.Model

Result:

Constructor Model   Type    Model
1   m_1 t_1 m_1
2   m_2 t_1 m_2
4   m_4 t_3 m_4

Et voilà - the correct result - unless I've misunderstood you?


Elegant Code to find continuous time period and generate id I have a table with two fields: time and value, both of them are integers and I want to find the continuous period with values bigger than 0 and then label the "value>0" period with continuous integers.

For example, if I have the input table like this:

I'd like the output table like this:

Using three CTE and two row_number() functions, I was able to do this. But I find the query too cumbersome. Does anyone have a more elegant code to do this?

I'm using sql server 2016 developer edition.

Here is my code:

CREATE TABLE #test1(
    [time] [int] NULL,
    [value] [int] NULL
)

insert into #test1
values(1,0),(2,0),(3,1),(4,0)
    ,(5,1),(6,2),(7,0),(8,0)
,(9,1),(10,2),(11,3),(12,0)
,(13,0),(14,0),(15,1),(16,0);

;with a1 as
(select *, [time] -  row_number() over (order by [time]) as group_num
    from #test1
where value>0),
a2 as
(select distinct group_num from a1
),
a3 as
(select group_num, row_number() over (order by group_num) as group_id
from a2)
select a1.*, a3.group_id
    from a1 left join a3
on a1.group_num = a3.group_num

You just need to apply DENSE_RANK to the result of the first CTE - Demo

WITH T
     AS (SELECT *,
                [time] - row_number() OVER (ORDER BY [time]) AS group_num
         FROM   #test1
         WHERE  value > 0)
SELECT *,
       DENSE_RANK() OVER (ORDER BY group_num) AS group_id
FROM   T


Need help with query over three related tables I am designing a database for a publishing house to track magazine shipping and also to keep a history of it.

I have three tables (simplified):

Subscriber
  id (pk)
  subscribers_number (unique)
  first_name
  last_name
  shipping_address
  last_payment

Shipping
  id (pk)
  date
  issue

ShippingAddress
  id (pk)
  shipping_id (fk)
  subscriber_id (fk)
  subscribers_number
  first_name
  last_name
  shipping_address

NonDeliveryReport
  id (pk)
  shipping_address_id (fk)
  reason
  resolved

Subscriber is a list of people who pay to receive a monthly issue of their magazine. Not every subscriber is eligible to receive the next issue. To make it simple, a subscriber receives the next issue only if last_payment date is withing the last 60 days. There are some more conditions, but I'll get into that later.

When the next issue is ready to be shipped, a Shipping is created.

Then we select all eligible Subscribers and create a ShippingAddress for each one and copy the value of subscribers_number, first_name, last_name and shipping_address from the Subscriber to the new ShippingAddress. We do this so that we have an unmutable history of shipments, even if a Subscriber changes his/her address, or is deleted from the database. For ease of use, there is a nullable foreign key subscriber_id in addition to the hard copy of the subscribers_number.

If the mail company could not deliver to a subsriber's ShippingAddress, they return a non-delivery report to us. For each report, a NonDeliveryReport is created and references the affected ShippingAddress.

Now we are getting to the important part of my question:

When we create a Shipping, we have to select all Subscribers, that are eligible. Eligible is every subscriber, except for anyone, who has a non-delivery report attached to him, that was NOT resolved. (For simplicity's sake, we just ignore all other conditions.)

So I need two queries:

Select all subscribes that are eligible.
Select all subscribers that are not eligible.

Query #2 was managable and rather easy:

SELECT DISTINCT
  Subscriber.id
FROM Subscriber
  INNER JOIN ShippingAddress ON (Subscriber.id = ShippingAddress.subscriber_id)
  INNER JOIN NonDeliveryReport ON (ShippingAddress.id = NonDeliveryReport.shipping_address_id)
WHERE NonDeliveryReport.resolved IS NULL;

But the queries I wrote for case #1 always returned fewer or more results than expected. I am stuck on this since last week and just can't get it to work. I hope you could help me with that.

This isn't part of my main question, but any thoughs (in form of comments pls) about my database design are welcome. I was already thinking about adding a field deliverable (bool) to Subscriber and just fill in the value programmatically, when importing the NDRs, but am hesitant because that'd probably defeat the purpose of database normalization.

Edit #1: The closes I came to was the following query, but it returns too many rows. *sigh*

SELECT DISTINCT
  Subscriber.id
FROM Subscriber
  LEFT JOIN ShippingAddress ON (Subscriber.id = ShippingAddress.Subscriber_id)
  LEFT JOIN NonDeliveryReport ON (ShippingAddress.id = NonDeliveryReport.ShippingAddress_id)
WHERE NonDeliveryReport.korrigiert_am IS NOT NULL OR NonDeliveryReport.id IS NULL OR ShippingAddress.id IS NULL

Edit #2: After thinking even harder (I didn't think that was possible any more) I found a solution.

When non-eligible are subscribers that have an open NDR (resolved IS NULL), then eligible is anyone who as no NDR in addition to anyone who has more than 0 unresolved NDR.

-- List of ELIGIBLE Subscribers
SELECT DISTINCT Subscriber.id
FROM Subscriber
LEFT JOIN ShippingAddress ON (Subscriber.id = ShippingAddress.subscriber_id)
WHERE ShippingAddress.id IS NULL OR Subscriber.id NOT IN
  (
    SELECT ShippingAddress.subscriber_id
    FROM ShippingAddress
    INNER JOIN NonDeliveryReport ON ShippingAddress.id = NonDeliveryReport.shipping_address_id
    WHERE (NonDeliveryReport.id IS NOT NULL AND NonDeliveryReport.resolved IS NULL)
    GROUP BY ShippingAddress.subscriber_id
  )
ORDER BY Subscriber.id;

I believe you are wanting something a little like this;

SELECT DISTINCT
Subscriber.id
FROM Subscriber
 INNER JOIN ShippingAddress ON (Subscriber.id =
ShippingAddress.subscriber_id)
  LEFT OUTER JOIN NonDeliveryReport ON (ShippingAddress.id = NonDeliveryReport.shipping_address_id)
WHERE NonDeliveryReport.shipping_address_id IS NULL OR NonDeliveryReport.resolved IS NOT NULL;


High delay lag between master/slave in Postgres 9.3 I have a Postgres 9.3 database server with this configuration:

# grep archive_ postgresql.conf
archive_mode = on            # allows archiving to be done
archive_command = 'rsync -a %p postgres@backupserver:9.3/standby/archive/%f'            # command to use to archive a logfile segment
archive_timeout = 300            # force a logfile segment switch after this

and another server that follows in hot standby mode.

I expect a WAL packet to be sent when it becomes full or 5 minutes (300 seconds).

If I run this query in the hot standby:

SELECT CASE
WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0
ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
END

Then I get a mean value of about 1500 seconds, that is about 5 times bigger than the configured 300 seconds.

Why?

In other servers with a lot of traffic the delay is small.

Related questions:
- When will PostgreSQL execute archive_command to archive wal files?

In other servers with a lot of traffic the delay is small.

By this statement, I am assuming that this server does not have a lot
of traffic.

If there are no transactions to replay, then the value of pg_last_xact_replay_timestamp() will not advance merely due to the passage of time.  Also, archive_timeout will only kick in when there is something to archive.  If there is no new WAL data since the last archive switch, then the existing WAL file (which is empty except for the initial header) will not be archived just because 5 minutes have passed.

A possible work around is to set checkpoint_timeout to 300 seconds as well.  In 9.3 with this setting, the checkpoint will create records to be archived, and the archival will create work to be checkpointed, so you will generate a nearly empty file every 5 minutes on an idle server.  Most people consider this to be a bug, but perhaps it is a feature for you.


SQL Server 2016 out of disk space I'm running SQL Server 2016. I have a database that has run, apparently, out of space on the D: drive. The daily index rebuild started producing the error shown below.

  Msg 1101, Level 17, State 12, Line 3
  Could not allocate a new page for database 'MarketDataAnalysisDB' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

First thing I did was remove the logs from the D: drive onto the C: drive so that I could (hopefully) delete some older historic data and free up space.  I was able to move the log files but the indexes still did not run (same error as above).

So I set out to delete rows of data with the intent of freeing up space so that future rows could grow back into the database. Following the delete statement, and 4 1/2 hours later, I got a similar error

  Msg 1105, Level 17, State 2, Line 2
  Could not allocate space for object '' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

While the error information is very informative I can do none of the suggested items. There is nothing on the drive but the database file. When I started reading about shrinking the database I realized I don't really understand why I'm getting this error. I've attached an image of the used space information in hopes that someone who understands this better than I, can provide a safe way to reduce the database size. The goal is to allow new records to come in while I figure out a rolling partition implementation that is currently beyond my SQL skills to implement on my own.

The other option I might consider is purchasing a larger hard drive for the data. But, from what I see, there appears to be space while at the same time SQL Server thinks I'm out of space.

What is the best way to reduce the size of my database?

DB Usage/space details

[EDIT]: I didn't realize it right away, but, my attempt to delete rows that took 4 hours and the log file located on the c: drive which had nearly 200GB free filled the log file so now the c: drive is out of disk space! What to do?

What to check in SSMS:

Right click on the database in question, click "Properties", then choose the "Files" tab.

Note all the files for the database, and the drives they are on. Note which files have auto-growth turned on, and (of those) which have a maximum file size.

Switch to the "Options" tab.

Check the recovery model (upper right corner). Is it "Full", "Simple", or "Bulk-logged"?

Repeat the first step for the system database tempdb.

Using Windows/File Explorer, check how much space is available on each of the drives you noted above.

Why you're having problems:

The index rebuild process takes up extra space in either your database itself, or in tempdb (depending on the settings for the index when it was created). In your case, it seems save to say the rebuild uses your database, not tempdb. Once the index is completely rebuilt, this space is released as available again; that's why it looks like you have free space in your data file, but you run out during the rebuild process.

Note that the database rebuild writes data to the transaction log, tracking the changes it's making, in case they have to be rolled back. The precise behavior of the transaction log depends on the recovery model of your database.

Databases using the "Simple" recovery model need to keep the details of every transaction around until the transaction is completed. Once the transaction is completed, the log space can be released to be reused. So, you can run out of space in your transaction log during a transaction, but have almost no space in use when you look at the log ten minutes later.

Databases using the "Full" recovery model (or "Bulk-logged") keep transaction information around until the transaction log is backed up. This means that a database in "Full", even with a regular full database backup, but with no transaction log backups scheduled, will eventually fill up, as transactions that completed months or even years ago must be kept in the log.

The main reason to use the "Full" recovery model is to allow for "point-in-time" recovery. You can combine your full backups with the string of transaction log backups to recover the database up to any point in time from when the full backup was taken, up to when the most recent transaction log backup was taken.

If you want the option to restore to a point more recent than your last full backup, then use the "Full" recovery model, and schedule transaction log backups to happen often enough that you don't mind losing the data that wasn't backed up. If you don't need transaction log backups (and you're sure about it), then put your DB into the "Simple" model.

A bit of advice: if you're using "Full" but haven't been backing up your transaction logs, you have the option of taking that first transaction log backup, if you have enough space to hold it. If you don't, you can basically "start over" by:

Shifting the recovery model to "Simple". This will mark all completed transactions as being OK to release. You can use the T-SQL CHECKPOINT command to ensure that the space is released immediately.
Switch back to "Full"
Immediately take a full backup of the database
Immediately after that, make sure your regular transaction log backup is set up.

This is one of the few scenarios where it actually makes sense to shrink your transaction log. If it's very large, try cutting it in half. Ideally, you don't want to shrink the file so small that it has to grow for normal operations. If you're looking for a good target size, I've heard the recommendation that you size the transaction log to twice the size of your largest index.

As noted, an index rebuild does write data to the transaction log. So, if you're using the "Full" model, and you do have transaction log backups running, you may want to run them more often when the rebuild is happening. I did this on one of my DBs a few years back - my usual schedule for transaction log backups was every 15 minutes, but I bumped that up to every 5 minutes during the rebuild process.

So, if all of that's set up fine, check to see if your drives are actually full, or if your files have an upper growth limit (or don't grow). If you still have drive space, then you can:

Manually increase the size of the file (on the afore-mentioned "Files" tab);
If auto-growth is not allowed, allow it (you can set a maximum if you like); or
If auto-growth is allowed, but a maximum is set, then increase the maximum (or remove it).

If you don't have any free disk space, and your transaction log is properly sized - then you need more disk space.


Moving a table to a different tablespace in Oracle using Online Redefinition Vs Alter table move tablespace I have a table with 1 Billion records with size 300GB to move to different tablespace.
My question here is - What is the advantage of using DBMS_REDEFINITION (Online redefinition) package
over Alter table  move ;
Which one is fast and why?
Can we have DML access to table in either ways of moving the table?

Thanks in advance

Those two methods are very different.

Alter table mytable move mytablespace

will move all the data blocks into the new tablespace. while it doing so, the table will be locked for DML.

DBMS_REDEFINITION will allow you to create a new table on the desired tablespace and sync the data between them ONLINE. when the sync ends the tables names will be switched.
you will have to be careful with DBMS_REDEFINITION as you have to create the new table youself including indexes, constraints, partition, privs, etc.


How is the default tablespace determined when creating a table? The Tablespace parameter is optional when creating tables.

Upon execution of a CREATE TABLE statement, Oracle assigns the default one if it was not defined.

In the default database there is a "USERS" tablespace.  If there were several tablespaces defined, which one would be assigned?

When you create a new user, you can optionally specify the default tablespace and default temporary tablespace for any objects created by that user. For example:

CREATE USER phil IDENTIFIED BY l33t
       DEFAULT TABLESPACE philtablespace
       TEMPORARY TABLESPACE philtemp;

If you omit the clauses when creating the user, the user will inherit the database default values. These can be queried as follows:

SQL> select *
  2  from database_properties
  3  where property_name like 'DEFAULT%TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                 Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS                Name of default permanent tablespace

SQL>

... and modified like so:

alter database default tablespace PHILTS;

alter database default temporary tablespace PHILTEMP;

To query the default values for a given user, use the following query:

SQL> select USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
  2  from DBA_USERS
  3  where USERNAME='PHIL';

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
PHIL                           USERS                          TEMP

SQL>


Availability groups NIC best practices I am planning to build a new AG setup in my QA environment with the following configuration. What I want to know is, can I have 2 heartbeat networks between the nodes for redundancy also, how many NIC's can be used in this scenario? The whole setup will be virtualized.

On both the nodes :

One public network for client connectivity
2 heartbeat networks for redundancy
One network for Database Mirroring

For this setup, I am assuming these are the IP addresses which are required

1 static IP for each of the node
1 static IP for the WSFC
1 static IP for the AG listner
4 static IP's for creating the 2 heartbeat networks

The nodes will be standalone instances with independent storage. The end result I am looking for is to create redundancy for the heartbeat network. Can I create one of it using a different IP range or from a different subnet? The nodes will be built at the Atlanta Datacenter where I am working right now. Please advice.

Thanks!

can I have 2 heartbeat networks between the nodes for redundancy
The whole setup will be virtualized.

I'm assuming you're going to bind these directly to the hardware NICs, otherwise it's kind of a moot point. If the host already has the NICs teamed, etc., then you really shouldn't need multiple interfaces in your guest (except for management or performance reasons, but not for health check reasons).
Cluster health checks go over any interface you allow cluster communication on (the public/private nomenclature died with Windows Server 2003). NetFT will take care of which interfaces are what and the metrics for each, adding additional interfaces wouldn't really be ideal. Instead use teaming or other technologies to make sure the interfaces stay up and available. Setting the client network for client and cluster communication will also do what you're asking without the need for extra interfaces.
Edit:
Unless you need to segregate traffic, such as the AG traffic for specific reasons such as NICs of higher speeds, network appliance between (compression/encryption), auditing needs, network protections, different virtual switches, etc., then there isn't really a need. If you meet one of these items, then it would make sense to have multiple NICs. Having a single NIC may require you to make changes to the host configs to have larger receive or send buffers.


Postgres 10 - Query slows down with order by I am running a query like

select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d';

on table that looks like

                Table "public.students"
          Column       |            Type             |             Modifiers
    -------------------+-----------------------------+------------------------------------
     id                | uuid                        | not null default gen_random_uuid()
     school_id        | uuid                        |
Indexes:
    "students_pkey" PRIMARY KEY, btree (id)
    "students_school_id_idx" btree (school_id)

The query plan for the select statement with just where looks like below-

explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d';
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on students  (cost=581.83..83357.10 rows=24954 width=16)
   Recheck Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)
   ->  Bitmap Index Scan on students_school_id_idx  (cost=0.00..575.59 rows=24954 width=0)
         Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)

This is fairly fast.

Now we add order by to the query with id that degrades the query.(Such a query is generated by Rails like student.first with some condition)

explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..488.51 rows=1 width=16)
   ->  Index Scan using students_pkey on students  (cost=0.43..12179370.22 rows=24954 width=16)
         Filter: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)

How can I improve the speed to return the results of this query? Currently there are around 4990731 records in the table and is taking more than 2 minutes!
Its running on RDS with db.t2.medium instance.

UPDATE
After running Analyze students;

explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;
                                                       QUERY PLAN
    -----------------------------------------------------------------------------------------------------------------
     Limit  (cost=8.46..8.46 rows=1 width=16)
       ->  Sort  (cost=8.46..8.46 rows=1 width=16)
             Sort Key: id
             ->  Index Scan using students_school_id_idx on students  (cost=0.43..8.45 rows=1 width=16)
                   Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)

    explain analyze select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;
                                                                          QUERY PLAN
    -----------------------------------------------------------------------------------------------------------------------------------------------------------
    Limit  (cost=8.46..8.46 rows=1 width=16) (actual time=1.853..1.855 rows=1 loops=1)
     ->  Sort  (cost=8.46..8.46 rows=1 width=16) (actual time=1.851..1.852 rows=1 loops=1)
           Sort Key: id
           Sort Method: quicksort  Memory: 25kB
           ->  Index Scan using students_school_id_idx on students  (cost=0.43..8.45 rows=1 width=16) (actual time=1.841..1.843 rows=1 loops=1)
                 Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)
    Planning time: 0.145 ms
    Execution time: 1.874 ms

PostgreSQL thinks that it will be faster avoiding the sort for the ORDER BY by scanning the rows in the sort order and discarding rows until it finds one with the right school_id.

There can be two reasons why this takes longer than expected:

The table statistics is off, and PostgreSQL overestimates the number of rows with that school_id.

Calculate new statistics, possibly with a higher value for default_statistics_target, to verify if that is the problem:

ANALYZE students;

The (many) rows with the correct school_id all happen to have a rather high id, so PostgreSQL has to scan way more rows than it bargained for until it finds a match.

In that case, you should modify the ORDER BY clause so that PostgreSQL cannot use the wrong index:

... ORDER BY id + 0


Migrated DB from 12c to 19c with IMPDP failed I have migrated a DB from 12c to 19c with IMPDP and EXPDP. While importing I have seen some errors. Below is an extract of the errors.

Errors During Import

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

VM247_RCDB_121GA.PK_TXI : sqlerrm = ORA-20000: Unable to set values for index PK
_TXI: does not exist or insufficient privileges

VM247_RCDB_121GA.PK_ARRE : sqlerrm = ORA-20000: Unable to set values for index P
K_ARRE: does not exist or insufficient privileges

VM247_RCDB_121GA.PK_ARA : sqlerrm = ORA-20000: Unable to set values for index PK
_ARA: does not exist or insufficient privileges

VM247_RCDB_121GA.SYS_C0034921 : sqlerrm = ORA-20000: Unable to set values for in
dex SYS_C0034921: does not exist or insufficient privileges

VM247_RCDB_121GA.PK_TXA : sqlerrm = ORA-20000: Unable to set values for index PK
_TXA: does not exist or insufficient privileges

Importing statistics failed for 20 object(s);

Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 22 error(s) at Wed Aug 7 08:2
8:32 2019 elapsed 0 00:05:25

The utilities are: expdp from 12c and impdp from 19c

Commands Issued

On the 12c instance i exported using:

expdp system/admin@orcl schemas=VM247_RCDB_121GA directory=DATA_PUMP_DIR
dumpfile=VM247_RCDB_121V10.dmp logfile=VM247_RCDB_121V10.log

...and imported on the 19c instance with:

impdp system/admin@orcl19c schemas=VM247_RCDB_121GA directory=DATA_PUMP_DIR
dumpfile=VM247_RCDB_121V10.DMP logfile=VM247_RCDB_121V10_imp.log

This is a known problem (bug 26380126). The workaround is to exclude statistics, and the re-gather in the target database. Or export using DBMS_STATS.

Actually, Oracle recommends that you never export the statistics with datapump. Use one of the above two mentioned methods instead.


SSAS 2000 vs 2008 Cube processing times I have been told that we can greatly reduce our processing times by rebuilding our 2000 cubes in 2008.  I was told the difference is substantial (2hrs down to 20mins).

Is this true?  If so, why?

Performance usually improves from version to version of SQL Server.  SQL Server 2000's Analysis Services was nowhere near quick.

As to why - Microsoft doesn't give us the source code so it's a little tough to analyze that. ;-)

Like @ConcernedOfTunbridgeWells pointed out in his comment, though, the real reason you should get off SQL Server 2000 is that it's out of support.


Theta join explanation Take the following relational schemes:

R(A, B, C)
S(D, E, F)

where the attributes A and D are the primary keys, respectively.

Assume to have an instance r of R with n tuples and an instance s of S with m tuples.

Moreover, assume to have a referential integrity constraint between C and the primary key of S.

Now, I was asked the following questions:

  How many tuples does the θ-join between s and r contain if the join predicate is C = D?
  How many tuples does the θ-join between s and r contain if the join predicate is B = E?

My answers:

n tuples since it all depends on the number of tuples belonging to the
second relation that match the tuples which are part of the first one
zero since there's no common attributes

Is my reasoning correct?

I think you need to think some more :)

2) "there's no common attributes" is certainly true but irrelevant. We know there is a correspondence between C and D (and therefore are of the same type) because of the referential integrity constraint even though they have difference names.

e.g. here's an example in SQL that satisfies the conditions yet returns a row:

CREATE TABLE s
(
 D INTEGER NOT NULL,
 E INTEGER NOT NULL,
 F INTEGER NOT NULL,
 PRIMARY KEY (D)
);

CREATE TABLE r
(
 A INTEGER NOT NULL,
 B INTEGER NOT NULL,
 C INTEGER NOT NULL,
 PRIMARY KEY (A),
 FOREIGN KEY (C)
    REFERENCES s (D)
);

INSERT INTO s (D, E, F) VALUES (1, 1, 1);
INSERT INTO r (A, B, C) VALUES (1, 1, 1);
INSERT INTO r (A, B, C) VALUES (2, 2, 1);

SELECT *
  FROM s JOIN r ON B = E;

p.s. where you used the term 'records' you should actually use the term 'tuples'.


Replicating 3000+ SQL Server databases I'm currently investigating the different options there are setting up a DR site for our main data centre. In an ideal world, we would have fewer, larger databases, however due to architectural constraints with the product, we have about 3000+ small (50MB to 300MB) databases.

I’ve had some experience with replication and I know that I can script setting up replication on each database, but my main concern is the performance overhead and the maintainability of this as a solution.

Before I go ahead and spend a lot of time testing this, has anyone else had any experience / advice on replication of multiple databases on this scale?

Thanks everyone for your comments and feedback. This is the first question I have asked on this forum so I’m having to edit my original question to respond to your comments and expand on the question…

In the main data centre we have a SQL Cluster with SQL2008R2 Standard connected to a SAN.

Regarding our RPO and RTO, the product is not a critical service, we would need to recover to a point in the last 24 hours and the recovery time needs to be within 24 hours of failure at the main data centre for all databases.

There are about 5 key databases which are used by all the other databases, however all other databases are independent of each other.

All databases have their recovery model is set to “Simple”, where full backups are made weekly and differential backups are made daily. Based off our RPO and RTO, log shipping does definitely sound like a viable solution.

Yes, I've got clients with 1k-10k databases per server. (Fog Creek is actually one of them, and I can mention it here because I know Joel's talked about it on the podcast.) Usually you run into problems at this scale, and you start thinking about moving down to VMs instead - it's easier to manage more virtual SQL Servers with less databases per instance.

Log shipping technically works, but you have to roll your own solution rather than use the built-in tools. The problem is that these types of businesses usually have new databases coming online all day long, at any time of day, and they need these new databases to be automatically protected by the log shipping. This means as soon as a database is created, you need a full backup taken, plus get it added into the log shipping setup. You'll be rolling your own scripts, and you can't just have one job. One job won't be able to keep up with the sheer number of databases if it backs up one database at a time - you end up with multiple jobs, each of which handles a range of databases or does something like the modulus of the database id.

SAN replication is much easier because it automatically protects any new databases that get added in. For critical databases, I recommend using a combination of VSS-integrated SAN snapshots, dirty (non-VSS) SAN snapshots, and regular SQL Server transaction log backups, but you mentioned you've got SIMPLE recovery mode and a 24-hour RPO/RTO. You'll be able to stick with a much simpler solution: dirty SAN snapshots plus conventional full backups.

The specifics will depend on your SAN vendor's replication options, but typically they can take a dirty snapshot without quiescing SQL Server's writes, then replicate that snapshot over to your DR site. Done correctly, this happens without impacting SQL Server performance. The problem is that without quiescing SQL Server's writes, you're rolling the dice on whether or not each database will come online. Odds are, when you fail over, you're going to have a handful of databases that don't recover correctly - no problem, that's where your full backups come in.

Every night, you should be doing full database backups to another location (not the SAN). That other location should also be replicated to your other site.

This way, when you fail over, most of your databases will come online instantly via the SAN-replicated copy. Some of them will not (usually a fairly small percentage). For those few databases, you restore the most recent full backup. End result: pretty quick recovery time objective at a relatively low complexity cost.

I would NOT recommend this solution if you need every database to be at the exact same moment in time, or if you needed up-to-the-second recoverability. (Just making it clear because some reader is going to freak out at this design - it's certainly not for everybody, just for this one specific business case.)


Will enabling row movements on list-partitioned table cause performance problems(oracle 11g r2) Our incident management system has run for years, and the large quantity of closed incident tickets makes the table rather huge and slows down the search queries.

Our database version is oracle 11g r2 enterprise edition(partition option added). The concerned two large tables are: incident and incident_area_info(one incident corresponds to multiple incident_area_info, and they're joined using incident_id). So I want to use the following strategy:

Split incident table into two partitions: closed_incidents, active_incidents, using list-partition and status as partition key. Also, I manually enabled row movements on incident table and incident_area_info table, so that I can close the incident.
Split incident_area_info using reference partition.
Drop the original indexes and replace them with corresponding partitioned local indexes
Search open incidents only by default

I have applied this strategy in my developing environment and the search operation's execution time reduces to roughly 10% of the original on average(we have nearly 4 million closed incidents and only about 40,000 active incidents).

But "row movements" is disabled by default, so maybe enabling it will cause some performance problems.  Well, of course, when a row moves – it will be updated, deleted and re-inserted with all relevant index entries adjusted accordingly. And rowid will be modified after a row is moved(I'm quite sure we do not use rowid in our system, so this won't be a problem).

Question1:

Besides those mentioned above, will there be any other bad side effects when enabling row movements?

Question2:

I suspect that moving rows will create space holes in the original partition and the data file will be fragmented after long-term running. Is this true?

Question3

If question2 is true, then is there a way to remove these space holes, like alter table mytable shrink space;

Question4

Here one guy said 'everybody should be carefull when enabling row movement in production system since enabling row movement invalidates all dependent views, which could result into plenty invalidate objects', but in my developing environment, after moving rows in incident table, the materialized view counting on incident table still works, and in dba_mviews everything seems to be fine. So, did I misunderstand what he means?

Any suggestion is greatly appreciated.

You should be fine; the rowid would be a show-stopper if you were using it
Temporarily, there would be "holes" in the data files, but Oracle will fill them in without any intervention.  Ensure you have automatic tablespace management enabled.
You can invoke that command but no need to; let Oracle take care of it
The person that stated that probably meant there was a one-time hit, or he didn't know what he was talking about, or using an old version.  You tested; his environment may have had a rat's nest of views.


Join query with closest date in "ON" clause I need to make an OUTER JOIN on two tables, based on 2 columns, first id an INTEGER id, which is pretty straightforward to use in the query, but the second one is a DATETIME, which doesn't actually matches a value on the other table, but needs to "match" the closest date.

As an added bonus to the problem, on the ref table data is not as DATETIME, but on two separate columns: one DATE and another TIME.

This question is a derivate of other two I've asked before, but as I need to narrow down the problems, I preferred to make separate questions.

The question is how to choose the closest date on a best performance approach. I've tried using between (1 minute tolerance) and things like that, but the query takes too long and the explain plan gives too many rows on the involved tables.

Perhaps something like using subqueries might work faster, but I just don't know how to build them for the JOIN syntax.

References:

Join on "poor" table
Join query with different column types in "on" part

BD: MySQL 5.5.43-0+deb8u1-log

Let's jump "out of the box"...

Have a column in the first table with the datetime rounded to the minute.  (One minute being your resolution.)

Have two columns in the second table, both datetime, one rounded up to the next minute, the other down.

Index both columns in second table:  (id, lower) and (id, upper).

Now run a UNION ALL of two SELECTs, one JOINing the first table to the second via id and lower, with equality; the other via id and upper.

This could catch pairs that are almost 2 minutes apart, so in the outer part of the UNION, do a WHERE to filter using the to-the-second columns -- checking which ones match within one minute.

This technique is likely to be much faster than any other.


How to have remote standby PostgreSQL server in different location (AWS)? We have a primary PostgreSQL 9.4 server running multiple instances in our DC. It is using Continuous Archiving via WAL-E out to AWS S3. I would like to have a hot/warm standby server in our VPC within AWS EC2 that would be ready to take over should we need to DR. I can recover the server via WAL-E but can't seem to get it to work in hot_standby mode. What could I be missing? Docs I read seem to suggest that the standby needs to talk to the primary? Is this so? Is this required when log-shipping via WAL-E?

There are two ways to run a replica. You can use either, or both together:

Streaming replication, where the replica makes a PostgreSQL protocol connection to the standby as configured with primary_conninfo in recovery.conf; or
WAL shipping, where the replica runs a restore_command (set in recovery.conf that fetches WAL archives to replay.

See recovery.conf.

Both require that standby_mode be set in recovery.conf.

WAL-E is designed to be used as an archive_command on the master, to store WAL into S3, then as a recovery_command on the replica to fetch WAL. There is no need for a streaming replication connection. You can add one anyway, and it will be used where available, and the system will fall back to WAL archive recovery if it can't connect over streaming replication. Or you can just use WAL-based archiving.

The main advantage of using streaming replication as well is that it's more timely; you don't have to wait until an entire WAL archive is filled and shipped before the replica sees the changes.

For more information see the manual.


update column based on the sort order of another query I'm trying to add arbitrarily ordered records to a database so that they can be sorted by the ui (or when I query the database ). My problem is I already have the list, and I need to add a default sorting based on alphabetical order. I'm thinking I should be able to do this with a subquery or coalesce, but I can't get it quire right. I'm doing this on MySQL so I'm hoping it's possible to do it at the database level.

Let's start with a table, put data in it, add a sort order column

USE test
DROP TABLE IF EXISTS ordered_names;
CREATE TABLE ordered_names
(
    id int not null auto_increment,
    firstname VARCHAR(20),
    lastname VARCHAR(20),
    PRIMARY KEY (id),
    KEY name_ndx (lastname,firstname)
);
INSERT INTO ordered_names (firstname,lastname) VALUES
('pamela','edwards'),('rolando','edwards'),('diamond','edwards');
ALTER TABLE ordered_names ADD COLUMN sort_order INT DEFAULT NULL;
SELECT * FROM ordered_names;

Let's actually run that:

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS ordered_names;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE ordered_names
    -> (
    ->     id int not null auto_increment,
    ->     firstname VARCHAR(20),
    ->     lastname VARCHAR(20),
    ->     PRIMARY KEY (id),
    ->     KEY name_ndx (lastname,firstname)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO ordered_names (firstname,lastname) VALUES
    -> ('pamela','edwards'),('rolando','edwards'),('diamond','edwards');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE ordered_names ADD COLUMN sort_order INT DEFAULT NULL;
Query OK, 3 rows affected (0.17 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ordered_names;
+----+-----------+----------+------------+
| id | firstname | lastname | sort_order |
+----+-----------+----------+------------+
|  1 | pamela    | edwards  |       NULL |
|  2 | rolando   | edwards  |       NULL |
|  3 | diamond   | edwards  |       NULL |
+----+-----------+----------+------------+
3 rows in set (0.00 sec)

mysql>

Next, let's populate the sort order column:

SET @x = 0;
UPDATE ordered_names SET sort_order = (@x:=@x+1) ORDER BY lastname,firstname;
SELECT * FROM ordered_names;

Let's run that:

mysql> SET @x = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE ordered_names SET sort_order = (@x:=@x+1) ORDER BY lastname,firstname;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM ordered_names;
+----+-----------+----------+------------+
| id | firstname | lastname | sort_order |
+----+-----------+----------+------------+
|  1 | pamela    | edwards  |          2 |
|  2 | rolando   | edwards  |          3 |
|  3 | diamond   | edwards  |          1 |
+----+-----------+----------+------------+
3 rows in set (0.00 sec)

mysql>

OK, good so far, let's add four(4) more people and reorder:

INSERT INTO ordered_names (firstname,lastname) VALUES
('javonne','washington'),('richard','washington'),
('carlik','washington'),('dominique','edwards');
SET @x = 0;
UPDATE ordered_names SET sort_order = (@x:=@x+1) ORDER BY lastname,firstname;
SELECT * FROM ordered_names;

Let's run that:

mysql> INSERT INTO ordered_names (firstname,lastname) VALUES
    -> ('javonne','washington'),('richard','washington'),
    -> ('carlik','washington'),('dominique','edwards');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SET @x = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE ordered_names SET sort_order = (@x:=@x+1) ORDER BY lastname,firstname;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 7  Changed: 6  Warnings: 0

mysql> SELECT * FROM ordered_names;
+----+-----------+------------+------------+
| id | firstname | lastname   | sort_order |
+----+-----------+------------+------------+
|  1 | pamela    | edwards    |          3 |
|  2 | rolando   | edwards    |          4 |
|  3 | diamond   | edwards    |          1 |
|  4 | javonne   | washington |          6 |
|  5 | richard   | washington |          7 |
|  6 | carlik    | washington |          5 |
|  7 | dominique | edwards    |          2 |
+----+-----------+------------+------------+
7 rows in set (0.00 sec)

mysql>

Give it a Try !!!


The use of multiple foreign keys on same column in SQL Server SQL Server allows me to create multiple foreign keys on a column, and each time using just different name I can create another key referencing to the same object. Basically all the keys are defining the same relationship. I want to know what's the use of having multiple foreign keys which are defined on the same column and reference to the same column in another table. What's the benefit of it that SQL Server allows us to do a thing like that?

SQL Server allows you to do a lot of silly things.

You can even create a foreign key on a column referencing itself - despite the fact that this can never be violated as every row will meet the constraint on itself.

One edge case where the ability to create two foreign keys on the same relationship would be potentially useful is because the index used for validating foreign keys is determined at creation time. If a better (i.e. narrower) index comes along later then this would allow a new foreign key constraint to be created bound on the better index and then the original constraint dropped without having any gap with no active constraint.

(As in example below)

CREATE TABLE T1(
    T1_Id INT PRIMARY KEY CLUSTERED  NOT NULL,
    Filler CHAR(4000) NULL,
)

INSERT INTO T1 VALUES (1, '');

CREATE TABLE T2(
    T2_Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    T1_Id INT NOT NULL CONSTRAINT FK REFERENCES T1 (T1_Id),
    Filler CHAR(4000) NULL,
)

ALTER TABLE T1 ADD CONSTRAINT
    UQ_T1 UNIQUE NONCLUSTERED(T1_Id)

/*Execution Plan uses clustered index*/
INSERT INTO T2 VALUES (1,1)

ALTER TABLE T2  WITH CHECK ADD  CONSTRAINT FK2 FOREIGN KEY(T1_Id)
REFERENCES T1 (T1_Id)

ALTER TABLE T2 DROP CONSTRAINT FK

/*Now Execution Plan now uses non clustered index*/
INSERT INTO T2 VALUES (1,1)

DROP TABLE  T2, T1;

As an aside for the interim period whilst both constraints exist any inserts end up being validated against both indexes.


Counting SQL Server Transactions Is there a method to count total transactions by type (read/write) by day/hour/peak?

Use the performance counters Transactions/sec and Write Transactions/sec documented here:

https://msdn.microsoft.com/en-us/library/ms189883.aspx


Does PG somehow support the ability to set a column to "variable type"? I sometimes get into the situation where I wish to store "arbitrary fields" with their value. Rather than creating a table structure with tons of columns, and perpetually having to change this table as more "fields" are added, I have a table with basically just a "field" and "value" column, where both are of text type, meaning it can "store any value", albeit in a less-than-optimal manner in terms of speed and "deep knowledge" by the database engine about the data in the table.
For example, it's unclear if a boolean value sent from the application will be stored as a "0"/"1" or literal "true" or "false". It just "feels icky", although I don't consider having actual columns for every value to be a sensible alternative to this approach, which I consider to be even more problematic.
Since this is probably a very common situation, it struck me that PG might support a "variable" column type of some sort, so that it can dynamically become a "boolean" or "text" or "date" or "integer" depending on the value inserted/updated for the "field" column for a specific row? Or perhaps based on what I specify with ::whatever?
Is this a thing? Is it stupid? I can work with my current system of "text columns that hold anything", but I always have the nagging feeling that "this isn't quite the optimal or intended way to do things".

AFAIK know there is no such union type. I would consider this a last resort, but here is a sketch if you truly need such type:
CREATE TABLE T
( attribute_key text not null
, attribute_value text not null
, attribute_type smallint not null -- example 1=int, 2=...
);

You can add a type check like:
attribute_type = 1 => attribute_value ~ '^[0-9]+'

and so forth. The above rule would be:
CHECK( attribute_type <> 1 or attribute_value ~ '^[0-9]+' );

The other option I could think of is to use XML to store the value. You can create a schema for the data types you are supporting, and validate against that schema during insert.


How can I get running totals of recent rows faster? I'm currently designing a transaction table. I realized that calculating running totals for each row will be needed and this might be slow in performance. So I created a table with 1 million rows for testing purposes.

CREATE TABLE [dbo].[Table_1](
    [seq] [int] IDENTITY(1,1) NOT NULL,
    [value] [bigint] NOT NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
    [seq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

And I tried to get 10 recent rows and its running totals, but it took about 10 seconds.

--1st attempt
SELECT TOP 10 seq
    ,value
    ,sum(value) OVER (ORDER BY seq) total
FROM Table_1
ORDER BY seq DESC

--(10 rows affected)
--Table 'Worktable'. Scan count 1000001, logical reads 8461526, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'Table_1'. Scan count 1, logical reads 2608, physical reads 516, read-ahead reads 2617, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--
--(1 row affected)
--
-- SQL Server Execution Times:
--   CPU time = 8483 ms,  elapsed time = 9786 ms.

I suspected TOP for the reason of slow performance from the plan, so I changed query like this, and it took about 1~2 seconds. But I think this is still slow for production and wondering if this can be improved further.

--2nd attempt
SELECT *
    ,(
        SELECT SUM(value)
        FROM Table_1
        WHERE seq <= t.seq
        ) total
FROM (
    SELECT TOP 10 seq
        ,value
    FROM Table_1
    ORDER BY seq DESC
    ) t
ORDER BY seq DESC

--(10 rows affected)
--Table 'Table_1'. Scan count 11, logical reads 26083, physical reads 1, read-ahead reads 443, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--
--(1 row affected)
--
-- SQL Server Execution Times:
--   CPU time = 1422 ms,  elapsed time = 1621 ms.

My questions are:

Why the query from 1st attempt is slower than the 2nd one?
How can I improve the performance further? I can also change schemas.

Just to be clear, both queries return the same result as below.

I recommend testing with a bit more data to get a better idea of what's going on and to see how different approaches perform. I loaded 16 million rows into a table with the same structure. You can find the code to populate the table at the bottom of this answer.

The following approach takes 19 seconds on my machine:

SELECT TOP (10) seq
    ,value
    ,sum(value) OVER (ORDER BY seq ROWS UNBOUNDED PRECEDING) total
FROM dbo.[Table_1_BIG]
ORDER BY seq DESC;

Actual plan here. Most of the time is spent calculating the sum and doing the sort. Worryingly, the query plan does almost all of the work for the entire result set and filters to the 10 rows that you requested at the very end. This query's runtime scales with the size of the table instead of with the size of the result set.

This option takes 23 seconds on my machine:

SELECT *
    ,(
        SELECT SUM(value)
        FROM dbo.[Table_1_BIG]
        WHERE seq <= t.seq
        ) total
FROM (
    SELECT TOP (10) seq
        ,value
    FROM dbo.[Table_1_BIG]
    ORDER BY seq DESC
    ) t
ORDER BY seq DESC;

Actual plan here. This approach scales with both the number of requested rows and the size of the table. Almost 160 million rows are read from the table:

To get correct results you must sum rows for the entire table. Ideally you would perform this summation only once. It's possible to do this if you change the way that you approach the problem. You can calculate the sum for the entire table then subtract a running total from the rows in the result set. That allows you to the find the sum for the Nth row. One way to do this:

SELECT TOP (10) seq
,value
, [value]
    - SUM([value]) OVER (ORDER BY seq DESC ROWS UNBOUNDED PRECEDING)
    + (SELECT SUM([value]) FROM dbo.[Table_1_BIG]) AS total
FROM dbo.[Table_1_BIG]
ORDER BY seq DESC;

Actual plan here. The new query runs in 644 ms on my machine. The table is scanned once to get the complete total then an additional row is read for each row in the result set. There's no sorting and nearly all of the time is spent calculating the sum in the parallel part of the plan:

If you'd like for this query to go even faster you just need to optimize the part that calculates the complete sum. The above query does a clustered index scan. The clustered index includes all columns but you only need the [value] column. One option is to create a nonclustered index on that column. Another option is to create a nonclustered columnstore index on that column. Both will improve performance. If you're on Enterprise a great option is to create an indexed view like the following:

CREATE OR ALTER VIEW dbo.Table_1_BIG__SUM
WITH SCHEMABINDING
AS
SELECT SUM([value]) SUM_VALUE
, COUNT_BIG(*) FOR_U
FROM dbo.[Table_1_BIG];

GO

CREATE UNIQUE CLUSTERED INDEX CI ON dbo.Table_1_BIG__SUM (SUM_VALUE);

This view returns a single row so it takes up almost no space. There will be a penalty when doing DML but it shouldn't be much different than index maintenance. With the indexed view in play the query now takes 0 ms:

Actual plan here. The best part about this approach is the runtime isn't changed by the size of the table. The only thing that matters is how many rows are returned. For example, if you get the first 10000 rows the query now takes 18 ms to execute.

Code to populate table:

DROP TABLE IF EXISTS dbo.[Table_1_BIG];

CREATE TABLE dbo.[Table_1_BIG] (
    [seq] [int] NOT NULL,
    [value] [bigint] NOT NULL
);

DROP TABLE IF EXISTS #t;
CREATE TABLE #t (ID BIGINT);

INSERT INTO #t WITH (TABLOCK)
SELECT TOP (4000) -1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

INSERT INTO dbo.[Table_1_BIG] WITH (TABLOCK)
SELECT t1.ID * 4000 + t2.ID, 8 * t2.ID + t1.ID
FROM (SELECT TOP (4000) ID FROM #t) t1
CROSS JOIN #t t2;

ALTER TABLE dbo.[Table_1_BIG]
ADD CONSTRAINT [PK_Table_1] PRIMARY KEY ([seq]);


How can I have SQL Server listening on more than one IP address? I have a an SQL server that normally listens on a private IP address (10.x.x.x). However, I would like to be able to connect to the database over the public (31.x.x.x). I tried to enable this tonight, but when I restarted the SQL Server service, the server wasn't listening on the internal IP address any more.

I was using the configuration manager to let the SQL Server know the IP addresses it should be listening on.

I am running SQL Server 2008R Standard.

On the Protocol tab of configuration manager, there is a "Listen All" option, make sure that is set to yes.

Keep in mind that having an active listener on a public connection is asking for trouble, best to do it over a VPN, or filter allowed incoming connections by IP address, or at least use something other than the default port.


SQL query to convert columns into rows I have a table as shown below.My question is:How can I convert columns into rows?BTW,I'm using Microsoft SQL Server 2005.

my input Data

  ONAY_ID  sip_RECno    KULLANICI       ONAY
   1           1         user1          true
   2           1         user2          true
   3           1         user3          false
   4           1         user4          true

Output would be

 sip_RECno  user1     user2    user3     user4

  1          true     true    false    true

EDIT for new data type revelation...

SELECT sip_RECno,
    user1 = MAX(CASE WHEN ONAY_ID = 1 THEN CONVERT(TINYINT, ONAY) END),
    user2 = MAX(CASE WHEN ONAY_ID = 2 THEN CONVERT(TINYINT, ONAY) END),
    user3 = MAX(CASE WHEN ONAY_ID = 3 THEN CONVERT(TINYINT, ONAY) END),
    user4 = MAX(CASE WHEN ONAY_ID = 4 THEN CONVERT(TINYINT, ONAY) END)
FROM dbo.tablename
WHERE sup_RECno = 1
GROUP BY sip_RECno;


Access write conflicts with views and insteadof triggers? I have a legacy linked table in a msaccess database that is linked to a sql-server table.

The linked table is being synced periodically to a different set of tables that have been split up for various reasons and have more columns than the users want to see. I'd like to get rid of the legacy table and the head aches of syncing it, by creating a view with instead of triggers to handle the data access. Let's say my view looks like:

Create view Compatibility_View
with schemabinding,view_metadata
as
select   a.Name,a.col_1 aliasedName,a.col_2 aliasedName2
        ,b.col_1 aliasNamed3 ,convert(float,b.col_2) aliasedName4
        ,a.modified_date,a.modified_by
from dbo.some a
join dbo.other b
  on a.some_Id = b.some_Id
where a.isActive = 1;

And I have instead of triggers that will soft delete the records and route the properties appropriately.

I also automatically update the modified_date and modified_by columns. (These use to be handled by after triggers on the legacy table).

Unfortunately, doing this second set of requirements causes access to issue a warning after each edit that data has been changed. Tracing the queries access issues it appears that it is doing an update to the view, making sure that all other columns haven't changed. e.g.

update "dbo"."Compatibility_View"
set "aliasedName"='v'
where "aliasedName2"=45 and "aliasedName3"='horse'
and "aliasedName4"=1.3e2 and "modified_date"='09/10/2014 12:35:00.00'
and "modified_by"='AD\SomeUser'

Since my trigger change the modified_date and modifed_by columns access views it as a descrepencies and sends out an ugly error prompt?

The users like seeing the modified_date and modified_by columns in access so removing this piece is not really an option. How can I get these columns in the view without access freaking out?

Access uses the values of all columns to decide if a row has been updated by some other process between Access reading the row and subsequently writing to that row.

You can prevent that behavior by adding a TIMESTAMP column to the table(s) in question.  Access will then use the single TIMESTAMP value to determine if the row has been modified by someone else.

The only way to prevent that error message is to not update the row prior to Access attempting its own update.  I would make Access modify the two fields currently being modified by the trigger.


Stored procedure is running slow but it used to perform well http://pastebin.com/index.php?e=1I know you might have heard about this issue so many times or at least once in your career but this has become a huge performance pain in our environment.

Database size: 1 TB after compression Table Size: 800 GB with more than billion records

Application usually queries the stored proc, When they run the report, it usually calls a stored proc and it eventually queries huge view ( it has close to 900 columns and one of the table in the view is above mentioned 800GB table ).

Environment Overview:

SQL 2014 enterprise, Always On No table partitioning ( planning to implement this year ) Windows 2012 Standard Memory : 512, 490 GB allocation to SQL Server CPU : 16 cores

Issue: when a report was run, it calls a stored proc and the stored proc ultimately generates dynamic SQL based on the user select dynamic parameters. So, problem here is for every execution it generates new plan in the plan cache.

when ever the issue arise, we tweak the code inside by adding/removing some filters as a temporary work around but what we are looking here is to find a definitive solution to this problem.

Things we have done:

We have setup a different customized update stats job based on rows modified with sample percent for each indexes in that huge table.
We don't rebuild indexes on this huge table because of the amount of time but we do reorganize based on page count.
Created filtered statistics on the problem table ( large table ) based on the recommendation from SQLSkills because of skewed data ( we are working in parallel with SQL Skills team )

Things to do:

Table Partitioning -
I'm thinking of creating filtered indexes. If you have encountered any of these issues, please let me know your thoughts or suggestions.

I'm curious about what the plans look like even performance is bad. Hopefully you can tune those plans, and then make sure the dynamic SQL produces the right queries for those conditions.

Some things you may want to look for though:

Make sure your view is set up to simplify out joins that are not needed. Watch http://bit.ly/Simplification to get a picture of how.

Consider using additional predicates that could be used by the QO if particular plan shapes are used. Watch http://bit.ly/Sargability at the section about inverted predicates. But also, consider things such as looking out for year(thedate) = @y so you can change it to thedate >= datefromparts(@y,1,1) and thedate < datefromparts(@y+1,1,1) instead (or as well, in case you have an indexed computed column).

Additional IS NOT NULL predicates can be useful for helping some joins to be simplified out.

Check that you're avoiding OR and IN where possible, or at least considering what the impact is. You may want to change someval in (1,2,4) (which is three seeks within one operator) to someval >= 1 and someval <= 4 and someval != 3 (one seek over a range) in some situations, but be wary of the impact of that on composite indexes. Read http://sqlperformance.com/2016/06/sql-plan/whats-actually-going-on-with-that-seek for more on that.

When a filter isn't used, make sure you're completely leaving it out rather than using catch-all predicate. You may find you're asking it to check values you really don't care about. Hopefully you're doing this already.


The difference between filtering a specific column in 'On Clause ' of a join statement and 'Where clause' I have two tables with these structures:

Table1(Date ,Code ,Name)
Table2(Date ,Code)

I need to filter a specific date for both tables. For instance I need data only for '10/28/2019' from each table.I want to know whether there are differences between these two queries in terms of the final result and query performance. In the First query I'm filtering dates in ON clause and in the second query I'm filtering dates in the where clause.
Will there be any condition that these two queries produce different result set?
Thanks in advance.

query1 :

      Select "Desired Columns"
      From Table1 T1 Inner Join Table2 T2
      On T1.Date = "The specific date" and
         T2.Date = "The specific date" and
         T1.Code = T2.Code

query2:

      Select "Desired Columns"
      From Table1 T1 Inner Join Table2 T2
      On T1.Code = T2.Code
      where T1.date ="The specific date" and T2.Date = "The specific date"

Your particular example uses and inner join. The results will always be the same whether you use ON or WHERE.

I think of the ON clause as applying the predicate to the row from the right-hand table before it is combined with the left-hand row. The WHERE applies after the combination is formed. This is just how I think inside my head. The query optimizer does not work this way. I'll use an outer join to demonstrate what I'm saying.

Table1       Table2
------       ------
A            A
B

Query 1:
select * from t1
left outer join t2
  on t2.col = t1.col
where t2.col is null;

Query 2:
select * from t1
left outer join t2
  on t2.col = t1.col
  and t2.col is null;

Let's step through Q1. First A is read from t1. Then the ON clause says "find matching rows from t2". This returns A from t2 and we have an output row (A, A). Then we read B from t1. Again the ON says "find matching rows from t2". This time there is no match. Since it's an outer join we return (B, NULL). Now we've read all the rows and we apply the WHERE clause. Only one output row has t2 as NULL so query 1 returns

(B, NULL)

Repeat with Q2. We read t1 and return A. We read t2 but now the ON says t2's value must by both A and also NULL. No row matches. It's an outer join so we return (A, NULL). We read the next row from t1 getting B. We look in t2 for "B and NULL" and do not get a match. We return (B, NULL). There is no where clause so we're done. Query 2 returns

(A, NULL)
(B, NULL)

Different results depending on whether the predicate is in the ON clause or the WHERE clause, but only with outer joins.


Using Join to get data from multiple tables in SQL Server A(a_id, a1, a2)
B(b_id, b1, b2)
C(c-id, a_id, b_id)

Now, I want to fetch all the data of table A, and from table B, I only need a record of b1. How can I use join query in SQL Server to fetch the desired data?

If you only need to JOIN table A and B, but the configuration is on table C:

SELECT
    A.*,
    B.b1
FROM db.A
JOIN db.C ON (C.a_id=A.a_id)
JOIN db.B ON (B.b_id=C.b_id)
WHERE `table`.`field` = `parameter` # <-- Aditional


Multiple table constraint in oracle A table of boxes, with a capacity column, and a table of items
with columns box and size, and you want to enforce the constraint that an item can't be put in a box of capacity smaller than the item.

AFAIK in oracle, this can be done with a materialised view, or a trigger. Which would perform better, and are there other reasons to use one rather than other?

A materialized view requires extra storage because it is a table.

You want to make your materialized view to be refreshed on commit (for an immediate check) and to be fast refreshable, as a complete refresh may take too long to wait for.

General Restrictions on Fast Refresh

Assuming multiple items can be placed in a single box, the sum of item sizes needs to be accounted for (aggregation), so :

Restrictions on Fast Refresh on Materialized Views with Aggregates

A materialized view requires materialized view logs (which are also tables, more extra storage + administration) on the source tables for fast refreshes.

Maintaining extra tables means generating more redo and undo as well.

A materialized view provides more functionality than you need, which you will never use.

What a trigger would perform on your DML operations (aggregate sizes of inserted/updated/deleted items), a materialized view refresh would perform as well.

When you remove an item, the materialized view needs to be refreshed, but you do not even need to execute the trigger on deletions.

When you update an item, the materialized view needs to be refreshed, but you can skip the aggregation in the trigger if :new.box_id = :old.box_id and :new.size <= :old.size.

Or even better, you can cache the size of boxes in memory (result cache) and access them in the trigger without performing any actual SQL aggregation.

Yes, some of the above seem insignificant, and they usually are, the difference may be unnoticeable.
But with such a use case, a trigger can have the same effect as the materialized view combined with a check constraint, but with lower storage/administration overhead and requirements, so I would choose the trigger.


How to TRUNCATE table if the table doesn't exists I want to run a few TRUNCATE statements. But in between some of the database doesn't exists. But SQL stops the rest of the queries.

Is there any way to ignore the error and continue to execute the rest of the statements.

TRUNCATE _aaaaa_tmp;
TRUNCATE _bbbbb_tmp;
TRUNCATE _ccccc_tmp;

Error Code: 1146. Table '_bbbbb_tmp' doesn't exist

It depends on how you are executing those queries:

If you are loading an SQL file, execute then with --force, and errors will be ignored (all errors! So if you want to catch other possible issues, that is going to be a problem)
If run from an application, you can execute each command individually and only ignore on truncate for the specific table not found error
With some sql logic, you can check for table existence on information_schema.tables, although you may need to create a procedure just for that (raw command line doesn't allow for programming logic directly).

I think implementing that outside of SQL would be the preferred method (there is no "good" dynamic SQL execution on MySQL (other than tricks with prepared statements). E.g. a bash one liner (no error or escaping handling):

mysql -BN -e "SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME)
              FROM information_schema.tables
              WHERE table_schema in ('schemas', 'to', 'process')
              AND table_name like '\_%\_tmp'"
| while read table; do mysql -e "TRUNCATE TABLE $table"; done


When one of masters is unreachable all other replica sets work too slow in mongodb 2.6.11 and web application throws error 71 When one of masters is unreachable all other replica sets work too slow in mongodb 2.6.11, also I can not list the databases on mongos servers, and that is because of one replica set. How to solve this problem.

Update:

I have found this https://jira.mongodb.org/browse/SERVER-7246, do they say "If you have no redundant network connection to remote site, then go to hell"?

Replica sets are individual entities unless you are sharing resources among them like CPU,RAM,Disk... running multiple mongods in a single server.

I believe your case is that on a sharded cluster scatter-gather queries speed is determined by the slowest shard, so when one shard gets slow all scatter-gather queries will turn slow.


Why do I have two collations in mysqldump, under stored procs? When I run mysqldump --routines  I'm seeing multiple collations for my stored procs. Neither my DB nor columns use utf8mb (I understand the benefits of utf8mb over utf8 but let's leave it for now).

/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
ALTER DATABASE `pushcoin` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_cron_get_money_pool_balance` */;
ALTER DATABASE `pushcoin` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_name...`

The database seems all utf8...

MySQL> SHOW VARIABLES LIKE  'char%';
+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | utf8                                          |
| character_set_connection | utf8                                          |
| character_set_database   | utf8                                          |
| character_set_filesystem | binary                                        |
| character_set_results    | utf8                                          |
| character_set_server     | utf8                                          |
| character_set_system     | utf8                                          |
+--------------------------+-----------------------------------------------+
8 rows in set (0.00 sec)

MySQL> SHOW VARIABLES LIKE  'coll%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

(Some of this answer is 'guesses'.)

Each database has a default charset and collation.  That is used only for providing the charset and/or collation for a CREATE TABLE when you don't explicitly provide it for the table.  (And perhaps for Stored Routines.)

Why are there two ALTERs in that dump?  I don't know.  Perhaps it would make sense if there were a CREATE PROCEDURE between them.

My point is that your question is about something that is essentially irrelevant.

The SHOW VARIABLES output does not say "The database seems all utf8".  It does say that certain defaults are utf8 and that connections will assume the client is using utf8 unless otherwise specified.


Can I do VACUUM FULL to pg_largeobject table? I have two tables (table1, table2) in a Postgres 9.1 database. Both having oid type. Each table 1 million records. And pg_largeobject table size is around 40GB. I have removed 0.9 million records from each table, and executed the below command.

vacuum full analyze table1;
vacuum full analyze table2;

Still no change in pg_largeobject table size (auto vacuum is enabled)

Do I need to execute above command to pg_largeobject table too? Will it impact anything?

You can run that, no problem:

VACUUM FULL ANALYZE pg_largeobject;

Might even remove some dead rows. Details:

VACUUM returning disk space to operating system

But it's probably not going to solve your actual problem.

When using the large object facility of Postgres, large objects ("blob": binary large object) themselves are broken up in chuncks of binary data stored in the system table pg_largeobject. The PK consists of two columns (loid, pageno), loid is the oid that is used to refer to the blob in user table(s). The same blob can be referenced by OID many times.

Deleting rows in user tables does not remove the blob. For one, the same blob might be referenced more than one times. It is your responsibility to keep track and actually delete "unlinked" blobs yourself. One way would be to use lo_unlink():

SELECT lo_unlink(173454);  -- deletes large object with OID 173454

Since you already deleted rows with the oid reference, you need to be a bit more creative to identify unlinked blobs. Assuming you do not reference blobs from any other places, you can use this query to fix:

SELECT lo_unlink(l.loid)
FROM   pg_largeobject l
GROUP  BY loid
HAVING (NOT EXISTS (SELECT 1 FROM table1 t WHERE t.oid = l.loid))
AND    (NOT EXISTS (SELECT 1 FROM table2 t WHERE t.oid = l.loid));

You need to be superuser to access pg_largeobject directly. Assuming the column name in table1 and table2 is oid. Simpler query based on pg_largeobject_metadata in Postgres 9.3 or later (like @Daniel commented):

SELECT lo_unlink(l.oid)
FROM   pg_largeobject_metadata l
WHERE (NOT EXISTS (SELECT 1 FROM table1 WHERE t.oid = l.oid))
AND   (NOT EXISTS (SELECT 1 FROM table2 WHERE t.oid = l.oid));

pg_largeobject_metadata is publicly readable. But I don't see the OID of the blob in the system table in versions before pg 9.3 (incl. pg 9.1) - at least not in the manual, I don't have an old version to test right now. So you probably must use my first query.

Compare before and after:

SELECT count(*) FROM pg_largeobject;
SELECT pg_size_pretty(pg_table_size('pg_largeobject'));

You might run VACUUM FULL now, and test again:

VACUUM FULL ANALYZE pg_largeobject;

You'll be interested in the additional module lo, that's available for Postgres 9.1. The manual has an accurate description for your problem:

  ... a table entry can reference a large object by OID, but there can be
  multiple table entries referencing the same large object OID, so the
  system doesn't delete the large object just because you change or
  remove one such entry.

Bold emphasis mine. The module offers a solution, too:

  The lo module allows fixing this by attaching a trigger to tables that
  contain LO reference columns. The trigger essentially just does a
  lo_unlink whenever you delete or modify a value referencing a large object.

For use cases where each blob is referenced exactly once in your whole DB.

And obviously also (like @Daniel mentioned) vacuumlo:

  vacuumlo is a simple utility program that will remove any "orphaned"
  large objects from a PostgreSQL database.


How can I identify non deterministic updates occurring in my SQL Server? SQL Server books online states

  Use caution when specifying the FROM clause to provide the criteria
  for the update operation. The results of an UPDATE statement are
  undefined if the statement includes a FROM clause that is not
  specified in such a way that only one value is available for each
  column occurrence that is updated, that is if the UPDATE statement is
  not deterministic.

I need to try and identify cases where non deterministic updates are occurring in our database. It appears that SQL Server does not throw an error or warning in such a case.

Does anyone know if there is an easy way of identifying where these issues are occurring, querying plan cache etc?. Doing a full code review to identify these issues will be time consuming given the size and complexity of the application.

Tested on SQL Server 2008\2017 developer editions.

Here is an example:

declare @t1 table (id int, col int);
insert into @t1 values(1,1);

declare @t2 table (id int, col int);
insert into @t2 values(1,10), (1,20);

update t1
set t1.col = t2.col
from @t1 t1 join @t2 t2
         on t1.id = t2.id;

select *
from @t1;

SQL Server does not warn you of this when using its proprietary UPDATE syntax. The MERGE statement would throw an error "The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row."

The execution plan for your example is

update t1  set t1.col = t2.col  from @t1 t1 join @t2 t2           on t1.id = t2.id
  |--Table Update(OBJECT:(@t1 AS [t1]), SET:(@t1.[col] as [t1].[col] = @t2.[col] as [t2].[col]))
       |--Stream Aggregate(GROUP BY:([Bmk1000]) DEFINE:([t2].[col]=ANY(@t2.[col] as [t2].[col])))
            |--Nested Loops(Inner Join, WHERE:(@t2.[id] as [t2].[id]=@t1.[id] as [t1].[id]))
                 |--Table Scan(OBJECT:(@t1 AS [t1]))
                 |--Table Scan(OBJECT:(@t2 AS [t2]))

The Bmk column is output from the scan on @t1 to act as a unique row identifier. The, potentially multiple, joining rows from @t2 are then added and collapsed down to an arbitrary one per Bmk using the ANY aggregate. The plan may in some circumstances replace the ANY aggregate with a DISTINCT SORT

So one way would be to search the plan cache for all execution plans containing update operators that have a descendant operator using either ANY or DISTINCT SORT as this may be a sign that SQL Server was arbitrarily removing the duplicates before doing the update.

This is far from fool proof as they may appear in the plans for other reasons but should give some viable candidates to review.

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT st.text
FROM   sys.dm_exec_cached_plans cp
       CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
       CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE  EXISTS(SELECT 1
              FROM   qp.query_plan.nodes('//Update') upd(n)
              WHERE  1 IN ( n.exist('//Aggregate[@AggType eq "ANY"]'),
                            n.exist('//RelOp[@LogicalOp eq "Distinct Sort"]') ))


Is it possible in Oracle to trace SQL statements that result in errors? We have Oracle 11g in production. Application system is still under active development. It will be very handy to get SQL statements which cause any error.

Does Oracle provide a standard function to trace and log these statements and additional (debug) info?

While I'm not sure if it's the easiest way, you can use an after servererror on database trigger to log all errors to a table.

From Log all database errors to a table by Ferdous Nadvi:

drop trigger log_errors_trig;
drop table   log_errors_tab;

create table log_errors_tab (
 error     varchar2(30),
 timestamp date,
 username  varchar2(30),
        osuser    varchar2(30),
        machine   varchar2(64),
 process   varchar2(8),
 program   varchar2(48));

create or replace trigger log_errors_trig
 after servererror on database
declare
 var_user     varchar2(30);
 var_osuser   varchar2(30);
 var_machine  varchar2(64);
 var_process  varchar2(8);
 var_program  varchar2(48);
begin
 select username, osuser, machine, process, program
 into   var_user, var_osuser, var_machine, var_process, var_program
 from   sys.v_$session
 where  audsid = userenv('sessionid');

 insert into log_errors_tab
   values(dbms_standard.server_error(1),sysdate,var_user,
          var_osuser,var_machine,var_process,var_program);
end;
/

See also Oracle System Event Triggers on PSOUG and the official documentation for system event triggers


MSSQL Agent Task Failing - Stored Procedure I have a stored procedure that is giving me some issues as an Agent Job

When running the task, I get the following error:

Executed as user: SQLUserName. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274).  The step failed.

The Task has 1 step and that step is just "EXEC dbo.Procdure_Name" and the "Run as" is blank (i'm unable to specify anything here). I have put in "Advanced", "Run as user" as SQLUserName.

When I remove this, I get the error:

Executed as user: domain\servename$. Login failed for user 'domain\servername$'. [SQLSTATE 28000] (Error 18456).  The step failed.

This is running a command on a linked server. When I remove that command it works.

The linked server is set to logon to "be made using the login's current security context" and testing the link server works fine.

If I log into SMSS as SQLUserName and run the procedure manually it works fine.

Would appreciate any suggestions.

I have put in "Advanced", "Run as user" as SQLUserName

This is not the option to use in your case.

Using this option you can specify the user, not login, to run your sp. However, when you connect to linked server, it's not a database user, but server login that is used, it's "login's current security context" that will be used.

So in your case all depends on the job's owner. If it's sysadmin, the job is executed under Windows account of your SQL Server Agent, and if it's not sysadmin, the job is executing using job's owner's login.

From your error it seems that your job's owner is sysadmin, as domain\servename$ is used. This is because your Agent is running under Local System or Network Service that when going on the network use your pc's account, domain\PCname$.

To fix you problem you can either change job owner to some login that has access to linked server or change SQL Server Agent's service account to domain account that should be mapped on linked server.