What's the most effective way to back-up / save data on Oracle 11g large data sets?

You could use Oracle Data Pump Export and unload data compressing the resulting dump file(s). Below is an example of unloading the tables EMPLOYEES and DEPARTMENTS owned by the user HR:

expdp hr parfile=hrexp.par

The parameter file hrexp.par contains the following parameters:

content=data_only
compression=all
include=table:"IN ('EMPLOYEES', 'DEPARTMENTS')"
directory=dmpdir
dumpfile=hrdata.dmp
reuse_dumpfiles=yes
nologfile=yes

The dump file is created in the directory specified by the directory parameter. You will probably want this directory to be located on the NFS so that it's available both to source database host and the remote host. The Oracle database should have appropriate privileges to be able to read from/write to this directory, and the user which performs the export operation should have read/write access to the directory object:

SQL> create directory DMPDIR as '/nfs/oraexp';
Directory created.

SQL> grant read, write on directory DMPDIR to hr;
Grant succeeded.

In the example, the value of parameter content instructs Data Pump Export to unload data only. The compression parameter here specifies that the exported data should be compressed.

And now the most important part concerning performance. Oracle Data Pump can use different methods during load and unload operations, and in your case Direct Path method may be preferable because of your CPU resource usage requirement. In order for Data Pump to be able to use this method, you should satisfy some requirements listed in the section "Situations in Which Direct Path Unload Is Not Used" of the Database Utilities Guide:

  If any of the following conditions exist for a table, then Data Pump
  uses the external table method to unload data, rather than the direct
  path method:

  Fine-grained access control for SELECT is enabled.
  The table is a queue table.
  The table contains one or more columns of type BFILE or opaque, or an object type containing opaque columns.
  The table contains encrypted columns.
  The table contains a column of an evolved type that needs upgrading.
  The table contains a column of type LONG or LONG RAW that is not last.
  The Data Pump command for the specified table used the QUERY, SAMPLE, or REMAP_DATA parameter.


Why am I getting nondeterministic results with the deterministic function STDEV()?

row_number is not deterministic if there can be ties (i.e. rows with the same PartitionField and DateField values).  Any of the tied values might end up with a PartitionRowId of 1 which would presumably change the final result.

You could use rank instead of row_number but that would cause you to consider all the tied rows which may not be what you want.  You could also update your analytic function to add additional order by criteria to ensure that row_number returns a deterministic first row.


How to optimize min/max queries for large tables on PostgreSQL

With only a few dozen distinct values of source_id, you can get fast execution on the index you built, using a loose index scan, aka a "skip scan".  Unfortunately PostgreSQL does not plan those automatically, so you have to force it into one, by using a recursive query.

with recursive t as (
   select min(source_id) as col from mydata
   union all
   select (select min(source_id) from mydata where source_id>t.col)
      from t where t.col is not null)
select
  col,
  (select min(last_updated_date) from mydata where source_id=col),
  (select max(last_updated_date) from mydata where source_id=col)
  from t;

Even if you don't resort to this, just doing the query as you originally wrote it should not take nearly an hour.  But without seeing an explain and an explain analyze, there isn't much more that can be said on that.


How to combine date and time to datetime2 in SQL Server?

This seems to work and keep the precision as well:

SELECT DATEADD(day, DATEDIFF(day,'19000101',@D), CAST(@T AS DATETIME2(7)))

The CAST to DATETIME2(7) converts the TIME(7) value (@T) to a DATETIME2 where the date part is '1900-01-01', which is the default value of date and datetime types (see datetime2 and the comment* at CAST and CONVERT page at MSDN.)

* ... When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.

The DATEADD() and DATEDIFF()function take care of the rest, i.e. adding the difference in days between the 1900-01-01 and the DATE value (@D).

Test at: SQL-Fiddle

As noticed by @Quandary, the above expression is considered not-determninistic by SQL Server. If we want a deterministic expression, say because it is to be used for a PERSISTED column, the '19000101'** needs to be replaced by 0 or CONVERT(DATE, '19000101', 112):

CREATE TABLE date_time
( d DATE NOT NULL,
  t TIME(7) NOT NULL,
  dt AS DATEADD(day,
                DATEDIFF(day, CONVERT(DATE, '19000101', 112), d),
                CAST(t AS DATETIME2(7))
               ) PERSISTED
) ;

**: DATEDIFF(day, '19000101', d) isn't deterministic as it does an implicit conversion of the string to DATETIME and conversions from strings to datetime are deterministic only when specific styles are used.


How to implement effective MySQL database backup mechanisms?

I think what you're looking for is mysqldump. Something like the following should get it done:

mysqldump -u root -p --databases DB_NAME > databasename.sql


T-SQL Syntax error copying views dynamically

You cannot do EXEC sp_executesql on combined literals.  You must use a variable as in:

ALTER PROCEDURE [dbo].[spCreateViews] (@SourceDB NVARCHAR(200), @DestinationDB NVARCHAR(200))
AS
BEGIN
    DECLARE @SQL2 NVARCHAR(MAX)
    SET @SourceDB = REPLACE(REPLACE(@SourceDB, '[', ''), ']','')
    SET @DestinationDB = REPLACE(REPLACE(@DestinationDB, '[', ''), ']','')

    SET @SQL2 =
        '
        BEGIN
        DECLARE @SQL NVARCHAR(MAX)
        DECLARE @cmd NVARCHAR(MAX);
        DECLARE CUR_V CURSOR FOR
        SELECT sm.definition
        FROM ' + QUOTENAME(@SourceDB) + '.sys.views av
            JOIN ' + QUOTENAME(@SourceDB) + '.sys.sql_modules sm ON sm.object_id = av.object_id
        OPEN CUR_V
        FETCH NEXT FROM CUR_V INTO @SQL
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC ' + QUOTENAME(@DestinationDB) + '.sys.sp_executesql @SQL;
            FETCH NEXT FROM CUR_V INTO @SQL
        END
        CLOSE CUR_V
        DEALLOCATE CUR_V
        END'

    EXEC sp_executesql @SQL2
END

This code uses @SourceDB and @DestinationDB in the dynamic SQL to correctly identify the source and destination databases.  This negates the need for the USE [Database] statement on line after SET @SQL2 =.


How to create a partitioned table based on date?

As the error message says, any partition-aligned unique index has to include the partitioning key in the index key.  This requirement exists so the engine can enforce uniqueness on updates without checking every partition.

In your case, this means including OrderDate in the nonclustered index key, or having a non-aligned index.  Both are potentially valid choices, depending on your circumstances.  To preserve alignment, your table and index definitions would be:

CREATE TABLE dbo.Orders
(
    OrderID     integer NOT NULL,
    Name        nvarchar(20) NULL,
    OrderDate   date NOT NULL,

    CONSTRAINT PK__Orders_OrderID_OrderDate
        PRIMARY KEY NONCLUSTERED
            (OrderID, OrderDate)
        ON PS (OrderDate)
)
ON PS (OrderDate);
GO
CREATE CLUSTERED INDEX CX__Orders_OrderDate
ON dbo.Orders (OrderDate)
ON PS (OrderDate);

Of course this changes the uniqueness that the nonclustered index enforces.  Now, only the combination of OrderID and OrderDate is guaranteed to be unique.  It is theoretically possible to add duplicate OrderIDs, so long as the OrderDate is different.  Whether this change of semantic is acceptable to you depends on your circumstances, but it is something to be aware of.

The alternative is to have the nonclustered primary key non-aligned:

CREATE TABLE dbo.Orders
(
    OrderID     integer NOT NULL,
    Name        nvarchar(20) NULL,
    OrderDate   date NOT NULL,

    CONSTRAINT PK__Orders_OrderID
        PRIMARY KEY NONCLUSTERED
            (OrderID)
        ON [PRIMARY]
)
ON PS (OrderDate);
GO
CREATE CLUSTERED INDEX CX__Orders_OrderDate
ON dbo.Orders (OrderDate)
ON PS (OrderDate);

This preserves the uniqueness of OrderID alone, and has some benefits with queries that compute MIN or MAX aggregates using the index, but you lose the ability to SWITCH partitions in and out without dropping the primary key and recreating it after the switching operation.

You can read more about partitioning in this section of Books Online and more on the issues of aligned and non-aligned indexes in this excellent answer by Remus Rusanu.


Even when user has 'bulkadmin' role, query says user does not have role - SQL Server 2012

You could have problems whereby the DC is not available, the user is actually a Windows group, or you could possibly have UAC issues. It may even be a permissions issue in AD as regards the account (run xp_logininfo 'RS\sqluser' and see if you get an 0x5 error).

I prefer to directly query the system tables to get that sort of information:

SELECT  1
FROM    sys.server_principals sp
        INNER JOIN sys.server_role_members srm ON sp.principal_id = srm.member_principal_id
        INNER JOIN sys.server_principals sp2 ON srm.role_principal_id = sp2.principal_id
WHERE   sp2.name = 'bulkadmin'
        AND sp.name = 'RS\sqluser';


Ident authentication failed for user "postgres"

If you are editing wrong hba file this should fix

Switch to postgres user on OS
run psql
should be connected to PostgreSQL
run show hba_file;
should be seeing to correct hba_file directory and edit that file.
reload postgresql

lloyd@localhost:~$ sudo su - postgres
postgres@localhost:~$ psql
psql (13.1 (Ubuntu 13.1-1.pgdg18.04+1), server 12.5 (Ubuntu
12.5-1.pgdg18.04+1)) Type "help" for help.
postgres=# show hba_file ;

hba_file
/etc/postgresql/12/main/pg_hba.conf (1 row)
postgres=# select pg_reload_conf();

Last query should return true if it's not, there is an error in hba file so PostgreSQL won't refresh hba file. So you should look for error in file. Also you can look at logs. After reloading PostgreSQL, it shows you error with line.


Which MySQL server parameters should be adjusted to improve INSERT performance for a large MyISAM table?

A long list of possible improvements:

Turn off updating "atime" on the filesystem?

INDEX(id1) can be DROPped since there is another index starting with that.  This would decrease from 6 to 5 the number of indexes to update -- a small speedup.

Toss any other indexes you are not using.

Use NOT NULL where appropriate.  I doubt if you meant for all columns to be NULLable?

With a 1G key_buffer, I hope that you have at least 6G of RAM.  If not, decrease that setting.

Increasing the key_buffer will not instantly improve the index caching -- it takes time for blocks to get into the key_buffer and become useful.

If practical, "batch" the inserts.  That is,

INSERT INTO mytable (...) VALUES (...), (...), ...;

Optimal is to batch 100-1000 at a time.

Switch to InnoDB.

What will you do with the data?  (Usually the performance problems occur on the read side.)

Two Hardware suggestions:  SSD; RAID-striping with write buffer.


Azure elastic pool: Can I create as many databases as I want?

The server has 96 GBs of RAM and a 3.4 Ghz processor

