Idiomatic way to implement UPSERT in PostgreSQL

UPDATE (2015-08-20):

There is now an official implementation for handling upserts through the use of ON CONFLICT DO UPDATE (official documentation). At the time of this writing, this feature currently resides in PostgreSQL 9.5 Alpha 2, which is available for download here: Postgres source directories.

Here is an example, assuming item_id is your Primary Key:

INSERT INTO my_table
    (item_id, price)
VALUES
    (123456, 10.99)
ON
    CONFLICT (item_id)
DO UPDATE SET
    price = EXCLUDED.price

Original Post...

Here is an implementation I arrived upon when desiring to gain visibility into whether an insert or update occurred.

The definition of upsert_data is to consolidate the values into a single resource, rather than having to specify the price and item_id twice: Once for the update, again for the insert.

WITH upsert_data AS (
    SELECT
    '19.99'::numeric(10,2) AS price,
    'abcdefg'::character varying AS item_id
),
update_outcome AS (
    UPDATE pricing_tbl
    SET price = upsert_data.price
    FROM upsert_data
    WHERE pricing_tbl.item_id = upsert_data.item_id
    RETURNING 'update'::text AS action, item_id
),
insert_outcome AS (
    INSERT INTO
        pricing_tbl
    (price, item_id)
    SELECT
        upsert_data.price AS price,
        upsert_data.item_id AS item_id
    FROM upsert_data
    WHERE NOT EXISTS (SELECT item_id FROM update_outcome LIMIT 1)
    RETURNING 'insert'::text AS action, item_id
)
SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome

If you don't like the use of upsert_data, here is an alternate implementation:

WITH update_outcome AS (
    UPDATE pricing_tbl
    SET price = '19.99'
    WHERE pricing_tbl.item_id = 'abcdefg'
    RETURNING 'update'::text AS action, item_id
),
insert_outcome AS (
    INSERT INTO
        pricing_tbl
    (price, item_id)
    SELECT
        '19.99' AS price,
        'abcdefg' AS item_id
    WHERE NOT EXISTS (SELECT item_id FROM update_outcome LIMIT 1)
    RETURNING 'insert'::text AS action, item_id
)
SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome


Why is supplying the hostname sometimes optional and otherwise not in psql?

The default for all Postgres command line tools is to use "local" connections, i.e. unix sockets (unless this is "overruled" by environment variables).
The next step in the authentication process is to look up if and how the user should authenticate - which is defined by the entries in pg_hba.conf.
Most probably the default entry in pg_hba.conf says "all local connections are authenticated using peer" which is the default and is controlled by the following line:
local   all             all                                     peer

So psql -U teamcity tries to use a "local" connection and pg_hba.conf requires peer authentication for the user teamcity - most probably your current Linux user is not named teamcity and thus the authentication fails.
If you add -h localhost to the command line, a "host" connection using TCP is established, which is controlled by a different set of rules in pg_hba.conf.  Maybe our file contains something like:
host    all             all             all                     md5

which allows TCP connection from any client if, with any database user as long as the correct password is provided.
You probably ran createuser and createdb while being logged in as the postgres Linux user, and for that peer authentication succeeds as the Linux username matches the Postgres database username.


Database design: Which will perform better, auto incremented column or a composite key for appointments

From a purely performance standpoint, using the identity key as a clustered primary key will be most efficient. From there I would do a non clustered key with a unique constraint on datetime, patientid.


What should I consider before I upgrade my SQL Server memory?

There is not much to check if you have found out that SQL Server requires more memory.Is your OS on physical machine or Virtual if it is virtual adding memory would be easy.

You must know that SQL Server 2012 standrd edition supports maximum of 64G memory. After adding new memory you must change max server memory setting using sp_configure. Set max server memory to appropriate value. Leave enough memory for OS and SSIS, SSAS,SSRS if you are using these features.


Transpose MySQL rows as column names - DYNAMIC

I worked it out, in-case anyone would like to know the following Query did it:

SELECT CONCAT(
'SELECT `ost_ticket_event`.ticket_id', GROUP_CONCAT('
 ,    `t_', REPLACE(state, '`', '``'), '`.timestamp
     AS `', REPLACE(state, '`', '``'), '`'
 SEPARATOR ''),
' FROM `ost_ticket_event` ', GROUP_CONCAT('
 LEFT JOIN `ost_ticket_event`   AS `t_', REPLACE(state, '`', '``'), '`
        ON `ost_ticket_event`. ticket_id = `t_', REPLACE(state, '`', '``'), '`.ticket_id
       AND `t_', REPLACE(state, '`', '``'), '`.state = ', QUOTE(state)
 SEPARATOR ''),
' GROUP BY `ost_ticket_event`.ticket_id'
) INTO @qry FROM (SELECT DISTINCT state FROM `ost_ticket_event`) t;

PREPARE stmt FROM @qry;
EXECUTE stmt;


How to interpret this "db.explain()" for this mongodb query

Yes, an index is used in both cases, just with different operators.  The key to determining whether a query has used an index or not is the "cursor" : field.  If this says BasicCursor then no index was used (a table scan was performed).  Otherwise an index was used.

In your case you are using a "2d" geo index each time, in fact for $nearsphere and $within, $centersphere to work, you must be using a geo index.

As for the rest of the output, they are both returning 50 objects, the first is significantly faster.  You can see the explanation of the various fields here:

http://www.mongodb.org/display/DOCS/Explain

You have not listed what indexes you have defined, but if you wanted to attempt to speed things up you can try adding a compound index.  Remember a query in MongoDB can only use one index at a time and for the geo commands to work that must be a 2d/geo index, so if you have indexes on your other fields, they will not be used unless they are part of a compound index with your geo index.  You should also note that the 2d index must currently be the first field in the compound index, per here:

http://www.mongodb.org/display/DOCS/Geospatial+Indexing#GeospatialIndexing-CompoundIndexes


Return ALL rows based on a matching date also matching a specific value in another column

try this

create table #colors (
   ID       int,
   date     date,
   type     varchar(10)
)

insert into #colors values (100, '1/1/2019', 'red')
insert into #colors values (100, '1/1/2019', 'blue')
insert into #colors values (101, '1/1/2019', 'blue')
insert into #colors values (101, '1/5/2019', 'blue')
insert into #colors values (101, '1/1/2019', 'green')
insert into #colors values (101, '1/9/2019', 'green')
insert into #colors values (102, '1/5/2019', 'blue')
insert into #colors values (102, '1/2/2019', 'green')
insert into #colors values (103, '1/1/2019', 'blue')
insert into #colors values (103, '1/9/2019', 'green')

select c.*
  from #colors c
  join #colors c1
    on c.id = c1.id
   and c1.type = 'blue'
   and c.date = c1.date
 where c.type <> 'blue'


How can I identify all large tables that have no index?

I would use this query to find that out.  The rowcount for Non-Clustered is the sum of ALL non-clustered indexes on the table, but the heap/clustered count is accurate.

;WITH CTE_RowCount AS
    (
    SELECT S.[name] AS SchemaName
        , O.[name] AS ObjectName
        , I.type_desc AS IndexType
        , SUM(P.[rows]) AS [RowCount]
    FROM sys.partitions AS P
        INNER JOIN sys.objects AS O ON O.object_id = P.object_id
        INNER JOIN sys.schemas AS S ON S.schema_id = O.schema_id
        INNER JOIN sys.indexes AS I ON I.object_id = P.object_id AND I.index_id = P.index_id
    GROUP BY S.[name], O.[name], I.[type_desc]
    )
SELECT *
FROM CTE_RowCount AS R
    PIVOT (SUM(R.[RowCount]) FOR IndexType IN ([HEAP], [CLUSTERED], [NONCLUSTERED])) AS Pvt
WHERE COALESCE(Pvt.[HEAP], Pvt.[CLUSTERED]) >= 10000
    --AND Pvt.[CLUSTERED] IS NULL -- Will only show if table is a HEAP
    --AND Pvt.[NONCLUSTERED] IS NULL --will only show if there are no non-clustered indexes.


Two things are needed to make it run faster:

Get suitable index

Without confirmed = 1, you need

INDEX(country, date_created)

With it, you need

INDEX(country, confirmed, date_created)

Avoid hiding the date in a function

LIKE '2015-10%' cannot use an index on date_created, nor can MONTH(date_created), so let's reformulate the query:

WHERE date_created >= '2015-10-01'
  AND date_created  < '2015-10-01' + INTERVAL 1 MONTH
  AND confirmed = 1   -- add this if you want to limit to confirmed rows

(This assumes that date_created is a DATETIME or TIMESTAMP datatype.)

All Months

This query can still use the two indexes I suggest, however, it won't necessarily use the date_created part.

SELECT LEFT(date_created, 7) AS YYYY_MM,
       COUNT(*) AS ct
    FROM tbl
    WHERE country = 'DE'
      AND confirmed = 1   -- if you want this
    GROUP BY YYYY_MM;

Note about country_code

A common mistake in defining the schema (please provide SHOW CREATE TABLE) is to define it as CHAR(2) and default to utf8.  This leads to using 6 bytes, when 2 would suffice.  Change to CHAR(2) CHARACTER SET ascii.


Efficient method to update character length of columns in large table

I would suggest the following steps

When possible, change your database recovery mode to Simple Recovery if otherwise
Drop your index(es) on the two varchar(max) columns (as you will need to rebuild it anyway)
Run the following

update MyTable Set MyCol_X=substring(MyCol_X, 1, 255), MyCol_Y=substring(MyCol_Y, 1, 255);
Alter table to change the column data type

alter table MyTable alter column MyCol_X varchar(255);

alter table MyTable alter column MyCol_Y varchar(255);
Rebuild your indexes that were dropped
Set recovery mode to previous state before step 1 is executed


Can I delete SQL Server 2008 R2 "audittrace" (.trc) files

Are those files really from C2 audit?

The audit file format is audittraceyyyymmddHHMMSS.trc

After you disable C2 audit using

EXEC sp_configure 'c2 audit mode', 0
go
reconfigure with override

and you are absolutely sure that you dont need the audit files, you can delete the files.

  If so, can I safely remove them without worring about data stored in databases?

The C2 Audit Mode uses SQL Trace to capture audit events, which are stored in trace files i.e. they are not stored in database, unless you have a job that loads the files into a database.

Note : The C2 security standard has been superseded by Common Criteria Certification.


Had forgotten about updating this one after getting help from Microsoft Support. Turns out that the build number on one nodes was slightly different to the other due to a Security Patch that had only been applied to one node, possibly due to a Windows Update.


How to find mongo document by ObjectID age

The general approach was to fabricate an ObjectID with the time stamp prefix of what I wanted.  The general query looked like

db.mycol.find({_id: {$gte:ObjectId("50b7feb00000000000000000")} }, {fieldIWant:1})

The corresponding mongoexport cmd for a json output file was

 mongoexport -d mydb -c mycol -q '{_id: {$gte:ObjectId("50b7feb00000000000000000")} }' -f 'fieldIWant' > myFile.json


How do I determine if a column is defined as a serial data type instead of an integer based off the catalog?

Let me add to efesar's answer that the documentation states the following:

  The data types smallserial, serial and bigserial are not true types,
  but merely a notational convenience for creating unique identifier
  columns (similar to the AUTO_INCREMENT property supported by some
  other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

  is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Which means that if

a column's data type is integer (bigint) and
it is NOT NULL and
its default value comes from a sequence owned by the column in question

then it is a serial column.  So checking these factors in the catalogs, as you proposed (with the addition of NOT NULL), are sufficient to identify a serial column.

For an actual query to find the (big)serials, see the excellent answer of Erwin Brandstetter.


The CTE first finds the first order than every item was purchased (per customer and factory) and then we simply count the distinct order IDs (and subtract 1 as the very first order doesn't count):

with cta as
(
  select
      FactoryID, CustomerID,
      min(RelatedRecordID) as MinRelatedRecordID
  from
      Table1
  group by
      FactoryID, CustomerID, ItemID
)
select
    FactoryID, CustomerID,
    count(distinct MinRelatedRecordID)-1 as OrdersWithNewItems
from
    cta
group by
    FactoryID, CustomerID
having
    count(distinct MinRelatedRecordID) > 3 ;

Tested in SQLfiddle.com


How can I list all the tablespaces without getting into each instance?

I don't have anywhere to test this at the moment, but either of these might work.

If you have su privileges to all instance owner users:
db2ilist | while read inst; do su - $inst db2pd -alldbs -tablespaces; done

If you have sufficient privileges on all instances yourself:
db2ilist | while read inst; do env DB2INSTANCE=$inst db2pd -alldbs -tablespaces; done


If you're going with traditional partitioned views with each table containing a different yearly quarter, then you have a few levels of separation here:

Each Table
Each Index on Each Table

So in other words, say you have 10 tables that the partitioned view accesses, and you have a clustered index and a nonclustered index on each table.  In this case you are looking at 20 different data structures that you need to place accordingly.

As to where to put each of these will largely depend on your workload.  For instance, if only the most recently two quarter tables get the most report queries and data modification, you'll want to ensure those are separate.  But in the same respect, you could benefit from each of those tables having their indexes on separate disks.

I say disks here because filegroups are logical groupings of database data files.  Even if you split each table and each index on their own filegroup (exaggerated to illustrate a point) but all the containing data files are on the same set of physical disks then you won't really see any underlying I/O performance gain.  It's really just administrative at that point.  You can't always divide the physical layer and the logical layer when squeezing performance out of a design.

So you need to visually lay out the physical disk separation, what type of common workload you will be receiving, and design your data placement in that manner.


Always On - keep secondary database readable when connection to primary node failed

Is there a way to change this behavior and keep secondary databases always online?

Yes.  Use SQL 2014 or later :)

  Beginning in SQL Server 2014 (12.x), readable secondary replicas can
  remain online even when the primary replica is offline due to user
  action or a failure. However, read-only routing does not work in this
  situation because the availability group listener is offline as well.
  Clients must connect directly to the read-only secondary replicas for
  read-only workloads.

Active Secondaries: Readable Secondary Replicas (Always On Availability Groups)


SQL Server - moved index behind PK constraint to different filegroup, but space not freed

Space in FG2 used by objects:

SELECT DS.name AS FileGropName
      ,OBJ.name AS ObjectName
      ,IDX.type_desc AS IndexType
      ,IDX.name AS IndexName
      ,AU.type_desc
      ,AU.total_pages * 8 / 1024 AS Mb
FROM sys.data_spaces AS DS
     INNER JOIN sys.allocation_units AS AU
         ON DS.data_space_id = AU.data_space_id
     INNER JOIN sys.partitions AS PA
         ON (AU.type IN (1, 3)
             AND AU.container_id = PA.hobt_id)
            OR
            (AU.type = 2
             AND AU.container_id = PA.partition_id)
     INNER JOIN sys.objects AS OBJ
         ON PA.object_id = OBJ.object_id
     INNER JOIN sys.indexes AS IDX
         ON PA.object_id = IDX.object_id
            AND PA.index_id = IDX.index_id
WHERE DS.name = 'FG2'
ORDER BY AU.total_pages DESC


mysql: [ERROR] unknown variable 'default-authentication-plugin=mysql_native_password'

You need specify like this in my.cnf file :
Note: Make sure it is under [mysqld] section.
default_authentication_plugin=mysql_native_password

Keeping in mind only changing default_authentication_plugin won't allow user to login, because it will still use the old authentication plugin using which the user was create.
To change to the plugin to need to follow below steps :
Step1 : Instance must be enabled with the old plugin only using which the user is created. In your case the default plugin will sha256 password.
Step2 : Now re-create user with mysql_native_password plugin.
CREATE USER 'nativeuser'@'localhost'IDENTIFIED WITH mysql_native_password BY 'password';

Step3: Change my.cnf with new plugin & restart instance.
default_authentication_plugin=mysql_native_password


Update records that match like regex from another table

Please try, if you could, this approach:

UPDATE
    ProductCategories,
    Products
SET
    ProductCategories.categoryid = CASE LEFT(UCASE(LTRIM(Products.productname)), 2)
                                      WHEN 'AP' THEN 1
                                      WHEN 'TO' THEN 2
                                      WHEN 'WA' THEN 3
                                      ELSE ProductCategories.categoryid
                                   END
    WHERE
        ProductCategories.product_id = Products.product_id

I assumed that product ID column is named product_id


Oracle SQL Statement alter index and add partition

No its not possible to Partition a non partitioned index using rebuild in Oracle.

For partitioning an index you will need to Drop the index and then create a partitioned index.

Hope it Helps

Vishad


You must concatenate those values. The best way is to use FOR XML PATH.

Query (SQL Fiddle):

SELECT [TicketID], [Type]
    , [Code] = stuff(
        (
            SELECT ', '+ [Code]
            FROM data d2
            WHERE [TicketID] = d1.[TicketID] AND [Type] = d1.[Type]
            FOR XML PATH('')
        ), 1, 1, ''
    )
FROM (SELECT DISTINCT [TicketID], [Type] FROM data) d1

Output:

TicketID                                Type    Code
44899B82-41C9-4020-9C2B-007C6000D283    ARTI    22150012, 22150017
44899B82-41C9-4020-9C2B-007C6000D283    CASH    NULL
76C6D696-55E7-4CA5-A1AA-0F0DB446D9A4    ARTI    22150018, 22150012, 22150011
76C6D696-55E7-4CA5-A1AA-0F0DB446D9A4    CASH    NULL

If [TicketID] and [Type] are in table Line and [Code] in table Article, LEFT JOIN must be used (SQL Fiddle):

SELECT [TicketID], [Type]
    , [Code] = stuff(
        (
            SELECT ', '+ [Code]
            FROM Line l2
            LEFT JOIN Article a ON a.ID = l2.ArticleId
            WHERE [TicketID] = l1.[TicketID] AND [Type] = l1.[Type]
            FOR XML PATH('')
        ), 1, 1, ''
    )
FROM (SELECT [TicketID], [Type] FROM Line) l1


postgresql sticking "enable_seqscan=False" in a UDF sensible?

enable_seqscan=true doesn't do what you think it will.  It only allows the planner to choose that.  It doesn't force it.

Note it should be on generally anyway.  Sequential scans are far less costly in PostgreSQL than in MySQL since they scan in physical order.  There are many times when they are the cheaper plan.


Database Directory storage is full need to change link it to new Partition

Here is the key:

2018-08-21T10:08:04.839360Z 0 [ERROR] InnoDB: os_file_readdir_next_file()
returned -1 in directory ./, crash recovery may have failed for some .ibd
files!

It says, it can't traverse the directory containing the database files, with the error code -1. Error -1 means

  permission denied

The user on which your mysql server is running, should have access to the directory. On most Linuxes, the command is: chown mysql:mysql -Rc /var/lib/mysql as root.


Creating a Trigger in MySQL that stores stock levels once a sale takes place

You must use SUM function with orderPrice

SELECT SUM(ORDERS.OrderPrice), SUPPLIER.SupplierName FROM ((ORDERS INNER JOIN PRODUCT ON ORDERS.ProductID = PRODUCT.ProductID) INNER JOIN SUPPLIER ON PRODUCT.SupplierID = SUPPLIER.SupplierID) GROUP BY SupplierName with ROLLUP


How to handle "reference tables" in CouchDB?

As a MySQL DBA staring at this, I think in relational terms. I also think in terms of doing JOINs. However, from a NoSQL standpoint, JOINs would have to be done programmatically.

Multiple documents, one for each ingredient : This will make smallest amount of static data to place in collections. Yet, you should be prepared for getting references, building temporary collections for yourself with CouchDB or sending data straight to your app.

One document with all references : If you are content with mining Collections out of Collections out of Collections ... (inifinity), you are willing to construct such collections, and you are comfortable with it, then storing the data this way would be OK. Please just make sure you are not the only CouchDB Jedi around (unless it's a matter of job security), since you might want your code understandable and maintainable by others.

One document per reference : Now, this is a nice middle ground. It will provide some flexibility in the event there are changes in the Collection layout. Simple retrievals for ingredients can be independent of retrievals for spices or utensils. Performing programmatic JOINs is still needed but not all that deep. Those who are a novice to NoSQL paradigms (like myself) would be comfortable understanding and retrieving data without the fear of breaking deeply-entrenched complex data representations.

My personal choice would be option #2.


How do I keep a database physically secure?

This is quite broad and the answer largely depends on how much your data is worth to you?

If you are from Amazon and you are storing millions of credit card numbers, then loss and/or revealing that data to third parties could be potentially costly in terms of fines from regulatory bodies and loss of consumer trust and future customers.

If they are the scores from your daughter's little league soccer, then the consequences mightn't be as severe financially (although annoyed little girls have ways of making that feeling very clear :-) ).

Maybe you could explain your potential exposure in more detail and ask more specific questions and you might get better advice tailored to your precise needs?

Take a look here (and links within) on how to ask good questions. This site is a great resource and it's worthwhile making the effort to follow the guidelines. Help us to help you!


Using SQLCMD in this way was new to me and got me searching the Internet for examples.

After studying the information in SQL Server Management Studio: Hacking SQLCMD Mode and pondering the limitations of SQLCMD variables, I took a slightly different approach to your problem. My solution may not fully answer your question about why :OUT is not working as expected, but I'll offer it up anyway.

My full script is at the end of my answer, but here are some fragments with explanations.

I started out by redirecting the output to my local user temp directory

set nocount on

--Redirect output to user temp folder
--On my computer, this was 'C:\Users\uswsh\AppData\Local\Temp'
:OUT $(TEMP)\DBScript.sql

Now, we invoke the cursor to read through the list of databases.  I'm using the PRINT command to generate operating system echo commands to create the individual database create files.  The output of the entire cursor execution is written to the DBScript.txt file in my user temp folder.

--Declare variables used by cursor
DECLARE @db sysname;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT d.name
FROM sys.databases d;
OPEN cur;
FETCH NEXT FROM cur INTO @db
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT '!!echo nul > C:\temp\create_database_' + @db + '.txt';
        FETCH NEXT FROM cur INTO @db
    END
CLOSE cur;
DEALLOCATE cur;
go

Upon execution of the above, the DBScript.txt file now contains these commands (The double bang at the beginning of each command will tell SQLCMD to execute these as operating system commands):

!!echo nul > C:\temp\create_database_master.txt
!!echo nul > C:\temp\create_database_tempdb.txt
!!echo nul > C:\temp\create_database_model.txt
!!echo nul > C:\temp\create_database_msdb.txt
!!echo nul > C:\temp\create_database_Test.txt
!!echo nul > C:\temp\create_database_AdventureWorks2014.txt

Now, back to the original script, we revert the SQLCMD output to stdout and execute the commands in DBScript.txt

--Revert output to stdout
:OUT stdout

--Execute the script you just created that has the !!echo commands
:r $(TEMP)\DBScript.sql
GO

When I look at my C:\Temp directory, I now have the following individual files:

Here is the full script:

set nocount on

--Redirect output to user temp folder
--On my computer, this was 'C:\Users\uswsh\AppData\Local\Temp'
:OUT $(TEMP)\DBScript.sql

--Declare variables used by cursor
DECLARE @db sysname;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT d.name
FROM sys.databases d;
OPEN cur;
FETCH NEXT FROM cur INTO @db
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT '!!echo nul > C:\temp\create_database_' + @db + '.txt';
        FETCH NEXT FROM cur INTO @db
    END
CLOSE cur;
DEALLOCATE cur;
go

--Revert output to stdout
:OUT stdout

--Execute the script you just created that has the !!echo commands
:r $(TEMP)\DBScript.sql
GO


Install the most recent service pack, then install the latest cumulative update.

According to SQL Server Builds, the most recent Service Pack for SQL Server 2014 is currently SP2.

CU10 is the most recent Cumulative Update.

Ensure you take a backup of your databases onto physically different media, and do this in a non-production environment to test the change prior to doing this to your production SQL Server.


Database Offset & Limit without order by?

Your syntax won't work with PostgreSQL, but I can answer your question for PostgreSQL anyway:

Your query will often work as you intend because the three statements will often use the same execution plan. There is no guarantee for that though.

One possibility where even a sequential scan in PostgreSQL will return a different order is if there is already a sequential scan of the table in progress: Then PostgreSQL will start scanning where the other statement is currently scanning the table, i.e. somewhere in the middle, so that both scan can execute somewhat synchronized to reduce the I/O operations necessary.


How to create custom error messages in APEX?

In Apex specifically, use validations. You can specify them per field or use a custom one that checks all the fields at once.

http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/bldr_validate.htm#BABCDBDE


Copy On Write Transparent database for MySQL

I am not aware of anything doing this kind of operation within mysql itself. For Oracle there is something, I think it is called Oracle Workspace Manager, but I am not sure, there are a lot of options available.

For mysql I think you need to do this on storage level, e.g. use a filesystem with good cloning functionality like ZFS. Or a SAN.

Put the data part of the mysql instance on a seperate filesystem
Run prod instance like usual
Clone the data fs when needed (does not distured prod instance)
Configure a second mysql instance which uses the cloned fs as data dir
Fire up second instance, which will do a recovery now
Use second instance for testing

I have not setup this myself, so minor things might be missing, but generally this is a way to do this - and its not too complicated I think.

It will not help you for things like load testing as prod and test share the same storage subsystem, but for the usual tests it is fine.


How to make Indexed Views work for SQLCLR?

Can indexed views do anything? Ever?

It actually is kind of miserable when you think of an indexed view as a solution, only to find your use case hits on one of the many limitations of indexed views (I'm looking at you, LEFT JOIN).

You're right that you can't include the SQLCLR in the index keys of the indexed view.  However, you can include it in the SELECT list of the view definition, which will persist the value to disk.  So you can at least avoid the cost of calculating the value on the fly when reading from the table.

In the AdventureWorks2014 sample database, I created this view on the delightfully named Person.Person table:

CREATE OR ALTER VIEW dbo.PersonWithHashForSomeReason
WITH SCHEMABINDING
AS
SELECT
    BusinessEntityID,
    PersonType,
    dbo.SpookyHash(CONVERT(binary(50), FirstName)) AS FirstNameHash
FROM Person.Person
GO

Note: I was too lazy to write my own CLR function, so this one is from this Q&A.

I can make that an indexed view by clustering it on BusinessEntityID:

CREATE UNIQUE CLUSTERED INDEX CX_BusinessEntityID
ON dbo.PersonWithHashForSomeReason (BusinessEntityID);
GO

Looking for a specific set of rows in the table produces an index scan (execution plan link).  Notice the lack of a Compute Scalar operator, which would normally be used to produce the hash value.  Since the hash is persisted to disk in the indexed view, that's not necessary:

SELECT BusinessEntityID, FirstNameHash
FROM dbo.PersonWithHashForSomeReason WITH (NOEXPAND)
WHERE FirstNameHash = 0x910C426C533F2C0AAF350158331E3B01;

I had to use a NOEXPAND hint to get it to use the view.

You'll notice the warnings in the plan are due to the fact that the whole table was scanned in order to find those values, since there is no index keyed on FirstNameHash.

Unfortunately, trying to create a nonclustered index on this persisted value fails:

CREATE NONCLUSTERED INDEX IX_FirstNameHash
ON dbo.PersonWithHashForSomeReason (FirstNameHash);

  Msg 1976, Level 16, State 1, Line 48
  Cannot create index or statistics 'IX_FirstNameHash' on view 'dbo.PersonWithHashForSomeReason' because cannot verify key column 'FirstNameHash' is precise and deterministic. Consider removing column from index or statistics key, marking column persisted in base table if it is computed, or using non-CLR-derived column in key.

This is because SQL Server doesn't trust us:

  Why don’t we trust the user? What can really go wrong here??

  Assume if the user incorrectly sets the custom attribute
  IsDeterminsitic to true for a non-deterministic function and assume he
  is able to create an index on a computed column that invoked this
  function without persisting it. [...] This might lead to index
  corruption as the function might return different values for the same
  input as the function is non-deterministic.

  So for the sake of user’s safety, Sql Server in this release REQUIRES
  the user to persist the computed columns (unlike the tsql case) to
  actually index the computed columns.

I imagine the same limitation exists for fulltext indexes, although I haven't tried it.

The takeaway from all this, as you mentioned in your own post, is that you're kind of stuck here.  Really your only option is to:

accept the table scan (not ideal, and probably not even practical based on your description), or
use triggers to "manually" keep this value up to date in a normal column


Postgresql single base on multiple HDD partitions

I would go for:

   CREATE TABLESPACE any_name LOCATION '/any_empty_dir';
   ALTER TABLE any_tables SET TABLESPACE any_name;

you can do this for a couple of tables to move them to a different filesystem.
it should be pretty easy to do.

you can also put entire databases on to different filesystems.


INSERT INTO with SELECT subquery db2 9.5

I'll put this as an answer as it is easier to comment on.

Generally you need to run RUNSTATS on every table (I'd vote for at least once a week). This tells the optimizer the number of records (and if specified, which I do recommend) the distribution of data across the records. With this information the optimizer can make better decisions regarding access paths.

The optimizer also uses packages as well. REBINDING the packages essentially alters the packages (ie, their access paths) based on the RUNSTATS found.

In your case you could run RUNSTATS on the tables you care about

RUNSTATS ON TABLE <schema>.<table> WITH DISTRIBUTION AND DETAILED INDEXES ALL

If you have a lot of tables, then I'd recommend hunting for them dynamically

--do note syntax is for Unix, having to worry about escape characters and what not.
db2 -tnx  "select 'runstats on table ' ||
ltrim(rtrim(tabschema))||'.'||ltrim(rtrim(tabname))
|| ' with distribution and detailed indexes all ;'
from syscat.tables where type = 'T' and ownertype = 'U' and
tabschema not in ('SYSCAT','SYSIBM','SYSIBMADM','SYSPUBLIC','SYSSTAT','SYSTOOLS')
order by tabschema asc" >> runstats.sql

You can then read this in to your database and execute it

db2 -tvf runstats.sql

REBINDs I find the easiest to do using the db2rbind utility. It beats having to know all the specific package names. Since we are a small shop I just tend to use the following syntax each time.

db2rbind <my db> -l rebind.log all -r any

If there are errors, you will see it in the log.

So generally you would want to issue the RUNSTATS first, then issue a COMMIT to release any locks. Then you can run the REBINDs through db2rbind. If you have not run these in a while you may wish to throw a db2 flush package cache dynamic in between the RUNSTATS and the db2rbind.


Two tables can't have the same ENUMs. MySQL assumes all ENUM keys are globally unique..

CREATE TABLE foo ( a ENUM ('A', 'B', 'C'), x int );
CREATE TABLE bar ( a ENUM ('A', 'C', 'D'), y int );
INSERT INTO foo ( a, x ) VALUES ('A',1), ('B',2), ('C',3);
INSERT INTO bar ( a, y ) VALUES ('A',4), ('C',5), ('D',6);

SELECT * FROM foo JOIN bar USING(a);
+------+------+------+
| a    | x    | y    |
+------+------+------+
| A    |    1 |    4 |
| C    |    3 |    5 |
+------+------+------+

So in this context, the value of the ENUM is the string value of the key. PostgreSQL will return the same result, but because it has actual ENUM types the comparisons in the join are interger and it doesn't have to resolve the ENUM to a string,

CREATE TYPE myType AS ENUM ('A', 'B', 'C', 'D');
CREATE TABLE foo ( a myType, x int );
CREATE TABLE bar ( a myType, y int );
INSERT INTO foo ( a, x ) VALUES ('A',1), ('B',2), ('C',3);
INSERT INTO bar ( a, y ) VALUES ('A',4), ('C',5), ('D',6);

SELECT * FROM foo JOIN bar USING (a);
 a | x | y
---+---+---
 A | 1 | 4
 C | 3 | 5

If foo and bar were different ENUM types as MySQL requires in it's enum "type", you'd have the same problem but PostgreSQL wouldn't allow the join at all, because they're different types. PostgreSQL also won't allow one enum type to reference a different discrete enum type type without manually providing a method to go from one enum to another.


Restore in Full Recovery model with no transaction log backups

Yes, you should be able to restore and you'd be at the point in time of the backup.

Restoring from a backup will literally create a physically identical structure as existed at the point of the backup - data and log files will be the same size, so yes, you will still have a 300 GB log file if that was its size at the time of the backup (even if it was only 1% full).

Now, you need to do one of two things once you're back up and running:

Turn on transaction log backups to better protect your data in the event of another failure in the future.
Switch to simple recovery mode. Normally (there are always exceptions), this will prevent your transaction log from growing as much as it has. Your ability to restore remains the same as in full recovery and not bothering to take log backups.

For a lot more information about what you should do next (including what to do about your 300 GB log file), please take the time to read this question and its answers - yes, it's a lot of information, but it's going to be invaluable, I promise:

Why Does the Transaction Log Keep Growing or Run Out of Space?

Your gut reaction may be to try to shrink the log file to as small as possible, but that's not necessarily a good idea.


Transformation of row data based on a grouping of rows preceding it

If the operation is an override_op_1 then the logic follows that of the override_op_2 unless an override_op_2 had previously
  been encountered (for the id grouping), in this case it should be
  disregarded (or more explicitly the transformed_value is 0)

In effect, to disregard such rows, change them:

value = 0 - no change in running sum.
operation = 'zero_op' - any non-overriding type (don't start a new partition)

Do that to qualifying rows before applying everything else I already worked out in my previous answer:

SELECT id, creation
     , CASE WHEN operation = 'override_op_1'
            AND  had_op_2 THEN 'zero_op' ELSE operation END AS operation
     , CASE WHEN operation = 'override_op_1'
            AND  had_op_2 THEN 0 ELSE value END AS value
FROM  (
   SELECT id, creation, operation, value
        , bool_or(operation = 'override_op_2')  -- `override_op_2` previously encountered
          OVER (PARTITION BY id ORDER BY creation) AS had_op_2
   FROM   tbl
) t

So the complete query becomes:

SELECT *
     , running_sum - lag(running_sum, 1, numeric '0')  -- data type must match!
                     OVER (PARTITION BY id ORDER BY creation) AS transformed_value
FROM (
   SELECT id, creation, operation, value
        , sum(value) OVER (PARTITION BY id, run ORDER BY creation) AS running_sum
   FROM   (
      SELECT *, count(*) FILTER (WHERE operation LIKE 'override_op_%')
                OVER (PARTITION BY id ORDER BY creation) AS run
      FROM  (
         SELECT id, creation
              , CASE WHEN operation = 'override_op_1'
                     AND  had_op_2 THEN 'zero_op' ELSE operation END AS operation
              , CASE WHEN operation = 'override_op_1'
                     AND  had_op_2 THEN 0 ELSE value END AS value
         FROM  (
            SELECT id, creation, operation, value
                 , bool_or(operation = 'override_op_2')
                   OVER (PARTITION BY id ORDER BY creation) AS had_op_2
            FROM   tbl
            ) t
         ) t
      ) t
   ) t
ORDER  BY id, creation DESC;

Alternatively, you could just delete such rows or move them out of the way (SET id = id * -1). Not sure if you need to preserve these "zero_op" rows in the result.


How to name tables which are responsible for storing dictionaries and configuration?

I would have 3 tables "Service", "ServiceInstance", and "Node".  1-1 relationships are generally best collapsed into a single table, and there's no reason to decorate your lookup tables with a a prefix.


Triggers or views on frequently updating table

... not more than 50k records ... database server consumes almost 100%

Heavy CPU load suggests (to me) that there's lots of stuff happening in memory, which suggests poorly performing queries doing a lot of table scanning.

Look in the Slow Query log to see which queries are taking along time to run and look to tune them.


For Postgres, how would I define a custom function to generate primary keys?

Turning the comments into an Answer so we can close this Question.

12-chars not random enough

A 12-character random string may be a poor choice for a surrogate key. Your generated values might have a significant chance of colliding, especially depending on your particular implementation for generating such values.

No need to invent such an identifier, as the industry has defined one already: UUID.

UUID

A better route would be using a standard Universally Unique Identifier (UUID). A UUID is a 128-bit value. Though usually displayed as a string of thirty-two hexadecimal characters plus four hyphens (36 chars total), Postgres knows how to compactly store a UUID natively as 128-bit. Postgres also supports indexing a UUID column, so the UUID type serves well as a primary key.

The original version of UUIDs are unique in space and time by combining the generating computer’s MAC address, the current moment, plus a small random number. You may choose alternative versions if security or privacy concerns require that you not encode the place (MAC address) or the time (current moment).

One advantage of UUIDs is that they may be generated either on the server-side and/or the client-side (within your app).

An advantage of using standard UUIDs instead of roll-your-own values is that all mainstream operating systems and development platforms already have high-quality implementations for generating quality UUIDs. No need to implement and document your roll-your-own algorithm.

Postgres has built-in support for storing UUID values. To generate UUID values, you need a plug-in, an “extension”.  The uuid-ossp extension is usually bundled with a distribution of Postgres, but you may need to activate it.

For more info, see the Question, Generating a UUID in Postgres for Insert statement?.


Date comparsion within subquery fails with: "out-of-range value"

The execution order is in much regards up to the query optimizer. So in your case, even thou the SQL code you wrote looks like the subquery would filter out the invalid dates first, what the actual execution of the query looks like, can be very different.

You can run your query with option(force order), but be careful with that one, very careful, and know what you are actually doing. Another option would be a temp table, to preselect the valid or the invalid dates, but that could be very costly as well.

While it feels dirty, and it is, you could also compare string values, to not run into this issue, just make sure to use a codepage that will sort as a string as it would as a date

select a.[id]
FROM (
    select [id], convert(varchar(23), convert(datetime, [START_DATE],106),121) as csd
    FROM [dbo].[input]
    where len([START_DATE]) > 0
    and substring([START_DATE],7,5) > 2010
    and isdate([START_DATE]) = 1
    ) a
    Where a.csd < convert(varchar(23, getdate(), 121)

all in all, it would be best to save the start_date as an actual date type thou.


Patching SQL Server 2008 and upgrading to 2014

The SQL Server 2014 Upgrade Techincal Guide states that  you need the following as the minimal version for an in-place upgrade

SQL Server 2005: SP4 is required.
SQL Server 2008: SP2 is required.
SQL Server 2008 R2: SP1 is required.
SQL Server 2012: SP1 is required

So if you are not on the stated service pack level you will need to upgrade to at least that.

It is not neccessary, but start by running the upgrade advisor.
Thomas LaRock has a checklist on his page both for the tasks needed before an inplace upgrade and what to do afterwards.


Does any MariaDB engine have the option to declaratively or configuratively abstract highly-redundant data?

In any RDBMS, "normalization" is an explicit task that the programmer does.

Having stuff buried inside JSON strings makes that task more difficult.


mysql emits BINLOG rows even though binlog_format=STATEMENT

Whenever mysql writes to binary logs, it has to encode all the operations.

There exists a base64 constant at the head of every binary log : 0xfe 0x62 0x69 0x6e. This is referred to the binlog magic number.

This value is registered in log_event.h in the mysql source code.

Other events are also encoded in log_event.h:

enum Log_event_type
{
  UNKNOWN_EVENT= 0, START_EVENT_V3= 1, QUERY_EVENT= 2, STOP_EVENT= 3,
  ROTATE_EVENT= 4, INTVAR_EVENT= 5, LOAD_EVENT= 6, SLAVE_EVENT= 7,
  CREATE_FILE_EVENT= 8, APPEND_BLOCK_EVENT= 9, EXEC_LOAD_EVENT= 10,
  DELETE_FILE_EVENT= 11, NEW_LOAD_EVENT= 12, RAND_EVENT= 13,
  USER_VAR_EVENT= 14, FORMAT_DESCRIPTION_EVENT= 15, XID_EVENT= 16,
  BEGIN_LOAD_QUERY_EVENT= 17, EXECUTE_LOAD_QUERY_EVENT= 18,
  TABLE_MAP_EVENT = 19, PRE_GA_WRITE_ROWS_EVENT = 20,
  PRE_GA_UPDATE_ROWS_EVENT = 21, PRE_GA_DELETE_ROWS_EVENT = 22,
  WRITE_ROWS_EVENT = 23, UPDATE_ROWS_EVENT = 24,
  DELETE_ROWS_EVENT = 25, INCIDENT_EVENT= 26,
  HEARTBEAT_LOG_EVENT= 27,
  ENUM_END_EVENT /* end marker */
};

Whenever mysqlbinlog dumps the contents of a binary logs, it has to see these event numbers, especially the binlog magic number. If this value is missing, that quickly reveals one of the following:

the file is not a binary log
the binary log is horrifically corrupt

If mysqlbinlog dumps the expression BINLOG, the file it is reading can be trusted to be a binary log because it encoutered the binlog magic number when parsing the file.

For more information on this: please see MySQL Internals on Binary Logs


Is it Possible to Overload SQL Server Functions?

There is no straight-forward way of carte-blanche overriding a built-in function in SQL Server.

You can kind of fake it by creating a function with the same name in a different schema, then calling that function with the schema name, as in:

SELECT dbo.COUNT(1)
FROM dbo.SomeTable st
GROUP BY st.SomeCol;

However, this is quite likely to cause more confusion than it is worth.


I wonder what "test" is. There is no database named test there.

This is just the default database that the mongo shell uses when it connects, unless you insert something, it is empty.  You can do this with any database in fact, test is just the default.

The first command will not work because you did not prefix it with db, as such it attempts to find "isikota" as a variable (javascript) and fails to find it.  Similarly your next attempt does the same with a non-existent "test" variable.

Finally, this command is the right form (prefixed with db):

db.isikota.tablebusiness.ensureIndex({"LatitudeLongitude" : "2d"})

But, you did not change database (with use <database name>) or at least you did not mention trying to at least.  What this is going to do is create a 2d (geoindex) index on a "LatitudeLongitude" field on a collection named "isikota.tablebusiness" in the test database (db always refers to the database you are currently using).  Even if this collection does not exist, the index will be created (this is just an insert into the system.indexes namespace) and hence the command succeeds.  You just won't see anything else, because I believe you are still operating on the test database.

Assuming your data is actually in a database called "isikota" and that "tablebusiness" is your collection, what you actually want to do is this:

use isikota;
db.tablebusiness.ensureIndex({"LatitudeLongitude" : "2d"});

That will create the index on the "LatitudeLongitude" field in a collection named "tablebusiness" in a database named "isikota".


Inserts in MySQL 8 are slower than Inserts in MySQL 5.7

A difference between 5.7 and 8.0 is that binary logging (to be used for replication and PITR) is on by default in MySQL 8.0. To run without binary logging in 8.0, start the MySQL server with --disable-log-bin.

More details The Binary Log

  The binary log contains “events” that describe database changes such
  as table creation operations or changes to table data. It also
  contains events for statements that potentially could have made
  changes (for example, a DELETE which matched no rows), unless
  row-based logging is used. The binary log also contains information
  about how long each statement took that updated data. The binary log
  has two important purposes:

  For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The
  master server sends the events contained in its binary log to its
  slaves, which execute those events to make the same data changes that
  were made on the master. See Section 17.2, “Replication
  Implementation”.
  Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that
  were recorded after the backup was made are re-executed. These events
  bring databases up to date from the point of the backup. See Section
  7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.


SQL Server - SQL Agent Alerts - Alert 824 warning about old problems

I'm assuming that this is an agent alert, if so you could try to right click on the alert -> properties -> History and check the 'Reset count' box and it should clear out.


Possible to update rows with data only accessible via API call?

"Columns" look different (dates vs strings vs numbers); the values within a given column all look the same (eg, all are dates)
"Rows" look similar, but with different values.
A "table" is a collection of any number of rows, but of a fixed number of columns.
An API call must go to some application language code (Java, PHP, or ...) which interprets the yucky REST syntax and proceeds to, for example, issue a SELECT (for a GET REST call) or INSERT/UPDATE (for a PUT), etc.  You must write this app code; MySQL does not understand REST.
I suspect REST is so restrictive that a PUT is not allowed to return what a GET would return.  So, perhaps you have to do 2 API calls.  This also happens with talking to MySQL -- an INSERT or UPDATE merely says 'ok' or 'err'; you then need to do a SELECT to fetch stuff.

I hope this helps.  To continue this Q&A, please provide

More details about your application.
Whether you have settled on an app language to code REST in.
Some hints of what column types your table(s) has.

(Otherwise, it will probably be closed as "too broad".)


Does CAST(DateTime AS DATE) cause the entire table to be CAST before comparison?

Whipped up a simple example on this using a handy calendar table.

DECLARE @StartDate DATETIME = '2014-04-16 09:35:36.510'
DECLARE @EndDate DATETIME = '2014-05-16 09:35:36.510'

select
    *
from Calendar
where CAST(BaseDate AS date) > CAST(@StartDate AS date)
    AND CAST(BaseDate AS date) > CAST(@StartDate AS date)

When we look at the query plan for this we can see it's doing a simple index seek and all is well.


How to change an incorrect datatype for a birth date field from VARCHAR to DATE

ALTER TABLE users ADD COLUMN DOB DATE;

UPDATE users
    SET DOB = STR_TO_DATE(birthdate, '%Y-%m-%d')
    WHERE birthdate REGEXP '[0-9]{4}-[0-9]{2}-[0-9]{2}';

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=96c9978ccc667b9289cb3b7b223f28d7


Performance issues moving 1 million rows between tables

Here are two really efficient solutions to this, since you're only moving data. These are efficient because they don't actually move data at all: they simply manipulate the metadata to present the data in the desired location. This means not only will they be fast, but the amount of logging required will be minimal.

If you're using Enterprise edition, take advantage of table partitioning. If there's only a single partition in a table, that partition is the table. Use ALTER TABLE ... SWITCH to "move" the data into table2.
Rename table1 to table2 using sp_rename, then recreate table1 using a SELECT TOP 0 * INTO statement, or by using the original table definition. Note that if there are any constraints/indexes/etc., you'll need to rename those, too, to avoid name clashes when the new table is created again. This method works with any edition, so I might actually recommend it over the other solution, which is Enterprise only.

Note that there are some restrictions to doing either of these -- see the documentation I've linked to. I'm not sure if you tagged the question with replication because the table is replicated (doesn't make much sense), or because you're trying to "replicate" the data (also doesn't make sense as you're wanting to move data, not make a copy, which is what replication implies).


Frequent, "random" SQL Server connection timeouts

Basically, when you failed to connect to your SQL Server, the issue
  could be:

  Network issue.
  SQL Server configuration issue.
  Firewall issue.
  Client driver issue.
  Application configuration issue.
  Authentication and logon issue.

I thik you can follow THIS article here for more information.


LoadBalancing JDBC DataSource Connect without URL

Thin client can also use alias defined in tnsnames.ora. In java call set -Doracle.net.tns_admin=...
 http://docs.oracle.com/cd/B19306_01/java.102/b14355/urls.htm


can we create number of fields at a time with same data type in mysql?

You can add several columns at once but you need to declare the types per column:

alter table t add column (s5 int, s6 int);

You can also use:

alter table t add (s5 int, s6 int);

but I prefer the first one. Note that the parentheses are mandantory.

You don't mention your version, but the syntax have been valid since at least 5.1:

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html


How to improve InnoDB DELETE performance?

You could delete data in batches.

In SQL Server, the syntax is delete top X rows from a table. You then do it in a loop, with a transaction for each batch (if you have more than one statement, of course), so to keep transactions short and maintain locks only for short periods.

In MySQL syntax: DELETE FROM userTable LIMIT 1000

There are restrictions on that (can't use LIMIT in deletes with joins, for instance) but in this case you might be able to do it that way.

There is an additional danger to using LIMIT with DELETE when it comes to replication; the rows deleted are sometimes not deleted in the same order on the slave as it was deleted on the master.


Deleting many rows on a live table in chunks - without locking for too long (PostgreSQL 9.3)

The table itself is only locked in ROW EXCLUSIVE mode, which shouldn't prevent any normal operations on the table, only things like DROP, ALTER, and CREATE INDEX.

Each individual row that is being deleted will be locked for the duration.  This should only block other processes if those other processes are trying to update the rows (or delete them themselves).  Normally with bulk deletions you are deleting rows that no one else cares about, which means no one else will be trying to update them.

If you really need to unlock all the deleted rows and the table periodically, then you need to do each loop of the deletions in a separate transaction.  There is no easy way to do this in a DO block because the entire block runs in a single transaction.  So you should put your loop outside of PostgreSQL, like in bash, perl, python, etc.


Selecting one row from each group based on a Varchar field

You can do something like this:

select * from (
  select *, row_number() over (partition by Avenger order by Type) as RN
) X
where RN = 1

This will number the rows for each avenger, and for your actual business case you'll most likely need to use a case statement in the order by if there's more than 2 different values, to define their precedence:

....order by case Type when 'bar' then 1 when 'foo' then 2 else 3 end


The reason for the performance difference lies in how scalar expressions are handled in the execution engine. In this case, the expression of interest is:

[Expr1000] = CONVERT(xml,DM_XE_SESSION_TARGETS.[target_data],0)

This expression label is defined by a Compute Scalar operator (node 11 in the serial plan, node 13 in the parallel plan). Compute Scalar operators are different from other operators (SQL Server 2005 onward) in that the expression(s) they define are not necessarily evaluated at the position they appear in the visible execution plan; evaluation can be deferred until the result of the computation is required by a later operator.

In the present query, the target_data string is typically large, making the conversion from string to XML expensive. In slow plans, the string to XML conversion is performed every time a later operator that requires the result of Expr1000 is rebound.

Rebinding occurs on the inner side of a nested loops join when a correlated parameter (outer reference) changes. Expr1000 is an outer reference for most of the nested loops joins in this execution plan. The expression is referenced multiple times by several XML Readers, both Stream Aggregates, and by a start-up Filter. Depending on the size of the XML, the number of times the string is converted to XML can easily number in the millions.

The call stacks below show examples of the target_data string being converted to XML (ConvertStringToXMLForES - where ES is the Expression Service):

Start-up Filter

XML Reader (TVF Stream internally)

Stream Aggregate

Converting the string to XML each time any of these operators rebind explains the performance difference observed with the nested loops plans. This is regardless of whether parallelism is used or not. It just so happens that the optimizer chooses a hash join when the MAXDOP 1 hint is specified. If MAXDOP 1, LOOP JOIN is specified, performance is poor just as with the default parallel plan (where the optimizer chooses nested loops).

How much performance increases with a hash join depends on whether Expr1000 appears on the build or probe side of the operator. The following query locates the expression on the probe side:

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_sessions s
    INNER HASH JOIN sys.dm_xe_session_targets st ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

I have reversed the written order of the joins from the version shown in the question, because join hints (INNER HASH JOIN above) also force the order for the whole query, just as if FORCE ORDER had been specified. The reversal is necessary to ensure Expr1000 appears on the probe side. The interesting part of the execution plan is:

With the expression defined on the probe side, the value is cached:

Evaluation of Expr1000 is still deferred until the first operator needs the value (the start-up filter in the stack trace above) but the computed value is cached (CValHashCachedSwitch) and reused for later calls by the XML Readers and Stream Aggregates. The stack trace below shows an example of the cached value being reused by an XML Reader.

When the join order is forced such that the definition of Expr1000 occurs on the build side of the hash join, the situation is different:

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st
    INNER HASH JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

A hash join reads its build input completely to construct a hash table before it starts probing for matches. As a result, we have to store all the values, not just the one per thread being worked on from the probe side of the plan. The hash join therefore uses a tempdb work table to store the XML data, and every access to the result of Expr1000 by later operators requires an expensive trip to tempdb:

The following shows more details of the slow access path:

If a merge join is forced the input rows are sorted (a blocking operation, just like the build input to a hash join) resulting in a similar arrangement where slow access via a tempdb sort-optimized worktable is required because of the size of the data.

Plans that manipulate large data items can be problematic for all sorts of reasons that are not apparent from the execution plan. Using a hash join (with the expression on the correct input) is not a good solution. It relies on undocumented internal behaviour with no guarantees it will work the same way next week, or on a slightly different query.

The message is that XML manipulation can be tricky things to optimize today. Writing the XML to a variable or temporary table before shredding is a much more solid workaround than anything shown above. One way to do this is:

DECLARE @data xml =
        CONVERT
        (
            xml,
            (
            SELECT TOP (1)
                dxst.target_data
            FROM sys.dm_xe_sessions AS dxs
            JOIN sys.dm_xe_session_targets AS dxst ON
                dxst.event_session_address = dxs.[address]
            WHERE
                dxs.name = N'system_health'
                AND dxst.target_name = N'ring_buffer'
            )
        )

SELECT XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')
FROM @data.nodes ('./RingBufferTarget/event[@name eq "xml_deadlock_report"]') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

Finally, I just want to add Martin's very nice graphic from the comments below:


Predict how much hard drive space a query might cost?

It is hard to make a prediction, because that depends on many factors like the chosen execution plan.

You should first find out where the files are that keep growing.

If they are in the base/12345 subdirectory of the PostgreSQL data directory (the number will be different), it is the materialized view that is being built. That would mean that your disk space is not sufficient to contain the resulting object.
If the growing files are in base/pgsql_tmp, they are temporary files that the query executor creates to cache a large intermediate result.

That might be an indication that you forgot a join condition somewhere - hard to tell, as we only see part of the query.

Since CTEs are materialized before v12, it could also be the intermediate result of a WITH expression. You can try them out one after the other and see if they make things go boom.

Sorry, but it is not possible to give you more than these guidelines.


Searching from the latest date in SQL Server

For starters, don't use @DealClauseString - just have your query.

Also, remove ORDER BY D ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING - when you're using the whole partition, there's no reason to have the ORDER BY clause at all.

Anyway - for your particular question, if I understand it correctly, you want it to be like your #t table only has records that are at least a month old. So you should put a WHERE clause in the filter CTE. So it becomes something like:

FROM #t
WHERE D <= DATEADD(MONTH,-1,SYSDATETIME())


Oracle auditing: Is it possible to log when a permission is being used?

Yes, that is entirely possible. The use of any privilege or grant can be audited by instance, by session, by success, or by failure. You can audit all uses, or just uses by specific users. See here for a basic guide to getting started: https://oracle-base.com/articles/8i/auditing, and here: https://oracle-base.com/articles/12c/auditing-enhancements-12cr1
For example:

CREATE AUDIT POLICY test_audit_policy
  PRIVILEGES CREATE TABLE, CREATE SEQUENCE
  WHEN    'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''TEST'''
  EVALUATE PER SESSION
  CONTAINER = CURRENT;

AUDIT POLICY test_audit_policy;

See here for a blog I wrote a few weeks ago about basic audits to use in 12c and up, called "Auditing By The Numbers"


Creating my first Elastic Database Job on Azure

The error on the portal is not clear, but you should be able to find details in deployment summary.

On portal go to your resource group and navigate to Deployment tab. There you can find history of the deployments with details about progress and errors.

The problem with Powershell looks like you don't set subscription context.

You should set the context to subscription which contains Reasource Group to which you want to deploy content.

To make this work use this example:
Example 1: Set the current subscription

After apply current subscription you should be able to see what is the real error during deployment.


Recovering from the loss of datafile in NOARCHIVELOG mode?

You cannot recover a data file or a database in NOARCHIVELOG mode, since there's no redo entries that you could apply to your previous backups to roll the data files forward up to the point of failure.

If the database is in ARCHIVELOG mode, the ARCn process constantly copies online redo log files to a persistent area when they are full, so that you can later apply the redo entries to the previous backups. Copied online redo log files are called archive log files.

In NOARCHIVELOG mode, LGWR writes redo entries to redo log files while DBWR writes data blocks to data files, however redo log files full of redo entries are not copied, but are overwritten in a round-robin fashion, and thus no redo data is available to apply to the previous backups.

Therefore, if your database is in NOARCHIVELOG mode, you cannot recover your database up to the point of failure, you can only restore it to a previous state which is the time of one of your backups.

For more info, read the section "Choosing Between NOARCHIVELOG and ARCHIVELOG Mode" in Database Administrator's Guide.


Select Into removes IDENTITY property from target table

The behaviors you're curious about are explained in the documentation:

  When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:
  ...- The identity column is part of an expression.
  ...If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property.

And in this thread from UC:

  Metadata is determined based on the source column and expressions used
  in the SELECT list. Below are the rules:

  Any expression that uses a built-in function like SUBSTRING, LEFT, RIGHT etc (except ISNULL) for example is considered as NULLable by the
  engine. So if you use CAST(somecol as char(8)) then the expression is NULLable.
  ...
  So to make an expression or column in the SELECT list not null then use ISNULL around the column or expression.


MS Access Data Type for time in hh:mm:ss format

You can store the data in Excel as the amount of time from the default date.  If you just enter the time in the format you wish, it will automatically add the default date.  Enter "4:30:00" as in [FinishTime] and it will save the data like in [Unformated Date], but will be visible format you wish and retain the proper time value.

When you create the table in Access you can just change the format to change how it displays itself.  Note, in access Minutes are stored as "nn" instead of "mm", distinguishing it from Months.

It will look the way you want.  Note, although the unformatted default date will appear different in Access than it will in Excel, the time will remain relative to the default start point.

From there, simply import your Excel into the Access table normally or enter the time in the same "hh:mm:ss" directly into the Access table.

When you want to see the data summed up, you can do so in a query, making sure you convert it to a date and format it to display how you wish.

In this case:

Format(CDate(Sum([FinishTime])),"hh:nn:ss")

Resulting in the total time that you wish

If you anticipate the total time going over 24 hours, there are more things to consider, but it doesn't sound like this project will.


How to access another Connect server table in my Local server in SQLyog 7.01?

If what you are asking is how to access a remote MySQL table from another MySQL server, that can be achieved with the FEDERATED engine (disabled by default in mysql 5.5) os with the CONNECT engine (only available on MariaDB 10).

Here you have the documentation to create such tables in both cases:

Creating a FEDERATED table using CONNECTION. You need to enable the FEDERATED engine beforehand
Connect table types - MySQL/MariaDB table

I hope you are not thinking about using it for nothing too serious, as the performance is usually not too good.


Applying (log backup) LSNs to the secondary node of an Availability Group

You have 2 options :

Easy one - since this is test instance and its secondary.

Stop the log backup on primary.
Remove the db/s from AG.
Take a differential backup or full backup if dbs are small.
restore then on secondary with norecovery.
take an additional log backup on primary.
restore the additional log backup taken on secondary with norecovery.
join the dbs to AG.

A little bit harder one -

install sp_restoregene on primary.
Remove the db/s from AG.
Run it for the db/s in question and it will generate the full and log backup sequence with stopat.
Restore the sequence on secondary.
Join back to AG.

(I am telling above is a little bit harder since if you have not done a point-in-time recovery and you are doing it for the first time, test it first to be comfortable).


Database space doesn't match ibdata1 size

This sounds a lot like another post I wrote back on August 27, 2012 : How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed

With innodb_file_per_table disabled, the system tablespace ibdata1 is the home of seven classes of information:

Data Pages for InnoDB Tables
Index Pages for InnoDB Tables
Data Dictionary
Double Write Buffer

Safety Net to Prevent Data Corruption
Helps Bypass OS for Caching

Insert Buffer (Streamlines Changes to Secondary Indexes)
Rollback Segments
Undo Logs
Click Here to see a Pictorial Representation of ibdata1

With innodb_file_per_table disabled, running OPTIMIZE TABLE against any InnoDB table is really asking for trouble. Why? Because all OPTIMIZE TABLE does is write all data pages and index pages for a given table contiguously in ibdata1. That makes ibdata1 grow. In light of this, shrinkage of ibdata1 is totally impossible.

Please note that even if innodb_file_per_table were enabled and you ran OPTIMIZE TABLE, that will extract the table into an external file. Yet, the space left behind is unrecoverable.

I wrote a nice one-time Cleanup Procedure of ibdata1 in StackOverflow back on Oct 29, 2010.

Here are my other posts on this subject of InnoDB and its effects on ibdata1

Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB
Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

SUMMARY

Even if you implement the InnoDB Cleanup (which separates all data and index pages from ibdata1), ibdata1 can still grow in a heavy-write, heavy transaction environment due to the 5 other classes of information (Data Dictionary, Double Write Buffer, Insert Buffer, Rollback Segments, Undo Logs).

If you want to know how much space is used by data and index pages in ibdata1, run this:

SELECT InnoDB_Bytes,InnoDB_Bytes/POWER(1024,3) InnoDB_GB
FROM (SELECT SUM(data_length+index_length) InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Now as for ibdata1, it still the Data Dictionary, Double Write Buffer, Insert Buffer, Rollback Segments, Undo Logs, and unused pages due to fragmentation. There is no really way to know:

the exact amount of fragmentation
how much space is used by the following (since they change rapidly):

Data Dictionary
Double Write Buffer
Insert Buffer
Rollback Segments
Undo Logs

For the sake of simplicity, I will say this: Just subtract InnoDB_Bytes from the filesize of ibdata1.

After doing the InnoDB Cleanup, you should schedule running OPTIMIZE TABLE on every InnoDB table that is transaction-heavy. That will actually shrink the .ibd file for every InnoDB tables. The ibdata1 file will grow much slower thereafter. Yet, you will still have to live with some transactional growth.

If you implement InnoDB Cleanup but leave innodb_file_per_table disabled, that will shrink shrink ibdata1, but it will just climb back to 99G and beyond through normal application usage.

If you do not implement InnoDB Cleanup, no OPTIMIZE TABLE will ever shrink ibdata1.

RECOMMENDATION

Please implement InnoDB Cleanup with innodb_file_per_table enabled. Going forward, you should create a cronjob to run OPTIMIZE TABLE on all tables what experience mass INSERTs, mass UPDATEs, and mass DELETEs.


Just an update to Dennis' comment from the AWS thread, this is now possible.
Quote from the thread:

  We just launched RDS PostgreSQL 10.5 which supports libprotobuf-c.
  https://aws.amazon.com/about-aws/whats-new/2018/10/rds-postgresql-supports-minor-versions/

The official release notes (with emphasis added):

  [...]
  With this update, we have also added support for extensions pglogical version 2.2.0 and pg_similarity version 1.0 in PostgreSQL 10.5 and 9.6.10; pageinspect version 1.6 extension is supported with PostgreSQL 10.5; Map Box Vector Tiles in PostGIS with libprotobuf-c is supported with PostgreSQL 10.5; wal2json has been updated to 01c5c1ec in PostgreSQL 10.5 and 9.6.10; and pg_hint_plan has been updated to 1.3.1 and 1.2.3.
  [...]

Reference: Amazon RDS for PostgreSQL Supports New Minor Versions 10.5, 9.6.10, 9.5.14, 9.4.19, and 9.3.24


Replacing an aggregation system of statistics

Disclosure: I am the product manager of Google Cloud Bigtable.

Is it fair to assume that these statistics relate somehow to a globally-unique identifier (user id, device id, something else)?

If so, you might be able to do everything you need in Bigtable, depending on the specific set of queries you need to issue. For example WePay does something quite similar, and they presented their solution and schema design in Bigtable for flexible aggregation of metrics data.

Since Bigtable is a wide-column NoSQL database, you can create a number of columns for any given key and store raw data in one column (with time series for events), partial aggregations for minutes, days, weeks, months, etc. each in separate columns, and then when you need a particular aggregation over an arbitrary time interval, read the partial aggregations as needed.

You can also do this in two different modes, depending on the trade-offs between cost and time:

if only some of your aggregations are accessed, compute them lazily on first access, and write them to Bigtable for storage and querying
if many of the aggregations are needed, and latency is important even for first access, compute eagerly what you can with Dataflow live, and post-compute what cannot be computed live with a background process

As an additional data point, Fastly recently posted how they moved their historical statistics from MySQL to Bigtable, but I don't have any further information on their schema design; hopefully, they will post a follow-up to the announcement with more technical details of their implementations.

If you'd like to discuss your specific use case in more detail privately, let's chat further.


How to select from table by comparing multiple rows with same key

Assuming only two possible values for status , eliminate all keys that has more than one row and filter for failed status

SELECT *
FROM key_status
WHERE status='FAILED'
  AND key <>
    (SELECT key
     FROM key_status
     --WHERE status IN('COMPLETED','FAILED')
     GROUP BY key
     HAVING COUNT(key) >1)


How to setup a script that automatically clones the DB every 2 hours?

You can't insert into one MySQL instance and expect the data to show up on another instance -- at least not without somehow inserting on the second instance.  But this defeats your original goal -- a performance problem caused by battling inserts and selects.

A Master-Slave setup does what I just described -- You insert on the Master; that insert is replicated to the Slave, where it is again executed.  Then you read from the Slave.  So, problem not solved.

First of all, you must use InnoDB, not MyISAM.

Are your "clicks" counters?  Or are they rows containing more information than just "1 more click for X"?  Assuming just a counter, such as INT UNSIGNED, ...

Second, keep the click counters in a table by themselves.  This table would have a PRIMARY KEY that is the same as the main table (with the possible exception of AUTO_INCREMENT.

But if that is not enough, then consider ways of collecting the clicks and applying them "after a while".  One ways would be a variant on http://mysql.rjweb.org/doc.php/staging_table .  This link would also provide a good way to gather whole rows instead of just a counter.

How many clicks per second do you get?  "Hundreds of thousands per day is only a few per second.  Do you really have a problem?


Is it possible to restore a database from corrupted .mdf file?

If it were me?

  Nuke it from orbit and restore from backup.

Stop any processes that rely on this database and restore from your most recent backups.

After you're stable, you can go through the academic exercise of trying to repair as much of the corrupt media as you've got the energy & budget to put up with. Assuming you have a strong understanding of the upstream & downstream dependencies of this media, you may be able to logically repair restore some of the data you lost in the time between the end of your last stable backup and the outage.


Create a sub query for sum data as a new column in SQL Server

What you're looking for is called a window function. It's similar to grouping, but it allows you to perform aggregate calculations on sets of records without actually grouping. Searching for "window function" or "over clause" should get you lots of helpful reading material.

select
  [ID]
  ,Amount
  ,[Total Amount] = sum(Amount) over(
    partition by left([ID],charindex('-',ID + '-') - 1)
    )
  from tblTemp


SQL Server - What taks/queries should I need to execute when there is a lack of space in TempDB?

Check your index rebuild plan, db.SORT usually indicates a SORT_IN_TEMPDB option being used and not enough space to accommodate it.


In your DB Parameter Group change innodb_flush_log_at_trx_commit from 1 to 0. This should speed up the writes since the default value writes completed transactions to the Redo Log Files (ib_logfile0, ib_logilfe1).

If you have binary logs enabled (when you enable Automated Backups), you can also change sync_binlog from 1 to 0.

You will not need to reboot since these two options are dynamic.

When your restore is complete, set them back their original values (innodb_flush_log_at_trx_commit back to 1, sync_binlog back to 1).

Give it a Try !!!

UPDATE 2018-02-02 14:27 EST

The only other thing I can think of would to increase the threads the CPUs use

If you look back at my 6.5 year old post Possible to make MySQL use more than one core?, I mention the following options:

innodb_read_io_threads (Reboot Required)

Default for MySQL 5.6 is 4
Percona Server default is 8
Please set this is 8

innodb_write_io_threads (Reboot Required)

Default for MySQL 5.6 is 4
Percona Server default is 8
Please set this to 8 or 16

innodb_thread_concurrency (Dynamic Option, Reboot Not Required)

Default for MySQL 5.6 is 0
Make sure it is 0
Percona recommended using 64 if you are using MySQL 5.7.


Example of loading into RDS from S3 using SQL*Loader

I thought of two ideas how you could (at least theoretically) access the data if you are on Linux.

mount S3 bucket using some kind of S3 FUSE. E.g. first hit on Google https://code.google.com/p/s3fs/wiki/FuseOverAmazon
create named pipe: mkfifo oraclepipe then configure SQL*Loader to read from the pipe probably using conventional path. And feed S3 file to the pipe: wget -O oraclepipe http://s3.com/hash.

We were using the second method on 9i to compress export online. And I do not recall problems with this approach. We ran two sessions from the shell script:

    gzip < oraclepipe > database.exp.gz &
    exp "username/passwd" FULL=y BUFFER=2048000 FILE=oraclepipe


Alias for multiple instances on a SQL Server

It's a little tricky with multiple IP addresses and different ports when configuring SQL Server. The most important bit, is that the whole configuration has to be done with the SQL Server Configuration Manager.

Open up the SQL Server Configuration Manager with Runas Administrator...
Navigate through the SQL Server Network Configuration branch and you should find two sub-branches for ...
a. Protocols for NAMED_INSTANCE_1
b. Protocols for NAMED_INSTANCE_2

Repeat the following steps for each instance:

Click on the Protocols for NAMED_INSTANCE_n branch and in the right pane right-click on the TCP/IP Protocol Name entry.
Open up the properties.
In the Protocol register verify the following:
a. Enabled = Yes
b. Listen All = No
Switch to the IP Addresses register
You should find sections in the form of IPn for each of your IP addresses 10.10.1.1 and 10.10.1.2. This is the tricky bit: You are already in an instance and want to configure only one IP address for this instance.
In the IPn section corresponding to the IP address for this instance check the following:
a. Active = Yes
b. Enabled = Yes
c. IP Address = 10.1.0.n
d. TCP Dynamic Ports = leave empty
e. TCP Port = 1433
Scroll down to the IPAll section
Verify the following:
a. TCP Dynamic Ports = leave empty
b. TCP Port = leave empty
In all other IPn sections ensure the following:
a. Enabled = Yes
b. Active = No (unless you want to communicate with the instance via the IP configuration settings in the section).

Repeat from step 3. for each instance.

You have to restart the instance and then it will only pick up on the IP address and port configured.

We have SQL Servers with up to 10 instances configured this way, but we leave the port at 1433 so that each instance will pick up on the default port. This allows for easier firewall configuration.

Good luck.


Create user with the replication access only

https://dev.mysql.com/doc/refman/5.5/en/show-slave-status.html

You have to grant one of two permissions - SUPER or REPLICATION_CLIENT.
REPLICATION_CLIENT would be your minimum, so probably what you're after?


How do I interpret the log when I run DBCC TRACEON (3502, 3504, 3605, -1)

The trace flags that you have turned on will tell you what a checkpoint is doing behind the scenes.

3502: writes to the error log when a checkpoint starts and finishes
3504: writes to the error log information about what is written to disk
3605: allows trace prints to go to the error log

Refer to Paul Randall's blog post for more details on the above. Also, Fine Tuning for Optimal Performance has an excellent info - especially In Search of Spikes section.

Some really internals reading :

How It Works: When is the FlushCache message added to SQL Server Error Log?
How It Works: SQL Server Checkpoint (FlushCache) Outstanding I/O Target
SQL Server checkpoint problems

Instead of concentrating directly on checkpoint behaviour, I would suggest you to look at DMVs and Perfmon (disk related) -

sys.dm_io_virtual_file_stats
Physical Disk Object: Avg. Disk Queue Length
Avg. Disk Sec/Read
Avg. Disk Sec/Write
Physical Disk: %Disk Time
Avg. Disk Reads/Sec
Avg. Disk Writes/Sec

You can refer to Investigating I/O bottlenecks


"Global" settings are initial values for when you login.  You failed to log out and log back in, hence it still said STATEMENT.

/etc/ is a place for configuration-like stuff, not for basedir.

/usr does not seem right for basedir, either.  Perhaps /usr/mysql or /usr/mariadb?


HA and Automatic Failover with PostgreSQL

pgPool is a client tool that can reroute client connections to the surviving server aftet the primary has died. However, without further activity, that surviving server will be a streaming replication standby, that is a read-only server.

You also need some software that can reliably detect that the primary server is down and promote a standby server. Such a clusterware is an integral component of a high-availability setup.

Probably Repmgr can serve that purpose; typically, Patroni is used.


What does double number sign ## mean in SQL?

## surrounds a query parameter in the Stack Exchange Data Explorer (SEDE).
The portion after the ? is the displayed default parameter value.
From the SEDE Tutorial written by Monica Cellio:

Note: the particular syntax used for parameters is specific to SEDE. Everything we've said so far about SQL is true for all flavors of SQL; this is different.
As shown in this query, you refer to a parameter by surrounding its name with doubled pound signs, such as ##MinScore##. Optionally you can specify the data type (int (a whole number), float (a number with a decimal value, like 2.5), or string): ##MinScore:int##. If you specify a type then SEDE will validate values against that type, so that if you're expecting a number for score and somebody types "unicorn" the query won't run. If you don't specify a type, your query may receive unexpected inputs.

## has no meaning in standard SQL. Though not the case here, it is used in Transact-SQL as a prefix to identify a global temporary object:

Rules for regular identifiers
An identifier that starts with a number sign denotes a temporary table or procedure. An identifier that starts with double number signs (##) denotes a global temporary object. Although the number sign or double number sign characters can be used to begin the names of other types of objects, we do not recommend this practice.


When Execution Plan Cost Differs From IOPs and Time

SQL server uses a cost based optimization model. The exact cost will depend on the row size and distribution info which will be context-sensitive and dynamic. Even in actual execution plans, the cost figure is based on estimates.

From : An Introduction to Cost Estimation

  In recent versions of SQL Server this cost should no longer be interpreted as seconds, milliseconds, or any other unit.

Back to your question :

  what's the best way to determine which is more effective?

I used to rely on statistics time and IO. Any change I make e.g. create index, rewrite the query, use query hint, update / create statistics etc. I would measure the CPU time, reads and writes.

Since I purchased SQL Sentry's Plan Explorer PRO, I just use it to compare the before and after plans.

Also, you can get Multiple Plans for an "Identical" Query - check the SET options, any cAsE and whitespace or schema references as well.

Great references :

Inside the Optimizer: Plan Costing
When is a Seek not a Seek?


Need to query records based on Date only (Sample Query given)

An open-ended range is the safest in terms of granularity and sargability, and being immune to underlying data type changes:

DECLARE @today DATETIME;
SET @today = DATEADD(DAY,DATEDIFF(DAY,'20000101',GETDATE()),'20000101');

SELECT ... WHERE ordDate >= DATEADD(DAY,-1,@today)
             AND ordDate < @today;

You may get suggestions for other creative ways to find the rows from yesterday, but they'll be less safe and/or slower. You will probably be suggested things like this:

WHERE CONVERT(CHAR(10), ordDate, 112) = CONVERT(CHAR(10), GETDATE(), 112);
-- not sargable! Will cause a table/index scan even if there is an index on ordDate

WHERE ordDate BETWEEN <yesterday at mignight> AND <yesterday at 23:59:59.997>
-- unsafe! Will break if variables or underlying columns change data types

Some links:

What do BETWEEN and the devil have in common?
Bad habits to kick : mis-handling date / range queries
What is the most efficient way to trim time from datetime?

On that last one, pay attention to the performance graphs, particularly how well the open-ended range does.

When you move to SQL Server 2008 or better, you can also consider the easier CONVERT(DATE approach, since this is one of the very few isolated scenarios where applying a function to a column is still sargable. However, you should also read this:

Cast to date is sargable but is it a good idea?

To create a procedure for this, in SQL Server:

CREATE PROCEDURE dbo.myreport
  @ReportDate DATETIME = NULL
AS
BEGIN
  SET NOCOUNT ON;

  -- report on yesterday if parameter is NULL:
  SET @ReportDate = COALESCE(@ReportDate, DATEADD(DAY, -1, GETDATE()));

  -- remove time from datetime, in case time is passed:
  SET @ReportDate = DATEADD(DAY, DATEDIFF(DAY, '20000101', @ReportDate), '20000101');

  SELECT ordID, ordName, ordDate, ordShipping, ordStateTax,
    ordHandling, ordTotal, ordDiscount, ordPrivateStatus
  FROM dbo.orders
  WHERE (ordStatus = 7)
    AND orderDate >= @ReportDate
    AND orderDate < DATEADD(DAY, 1, @ReportDate);
END
GO

Now in your ASP code, after opening oConn, you do something like this:

set cmd = CreateObject("ADODB.Command")
cmd.Activeconnection = oConn
cmd.CommandText = "dbo.myreport"
cmd.commandType = 4 'adCmdStoredProc
dateParam = date()
cmd.parameters.Append ccmd.CreateParameter("ReportDate", adDate, 1, 0, dateParam)
set oRS = cmd.execute()
do while not oRS.eof
  ...

Sorry, my classic ASP is rusty, so you may have to make adjustments. Thankfully, there are dozens and dozens of sites out there that include tutorials on using connection, command and recordset objects in classic ASP.


Connect to Oracle RAC from SQL Developer?

Looks like the JDBC 11g NAT+RAC issue.

JDBC Connections Using SCAN Fail With ORA-12516/ORA-12520 (Doc ID 1555793.1)

  JDBC thin 11g connections using  Single Client Access Name (SCAN)
  fail with errors:

  ORA-12520: TNS:listener could not find available handler for requested
  type of server

  ORA-12516: TNS : listener could not find instance with
  matching protocol stack

  when client and database are on different networks and Network Address
  Translation (NAT) router is used for translation.  JDBC OCI and
  SQL*Plus connections work fine. JDBC connections using the HOST vip
  names work fine over the NAT router, only connections using SCAN fail.
  JDBC thin connections using SCAN without NAT router work without any
  problems

The above note suggests upgrading the JDBC driver to 12c.

Whenever you connect through SCAN, your connection is redirected. Previous versions of the Oracle JDBC driver can not handle that properly.

If you download the latest SQL Developer, that comes with the 12.1.0.2 JDBC driver.

Another solution that I prefer is to use OCI with an Oracle Client instead of JDBC (this makes possible to cancel long-running queries in SQL Developer). After installing the Oracle Client, you can configure this in Tools / Preferences / Database / Advanced.


Reclaim disk space from dropped column without downtime

ALTER TABLE .. DROP COLUMN ... marks the column as deleted in the system table pg_attribute. The table itself is not otherwise manipulated until rows are rewritten some way or another. The drop itself is very fast, but it does take a brief ACCESS EXCLUSIVE lock. I would not call that "downtime", though.
Actually reclaiming disk space is the tricky part. You could use
the community tool pg_repack instead of VACUUM FULL or CLUSTER (which lock the table exclusively). It avoids exclusive locks, but it needs free space on disk to operate. More details:

VACUUM returning disk space to operating system

Or, if rows in your table are updated eventually, you can just wait it out. Every new row version is written with the dropped column set to NULL. When the old row version is overwritten or removed by VACUUM you basically regain the space. Some corner case exceptions apply, though. See:

Dropping column in Postgres on a large dataset


Store wait_stats automatically and query it whenever required

You could get a monitoring tool like Idera SQL Diagnostic Manager or Dell Spotlight to do all of that for you. If you want to go the manual route and get the wait stats yourself you can check out sp_BlitzFirst from Brent Ozar. You can use that to store wait stats in a table. If you click through the link they give you directions on how to set it up to log to a table.


Duplicated SQL Server Execution Times For Dynamic Query

You get an output for each statement. The query in @sql is one statement that gives you the first output and calling exec is a statement of its own and outputs duration for all the statements in @sql.
So if you have two statements in @sql you get three outputs.
exec (N'select * from dbo.YourTable; select * from dbo.YourTable;');

Output with set statistics time on:
(74 rows affected)

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 164 ms.

(74 rows affected)

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 144 ms.

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 309 ms.


Mysql - unknown variable 'table_cache=64'

Version 5.1.3 "Renamed the table_cache system variable to table_open_cache. Any scripts that refer to table_cache should be updated to use the new name."  However, it did not become an error until years later, in 5.7.6.  Change to table_open_cache.

However, what does the line in my.cnf look like?  Sounds like there is something else wrong.  Change it to

table_open_cache = 200

(If you have a tiny machine, 64 might be better.  But in today's machines, a few hundred is a better value.)


Mysql : user with privileges to create databases, users and grant all privileges on those dbs to users

There is nothing special about "root".
CREATE USER foo@localhost ...;   -- establish the existence and pwd of the user
GRANT CREATE DATABASE     -- so foo can do CREATE DATABASE
      ...                 -- and other things (as needed)
    ON *.*                -- probably have to give permission at this level
    TO foo@localhost      -- the user
    WITH GRANT OPTION;    -- so foo can do GRANTs

See also SUPER -- decide whether or not you want this non-user to have all the privileges it entails.
For security reasons, only @localhost should be used with WITH GRANT OPTION.


Logical trees: difference between Before Project Normalization and Join Collapsed

Is it possible to construct a query where there is a difference between those two trees?

No.
The sequence of events is:

The Join-collapsed Tree is printed immediately after Heuristic Join Reordering.
Memory usage before project normalization is printed (if enabled).
Normalize Projects is called.

If project normalization has been disabled, it exits.
Otherwise, a call to Derive Group Properties is made.
Nothing has been added or changed, so properties are not rederived.
Normalize Projects prints the Tree Before Project Normalization.

The extra call to Derive Group Properties has almost zero cost if no work needs to be done. One can only speculate why the tree is debug printed on entry to Normalize Projects.


Since the index (EID,Salary) has a height of h, the PK on EID must have height <=h.  If this is a clustered index, then for <= h LIOs (page reads) the PK can satisfy the query.

Then assuming the PK is not clustered, using the PK costs h or h + 1 LIOs.

  condition will be checked and we will either get this employee or not. So how can I calculate the costs of each in order to compare between them?

The probability that the salary >60000 is about 0.5, so you can calculate an expected cost.  Using the index on (EID,Salary) has an expected cost of
0.5 * h + 0.5 * (h+1) = h + 0.5 LIOs.

Using the (Salary,EID) index costs h LIOs to seek to the first row with Salary >60000, and then you can scan across the leaf pages to find EID=5, which will require reading (0 -.05] of the total leaf pages.  If Employee has 2 rows per page the (Salary, EID) index would have, perhaps, 8 rows per page.  So you scan, on average, .25 of the rows or (20,000/8)/4=625.  So that would cost, on average 625+h LIOs.


A few questions regarding Sql Server Utility

Plucking an answer out of thin air..

I'd suggest the same load. At the end the day, the same dmvs will be queried by any monitoring tool. The same ones that I'd query adhoc or use in scheduled queries. SQL Server utility or a 3rd party tool just automate this for you.


Migrating data from a boolean column to a varchar column

In MySQL, the BOOLEAN data type does not really exist. If you create something like this:

mysql> CREATE TABLE imp (id int PRIMARY KEY auto_increment, important BOOLEAN);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO imp (important) VALUES (0), (0), (0), (1), (0), (1), (1), (1), (0), (0), (0), (1);
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

You will see that BOOLEAN is just an alias for tinyint(1):

mysql> SHOW CREATE TABLE imp;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| imp   | CREATE TABLE `imp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `important` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

And you can even insert a 2 value:

mysql> INSERT INTO imp (important) VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM imp WHERE important = 2;
+----+-----------+
| id | important |
+----+-----------+
| 13 |         2 |
+----+-----------+
1 row in set (0.00 sec)

The less traumatic way, but more secure and flexible that I can think is, instead of creating an enum or a string, reuse the column and set a foreign key if you are using InnoDB:

mysql> CREATE TABLE levels (id tinyint primary key, name varchar(30));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into levels values (0, 'normal'), (1, 'urgent'), (2, 'extreme');  Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE imp ADD CONSTRAINT important FOREIGN KEY (important) REFERENCES levels(id);
Query OK, 13 rows affected (0.02 sec)
Records: 13  Duplicates: 0  Warnings: 0

That way you cannot insert values that do not exist on the levels table and you can add values later very easily (also, you do not have to rebuild the original table).