The elastic pool is mainly for smaller workloads, and multi-tenant applications.  For lift-and-shift of existing SQL Servers, you're normally better-off with Azure SQL Database Managed Instance.

Only Managed Instance (or SQL Server on an Azure VM) supports cross-database queries, SQL Server Linked Servers, Transactional Replication publication and distribution, SQL Agent, and other features an on-prem SQL Server may be using.

Managed Instance starts at 4 vCores, where Elastic Pool is available in smaller sizes, down to less than 1 vCore.


.wrk files are just temporary extension names, while log files are being copied. If log is small enough you wont even notice that change, but in your case it is notable.
Basically this is way of sql server ensuring that log that has being copied wont be accidentally restored by LSRestore job or by a manual restore.
As soon as log has been copied successfully it will change into .trn.

I dont know anything about your SQL, hardware and network specifications but having 570 transaction log backups in a span of one hour once in 24h could cause overhead on disk IO.
Check the log file size, perhaps adding to the schedule one more LS during 24h span would be beneficial (lower tran log size).


Select statement for selecting every week's log record

It is possible do this in SQL.  There are really 2 problems here:

Find 1 record per week for each database
Calculate the average rate of consumption

For the first problem, you have a couple of ways to do this:

select databasename, logrecordnumber, logtimestamp
  from MyTable
 where logtimestamp > current timestamp - 42 days
   and dayofweek(logtimestamp) = 0
   and time(logtimestamp) between '11:59:30' and '12:00:30'

This looks at only records in the last 6 weeks, and then finds records that occurred on Sunday (dayofweek() = 0) at 12:00.  (Using the between predicate should handle minor differences in clocks on your database servers and/or instances when your script doesn't execute exactly on the minute.

It may also be possible to write this using OLAP windowing functions, but I'm not sure it would be any more efficient.

For the second problem you may want to look at using the LAG() function so you can calculate the delta in the log record:

select
    databasename
   ,logrecordnumber
   ,logtimestamp
   ,logrecordnumber - lag(logrecordnumber,1) over (partition by database order by logtimestamp) as logrecordnumber_growth
from
    MyTable
where
    logtimestamp > current timestamp - 42 days
    and dayofweek(logtimestamp) = 0
    and time(logtimestamp) between '11:59:30' and '12:00:30'

This example adds a calculation showing how much the logrecordnumber has grown since the week before.

Doing the rest of the calculation (extrapolating to when LSNs might run out) is left as an exercise for the OP.


How to write "and" queries for a lookup table when you can't use the AND keyword?

A classical case of relational-division!
One of the most efficient queries techniques for what you ask:
SELECT h.name,
       h.vacancies
FROM   hotels_amenities_lookup ha1
JOIN   hotels_amenities_lookup ha2 USING (hotel_id)
-- more?
JOIN   hotels h ON h.id = ha1.hotel_id
WHERE  ha1.amenity_id = (SELECT id FROM amenities WHERE key = 'wheelchair_accessible')
AND    ha2.amenity_id = (SELECT id FROM amenities WHERE key = 'wifi')
-- more?

db<>fiddle here (reusing McNets fiddle, thanks!)
These indices help:

amenity(key, id)  -- ①
hotel_amenity(amenity_id, hotel_id)
hotel_amenity(hotel_id, amenity_id) -- ②
hotel(id) -- PK

① Maybe in the form of UNIQUE (key) INCLUDE (id). Related:

Do covering indexes in PostgreSQL help JOIN columns?

② Yes, another multi-column index with reversed columns. See:

Is a composite index also good for queries on the first field?

But there is a plethora of other ways:

How to filter SQL results in a has-many-through relation

Aside 1: The above is using AND, but it's not really about avoiding the keyword, is it?
Aside 2: Just hotels_amenities instead of hotels_amenities_lookup?
Database design
But, as Laurenz commented, the first question is about the right database design. And that heavily depends on exact (undisclosed) specifications and requirements.
Your fully normalized many-to-many implementation is a "catch-all" solution. You can combine as many hotels and as many amenities as you like, rename, add, drop hotels and amenities easily, add any amount of additional information in each table, and indexing with plain btree indexes is simple. Robust against heavy concurrent write load with many updates in any or all tables. But the disk footprint is high. And optimizing your query with many amenities needs some work.
For combining just a hand full of amenities the presented query is fast and optimized by the query planner automatically (using MCV lists in the column statistics). For more than a few amenities (more than join_collapse_limit) you'll have to switch to a smarter solution like outlined in the related answer you found:

Using same column multiple times in WHERE clause

The linchpin of the solution is to filter on rare amenities first.
About join_collapse_limit:

Postgres JOIN conditions vs WHERE conditions
Are implicit joins as efficient as explicit joins in Postgres?

OTOH, for a small, stable set of amenities a simple design with boolean flags like Laurenz suggested has a much smaller disk footprint. Maybe add one partial index per flag.
Or an array of amenities for more than a few options (optimized with "char"[]?) with a GIN index. May even be combined with your current design in the form of a MATERIALIZED VIEW.
Each with a matching query style.
If storage space is important (it always is in some way):

Should I use the PostgreSQL bit string?

As always: it depends.


Issues with setting a global setting in PostgreSQL 9.5/PostGIS2.2

Edit the /etc/postgresql/9.x/main/environment file and set following environment variables POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL POSTGIS_ENABLE_OUTDB_RASTERS=1. Restart then the server.


How to use / as sysdba in pluggable database?

First of all, to export from a PDB you shouldn't use SYS for that. It is way over-privileged. You should create a user in the PDB and use that user to connect to the database. You can use the privilege "exp_full_database" to grant the necessary privileges.

Next, you have to specify a connect string to go directly into the PDB (via the relevant service). It is "mypdb_oracle_net" in your example. However, when doing so, you must specify the password for SYS. You can't use OS authentication when doing this.

To solve your problem I would do something like this:

SQLPLUS / AS SYSDBA
ALTER SESSION SET CONTAINER=mypdb;
CREATE USER EXPORT_USER IDENTIFIED BY YOUR_PASSWORD;
GRANT CREATE SESSION TO EXPORT_USER;
GRANT exp_full_database TO EXPORT_USER;

EXPDP EXPORT_USER/YOUR_PASSWORD@mypdb_oracle_net dumpfi......


Random performance of stored procedure across databases

Have you checked the CPU / Memory stats across servers where one SP is significantly different from another? (I assume your I/O are of the same performance more or less)?

I would also check various wait statistics, using Paul Randal's query as good starting point

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/


It's a case-sensitivity issue.

"dateType" including the quotes is a quoted identifier, which implies that it retains its case (as opposed to an unquoted identifier which is implicitly converted to lower case). Quoted identifiers allows other columns of the same table named for instance "DateType" or "datetype" or other variants that differ only by case (not that it would be a good idea).

The drawback is that every subsequent SQL instruction refering to that column must use "dateType" too, with the double quotes and the exact same case. In your UPDATE, the double quotes are lacking, which is why it errors out saying that "datetype" does not exist (note how it differs from "dateType").

In practice, an identifier should be either always double-quoted, or never. A mix and match is not allowed. When an identifier is not quoted, it can be written in camel case if you fancy that style: the SQL parser will ignore the case.

See Identifiers and keywords in PostgreSQL documentation for more.


Does the PCI require to protect the Credit Card Info in the transaction log?

This means text logs, such as those from Log4net.
Not database redo/undo/transaction files

Why?

PCI says that Credit card numbers (PAN) should be sent and stored as encrypted in the database, which means only the encrypted value will appear in any database log file (whether an error log or the actual redo/undo/transaction log file). See Page 8 and item 3.4 of your document for this.

Edit, after @Shark's observation

The database doesn't do encryption/decryption: only storage, no processing
The sending/receiving application does encryption/decryption: only processing, no storage


Are regular VACUUM ANALYZE still recommended under 9.1?

VACUUM is only needed on updated or deleted rows in non-temporary tables. Obviously you're doing lots of INSERTs but it's not obvious from the description that you're also doing lots of UPDATEs or DELETEs.

These operations can be tracked with the pg_stat_all_tables view, specifically the n_tup_upd and n_tup_del columns. Also, even more to the point, there is a n_dead_tup column that tells, per table, how much rows need to be vacuumed.
(see Monitoring statistics in the doc for functions and views related to statistics gathering).

A possible strategy in your case would be to suppress the scheduled VACUUM, keeping an eye on this view and checking on which tables the n_dead_tup is going up significantly. Then apply the aggressive VACUUM to these tables only. This will be a win if there are large tables whose rows never get deleted nor updated and the aggressive VACUUM is really necessary only on smaller tables.

But keep running the ANALYZE for the optimizer to always have fresh statistics.


Normalize or Denormalize - Which would be better

First of all the data model must be accurate. It must capture the values required by the business in order to perform its functions and answer its queries.

In this particular case I believe you want to associate each user with one particular showroom. If my belief is correct then the model you show fulfils this requirement.

The second concern is to make the system fast. You seem worried that having between 160 million and 300 million rows in a single Users table will be unworkable. You propose to preemptively address this by having multiple Users tables, one per region, for about 150 Users_xxx tables in total. The rationale for splitting at this level is that the majority of the queries will aggregate by region.

I would suggest retaining a single user table, for DB maintenance and application development reasons.

It is unlikely the list of regions is permanently fixed. This means new regions will be introduced and current ones removed. In turn that means tables must be created and dropped, with application changes to match. What should be a simple data change has become a system maintenance project.

A typical application function is to find a user based on natural keys, surname for example. With a single table this is implemented as

select <columns>
from Users
where surname = 'the-name'

Surname (and other columns used in queries) can be indexed to make this fast. Covering indexes can be defined to keep all the required data contiguous on disk. Range scans can be made fast with disk read-ahead etc.

With split tables it is

select <columns>
from Users_AAA
where surname = 'the-name'

union all

select <columns>
from Users_BBB
where surname = 'the-name'

union all

...
repeat 147 more times
...

select <columns>
from Users_ZZZ
where surname = 'the-name'

Each table will have its own little bit of disk. These are unlikely to be contiguous. Every query will have to hit every table, even if they're indexed. It is unlikely to be fast. Once again this query must change every time the list of regions changes. It can be hidden from the application somewhat by enclosing it in a view, but the maintenance task remains.

As a starting point, I would maintain a single Users table with a foreign key to showroom, just as you've shown. If testing proved to be a problem in practice at the anticipated scale, on production-sized hardware, I'd consider propagating the key of Region into SubRegion and Showroom, giving them compound keys. Users table can then be indexed / clustered by Region to satisfy the given query. I may even use the natural key for this, rather than the surrogate, to remove further steps from the execution plan. The foreign key constraints will ensure only correct Region_Id appear in the child tables.

This could also be achieved by demnormalization. Region_Id column is added to Users but not to SubRegion or Showroom. The value is copied in when a row is written to Users. Assuming a user never switches Showroom/ Subregion / Region you're done. The system cannot help you ensure Users.Showroom_Id and Users.Region_Id correspond to each other.


The first 2 answers are from the SQL Server Internals Book p.278

0x38 is 56 in decimal. This indicates int in sys.types (system_type_id column)
0x01 is the version number of the sql_variant format (always 1 in SQL Server 2008)
This is the way the literal 1 is always interpreted in SQL Server. e.g. SELECT 1 AS foo INTO NewTable will create a new column of integer datatype. Use an explicit cast if you want it to be treated as a different datatype.
This is part of the product source code. You cannot view the definition.

BTW: If you are looking at other datatypes as well you may encounter some additional bytes between the version number and the column value as follows.

numeric/decimal have 1 byte each for precision and scale.
[n][var]char have 2 bytes for max length and 4 bytes for collation id.
[var]binary have 2 bytes for max length.


SQL Server - Copying a key from one table to another

For permanent reused code like this, you really should be explicitly defining your temporary table definitions.  If you are looking for an easy way to do that, please see below.  I'll also include information on your actual question.

Easy way to generate table definition:

locate the object in object explorer
right click on it and select "script --> create table --> to SSMS window"

This should generate a new SSMS window with the table you selected scripted there.  Depending on your options, you will have indexes as well at the bottom.

To make it a temporary table, just change the name of the table.

Remove IDENTITY

Locate the identity column and remove the identity definition more than likely "IDENTITY(1,1)".

Example:

CREATE TABLE #INS
(
MyIdentityID INT NOT NULL PRIMARY KEY
, MyValue VARCHAR(100) NOT NULL
)

Please note, this is a simple and straightforward method that directly answers your question.  Any use of temporary tables in triggers makes me suspicious and I would like to know the full business reason behind it; it's possible that a better solution exists.


Oracle Monitoring RMAN: report need backup

A) Oracle replied to my SR that it is not supported run the SQLs manually. This is not entirely correct but I expected something like that.

B) The second command report unrecoverable can be queried with SQL. The result is even more accurate than from RMAN.

select file#, unrecoverable_change#, unrecoverable_time from v$datafile;

RMAN's report unrecoveryble will only report files which contain content which are unrecoveryble after the latest backup. This SQL reports unrecoverable things in general. Normally we do not try to make the database only recoverable after the latest backup.

C) For both commands I wrote a "screen reader". Here are some code snippets:

## Execute RMAN command report need backup
RMANOUT=$(rman target / << EOF 2>&1
report need backup;
exit
EOF
)

## Execute RMAN command report unrecoverable
RMANOUT=$(rman target / << EOF 2>&1
report unrecoverable;
exit
EOF
)

# Filter file list
DATAFILES=$(echo -e "${RMANOUT}" | sed '1,/---/d' | sed '/Recovery Manager complete./d' | sed 's/RMAN>//g' | sed '/^[[:space:]]/d')

## Parse all files
while IFS=$'\n' read -a ROW
do
  # Convert lines into array (separator " ")
  IFS=' ' read -a VAL <<< ${ROW[0]}

  # No files reported
  if [ "${VAL}" == "" ]; then break; fi

  # Do whatever you like! In my case generate XMLdoc
  XMLDOC="${XMLDOC}<ROW><DBID>${DBID}</DBID><INST_ID>${INST_ID}</INST_ID><FILE_ID>${VAL[0]}</FILE_ID><BACKUP_CNT>${VAL[1]}</BACKUP_CNT><FILE_NAME>${VAL[2]}</FILE_NAME></ROW>"
done < <(echo -e "${DATAFILES}")


Move a huge databases from one SQL Server to another

If you are using Enterprise edition, then you can use backup compression in sql server 2008.

In sql server 2008 R2 and up, even compression is offered in standard edition.

To move database, you can do a backup and then restore on the destination server.

Alternatively, you can just script out the schema and then move data using BCP OUT and BCP IN. You can even zip those files and move them across the destination server.

  Will I have to create same users on the new one?

Yes, you have to run sp_help_revlogin. on source server and then copy the output on the destination server. This will create the same logins with the SID's from original server. So when you restore your database, you wont have to sync up your users.

One more thing to add :

Make sure that Instant File Initialization is enabled on the server. That will dramatically reduce the restore time.


sql server 2016 sp1 to sp2 with latest cumulative

Do I then install the last cumulative?

Yes, you should install the latest cumulative update.  Some folks would advise to install the "next to latest" version.  Since the latest one just came out 2 days ago at the time of this writing, it might be prudent to wait and see if any issues arise before going there.

Are there any tools that would help me check what issues may come up after this update.

There's not a tool I'm aware of.  You basically need to go through the KB articles for each update if you want to see what's included.  And check the KB for the CU you're installing specifically to see if there are any "known issues" with that version.  Here are the KB articles for SQL Server 2016 releases: SQL Server 2016 build versions
You can also follow the SQL Server blog, which will notify you of new releases, and also if there are significant issues with a release or if one gets pulled back entirely.


Which string variables in mysql support utf-8?

You'll have to specify a character set of UTF8 on the table schema.  See http://dev.mysql.com/doc/refman/5.5/en/charset-syntax.html

Depending on your needs you can specify table defaults which then apply to all unspecified text columns (char/varchar/text) or you can specify on a per column level.

You'll also need to have your applications to specify a UTF-8 character encoding.  The specifics on how to do this will depend on the language you are using.


MySQL table with 100,000 records queried often

You will have to compare the WHERE clauses and GROUP BY and ORDER BY statements of all your queries to make sure your current indexes can support them in their EXPLAIN plans.

Yesterday, I answered this question : InnoDB vs MyISAM with many indexes

In that question I suggested doing something to the MyISAM table that you can do as well

ALTER TABLE orders ROW_FORMAT=Fixed;

This will treat all VARCHARs as CHARs. Every row will be the exact same length. This will increase disk space 80%-100%. Your table will bloat to the maximum size for the row layout times the number of rows. You table could double or triple in size.

Where is the benefit? Your MyISAM table will then be read from/written to anywhere from 20% - 30% faster without changing anything else.

I learned that from pages 72,73 from MySQL Database Design and Tuning.

I have written about this in the past:

Which is faster, InnoDB or MyISAM? (May 03, 2012)
Which mysql storage engine to choose? (May 02, 2012)
Best of MyISAM and InnoDB (Sep 20, 2011)
Which DBMS is good for super-fast reads and a simple data structure? (Aug 12, 2011)
What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (May 10, 2011)


Postgres 10 and malformatted array literal when using EXECUTE

I think your function does not work because it does USING $1 instead of the parameter name (USING _ids). Also, the concatenation is better replaced with the format function. And you can use one query instead of array_length trick.

This should work:

CREATE OR REPLACE FUNCTION "public"."has_any_ids"(
  _ids TEXT[],
  _column_name name,
  _table_name name,
  _schema_name name DEFAULT 'public'
)
RETURNS BOOLEAN
STABLE
LANGUAGE plpgsql AS $$
/* Checks whether any given id exists in the target table. */
DECLARE
    _query TEXT;
    _result BOOLEAN;
BEGIN
    _query := format(
        'SELECT EXISTS(
            SELECT 1 FROM %I.%I t WHERE t.%I = ANY(%L::text[]))',
        _schema_name, _table_name, _column_name, _ids);
    EXECUTE _query INTO _result;
    RETURN _result;
END;
$$;

CREATE OR REPLACE FUNCTION "public"."has_all_ids"(
  _ids TEXT[],
  _column_name name,
  _table_name name,
  _schema_name name DEFAULT 'public'
)
RETURNS BOOLEAN
STABLE
LANGUAGE plpgsql AS
$$
/* Checks whether all given ids exist in the target table. */
DECLARE
    _query TEXT;
    _result BOOLEAN;
BEGIN
    _query := format(
        'SELECT NOT EXISTS(
            (SELECT u FROM unnest(%L::text[]) u)
            EXCEPT
            (SELECT t.%I::text FROM %I.%I t WHERE t.%I = ANY(%L::text[]))
        )',
        _ids, _column_name, _schema_name, _table_name, _column_name, _ids
    );
    EXECUTE _query INTO _result;
    RETURN _result;
END;
$$;


Performance impact of setting PostgreSQL to log all statements

Like almost everything else, it depends on your hardware, configuration and workload.

If you're running few expensive statements it'll make little difference; if you're running lots of tiny simple statements it'll make more difference.

If your logs are on the same file system or even just the same disk as the database, the logging will make more difference than if the logs are stored on a separate disk. Even though the logs aren't explicitly fsync()'d and are written sequentially, they're still competing with the main DB for I/O.

More importantly, some file systems (ext3 in particular) will flush all data when any file is fsync()'d, so the log writes become closer to random I/O than sequential I/O and slow down every commit. IIRC this is not an issue on ext4 or xfs.

It'll also depend on whether you have statement timing enabled, the logging method you're using, and more.

The best option is to benchmark it with a simulation of your real workload and see.


MySQL database for storing city bounding boxes and names

(Not a full answer, but too long for a Comment.)

With 200 cities with 'retangular' bounding boxes, virtually anything will do.  Even a simple table without a SPATIAL index, but with INDEX(latitude), INDEX(longitude).

3 million cities, on the other hand, needs some serious indexing.  SPATIAL, as you are asking about, is probably the 'right' way to do it.

Here is an efficient way to do it without SPATIAL, using PARTITIONing and special code instead.  (However, the code given is aimed at a different problem, so it is not directly applicable.)


Will “Oracle Express 11gR2” installation mess with the "Opera PMS" software

It's hard to know what new software breaks prior to testing it out on your system, so it's just best to create a Recovery Point on your PC prior to doing so to be safe.  After you create a manual Recovery Point, perform your install, reboot, and test stuff out.  If you feel dependencies were affected and you're unable to resolve them, you can always recover your system back to the recover point, effectively rolling back the installation (and any damage it caused).


How can I use `psql` without installing via brew

I fixed it here: https://github.com/PostgresApp/PostgresApp/issues/338

The problem was I used install_name_tool not 100% correctly, and I forgot to create aliases.


What privileges do I need to change the password of sys in an Oracle Database?

You'll need to be connected as a user with the SYSDBA privilege.  Even "alter any user" will not be enough.
SQL> select *
  2  from   session_privs
  3  where  privilege like 'ALTER ANY%';

PRIVILEGE
----------------------------------------
ALTER ANY TABLE
ALTER ANY CLUSTER
ALTER ANY INDEX
ALTER ANY SEQUENCE
ALTER ANY ROLE
ALTER ANY PROCEDURE
ALTER ANY TRIGGER
ALTER ANY MATERIALIZED VIEW
ALTER ANY TYPE
ALTER ANY LIBRARY
ALTER ANY OPERATOR
ALTER ANY INDEXTYPE
ALTER ANY DIMENSION
ALTER ANY OUTLINE
ALTER ANY EVALUATION CONTEXT
ALTER ANY RULE SET
ALTER ANY RULE
ALTER ANY SQL PROFILE
ALTER ANY EDITION
ALTER ANY ASSEMBLY
ALTER ANY MINING MODEL
ALTER ANY CUBE DIMENSION
ALTER ANY CUBE
ALTER ANY SQL TRANSLATION PROFILE
ALTER ANY MEASURE FOLDER
ALTER ANY CUBE BUILD PROCESS
ALTER ANY ATTRIBUTE DIMENSION
ALTER ANY HIERARCHY
ALTER ANY ANALYTIC VIEW

29 rows selected.

SQL> alter user sys identified by newpass;
alter user sys identified by newpass
*
ERROR at line 1:
ORA-01031: insufficient privileges


Restore Backup Fails - File 'Db' cannot be restored to '{...}.mdf'. Use WITH MOVE to identify a valid location for the file

Take another look at the output from your first command. There is an error messag in it that points you to the fix.

Look for this section:

Use WITH MOVE to identify a valid location for the file.

Basically, the file path on your machine doesn't match the original machine. The MOVE option will let you fix that.

Here is an example of the RESTORE command using the MOVE option:

USE [master]
RESTORE DATABASE [AdventureWorks2008R2]
FROM  DISK = N'C:\SQL Backups\AdventureWorks2008R2.bak'
WITH  FILE = 1,
MOVE N'AdventureWorks2008R2_Data' TO N'C:\MyNewDataLocation\AdventureWorks2008R2_Data.mdf',
MOVE N'AdventureWorks2008R2_Log' TO N'C:\MyNewLogLocation\AdventureWorks2008R2_Log.ldf',
MOVE N'FileStreamDocuments2008R2' TO N'C:\MyNewFileStreamLocation\Documents2008R2',
NOUNLOAD,  REPLACE,  STATS = 1
GO

Hope this helps!


What relevance does the table-level ANSI_NULL setting have?

The table-level ANSI_NULLS setting matters for automatic table-level operations like when processing computed columns. This also applies to other objects such as triggers (as seen here).

For operations against the table (i.e. comparisons in queries) the connection applies though - this can be quite confusing if you are not expecting a difference in behaviour between what you actively do and automatic operations.


SQL Server Express 2008R2 - restore db failing due to size limit, but file is under 10GB

The database size limit is for the total size of all database files, both data file(s) and log file(s). So you've got a 7GB problem, not a 0.1GB problem.

You could try to shrink the log file down to near-zero, but even then it is going to be very close (it also may expand as soon as you try to query the database).


Decrease size of database (with an expression index)?

If hash() is an IMMUTABLE function (which should be the case for a function called "hash"!) you can omit storing the functionally dependent attribute_one in the table altogether and add an expression index to support queries on the expression hash(customer_one):

CREATE TABLE customer (
   privatekeytype customer_type     -- move the enum to 1st pos to save some more
 , customer_one   text PRIMARY KEY
 , attribute_two  text
);

Expression index:

CREATE INDEX customer_attribute_one_idx ON customer (hash(customer_one));

This is exactly as big (identical) as the index supporting your original UNIQUE constraint on the redundant column attribute_one.

Query:

SELECT *
FROM   customer
WHERE  'XXX' IN (customer_one, hash(customer_one));

Testing with EXPLAIN you'll see index or bitmap index scans like:

->  BitmapOr  (cost=5.34..5.34 rows=5 width=0)
     ->  Bitmap Index Scan on customer_pkey  (cost=0.00..2.66 rows=1 width=0)
           Index Cond: ('XXX'::text = customer.customer_one)
     ->  Bitmap Index Scan on customer_attribute_one_idx  (cost=0.00..2.68 rows=4 width=0)
           Index Cond: ('XXX'::text = hash(customer.customer_one))

About the same performance as with the redundant table column or faster since the table is smaller, yet - which helps overall performance in various ways.

Moving the enum to first position saves a few bytes of alignment padding per row as explained in my previous answer:

Why is my database 12 times bigger than inserted data?

Why does the function have to be IMMUTABLE? See:

How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?
Does PostgreSQL support “accent insensitive” collations?


Publishing database changes with SSDT that include adding columns to existing table to the target database through Project

This is happening because you've added the column in the middle of the CREATE TABLE script.  This causes a "table rebuild" to happen.  I talked about this on my blog a few months ago: SSDT problems: Table Rebuilds

The section on column ordinality is the specific problem you have.  Say you have a table like this:

CREATE TABLE [dbo].[Post]
(
    [Id] INT IDENTITY(1,1) NOT NULL,
    [PostType] VARCHAR(10) NOT NULL
);

And then add a column in the middle of the column list:

CREATE TABLE [dbo].[Post]
(
    [Id] INT IDENTITY(1,1) NOT NULL,
    [CommentCount] INT NULL, -- NEW COLUMN IN THE MIDDLE
    [PostType] VARCHAR(10) NOT NULL
);

This will "rebuild" the whole table in order to keep the underlying table metadata in sync between the model (your SSDT project) and the target database.

The solution?  Just add the column to the end:

CREATE TABLE [dbo].[Post]
(
    [Id] INT IDENTITY(1,1) NOT NULL,
    [PostType] VARCHAR(10) NOT NULL
    [CommentCount] INT NULL -- NEW COLUMN AT THE END
);

This doesn't rebuild the table, and thus doesn't cause potential data loss.


PostgreSQL Upsert not working on Partitioned Table

Upsert on partitioned tables is not implemented in versions earlier than Postgres 11.

In Postgres 9.6:

  INSERT statements with ON CONFLICT clauses are unlikely to work as expected, as the ON CONFLICT action is only taken in case of unique violations on the specified target relation, not its child relations.

Declarative partitioning does not resolve the problem, Postgres 10:

  Using the ON CONFLICT clause with partitioned tables will cause an error, because unique or exclusion constraints can only be created on individual partitions. There is no support for enforcing uniqueness (or an exclusion constraint) across an entire partitioning hierarchy.

Workaround

create unique indexes ("user", type, date) on all child tables,
create and use a function for insert/update based on the Example 42.2. Exceptions with UPDATE/INSERT described in the documentation.

In Postgres 11 you can use ON CONFLICT on partitioned tables, see lad2025's answer.


SSMS not coloring tables, columns and variables

Colors are controlled via the options dialog box, which you linked to in your question.

Check the following, to see what it shows in the "Sample" box:


If your database structure is relatively static then dump it separately (once), and dump only tables data using SELECT .. INTO OUTFILE - this is more fast.
Of course you need:

create a script (the best way - an event procedure) which dumps all (or only changeable) tables data
create a script (maybe as stored procedure in structure dump) which restores the data from this dump taking into account that relational data must be restored in "one then many" order strictly


Thanks for posting the sample data. It would also be helpful to describe in words and with actual output what you want to be returned by your query.

I'm guessing that you want something like

SQL> ed
Wrote file afiedt.buf

  1  WITH T1 AS
  2  (
  3  SELECT 1 AS SEQ, 'NOTHING 1' AS SOME_TYPE FROM DUAL UNION ALL
  4  SELECT 2 AS SEQ, 'NOTHING 2' AS SOME_TYPE FROM DUAL UNION ALL
  5  SELECT 3 AS SEQ, 'SOMETHING 1' AS SOME_TYPE FROM DUAL UNION ALL
  6  SELECT 4 AS SEQ, 'SOMETHING 2' AS SOME_TYPE FROM DUAL UNION ALL
  7  SELECT 5 AS SEQ, 'SOMETHING 3' AS SOME_TYPE FROM DUAL UNION ALL
  8  select 6 as seq, 'SOMETHING 4' AS SOME_type from dual
  9  )
 10  , T2 AS
 11  (
 12  SELECT 'A' AS COMPARE_TYPE FROM DUAL UNION ALL
 13  SELECT 'B' AS COMPARE_type FROM DUAL
 14  )
 15  SELECT T2.*, T1.*
 16    FROM T1, T2
 17   WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND
 18                    T1.SOME_TYPE LIKE 'NOTHING%'
 19                 THEN 1
 20               WHEN T2.COMPARE_TYPE != 'A' AND
 21                    T1.SOME_TYPE NOT LIKE 'NOTHING%'
 22                 THEN 1
 23               ELSE 0
 24*           END) = 1
SQL> /

C        SEQ SOME_TYPE
- ---------- -----------
A          1 NOTHING 1
A          2 NOTHING 2
B          3 SOMETHING 1
B          4 SOMETHING 2
B          5 SOMETHING 3
B          6 SOMETHING 4

6 rows selected.

But I'm making a lot of guesses about what your code is supposed to mean.

That this appears to be identical to a question someone asked in the OTN forums.  My answer is the same in both places.


How do I build a PostgreSQL ORDER BY expression based on a concatenation of columns?

There are two things I can say here,

The solution: "," is different from ',' single-quotes is the literal quote mechanism. Double quotes means the column named , which is perfectly valid however horrible it would be in practice CREATE TABLE foo ("," int);

-- Identifier quoting
order by lower(addresses.city) || "," || addresses.state_id;
-- Literal quoting
order by lower(addresses.city) || ',' || addresses.state_id;

The larger problem: why concatenate the strings? It can only ever slow this down or lose accuracy. You can order by more than one column; just do this..

order by lower(addresses.city), addresses.state_id;


BobMgr::GetBuf: Sort Big Output Buffer write not complete after 60 seconds.

Big Output Buffers (BoBs) are used when a sort spills to tempdb.

The 'big' buffer optimizes for large sequential I/O instead of writing sort records individually.

A delay of this length indicates a problem with the disk subsystem.


In a trigger fired AFTER the insertion it makes no sense to modify the NEW record, as it has been already inserted. Use a BEFORE trigger.

CREATE TRIGGER trg_calcul_altiinf
BEFORE INSERT OR UPDATE ON table2    -- + UPDATE
FOR EACH ROW EXECUTE PROCEDURE calcul_altiInf();

In the function body you should query only the table1, as the current value of n_sd is in the NEW record:

IF TG_OP IN ('INSERT', 'UPDATE') THEN    -- + UPDATE
    NEW.alti_inf = (
        SELECT z - NEW.prof
        FROM table1
        WHERE NEW.n_sd = table1.objectid);
    RETURN NEW;
END IF;


What service that lets you create records/data into an immutable database storage system that cannot be deleted?

While the specifics will depend on the database system itself this could be handled with pretty much any of the RDBMS platforms out there.  Permissions will pretty much handle what you want.  The process that puts the data in would need to have insert permissions and all other access would be select only.

As far as tracking the modifications - most of the vendors have some sort of auditing capabilities.  I am most familiar with Oracle and in that you can audit the activity of a user, activity on a specific table, or even any time a specific data modification statement (insert, update, delete) is executed.  Again this is vendor and version specific.

As a further protection - make sure to have good backups that can be used to restore to a specific point in time.  This will ensure that you can return the data to a specific state in case of an error or system failure of some type.


Change an index order from ascending to descending

If you dont want to lock table data you can use the clause CONCURRENTLY to drop the index an recreate it, this process is slower but avoids to lock access to the users.

DROP INDEX CONCURRENTLY my_index;
CREATE INDEX CONCURRENTLY my_index ON my_table (my_column DESC);

You can take a look at the postgres index documentation for more detailed information.


What type of replication do I need to push webdata to offline database for analytics?

It sounds like you will be replicating from 1 db server (the web backend) to 1 reporting server and that the data will all be flowing in one direction. If what I wrote is correct I think that transactional replication will be the best fit to your needs - one of the nice things about replication is that you don´t have to include all the tables (indeed you can filter out columns or rows if required). Start by checking the tables you want to replicate have a primary key defined.


Dump only the Stored Procedures in MySQL

This should do it for you:

mysqldump -h... -u... -p... -n -d -t --routines --triggers --all-databases > MySQLStoredProc.sql

  -n, --no-create-db     Suppress the CREATE DATABASE ... IF EXISTS statement
                         that normally is output for each dumped database if
                         --all-databases or --databases is given.
  -d, --no-data          No row information.
  --triggers             Dump triggers for each dumped table.
                         (Defaults to on; use --skip-triggers to disable.)
  -R, --routines         Dump stored routines (functions and procedures).
  -t, --no-create-info   Do not write CREATE TABLE statements that create each
                         dumped table.

CAVEAT

It would be much better not to separate the stored procedures from the database so that specific stored procedures will be created in the database it was meant for. The same goes for triggers. This would be preferrable:

mysqldump -h... -u... -p... -d --routines --triggers --all-databases > MySQLStoredProc.sql


Why MySQL init_connect variable gets updated after rebooting?

You will have to find the my.ini, look for init_connect, and change it, remove it, or comment it out.

Then, run

net stop mysql
net start mysql

If the values keep getting reset anyway, you have to do the following:

Create a file called C:\mysql_init.sql the the contents

SET GLOBAL init_connect='';

Go back to my.ini and add this line

init_file=C:/mysql_init.sql

then run

net stop mysql
net start mysql

If you do not do this, you will have to run SET GLOBAL init_connect=''; after every reboot

This is funny. I talked about this before : Screwed up MySQL init_connect. Based on that old post of mine, I would check what character sets you are working with.

Perhaps you can put something bogus like

[mysqld]
init_connect='SET autocommit=1'

and restart mysql.


ORA-14224 Deferred Segment Creation is not supported for this schema

Sure, do not use SYS for such operations.

SQL> show parameter deferred

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

SQL> conn bp/bp
Connected.

SQL> CREATE TABLE NEWTABLE3 (n1 number) SEGMENT CREATION DEFERRED;

Table created.

SQL> conn / as sysdba
Connected.
SQL> CREATE TABLE NEWTABLE3 (n1 number) SEGMENT CREATION DEFERRED;
CREATE TABLE NEWTABLE3 (n1 number) SEGMENT CREATION DEFERRED
*
ERROR at line 1:
ORA-14224: Deferred segment creation is not supported for this schema

Restrictions on Deferred Segment Creation

  This clause is subject to the following restrictions:

  You cannot defer segment creation for the following types of tables: clustered tables, global temporary tables, session-specific temporary
  tables, internal tables, external tables, and tables owned by SYS,
  SYSTEM, PUBLIC, OUTLN, or XDB.
  Deferred segment creation is not supported in dictionary-managed tablespaces.
  Deferred segment creation is not supported in the SYSTEM tablespace.
  Serializable transactions do not work with deferred segment creation. Trying to insert data into an empty table with no segment
  created causes an error.


Where should I put nvarchar(max) dimensions in my data warehouse?

Microsoft recommends using a CCI for large data warehouse tables with a few caveats including:

Don't use a clustered columnstore index when:

The table requires varchar(max), nvarchar(max), or varbinary(max) data types. Or, design the columnstore index so that it doesn't include these columns.

Simply put, your options are to forgo columnstore entirely, create a nonclustered columnstore index on the table that doesn't include the VARCHAR(MAX) column, or to move the LOB columns to a separate table. You said that end users will sometimes access the tables without querying the VARCHAR(MAX) columns so I would try to use columnstore when possible so those queries can get the full benefit.
If I was designing this my first attempt would be to test your workload with nonclustered columnstore indexes that include every column except the VARCHAR(MAX) ones. That is a separate index so you will incur extra storage for the columns, but if you see the typical CCI compression ratio it will only be 10% extra. It's the simplest design and will put you in a good position to take advantage of SQL Server 2017's availability to include VARCHAR(MAX) columns in clustered columnstore indexes. Niko Neugebauer wrote a blog post about some testing on CCIs with LOB data in SQL Server vNext which you can find here.


Where can I find a list of the available extensions for PostgreSQL?

This is a frequently asked question.

Users and SQL calls them "extensions". You add them with CREATE EXTENSION.
They get packaged by distros like Ubuntu as postgresql-contrib or the like.
The docs calls them "Additional Supplied Modules", but "modules" isn't really used anywhere else.  I'm not sure you should ever call anything a module, but it seems to be used here to refer to packaged extensions. Perhaps it's from the days when extensions were just SQL scripts.

You can find the list of PostgreSQL extensions officially supported here.

PGXN.org is a great resource for finding third party modules/extensions/contrib-ie stuff too.


Is there an Oracle function to check for valid Japanese characters (Non-ASCII)?

I think the answer given the specifics is "no there is not."  I would think, however, in Oracle you could use a regular expression perhaps to find fields with characters not in a specific set.  See http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm

I don't know how regex usage works with indexes on Oracle however.


What are the risks of the parameter PRESERVE_COMMIT_ORDER of Oracle's DBMS_LOGSTDBY?

I found the following white paper: SQL Apply Best Practices: Oracle Data Guard 11g Release 1 that brings a lot of enlightement.
On page 12 is says

If you are using the SQL Apply database only for disaster-recovery purposes or if the reporting application using the standby database can accommodate transactions being applied out of sequence, then set the PRESERVE_COMMIT_ORDER parameter to FALSE. When SQL Apply is running in this model, transactions that are unrelated to each other may be applied out of sequence.

there are some examples that illustrates the effects (logical and performance related) of setting this parameter to false.

Most reporting application are reporting about data that was processed some time ago (yesterday, the last month) it seems to be no problem to et this parameter to true

The most performant way to run a standby is to run it as a physical standby with the limitation that the database is a read only 1:1 copy of the primary database.


You don't mention what it is that does not work, but clearly:

 WHERE id_project = 1

matches 2 rows, so if the problem is duplicates, that is the root cause. Easiest to solve by adding distinct

SELECT DISTINCT
(SELECT  value
 FROM    table
 WHERE   id_field = 1) AS field_1,
(SELECT  value
 FROM    table
 WHERE   id_field = 2) AS field_2
FROM table
WHERE id_project = 1;

Note that if id_field is not unique, you need a correlation against the outer select:

SELECT DISTINCT
(SELECT  value
 FROM    table
 WHERE   id_field = 1
   AND   id_project = t1.id_project ) AS field_1,
(SELECT  value
 FROM    table
 WHERE   id_field = 2
   AND   id_project = t1.id_project ) AS field_2
FROM table as t1
WHERE id_project = 1;

Another option would be:

with t (id_project, id_field, value) as (
    values (1,1,'abc')
         , (1,2,'xfe')
         , (2,3,'ufj')
         , (2,4,'osj')
)
SELECT MAX(x.val1), MAX(x.val2)
FROM t
CROSS JOIN LATERAL (
    VALUES (CASE t.id_field WHEN 1 THEN value END
           ,CASE t.id_field WHEN 2 THEN value END)
) x (val1, val2)
WHERE t.id_project = 1;


Yes, there's some performance impact on the master because of logical decoding. It's not usually too bad.

You can have both streaming physical replication for HA/failover and pglogical in use at the same time. However, currently if you have to fail over, pglogical can't switch to reading from the promoted replica so you'll have to re-create the subscription before you re-enable writes to the master after failover if you want to ensure consistency.


Sounds like what you need is sparse columns and filtered indexes and go with option 1. These are fully supported and documented features for exactly this scenario.

  The SQL Server Database Engine uses
  the SPARSE keyword in a column
  definition to optimize the storage of
  values in that column. Therefore, when
  the column value is NULL for any row
  in the table, the value requires no
  storage.

I can't imagine an XML solution performing well in this scenario, it will have a huge overhead of redundant metadata and will be slow to query.


Mysql auto insert record in primary table if not exist

You cannot get it automated via FOREIGN KEY.

Plan A:  Use a TRIGGER.

Plan B:  Use INSERT .. ON DUPLICATE KEY UPDATE ...


How to definitively clear away database before restoring from new version of backup file

Just use WITH REPLACE in your restore command and it will overwrite the existing databases on the destination.   Simple, and done.   You can DROP first, but its not necessary.

WITH REPLACE is the T-SQL keyword that corresponds to "overwrite the existing database" in the restore database GUI (added for future readers of this thread)

Oh...and are you running an RC version of 2017?  RTM isn't until next Monday 10/2...just an afterthought.


It looks like you see a difference between the clients you use.

In my experience the different PostgreSQL clients need very different times for displaying the data they got from the server.  Far the fastest was psql and then pgAdminIII, EMS SQL Manager and phppgadmin in who-remembers-what order.

When tried with commands which return no or few rows, the difference is usually negligible or at least very small.  Furthermore, you should see the same actual time consumed (with the usual small differences) when you issue an EXPLAIN ANALYZE ... command instead of the actual query which returns the data much slower in one client than the other.


SQL Server 2008 and compatibility level 80

In theory it should work.  You better test it thoroughly before converting production.  If your application uses DTS packages, they will have to be converted to SSIS.  Your OS will have changed as well, so double check to make sure your app does not rely on operating system level functionality.

To further answer your question, the vendor is likely not wanting to support it as a way of covering their butts.  They certified the application to run on SQL 2000, not SQL 2008.

I would stop giving them money if I were you.


Load balancing after clustering is failed in SQL Server cluster

GTT,

You stated 'SQL Server' so I assume you mean MS SQL Server.  The reasons it can be overloaded is because that single node is now handling the additional instances and that puts load on the shared resources (Memory, Cpu, Cpu threads, not disks though, etc.).  There are several ways of resolving this:

1-If you're going active-active, you should follow the N+1 rule so you would want 3 servers in the cluster.  This way if 1 fails over, if you setup the preferred nodes properly, it'll go to the empty node and you can finish your maintenance on the original node and the nodes aren't overloaded.

2-Setup resource groups per cluster, you should do this on an active-active implementation anyways.  You can limit the amount of RAM and set the CPU affinity (how many cpu cores the instance will use).  By doing this you can adjust the usage of the limited resources in advance, so the other instance which might not be as critical won't hog all the resoruces.

3-How do they feel about the other server being so far?  Distance increases latency which will have a direct impact on the customer receiving their queries, especially if best practices aren't being used and extra data is being generated (NO COUNT off, SELECT * instead of just what they need, etc.).  You should also check the wait times to see if the network is now a issue too.  For all we know the bottleneck is just the network and everything is fine.

This answer would satisfy me if I was interviewing you.   Good luck.  Have you been studying hard on SQL Server?


Figured it out. Because the line given isn't the same as written, the following query worked:

select workid, charid, wordcount from paragraph
where plaintext like '%rose%'
and plaintext like '%other%'
and plaintext like '%name%'
and plaintext like '%smell%'
and plaintext like '%sweet%';


How to store date & time in DECIMAL(18,3)?

MySQL has two date+time formats:

For 1-second resolution, use the datatype DATETIME or TIMESTAMP:

mysql> SELECT NOW() AS sample_DATETIME, UNIX_TIMESTAMP() AS sample_TIMESTAMP;
+---------------------+------------------+
| sample_DATETIME     | sample_TIMESTAMP |
+---------------------+------------------+
| 2018-11-26 12:45:15 |       1543265115 |
+---------------------+------------------+

To include milliseconds, use DATETIME(3) or TIMESTAMP(3):

mysql> SELECT NOW(3) AS sample_DATETIME;
+-------------------------+
| sample_DATETIME         |
+-------------------------+
| 2018-11-26 12:46:35.093 |
+-------------------------+

Think of DATETIME as a picture of the clock.  Note that it has suitable hiccups when Daylight Savings comes or goes.

TIMESTAMP is essentially UTC, but converted from/to your timezone.

I have not heard of anyone using DECIMAL.


That is a great list. I would also use Brent Ozar Unlimited sp_Blitz, http://www.brentozar.com/first-aid/sql-server-downloads/. It will give you great overview of what might not be setup quite right.


There are plenty of differences.  One that comes to mind right away is the MERGE statement, which first became available in 2008.


Currently I am restoring SAN snapshots to another server nightly and running weekly checkdbs on that test server.

Read this article by Denny Cherry - A SAN Snapshot is not a backup !.

I would suggest you to take native SQL Server backup (preferably with compression - to reduce disk footprint) and then restore it on a test server and run below command to see if you get the same error or not.

DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS


Check the "Data Model Resource Book, Vol. 1". It covers standard schemata for - among other things - contact management. Pretty much the whole CRM and accounting etc. side.

And yes, there are a lot of problems with your approach - but I wont replicate 30 pages or so from that book describing all the issues.


How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?

Simple case, static SQL
The non-dynamic solution with crosstab() for the simple case:
SELECT * FROM crosstab(
  'SELECT b.x, f.name, f.x * b.x AS prod
   FROM   foo f, bar b
   ORDER  BY 1, 2'
   ) AS ct (x int, "A" int, "B" int, "C" int, "D" int, "E" int
                 , "F" int, "G" int, "H" int, "I" int, "J" int);

I order resulting columns by foo.name, not foo.x. Both happen to be sorted in parallel, but that's just the simple setup. Pick the right sort order for your case. The actual value of the second column is irrelevant in this query (1-parameter form of crosstab()).
We don't even need crosstab() with 2 parameters because there are no missing values by definition. See:

PostgreSQL Crosstab Query

(You fixed the crosstab query in the question by replacing foo with bar in a later edit. This also fixes the query, but keeps working with names from foo.)
Unknown return type, dynamic SQL
Column names and types cannot be dynamic. SQL demands to know number, names and types of resulting columns at call time. Either by explicit declaration or from information in the system catalogs (That's what happens with SELECT * FROM tbl: Postgres looks up the registered table definition.)
You want Postgres to derive resulting columns from data in a user table.  Not going to happen.
One way or the other, you need two round trips to the server. Either you create a cursor and then walk through it. Or you create a temp table and then select from it. Or you register a type and use it in the call.
Or you simply generate the query in one step and execute it in the next:
SELECT $$SELECT * FROM crosstab(
  'SELECT b.x, f.name, f.x * b.x AS prod
   FROM   foo f, bar b
   ORDER  BY 1, 2'
   ) AS ct (x int, $$
 || string_agg(quote_ident(name), ' int, ' ORDER BY name) || ' int)'
FROM   foo;

This generates the query above, dynamically. Execute it in the next step.
I am using dollar-quotes ($$) to keep handling of nested quotes simple. See:

Insert text with single quotes in PostgreSQL

quote_ident() is essential to escape otherwise illegal column names (and possibly defend against SQL injection).
Related:

PostgreSQL convert columns to rows? Transpose?
Dynamic alternative to pivot with CASE and GROUP BY


QUERY

With regard to the query, please make this index

ALTER TABLE news ADD INDEX status_news_id_index (status,news_id);

and refactor the query as follows:

SELECT nw.news_id, nw.title, nw.description, nw.url, nw.date, nw.image,
       nw.status, src.title as src_title, src.keyword as src_keyword,
       src.url as src_url, src.icon as src_icon, cat.title as cat_title
FROM
(
    SELECT * FROM news
    WHERE status=1
    ORDER BY news_id DESC LIMIT 0,140
) as nw
LEFT JOIN sources as src ON nw.source=src.source_id
LEFT JOIN categories as cat ON nw.category=cat.category_id;

STORAGE ENGINE

Here is the problem with your InnoDB : You did not configure anything for it.

Each storage engine caches differently. MyISAM caches index pages from the .MYI files into the key buffer. InnoDB caches data and index pages in the InnoDB Buffer Pool which you did not configure. The default is 8MB.

There is a default set of innodb files located in /var/lib/mysql

ibdata1
ib_logfile0
ib_logfile1

These must be resized.

Since you are using MySQL 5.1, please do the following:

STEP 01) Convert all InnoDB tables back to MyISAM

STEP 02) service mysql stop

STEP 03) Add the following lines to my.cnf after tmp_table_size = 16M

innodb_file_per_table
innodb_buffer_pool_size=512M
innodb_log_file_size=128M
innodb_fast_shutdown=0
innodb_thread_concurrency=0

STEP 04) Erase InnoDB Files

rm -f /var/lib/mysql/ib*

STEP 05) service mysql start

On startup, mysqld will do the following

create /var/lib/mysql/ibdata1 as a 10MB file, run some stuff, and end up 18MB
create /var/lib/mysql/ib_logfile0 128M
create /var/lib/mysql/ib_logfile1 128M

STEP 06) Convert all MyISAM tables back to InnoDB


Foreign Key Constraints and Indexes are two completely separate entities within a database.  As you have explicitly stated in your question, foreign key relationships are to ensure Referential Integrity.  If your database copy is soley for OLAP that wouldn't really come into play (as long as you aren't doing any DML batches against it).

The indexes are what are going to impact your query performance.  And if the above is true (no INSERT, UPDATE, or DELETE), you shouldn't be too concerned with the overhead of index maintenance.

Because of this, you can wisely choose indexes in order to maximize performance.  But without seeing more details on your data, and what already exists, generalized ideas are all that can be given to you.

EDIT:  As per Grant Fritchey's blog post, there can be a performance gain due to foreign key constraints, as the query optimizer can omit certain unnecessary operations due to the referential aspects of the constraint.  For more information, please see Grant's blog post: Do Foreign Key Constraints Help Performance?


Update all tables containing column name

This query will give you a list of your table names and the field name into a temp table.

IF OBJECT_ID('tempdb..#BaseData') IS NOT NULL DROP TABLE #BaseData
GO
CREATE TABLE #BaseData (Schema_Name sysname, Table_Name sysname, Field_Name sysname, SQLScript varchar(max));

DECLARE @FieldName varchar(20); SET @FieldName = 'COLUMN_NAME';

INSERT INTO #BaseData (Schema_Name, Table_Name, Field_Name, SQLScript)
SELECT
s.name
,o.name
,c.name
,'ALTER TABLE ' + s.name + '.' + o.name + ' SET ' + c.name + ' = ''123'''
FROM sys.columns c
JOIN sys.types t
    ON c.user_type_id = t.user_type_id
JOIN sys.objects o
    ON c.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id=s.schema_id
WHERE c.name LIKE @FieldName
    AND o.type_desc = 'USER_TABLE';
    SELECT * FROM #BaseData

Results will look like this. The first three columns are not entirely necessary but I've included them to show how the data is put together;

Schema_Name Table_Name  Field_Name    SQLScript
dbo         Table1      Column_Name   ALTER TABLE dbo.Table1 SET Column_Name = '123'
dbo         Table2      Column_Name   ALTER TABLE dbo.Table2 SET Column_Name = '123'
dbo         Table3      Column_Name   ALTER TABLE dbo.Table3 SET Column_Name = '123'
dbo         Table4      Column_Name   ALTER TABLE dbo.Table4 SET Column_Name = '123'

You can then use a cursor to execute each line of your data;

DECLARE @sql VARCHAR(MAX)
DECLARE @SqlScript VARCHAR(max)

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT SQLScript
    FROM #BaseData;
OPEN c;
FETCH NEXT FROM c INTO @SQLScript;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = @SqlScript

    EXEC(@sql);

    FETCH NEXT FROM c INTO @SQLScript;
END

CLOSE c;
DEALLOCATE c;

This will execute each line of code that you've previously created. The example code above will update all tables so that the value in Column_Name = '123'


Why is this sqlite query much slower when I index the columns?

In SQLite, joins are executed as nested loop joins, i.e., the database goes through one table, and for each row, searches matching rows from the other table.

If there is an index, the database can look up any matches in the index quickly, and then go to the corresponding table row to get the values of any other columns that are needed.

In this case, there are three possible indexes. Without any statistical information (which would be created by running ANALYZE), the database chooses the smallest one, to reduce I/O. However, the middleinitial index is useless because it does not greatly reduce the number of table rows that need to be fetched; and the additional step through the index actually increases the I/O needed because the table rows are no longer read in order, but randomly.

If there is no index, the lookup of matching rows would require a complete table scan of the second table for each row of the first table. This would be so bad that the database estimates that it is worthwhile to create and then drop a temporary index just for this query.
This temporary ("AUTOMATIC") index is created on all colunms used for the search. The COUNT(*) operation does not need values from any other columns, so this index happens to be a covering index, which means that it is not necessary to actually look up the table row corresponding to an index entry, which saves even more I/O.

To speed up this query, create this index permanently, so that it is no longer necessary to construct a temporary one:

CREATE INDEX uk_all_names ON fakenames_uk(surname, givenname, middleinitial);

EXPLAIN QUERY PLAN
SELECT count(*)
FROM fakenames_uk
JOIN fakenames_usa USING (givenname, middleinitial, surname);

0|0|1|SCAN TABLE fakenames_usa
0|1|0|SEARCH TABLE fakenames_uk USING COVERING INDEX uk_all_names (surname=? AND givenname=? AND middleinitial=?)

The index on surname is no longer needed because the three-column index can be used for any lookups on this column.
The index on givenname might be useful if you will do lookups on this column only.
The index on middleinitial is always worthless: a query that searches for one of the 26 possible values is faster if it just scans the entire table.


Microsoft just announced Hekaton which allows you to have a mix of in-memory tables and traditional on-disk tables in the same database, and seemlessly integrate the two technologies.  It will ship in the next major release.  They annouced it at the 2012 SQLPass Summit during the day 1 keynote (viewable online).


Insert incoming data (and pk from 2nd table) into 1st table if true boolean value from 2nd table

Maybe the following SQL code will help you to find a solution.  The comments "-- step1, -- step 2" etc refer to your question. Dbfiddle here , also tested with MySQL 5.5.

create table device (
  serialno int primary key
, dataval int
, state tinyint
);
-- use tinyint instead of bool 0 = false, non-zero = true
-- https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html

insert into device (serialno, dataval, state)
values
 (1,11,1),(2,22,0),(3,33,1),(4,44,0)
,(5,55,1),(6,66,0),(7,77,1),(8,88,0);

create table datain (
  id int auto_increment primary key
, device_serialno int references device(serialno)
, datain int
);

"Steps to complete" ... NOTICE: "step 3" contains both the SELECT and INSERT, as required (as one statement).  Steps 1 and 2 are just for "illustration".

-- step 1
select serialno, state from device;

-- find all devices and states (CASE - just to make the output more "readable")
select serialno
, state
, case
    when state = 0 then 'disabled'
    else 'enabled'
  end state_
from device;

-- "supplied sensor serial number"
set @id_ = 5;

-- step 2
select serialno, state
from device
where serialno = @id_
  and state <> 0 ;

-- step 3
insert into datain (device_serialno, datain)
select serialno, dataval
from device
where serialno = @id_
  and state <> 0 ;

If you wanted to insert data from all enabled devices, you could maybe use something like ...

insert into datain (device_serialno, datain)
select serialno, dataval
from device
where state <> 0 ;


How much RAM should I get for a cloud-hosted PostgreSQL data warehouse?

Estimating database server memory size by the size of tables (or indexes) is going to be misleading. On the one hand, depending on the database workload, it may not ever need to load the entire table (or index) in memory. On the other hand, there are many other possible memory consumers that have little or no correlation to the data size, such as sort buffers or client connections.

If your current server is not memory-bound, then you can use its configuration as a guideline. Otherwise you might as well buy yourself as much memory as you can afford.


Is there a SQL Server equivalent to "OVERRIDING USER VALUE"

In SQL Server you actually shouldn't need to make any changes to the outlying code or add an INSTEAD OF trigger to make this work. Here is a quick example, tested on SQL Server 2012, but should work fine on 2005 as well:

CREATE TABLE dbo.source(bar INT, x UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID());
GO
CREATE TABLE dbo.[target](bar INT, x UNIQUEIDENTIFIER PRIMARY KEY);
GO

INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO

ALTER TABLE dbo.[target] ADD TargetID INT IDENTITY(1,1);
GO

TRUNCATE TABLE dbo.source;
INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO

SELECT * FROM dbo.[target];

Results:

bar   x                                      TargetID
---   ------------------------------------   --------
1     EFF8DAC4-FB3E-4734-80BE-6DC229846203   1
1     5036688D-C04A-45FC-920E-FF44D7D501D1   2

Now I can also change the primary key and repeat the process:

ALTER TABLE [dbo].[target] DROP CONSTRAINT PK__target__3BD019E50386B4EA;
ALTER TABLE [dbo].[target] ADD CONSTRAINT PK__target__3BD019E50386B4EA
  PRIMARY KEY (targetID);

TRUNCATE TABLE dbo.source;
INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO

SELECT * FROM dbo.[target];

Results:

bar   x                                      TargetID
---   ------------------------------------   --------
1     EFF8DAC4-FB3E-4734-80BE-6DC229846203   1
1     5036688D-C04A-45FC-920E-FF44D7D501D1   2
1     41FE97FF-7D45-46EB-8A0D-B2C3BA1E67EA   3

So I haven't had to change my bad code that uses insert/select without any column lists, as long as the source table doesn't also change and assuming that the only change to the target is the addition of an identity column.

PS here is how you can automate the generation of the IDENTITY columns (assuming you will want <tablename>ID):

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + '
  ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
  + '.' + QUOTENAME(t.name) + ' ADD ' + t.name + 'ID INT IDENTITY(1,1);'
FROM sys.tables AS t
WHERE name IN (...) -- you will need to fill in this part
AND NOT EXISTS
(
  SELECT 1 FROM sys.columns WHERE [object_id] = t.[object_id]
    AND (is_identity = 1 OR name = t.name + 'ID')
);

SELECT @sql;
-- EXEC sp_executesql @sql;

(Note that the SELECT output will show you roughly what the command looks like, but due to output limitations in SSMS and depending on how many tables you have, it won't necessarily show you the full command that will get executed when you uncomment the EXEC.)

And the drop / re-create of the primary keys:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + '
  ALTER TABLE ' +
  + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
  + '.' + QUOTENAME(t.name) + ' DROP CONSTRAINT ' + k.name + ';
  ALTER TABLE ' +
  + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
  + '.' + QUOTENAME(t.name) + ' ADD CONSTRAINT '
  + k.name + ' PRIMARY KEY (' + t.name + 'ID);'
FROM sys.key_constraints AS k
INNER JOIN sys.tables AS t
ON k.parent_object_id = t.[object_id]
WHERE k.[type] = 'PK'
AND t.name IN (...); -- again, you'll want to identify the list of tables

SELECT @sql;
-- EXEC sp_executesql @sql;

And you'll want to do this while the database is in SINGLE_USER mode or while the application(s) are otherwise not able to connect to the database. You'll also want to test all this on a QA or dev system before unleashing any of it on production.

Now, this still isn't exactly best practice - I highly recommend you stop embedding SQL code in your apps, especially SQL code that does insert/select without specifying column lists.


Is there a way to use Oracle XE as an easy-rollback "proxy" to regular Oracle

Oracle 12c Snap Clone feature would probably fit your bill. Via a self-service portal through OEM, your non-DBA users (i.e., developers) can spin up private thin clones DBaaS-style. Since it uses copy-on-write, it doesn't physically duplicate the source. Each clone just needs space for the deltas. End users can make snap clones off of a TB sized master in minutes and see when they are ready for use. The clones can quickly be deprovisioned and reprovisioned, which would take care of "refreshing" the private DBs when desired. Really cool stuff, but requires an experienced Oracle DBA and equally good & patient Linux/Solaris admin to stand up this infrastructure, basically a private cloud.

UPDATE: although this requires OEM 12c, it works with Database 11gR2 & 12c.  This 20-minute video from Oracle is illuminating:  http://youtu.be/J7fnfLS5Dxg


MySQL Workbench: I need to update values in a table based on values from a csv

Import CSV file to temporary table and then run the UPDATE. For example:

DROP TEMPORARY TABLE IF EXISTS tempTable;
CREATE TEMPORARY TABLE tempTable
(id_inv int,
unit varchar(255),
PRIMARY KEY (id_inv)
);

LOAD DATA INFILE 'C:\\PathToYour\\FileName.csv'
INTO tempTable (id_inv, unit)
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

UPDATE DBtable t1
JOIN tempTable t2 ON t1.id_inv = t2.id_inv
SET t1.unit = t2.unit;

DROP TEMPORARY TABLE tempTable;

You may need to use different symbols for fields and lines terminators and file path of course. Also if you are working with remote server use "LOAD DATA LOCAL INFILE" command.


Count total number of impression views and clicks for same ad id for diffrent ip

I think the expected result you want can be fetched using this query.

select
  ad_id,
  sum(impression) total_impression,
  sum(view) total_views,
  sum(clicks) total_clicks,
from
  (select
     ad_id,
     max(impression)  impression,
     max(view)  view,
     max(clicks)  clicks,
     visitor_ip,
     publisher_id
  from ad_analytics
  where publisher_id = 109
  group by ad_id, visitor_ip) t group by t.ad_id;

UPDATE

Removed columns from select that are neither in group by nor aggregated, and added date_event

select
  ad_id,
  sum(impression) total_impression,
  sum(view) total_views,
  sum(clicks) total_clicks,
  date_event
from
  (select
     ad_id,
     max(impression)  impression,
     max(view)  view,
     max(clicks)  clicks,
     visitor_ip,
     date_event
  from ad_analytics
  where publisher_id = 109
  group by ad_id, visitor_ip,date_event) t
group by t.ad_id,t.date_event;


What can cause statistics to get out of line?

I'll guide you to an answer to an older question. Please read the comments, too, they are very valuable.

In short, MSDN says:

"Insert Operations Occur on Ascending or Descending Key Columns Statistics on ascending or descending key columns, such as IDENTITY or real-time timestamp columns, might require more frequent statistics updates than the query optimizer performs. Insert operations append new values to ascending or descending columns. The number of rows added might be too small to trigger a statistics update. If statistics are not up-to-date and queries select from the most recently added rows, the current statistics will not have cardinality estimates for these new values. This can result in inaccurate cardinality estimates and slow query performance.

For example, a query that selects from the most recent sales order dates will have inaccurate cardinality estimates if the statistics are not updated to include cardinality estimates for the most recent sales order dates.

After Maintenance Operations Consider updating statistics after performing maintenance procedures that change the distribution of data, such as truncating a table or performing a bulk insert of a large percentage of the rows. This can avoid future delays in query processing while queries wait for automatic statistics updates."

So you would benefit from searching for statistics that were not updated recently.


You cannot predict the order of a query. Even though when the query is only reading one table. This has to dowith several factors like vacuuming or used indexes. Always think in sets with databases.


SQL Server normally uses a different locking strategy to Oracle. The default strategy used by SQL Server means that selecting rows causes read locks (on rows, pages or the whole table) to be placed on them*.  Hence the NOLOCK is sometimes a useful clause - although the general advice is to never use it, as it changes the semantics of isolation levels and may cause inconsistent results in the queries' output.

* (Note: that is the default. If SNAPSHOT isolation is chosen, the behaviour is different and readers do not block writers.)

In Oracle a read process will never block a write process. The following is an excerpt from 'Oracle Database Concepts 11g Release 2'. I encourage you to take a look if you are interested in how this is handled by Oracle.

Data Concurrency and Consistency

  Summary of Locking Behavior

  The database maintains several different types of locks, depending on
  the operation that acquired the lock. In general, the database uses
  two types of locks: exclusive locks and share locks. Only one
  exclusive lock can be obtained on a resource such as a row or a table,
  but many share locks can be obtained on a single resource.

  Locks affect the interaction of readers and writers. A reader is a
  query of a resource, whereas a writer is a statement modifying a
  resource. The following rules summarize the locking behavior of Oracle
  Database for readers and writers:

  •A row is locked only when modified by a writer.

  When a statement updates one row, the transaction acquires a lock for
  this row only. By locking table data at the row level, the database
  minimizes contention for the same data. Under normal circumstances
  1  the database does not escalate a row lock to the block or table
  level.

  •A writer of a row blocks a concurrent writer of the same row.

  If one transaction is modifying a row, then a row lock prevents a
  different transaction from modifying the same row simultaneously.

  •A reader never blocks a writer.

  Because a reader of a row does not lock it, a writer can modify this
  row. The only exception is a SELECT ... FOR UPDATE statement, which is
  a special type of SELECT statement that does lock the row that it is
  reading.

  •A writer never blocks a reader.

  When a row is being changed by a writer, the database uses undo
  data to provide readers with a consistent view of the row.


Lock a table on purpose without returning results

There may be other ways, but this technique seems simple enough:

SET NOCOUNT ON
BEGIN TRANSACTION
declare @DummyVariable INT
SET @DummyVariable = (SELECT  Top 0 Null FROM Test1 with (holdlock, tablockx))


Insert but ignore if duplicate AND set a value different if exist in another table

You can try in two steps as

INSERT IGNORE INTO `invitations` (`iSender`, `iReceiver`)
VALUES ('SENDER', 'RECEIVER');

This will ignore if the record already exists.

Then update with by joining the two tables

UPDATE `invitations` as i
INNER JOIN users AS u on i.iReceiver = u.uOAuthID
SET `iProcessed` = 1 ;


Upgrading Postgres 12 to 13: 'pg_upgrade.exe' fails

The answer had to do with the option "Beta: Use Unicode UTF-8 for worldwide language support" being checked in my locale settings in Windows.
I disabled that option and restarted the computer, the upgrade went without issues after that.


ALTER TABLE engine from MyISAM to InnoDB: why does mysqldump --order-by-primary help me?

MyISAM table is stored as a heap, rows are being placed inside the data file into an empty place they can fit. If you are only inserting then that means appending new rows, but when the rows are being updated or deleted, some gaps are created and then later filled by other rows so the order of full table scan (which is what dump does by default) is not defined in any way.

InnoDB table is organized differently. The rows are stored as items inside a B-Tree "indexed" by the PRIMARY KEY. So entire table is clustered by the PRIMARY index defined. (As a side note InnoDB table always has a PK, if you do not define one, it picks one for you or creates artifical one.) That means that the full table scan is actually executed by scanning all data pages of the PRIMARY index and so the rows are returned ordered by that.

If you dump MyISAM table ordered by PRIMARY, the dump may be a bit slower than the unordered one, but a dump is usually the faster part of migrating so thats mostly a small penalty.

When you insert data into InnoDB table, the B-Tree index dynamically balances itself to keep the logarithmic depth. That often means some page splits are done.

If you insert the data in random order, each row may go to a different part of the tree, hitting different pages on-disk and initiating random splits. That incurs some IO performance penalty and it can lead to suboptimal fill factor (percentage of empty space in each page), as full page is split to two pages only 50% full which means that in bad case your data storage may be 2 times greater than needed (and all queries have to read twice as much data). Another possible outcome is fragmentation of the pages - when logically consecutive pages of the the lowest level of the B-Tree are stored in random separate locations of the disk drive (but this state is not easily identifiable and seems to be a theoretical issue, in practice encountered only rarely if ever).

But if you insert the data ordered by the PRIMARY KEY, the new rows are being "appended" to the table. Only the "last" pages on each level need to be taken care of (and if I understand it correctly - the suboptimal fill factor can be avoided as InnoDB can identify that "appending" is happening and add new pages more effectively). And the pages may be allocated more sequentially too.

So insertion in the PK order leads to faster execution and probably better storage usage in InnoDB tables.


What's the difference between DBA and DA?

According to this and that, a DA is a Data Administrator.  The DA is a buisness oriented individual more involved in requirements gathering, analysis, and design than the DBA.  They establish the flow of data around the organization and between databases and other systems.  Here is a section from the second site:

  Database Administration

  Any reasonably sized organization that relies on a database for its business
  processes will probably have a Database Administrator or DBA.

  The DBA is responsible for:

  Installing and configuring the DBMS
  Assisting in the implementation of information systems
  Monitoring the performance of the database and tuning the DBMS for optimal performance
      Maintaining documentation including recording all changes to the database and DBMS.
  Ensuring data integrity is maintained and appropriate backups are made.

  Thus a DBA is mainly concerned with the day to day operational aspects of database systems.

  Data Administration

  Many organizations, in addition to DBAs, will also have a Data Administrator.
  DAs are concerned with the data needs and data flows throughout the entire organization.
  Thus DAs are responsible for:

  Specifying data standards across databases
  Establishing policies: data usage, security and authorization, data flows into and out of the organization
  Assisting the application development process by identifying data resources in the organization
  Arbitrating the sharing of data across departments
  Increasing the return on an organization's data investment


Compound indexes are great for static values

Flip the WHERE components so that static values are first

db.data.find( { c:"test", d:"yes", a: {$gte:1,$lt:2}, b: {$gt: 3} }).sort( { b: 1 } )

Create a different compound index

db.data.ensureIndex({c:1},{d:1},{b:1},{a:1})

How does it help ?

Static c value
Static d value
Sorted Range on b

One more thing

{$gte:1,$lt:2} is really {$eq:1}

I have discussed covering indexes and how the order of columns can changes things. In these posts, I talked about it from the MySQL perspective:

Apr 11, 2014 : Why is MySQL not using the index with the higher cardinality?
Aug 11, 2013 : How to index for a query with independent range conditions?
Nov 13, 2012 : Must an index cover all selected columns for it to be used for ORDER BY?


Database Mirroring - Will forcing the service only lose non-committed data?

Correct, once the Primary loses connectivity with the Secondary (closes), all in-flight transactions will be rolled-back on the Primary and never make it to the secondary.  The committed transactions are guaranteed to be written to disk on the mirror server in that mode.

Here is an excellent article with more detail on this subject -
https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-130-in-flight-transactions-continue-after-a-failover/


Possibly the ANSI 92 definition held at

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

gives the answer. Section 3.1 Definitions seems to draw a distinction between the idea of a result set and a result sequence. The distinction was not in my awareness when I wrote the question using the former phrase

  v) sequence: An ordered collection of objects that are not necessarily
  distinct.

  w) set: An unordered collection of distinct objects. The collection
  may be empty.


Table with relation to two others (three-way relation?)

There is a third option:

Create tables visit_attribute and visit_action_attribute with name and value columns in each (and drop the attribute table altogether).

This removes the possibility of a given attribute being linked to both a visit and a visit_action, with no need for id columns with NULL values.


Despite the mainstream of noSQL databases IMHO the decision about adopting such technology should be made according to the achievements needed according to the information stored, not only attending to the performance you currently have. This means that maybe your best option is to stick to the SQL database and improve your HW.

But additionally I read something in your question that made me think. There is not much about the current status of your database but your sentence "we basically store data entered by users in various ways as "key-value" lists" makes me think about if the problem wouldn't be a poor data model rather than the lack of physical resources. I've managed really large tables (+10 billion rows) with incredible performance in "traditional" SQL databases.

I'm not saying it is wrong, just, since of course I cannot assess you in the right data model with such little information about your current solution, but just think about revisiting your data model as an additional option along with the rest since you may find some clue scratching there.

Usually key-value lists are fine as a trade-off when you cannot implement the model in its final state because you don't know the different keys you'll have to face, or when you will need the values of one of the possible keys for a certain element. But when implemented, I usually like to re-think such decisions after a while when you have gathered enough amount of information to identify the common case of use and decide whether data model decision is the best. If you know you'll have a certain number of keys, try to do some benchmark with a design of a regular table in the traditional way

CREATE TABLE benchmarkTable (
  element INTEGER,
  key1 VARCHAR(xx),
  key2 INTEGER,
  key3 DECIMAL(5,2),
...
);

...and adding the corresponding indices. Try it out and measure execution plans with both approaches. You may be surprised specially if you gather more than one key at a time, since, among other advantages the data block size should be reduced and thus the performance would be improved.

Hope this helps, or at least broadens the possibilities and opens a new line for investigation.


How can I find all rows that have at least one element in an array matching a pattern?

You can use the official ltree extension.

CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TEMP TABLE testing (
    names ltree[]
);

INSERT INTO testing (names)
VALUES
    ('{toplevel1}'),
    ('{toplevel1.sub1, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub2, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub3, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub3.sub3, toplevel2.sub2}'),
    ('{toplevel2.sub1}');

SELECT * FROM testing WHERE names ~ 'toplevel1.*';

It is also doable in "plain" postgresql:

SELECT * FROM testing AS t
WHERE (
    SELECT true
    FROM unnest(t.names) AS n
    WHERE n ILIKE 'toplevel1.%'
    LIMIT 1
);

The ltree extension is not very widely used, and obviously it has some limitations (including label length & character set), but it's stable and can achieve better performance than unnest-based version. I would really give it a try.


Is there a UI tool for moving data from two different tables?

Why use a UI tool when the query is this short?

INSERT INTO db1.users (id, name, phone)
    SELECT id, first_name, mobile FROM db2.users;

Caveats:  You probably should not copy id if it is AUTO_INCREMENT.  I was guessing which direction you wanted to copy.


That's odd, When I run your statements on 5.5 I get a syntax error:

mysql> CREATE TABLE TEST(COLA INT,DESCRIPTION VARCHAR(20));
Query OK, 0 rows affected (0.02 sec)

mysql> UPDATE TEST
    -> SET DESCRIPTION=REPLACE(DESCRIPTION,CHAR(92),CHAR(7))
    -> WHERE DESCRIPTION like '%' CHAR(07) '%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'CHAR(07) '%'' at line 3

Anyway, the proper way to do what you want to achieve would be concatenating '%' and the \G, that is:

UPDATE TEST
SET DESCRIPTION=REPLACE(DESCRIPTION,CHAR(92),CHAR(7))
WHERE DESCRIPTION like CONCAT('%',CHAR(07),'%');


Oracle query get schema name, size and creation date

select u.username,
       u.created,
       round(nvl(sum(s.bytes)/1024/1024,0),2) size_mb
  from dba_users u
  left outer join dba_segments s on (s.owner = u.username)
 group by u.username, u.created
 order by u.username;


MongoDB failover when master is stuck on IOWait?

A closely related question is discussed extensively in the comments on SERVER-14139, a bug report against filed against mongodb. To summarize, it is not feasible to build a fully general hang detection system inside a server process.

The commentary in discuss a monitoring approach that kills either the process or shuts down the operating system and can be done using a cron job or the watchdog daemon. Because a mongod process cannot win an election before it has read and written some data to its storage engine, it is safe to immediately attempt to restart mongod after you kill it. The restarted process should not accept connections and certainly will not be able to win an election for primary.

A ticket linked to SERVER-14139 covers an implementation in the enterprise (non-free) version of MongoDB of a storage watchdog timer. Organizations that can instead use the watchdog daemon or an external monitoring process should prefer that approach, because it can protect against more kinds of resource failure.