Opponent Winrate Over another opponent sql

I've mocked up the minimal tables you mentioned in your question.

First, we'll run this in tempdb and cleanup anything that might get in the way:

USE tempdb;
IF OBJECT_ID(N'dbo.MatchesParticipants', N'U') IS NOT NULL
DROP TABLE dbo.MatchesParticipants;
IF OBJECT_ID(N'dbo.Matches', N'U') IS NOT NULL
DROP TABLE dbo.Matches;
IF OBJECT_ID(N'dbo.Participants', N'U') IS NOT NULL
DROP TABLE dbo.Participants;
GO

Next, we create three tables.  Matches, Participants, and MatchesParticipants:

CREATE TABLE dbo.Matches
(
    MatchID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Matches
        PRIMARY KEY CLUSTERED
    , VersionNum int NOT NULL
    , MatchType int NOT NULL
);

CREATE TABLE dbo.Participants
(
    ParticipantID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Participants
        PRIMARY KEY CLUSTERED
    , ParticipantName varchar(100) NOT NULL
);

CREATE TABLE dbo.MatchesParticipants
(
    MatchID int NOT NULL
        FOREIGN KEY REFERENCES dbo.Matches(MatchID)
    , ParticipantID int NOT NULL
        FOREIGN KEY REFERENCES dbo.Participants(ParticipantID)
    , TeamID int NOT NULL
    , Winner bit NOT NULL
    , CONSTRAINT PK_MatchesParticipants
        PRIMARY KEY CLUSTERED (MatchID, TeamID, ParticipantID)
);

Here we create some "matches"; on my system this inserted over 522,000 rows:

INSERT INTO dbo.Matches (VersionNum, MatchType)
SELECT c1.object_id % 5
    , c2.object_id % 5
FROM sys.columns c1
    CROSS JOIN sys.columns c2;

And this inserts the "Participants"; on my system I got over 700:

INSERT INTO dbo.Participants (ParticipantName)
SELECT c1.name
FROM sys.columns c1;

I used this to create rows in "MatchParticipants".  The code uses a CTE to cross join Matches and Participants in such a way that only 1/50th of the rows in a "real" cross join are inserted.  This resulted in 7,558,671 rows.  The CASE WHEN ASCII(CRYPT_GEN_RANDOM(1)) ... piece randomly selects each participant to be either on Team 0 or Team 1, with Team 1 always being the "winner":

;WITH src AS
(
SELECT m.MatchID
    , p.ParticipantID
    , val = CASE WHEN ASCII(CRYPT_GEN_RANDOM(1)) < 127
        THEN 1 /* randomly assigned winning team */
        ELSE 0
        END
FROM dbo.Matches m
    INNER JOIN dbo.Participants p
        ON m.MatchID % 50 = p.ParticipantID % 50 /* 1/50th of a CROSS JOIN  */
)
INSERT INTO dbo.MatchesParticipants (MatchID, ParticipantID, TeamID, Winner)
SELECT src.MatchID
    , src.ParticipantID
    , src.val
    , src.val
FROM src;

Some sample data from the MatchesParticipants table:

╔═════════╦═══════════════╦════════╦════════╗
║ MatchID ║ ParticipantID ║ TeamID ║ Winner ║
╠═════════╬═══════════════╬════════╬════════╣
║ 739     ║ 39            ║ 0      ║ 0      ║
║ 739     ║ 89            ║ 0      ║ 0      ║
║ 739     ║ 139           ║ 0      ║ 0      ║
║ 739     ║ 189           ║ 0      ║ 0      ║
║ 739     ║ 239           ║ 0      ║ 0      ║
║ 739     ║ 289           ║ 0      ║ 0      ║
║ 739     ║ 339           ║ 0      ║ 0      ║
║ 739     ║ 389           ║ 0      ║ 0      ║
║ 739     ║ 439           ║ 1      ║ 1      ║
║ 739     ║ 489           ║ 0      ║ 0      ║
║ 739     ║ 539           ║ 1      ║ 1      ║
║ 739     ║ 589           ║ 0      ║ 0      ║
║ 739     ║ 639           ║ 0      ║ 0      ║
║ 739     ║ 689           ║ 0      ║ 0      ║
║ 740     ║ 40            ║ 1      ║ 1      ║
║ 740     ║ 90            ║ 0      ║ 0      ║
║ 740     ║ 140           ║ 0      ║ 0      ║
║ 740     ║ 190           ║ 0      ║ 0      ║
║ 740     ║ 240           ║ 1      ║ 1      ║
║ 740     ║ 290           ║ 1      ║ 1      ║
║ 740     ║ 340           ║ 1      ║ 1      ║
║ 740     ║ 390           ║ 1      ║ 1      ║
║ 740     ║ 440           ║ 0      ║ 0      ║
║ 740     ║ 490           ║ 1      ║ 1      ║
║ 740     ║ 540           ║ 1      ║ 1      ║
║ 740     ║ 590           ║ 1      ║ 1      ║
║ 740     ║ 640           ║ 1      ║ 1      ║
║ 740     ║ 690           ║ 0      ║ 0      ║
╚═════════╩═══════════════╩════════╩════════╝

This shows the row counts for each table:

SELECT ParticipantCount = (SELECT COUNT(1) FROM dbo.Participants)
    , MatchCount = (SELECT COUNT(1) FROM dbo.Matches)
    , MatchParticipantCount = (SELECT COUNT(1) FROM dbo.MatchesParticipants)

╔══════════════════╦════════════╦═══════════════════════╗
║ ParticipantCount ║ MatchCount ║ MatchParticipantCount ║
╠══════════════════╬════════════╬═══════════════════════╣
║ 723              ║ 522729     ║ 7558671               ║
╚══════════════════╩════════════╩═══════════════════════╝

To get the entire list of stats for each pair of players, I used this query:

;WITH source AS
(
    SELECT Winner = Winners.ParticipantID
        , Loser = Losers.ParticipantID
        , MatchID = Winners.MatchID
    FROM (
            SELECT *
            FROM dbo.MatchesParticipants mp1
            WHERE mp1.Winner = 1
        ) Winners
        INNER JOIN (
            SELECT *
            FROM dbo.MatchesParticipants mp2
            WHERE mp2.Winner = 0
            ) Losers ON Losers.MatchID = Winners.MatchID
                AND Losers.ParticipantID <> Winners.ParticipantID
)
, WinsLosses AS
(
    SELECT source.Winner
        , source.Loser
        , Wins = COUNT(1)
        , Losses = (SELECT COUNT(1)
                     FROM source l
                     WHERE l.Loser = source.Winner
                         AND l.Winner = source.Loser
                   )
    FROM source
    GROUP BY source.Winner
        , source.Loser
)
SELECT Winner = pw.ParticipantName
    , Loser = pl.ParticipantName
    , WinsLosses.Wins
    , GamesPlayed = WinsLosses.Wins + WinsLosses.Losses
    , WinRatio = CONVERT(decimal(10,5), WinsLosses.Wins) /
                      (WinsLosses.Losses + WinsLosses.Wins)
FROM WinsLosses
        INNER JOIN dbo.Participants pw ON WinsLosses.Winner = pw.ParticipantID
        INNER JOIN dbo.Participants pl ON WinsLosses.Loser = pl.ParticipantID
ORDER BY pw.ParticipantName
    , pl.ParticipantName;

Results:

╔══════════╦═════════════════════╦══════╦═════════════╦════════════════════╗
║ Winner   ║ Loser               ║ Wins ║ GamesPlayed ║ WinRatio           ║
╠══════════╬═════════════════════╬══════╬═════════════╬════════════════════╣
║ addr     ║ column_id           ║ 2651 ║ 5252        ║ 0.5047600913937547 ║
║ addr     ║ csn                 ║ 2535 ║ 5210        ║ 0.4865642994241842 ║
║ addr     ║ depid               ║ 2663 ║ 5294        ║ 0.5030222893842085 ║
║ addr     ║ fileguid            ║ 2592 ║ 5256        ║ 0.4931506849315068 ║
║ addr     ║ grantee             ║ 2604 ║ 5179        ║ 0.5027997682950376 ║
║ addr     ║ internalstatus      ║ 2636 ║ 5224        ║ 0.5045941807044410 ║
║ addr     ║ name                ║ 2662 ║ 5265        ║ 0.5056030389363722 ║
║ addr     ║ name                ║ 2552 ║ 5206        ║ 0.4902036112178255 ║
║ addr     ║ ParticipantName     ║ 2580 ║ 5248        ║ 0.4916158536585365 ║
║ addr     ║ pgfirst             ║ 2593 ║ 5252        ║ 0.4937166793602437 ║
║ addr     ║ product             ║ 2599 ║ 5280        ║ 0.4922348484848484 ║
║ addr     ║ protocol            ║ 2635 ║ 5238        ║ 0.5030546009927453 ║
║ addr     ║ service_id          ║ 2610 ║ 5216        ║ 0.5003834355828220 ║
║ affinity ║ binary_message_body ║ 2605 ║ 5222        ║ 0.4988510149368058 ║
║ affinity ║ dbid                ║ 2568 ║ 5208        ║ 0.4930875576036866 ║
║ affinity ║ diffbaseseclsn      ║ 2554 ║ 5199        ║ 0.4912483169840353 ║
║ affinity ║ encoding_type       ║ 2652 ║ 5259        ║ 0.5042783799201369 ║
╚══════════╩═════════════════════╩══════╩═════════════╩════════════════════╝

The plan:

The Source CTE joins the "winners" and the "losers" for each match.  The WinsLosses CTE provides the total for Wins and Losses.  Finally, the main query shows each participant name, their opponent's name, the number of Wins, Games Played, and of course, the Win Ratio.  On my system, this generates 9,744 rows in about 18 seconds - the results of this query could be pre-computed every 15 minutes for display to the end user.

To get that speed out of this query, I created these two covering indexes:

CREATE NONCLUSTERED INDEX MatchesParticipants_ix01
ON dbo.MatchesParticipants(Winner, MatchID)
INCLUDE (ParticipantID);

CREATE NONCLUSTERED INDEX MatchesParticipants_ix02
ON dbo.MatchesParticipants (ParticipantID, Winner)
INCLUDE (MatchID);

If you're interested in obtaining the stats for any specific given combination of players, this query does the trick:

DECLARE @ParticipantID1 int;
DECLARE @ParticipantID2 int;

SET @ParticipantID1 = 216;
SET @ParticipantID2 = 566;

;WITH source AS
(
    SELECT Winner = Winners.ParticipantID
        , Loser = Losers.ParticipantID
        , MatchID = Winners.MatchID
    FROM (
            SELECT *
            FROM dbo.MatchesParticipants mp1
            WHERE mp1.Winner = 1
        ) Winners
        INNER JOIN (
            SELECT *
            FROM dbo.MatchesParticipants mp2
            WHERE mp2.Winner = 0
            ) Losers ON Losers.MatchID = Winners.MatchID
                AND Losers.ParticipantID <> Winners.ParticipantID
)
, WinsLosses AS
(
    SELECT source.Winner
        , source.Loser
        , Wins = COUNT(1)
        , Losses = (SELECT COUNT(1) FROM source l WHERE l.Loser = source.Winner AND l.Winner = source.Loser)
    FROM source
    GROUP BY source.Winner
        , source.Loser
)
SELECT Winner
    , Loser
    , WinsLosses.Wins
    , GamesPlayed = WinsLosses.Wins + WinsLosses.Losses
    , WinRatio = CONVERT(decimal(10,2), WinsLosses.Wins) / (WinsLosses.Losses + WinsLosses.Wins)
FROM WinsLosses
WHERE Winner = @ParticipantID1
    AND Loser = @ParticipantID2
ORDER BY Winner
    , Loser;

The results of the query:

╔════════╦═══════╦══════╦═════════════╦═════════════════╗
║ Winner ║ Loser ║ Wins ║ GamesPlayed ║ WinRatio        ║
╠════════╬═══════╬══════╬═════════════╬═════════════════╣
║ 216    ║ 566   ║ 2637 ║ 5233        ║ 0.5039174469711 ║
╚════════╩═══════╩══════╩═════════════╩═════════════════╝

Results are returned immediately.  This is the actual execution plan:


Retrieve Data faster from a huge Oracle Table

When I recall correctly this is a query on BaaN version 4 or so probably. The official BaaN policy is that the Oracle drivers require ALL text fields to be VARCHAR which has some different semantics than VARCHAR2. I guess that your rpad was introduced by someone to compensate for that but it should not be there.

Also, you should try to avoid adding indexes etc yourself. The BaaN middleware takes care of that and although it is possible to add indexes things can go wrong during upgrades etc.

I don't have a BaaN system on Oracle present at this moment, but I think you are missing some leading columns of the index in your query, something like company/warehouse since you seem to be determing possibly the last inventory transaction per logistic item. Oracle version 9 and higher sometimes can compensate for that.

My advice is to press F4 in TOAD or Invantive Query Tool on ttdinv700 and check the columns listed in the index. Add missing entries to the where clause. Or check contents of user_ind_columns.

Then do the same for ttditm700 (the items). The rpad effectively disables index use but depending on your statistics it should be the driving table for the query (most companies have relatively few items compared to their stocks). Try to enable the index on ttditm700.

IMHO this question belongs on stackoverflow and not here.


Proper use of database functions for business logic

Two queries - two replies:

a) Placing business logic to database has strong defenders and strong opponents. Lot of arguments for/against are volatile and valid only for some configurations and environment. Some databases has not good capabilities for stored procedural programming, some companies has not good personal resources for programming in relative different environments. Some projects doesn't require really effective data processing and more valuable is simply monolithic deployment. There are a some arguments for placing business logic to database (only processes that are not INTERACTIVE!). You have to divide application to interactive (presentation) layer and non interactive layer (what is usually a best way, what you can do):

natural decomposition of your application between data processing and data presentation layers
very well accessible API in heterogeneous environment - logic in database is accessible from shell scripts, all languages, ...
very efficient data processing due closeness data and processing unit (in PostgreSQL it is same process). It removes a network latency, conversions between protocols, layers, drivers - PL/pgSQL runs in same process as PostgreSQL core database engine and it use same data types.
you can create a API based on stored procedures, so any changes on database layer are transparent for you and for your application.

Generally I can say, a power of stored procedures is more significant in heterogenous environment and less in monolititic single application environment.

b) PostgreSQL has a CREATE OR REPLACE FUNCTION statement and data visibility based on snapshots - so update in production usually is not problem. You can update database dictionary (with functions) under transaction.


Understanding where pg_toast is coming from

You can use this query to find out all tables with TOAST tables:

SELECT oid::regclass,
       reltoastrelid::regclass,
       pg_relation_size(reltoastrelid) AS toast_size
FROM pg_class
WHERE relkind = 'r'
  AND reltoastrelid <> 0
ORDER BY 3 DESC;

To find out which of the columns in the table consumes most space, you could try a query like

SELECT sum(length(col1)) AS col1_size,
       sum(length(col2)) AS col2_site
FROM some_table;

Here col1 and col2 would be text, varchar, char or bytea columns, which are usually the largest ones. With other data types like jsonb you could cast to text to get an estimate.


All the ways of influencing parameter sniffing before SQL 2017 remain.  There's just a new database scoped configuration option to turn off parameter sniffing entirely.


MySQL running in safe mode but not in normal mode. Can't find file: './mysql/plugin.frm' (errno: 13)

Welcome.

Please do not use mysql in safe mode.
As it crashed at one point and after which it did not start, it is better to look at log files of that time rather than --verbose.
Most probably (since you report a missing file too), you could try re-installing.

But try with Item 2. If you do not understand please post the log (of that time) here to discuss further.


PostgreSQL duplicate key violates unique constraint error

(I try to compile an answer from the comments.)

It looks like there is a problem in the process with which your application generates the value for the primary key.  Generally it is wiser to leave this to the DBMS: define a sequence and leave it fill the PK values.  If you have a sequence, you have at least to options: either set the value like

$eventid = exec(SELECT nextval('your_sequence')) // pseudocode!

and use this in the INSERT statement, or completely leave it to PostgreSQL by omitting PK from the INSERT:

INSERT INTO events (source, object, [...])
VALUES (2, 3, [...]);

(Personally I usually opt for the second solution.)

You can change to this behaviour by setting the data type of your PK column to bigserial (in case of an integer, it would be just serial):

ALTER TABLE events ALTER COLUMN eventid TYPE bigserial;

This will raise a notice which tells you that a sequence called (probably) events_eventid_seq is generated.  You can use it as described above.

EDIT (2012-11-27)

You can set the sequence by a query like

SELECT setval('events_eventid_seq', (SELECT max(eventid) FROM events));

In order to let the sequence produce the next value for eventid, you must not set a value explicitly in your INSERT.  If you do, it will take the manually supplied value and won't advance the sequence.  This will cause the same unique violation error later.

Regarding ALTER TABLE: as you could see, there is some discrepancy between ALTER TABLE - type does not exists and CREATE TABLE.  Are you sure you try this on the same database?  I can't remember the older versions, but already 8.3 had the bigserial shorthand (it's not a real type).  (Giving it further thought, it is quite possible you can't use it in ALTER TABLE, since it is a shorthand...)  You can solve the problem by following the manual:

CREATE SEQUENCE events_eventid_seq;

ALTER TABLE events
ALTER COLUMN eventid TYPE bigint;

ALTER SEQUENCE events_eventid_seq OWNED BY events.eventid;

SELECT setval('events_eventid_seq', (SELECT max(eventid) FROM events));


Choosing the correct database type for single type of entry

NoSQL databases are for unstructured data or unpredictably structured data that is often queried using full-text searching.

Capturing time-series statistics for graphing sounds like a pretty well-structured data set to me.  What do you have, three tables?  Users, Servers and Measurements?  It sounds to me like you have data which is perfectly suited to a relational database.

It may be that you need to think about whether your relational database is going to be fully normalized or partially denormalized for reporting performance.  That's something you can decide is necessary once you do some load testing with production volumes in order to see whether denormalization is helpful.


Oracle to PostgreSQL: Create type object

Although the syntax is different, you can achieve exactly what you want.

You can create type by using the script below;

CREATE TYPE tr_del AS (row_id varchar(18), pk_id integer);

Then your table structure should look like this;

CREATE TABLE employees (
   department_id integer,
   any_field_name tr_del,
   ...
);

After that you can use it in the query like this;

SELECT
    (any_field_name).row_id AS last_name,
    (any_field_name).pk_id AS employee_id
FROM
    employees
WHERE
    department_id = 30;

Fiddle is here

You may want to read Composite Types section.


Use --complete-insert

Use complete INSERT statements that include column names.

Source: http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_complete-insert


How msdb.dbo.backupset works ( backup_size )

BACKUP DATABASE, by default, does not overwrite existing backup sets contained within a backup file.

According to Microsoft Docs on BACKUP DATABASE:

  If the physical device exists and the INIT option is not specified in the BACKUP statement, the backup is appended to the device.

To test this, the following code will create a blank database, then take three backups of that database, without the INIT option.  The size of the backup file increases each time a backup is taken.

USE master;

IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'BackupTest')
BEGIN
    ALTER DATABASE BackupTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE BackupTest;
END
CREATE DATABASE BackupTest;
BACKUP DATABASE BackupTest TO DISK = 'C:\temp\BackupTest.bak';
EXEC sys.xp_cmdshell 'DIR "C:\temp\BackupTest.bak"';

 Volume in drive C is Windows
 Volume Serial Number is 1E06-5D89

 Directory of C:\temp

 05/15/2018  02:55 PM           399,360 BackupTest.bak
               1 File(s)        399,360 bytes
               0 Dir(s)  111,145,492,480 bytes free

BACKUP DATABASE BackupTest TO DISK = 'C:\temp\BackupTest.bak';
EXEC sys.xp_cmdshell 'DIR "C:\temp\BackupTest.bak"';

 Volume in drive C is Windows
 Volume Serial Number is 1E06-5D89

 Directory of C:\temp

 05/15/2018  02:55 PM           796,672 BackupTest.bak
               1 File(s)        796,672 bytes
               0 Dir(s)  111,145,091,072 bytes free

BACKUP DATABASE BackupTest TO DISK = 'C:\temp\BackupTest.bak';
EXEC sys.xp_cmdshell 'DIR "C:\temp\BackupTest.bak"';

 Volume in drive C is Windows
 Volume Serial Number is 1E06-5D89

 Directory of C:\temp

 05/15/2018  02:55 PM         1,193,984 BackupTest.bak
               1 File(s)      1,193,984 bytes
               0 Dir(s)  111,144,693,760 bytes free

If we look at the content of the backup file with RESTORE HEADERONLY we see the three backups contained within the file:

╔════════════╦═══════════════════╦════════════╦════════════════╦════════════╦══════════╦════════════╦══════════╦════════════╦══════════════╦═════════════════╦═════════════════════════╦════════════╦═══════════════════╦═══════════════════╦═══════════════════╦═══════════════════╦═════════════════════════╦═════════════════════════╦═══════════╦══════════╦═════════════════╦════════════════════════╦════════════════════╦══════════════════╦══════════════════════╦══════════════════════╦══════════════════════╦═════════════╦═══════╦══════════════════════════════════════╦══════════════════════════════════════╦══════════════════════════════╦══════════════════════════════════════╦═══════════════════╦════════════╦════════════╦══════════════╦════════════════════╦═══════════╦════════════════╦═══════════════════════╦════════════════╦════════════╦══════════════════════════════════════╦══════════════╦═══════════════╦═════════════════════╦══════════════════════╦═══════════════════════╦══════════════════════════════════════╦══════════════════════╦═════════════╗
║ BackupName ║ BackupDescription ║ BackupType ║ ExpirationDate ║ Compressed ║ Position ║ DeviceType ║ UserName ║ ServerName ║ DatabaseName ║ DatabaseVersion ║  DatabaseCreationDate   ║ BackupSize ║     FirstLSN      ║      LastLSN      ║   CheckpointLSN   ║ DatabaseBackupLSN ║     BackupStartDate     ║    BackupFinishDate     ║ SortOrder ║ CodePage ║ UnicodeLocaleId ║ UnicodeComparisonStyle ║ CompatibilityLevel ║ SoftwareVendorId ║ SoftwareVersionMajor ║ SoftwareVersionMinor ║ SoftwareVersionBuild ║ MachineName ║ Flags ║              BindingID               ║            RecoveryForkID            ║          Collation           ║              FamilyGUID              ║ HasBulkLoggedData ║ IsSnapshot ║ IsReadOnly ║ IsSingleUser ║ HasBackupChecksums ║ IsDamaged ║ BeginsLogChain ║ HasIncompleteMetaData ║ IsForceOffline ║ IsCopyOnly ║         FirstRecoveryForkID          ║ ForkPointLSN ║ RecoveryModel ║ DifferentialBaseLSN ║ DifferentialBaseGUID ║ BackupTypeDescription ║            BackupSetGUID             ║ CompressedBackupSize ║ Containment ║
╠════════════╬═══════════════════╬════════════╬════════════════╬════════════╬══════════╬════════════╬══════════╬════════════╬══════════════╬═════════════════╬═════════════════════════╬════════════╬═══════════════════╬═══════════════════╬═══════════════════╬═══════════════════╬═════════════════════════╬═════════════════════════╬═══════════╬══════════╬═════════════════╬════════════════════════╬════════════════════╬══════════════════╬══════════════════════╬══════════════════════╬══════════════════════╬═════════════╬═══════╬══════════════════════════════════════╬══════════════════════════════════════╬══════════════════════════════╬══════════════════════════════════════╬═══════════════════╬════════════╬════════════╬══════════════╬════════════════════╬═══════════╬════════════════╬═══════════════════════╬════════════════╬════════════╬══════════════════════════════════════╬══════════════╬═══════════════╬═════════════════════╬══════════════════════╬═══════════════════════╬══════════════════════════════════════╬══════════════════════╬═════════════╣
║ NULL       ║ NULL              ║          1 ║ NULL           ║          1 ║        1 ║          2 ║ me       ║ machine    ║ BackupTest   ║             706 ║ 2018-05-15 15:06:09.000 ║    2633728 ║ 35000000005500064 ║ 35000000008500001 ║ 35000000005500064 ║                 0 ║ 2018-05-15 15:06:10.000 ║ 2018-05-15 15:06:10.000 ║        52 ║        0 ║            1033 ║                 196609 ║                110 ║             4608 ║                   11 ║                    0 ║                 6260 ║ machine     ║   512 ║ 2E885286-1648-4085-96D8-0AFF92CCE995 ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║ SQL_Latin1_General_CP1_CI_AS ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║                 0 ║          0 ║          0 ║            0 ║                  0 ║         0 ║              0 ║                     0 ║              0 ║          0 ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║ NULL         ║ FULL          ║ NULL                ║ NULL                 ║ Database              ║ 94CA4CD7-659F-406B-9411-AF00DCC8E900 ║               397660 ║           0 ║
║ NULL       ║ NULL              ║          1 ║ NULL           ║          1 ║        2 ║          2 ║ me       ║ machine    ║ BackupTest   ║             706 ║ 2018-05-15 15:06:09.000 ║    2632704 ║ 35000000009500037 ║ 35000000011200001 ║ 35000000009500037 ║ 35000000005500064 ║ 2018-05-15 15:06:10.000 ║ 2018-05-15 15:06:11.000 ║        52 ║        0 ║            1033 ║                 196609 ║                110 ║             4608 ║                   11 ║                    0 ║                 6260 ║ machine     ║   512 ║ 2E885286-1648-4085-96D8-0AFF92CCE995 ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║ SQL_Latin1_General_CP1_CI_AS ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║                 0 ║          0 ║          0 ║            0 ║                  0 ║         0 ║              0 ║                     0 ║              0 ║          0 ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║ NULL         ║ FULL          ║ NULL                ║ NULL                 ║ Database              ║ 840F2E51-1805-4270-AF31-BB09F4799B4F ║               396242 ║           0 ║
║ NULL       ║ NULL              ║          1 ║ NULL           ║          1 ║        3 ║          2 ║ me       ║ machine    ║ BackupTest   ║             706 ║ 2018-05-15 15:06:09.000 ║    2632704 ║ 35000000012200037 ║ 35000000013900001 ║ 35000000012200037 ║ 35000000009500037 ║ 2018-05-15 15:06:11.000 ║ 2018-05-15 15:06:11.000 ║        52 ║        0 ║            1033 ║                 196609 ║                110 ║             4608 ║                   11 ║                    0 ║                 6260 ║ machine     ║   512 ║ 2E885286-1648-4085-96D8-0AFF92CCE995 ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║ SQL_Latin1_General_CP1_CI_AS ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║                 0 ║          0 ║          0 ║            0 ║                  0 ║         0 ║              0 ║                     0 ║              0 ║          0 ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║ NULL         ║ FULL          ║ NULL                ║ NULL                 ║ Database              ║ 448F651F-D847-463D-A5DA-8C2F8EA8ECC5 ║               396295 ║           0 ║
╚════════════╩═══════════════════╩════════════╩════════════════╩════════════╩══════════╩════════════╩══════════╩════════════╩══════════════╩═════════════════╩═════════════════════════╩════════════╩═══════════════════╩═══════════════════╩═══════════════════╩═══════════════════╩═════════════════════════╩═════════════════════════╩═══════════╩══════════╩═════════════════╩════════════════════════╩════════════════════╩══════════════════╩══════════════════════╩══════════════════════╩══════════════════════╩═════════════╩═══════╩══════════════════════════════════════╩══════════════════════════════════════╩══════════════════════════════╩══════════════════════════════════════╩═══════════════════╩════════════╩════════════╩══════════════╩════════════════════╩═══════════╩════════════════╩═══════════════════════╩════════════════╩════════════╩══════════════════════════════════════╩══════════════╩═══════════════╩═════════════════════╩══════════════════════╩═══════════════════════╩══════════════════════════════════════╩══════════════════════╩═════════════╝

However, if we now use the INIT option:

BACKUP DATABASE BackupTest TO DISK = 'C:\temp\BackupTest.bak' WITH INIT;
EXEC sys.xp_cmdshell 'DIR "C:\temp\BackupTest.bak"';

We see the size of the backup file reflects a single backup again:

 Volume in drive C is Windows
 Volume Serial Number is 1E06-5D89

 Directory of C:\temp

 05/15/2018  03:02 PM           398,336 BackupTest.bak
               1 File(s)        398,336 bytes
               0 Dir(s)  111,145,304,064 bytes free

And if we look at RESTORE HEADERONLY, we see a single backup is contained in the file:

╔════════════╦═══════════════════╦════════════╦════════════════╦════════════╦══════════╦════════════╦══════════╦════════════╦══════════════╦═════════════════╦═════════════════════════╦════════════╦═══════════════════╦═══════════════════╦═══════════════════╦═══════════════════╦═════════════════════════╦═════════════════════════╦═══════════╦══════════╦═════════════════╦════════════════════════╦════════════════════╦══════════════════╦══════════════════════╦══════════════════════╦══════════════════════╦═════════════╦═══════╦══════════════════════════════════════╦══════════════════════════════════════╦══════════════════════════════╦══════════════════════════════════════╦═══════════════════╦════════════╦════════════╦══════════════╦════════════════════╦═══════════╦════════════════╦═══════════════════════╦════════════════╦════════════╦══════════════════════════════════════╦══════════════╦═══════════════╦═════════════════════╦══════════════════════╦═══════════════════════╦══════════════════════════════════════╦══════════════════════╦═════════════╗
║ BackupName ║ BackupDescription ║ BackupType ║ ExpirationDate ║ Compressed ║ Position ║ DeviceType ║ UserName ║ ServerName ║ DatabaseName ║ DatabaseVersion ║  DatabaseCreationDate   ║ BackupSize ║     FirstLSN      ║      LastLSN      ║   CheckpointLSN   ║ DatabaseBackupLSN ║     BackupStartDate     ║    BackupFinishDate     ║ SortOrder ║ CodePage ║ UnicodeLocaleId ║ UnicodeComparisonStyle ║ CompatibilityLevel ║ SoftwareVendorId ║ SoftwareVersionMajor ║ SoftwareVersionMinor ║ SoftwareVersionBuild ║ MachineName ║ Flags ║              BindingID               ║            RecoveryForkID            ║          Collation           ║              FamilyGUID              ║ HasBulkLoggedData ║ IsSnapshot ║ IsReadOnly ║ IsSingleUser ║ HasBackupChecksums ║ IsDamaged ║ BeginsLogChain ║ HasIncompleteMetaData ║ IsForceOffline ║ IsCopyOnly ║         FirstRecoveryForkID          ║ ForkPointLSN ║ RecoveryModel ║ DifferentialBaseLSN ║ DifferentialBaseGUID ║ BackupTypeDescription ║            BackupSetGUID             ║ CompressedBackupSize ║ Containment ║
╠════════════╬═══════════════════╬════════════╬════════════════╬════════════╬══════════╬════════════╬══════════╬════════════╬══════════════╬═════════════════╬═════════════════════════╬════════════╬═══════════════════╬═══════════════════╬═══════════════════╬═══════════════════╬═════════════════════════╬═════════════════════════╬═══════════╬══════════╬═════════════════╬════════════════════════╬════════════════════╬══════════════════╬══════════════════════╬══════════════════════╬══════════════════════╬═════════════╬═══════╬══════════════════════════════════════╬══════════════════════════════════════╬══════════════════════════════╬══════════════════════════════════════╬═══════════════════╬════════════╬════════════╬══════════════╬════════════════════╬═══════════╬════════════════╬═══════════════════════╬════════════════╬════════════╬══════════════════════════════════════╬══════════════╬═══════════════╬═════════════════════╬══════════════════════╬═══════════════════════╬══════════════════════════════════════╬══════════════════════╬═════════════╣
║ NULL       ║ NULL              ║          1 ║ NULL           ║          1 ║        1 ║          2 ║ me       ║ machine    ║ BackupTest   ║             706 ║ 2018-05-15 15:06:09.000 ║    2632704 ║ 35000000014900037 ║ 35000000016600001 ║ 35000000014900037 ║ 35000000012200037 ║ 2018-05-15 15:06:11.000 ║ 2018-05-15 15:06:11.000 ║        52 ║        0 ║            1033 ║                 196609 ║                110 ║             4608 ║                   11 ║                    0 ║                 6260 ║ machine     ║   512 ║ 2E885286-1648-4085-96D8-0AFF92CCE995 ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║ SQL_Latin1_General_CP1_CI_AS ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║                 0 ║          0 ║          0 ║            0 ║                  0 ║         0 ║              0 ║                     0 ║              0 ║          0 ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║ NULL         ║ FULL          ║ NULL                ║ NULL                 ║ Database              ║ EA10F663-FAF3-422C-AE4E-3478AA7658BC ║               396310 ║           0 ║
╚════════════╩═══════════════════╩════════════╩════════════════╩════════════╩══════════╩════════════╩══════════╩════════════╩══════════════╩═════════════════╩═════════════════════════╩════════════╩═══════════════════╩═══════════════════╩═══════════════════╩═══════════════════╩═════════════════════════╩═════════════════════════╩═══════════╩══════════╩═════════════════╩════════════════════════╩════════════════════╩══════════════════╩══════════════════════╩══════════════════════╩══════════════════════╩═════════════╩═══════╩══════════════════════════════════════╩══════════════════════════════════════╩══════════════════════════════╩══════════════════════════════════════╩═══════════════════╩════════════╩════════════╩══════════════╩════════════════════╩═══════════╩════════════════╩═══════════════════════╩════════════════╩════════════╩══════════════════════════════════════╩══════════════╩═══════════════╩═════════════════════╩══════════════════════╩═══════════════════════╩══════════════════════════════════════╩══════════════════════╩═════════════╝

I have backup compression turned on, so msdb.dbo.backupset shows the following sizes for the backups I just took:

SELECT bs.backup_finish_date
    , bs.backup_size
    , bs.compressed_backup_size
FROM msdb.dbo.backupset bs
WHERE bs.database_name = 'BackupTest'
ORDER BY bs.backup_set_id DESC

╔═════════════════════════╦═════════════╦════════════════════════╗
║   backup_finish_date    ║ backup_size ║ compressed_backup_size ║
╠═════════════════════════╬═════════════╬════════════════════════╣
║ 2018-05-15 15:06:11.000 ║     2632704 ║                 396310 ║
║ 2018-05-15 15:06:11.000 ║     2632704 ║                 396295 ║
║ 2018-05-15 15:06:11.000 ║     2632704 ║                 396242 ║
║ 2018-05-15 15:06:10.000 ║     2633728 ║                 397660 ║
╚═════════════════════════╩═════════════╩════════════════════════╝

As you can see the backup_size is the uncompressed size of the backup, in bytes, and the compressed_backup_size closely matches the size reported by the filesystem, assuming you incrementally add the sizes as each backup is added to the backup file.


To see everything with aggregation, you could do this

SELECT src,CONCAT(dst,':',dstport) dsp_port,COUNT(1) hits
FROM logs
GROUP BY src,dsp_port;

To see subtotals

SELECT
    IF
    (
        ISNULL(src)=1,
        'Total',
        IF
        (
            ISNULL(dst_port)=1,
            CONCAT(src,' Total'),
            CONCAT(src,' ',dst_port)
        )
    ) Statistic,hits
FROM
(
    SELECT src,CONCAT(dst,':',dstport) dst_port,COUNT(1) hits
    FROM logs
    GROUP BY src,dst_port
    WITH ROLLUP
) A;

Using your sample data

mysql> DROP DATABASE IF EXISTS rob_d;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE rob_d;
Query OK, 1 row affected (0.00 sec)

mysql> USE rob_d
Database changed
mysql> CREATE TABLE logs
    -> (
    ->     src CHAR(15) NOT NULL,
    ->     dst CHAR(15) NOT NULL,
    ->     dstport  INT NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO logs (src,dst,dstport) VALUES
    -> ('10.110.0.10',   '10.2.9.124'    ,53),
    -> ('10.110.0.10',   '192.168.9.124' ,53),
    -> ('10.192.31.23',  '10.192.1.120'  ,8082),
    -> ('10.192.31.19',  '10.192.1.186'  ,8081),
    -> ('192.168.31.131','192.168.31.130',80);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM logs;
+----------------+----------------+---------+
| src            | dst            | dstport |
+----------------+----------------+---------+
| 10.110.0.10    | 10.2.9.124     |      53 |
| 10.110.0.10    | 192.168.9.124  |      53 |
| 10.192.31.23   | 10.192.1.120   |    8082 |
| 10.192.31.19   | 10.192.1.186   |    8081 |
| 192.168.31.131 | 192.168.31.130 |      80 |
+----------------+----------------+---------+
5 rows in set (0.00 sec)

Here is the query's output

mysql> SELECT
    ->     IF
    ->     (
    ->         ISNULL(src)=1,
    ->         'Total',
    ->         IF
    ->         (
    ->             ISNULL(dst_port)=1,
    ->             CONCAT(src,' Total'),
    ->             CONCAT(src,' ',dst_port)
    ->         )
    ->     ) Statistic,hits
    -> FROM
    -> (
    ->     SELECT src,CONCAT(dst,':',dstport) dst_port,COUNT(1) hits
    ->     FROM logs
    ->     GROUP BY src,dst_port
    ->     WITH ROLLUP
    -> ) A;
+----------------------------------+------+
| Statistic                        | hits |
+----------------------------------+------+
| 10.110.0.10 10.2.9.124:53        |    1 |
| 10.110.0.10 192.168.9.124:53     |    1 |
| 10.110.0.10 Total                |    2 |
| 10.192.31.19 10.192.1.186:8081   |    1 |
| 10.192.31.19 Total               |    1 |
| 10.192.31.23 10.192.1.120:8082   |    1 |
| 10.192.31.23 Total               |    1 |
| 192.168.31.131 192.168.31.130:80 |    1 |
| 192.168.31.131 Total             |    1 |
| Total                            |    5 |
+----------------------------------+------+
10 rows in set (0.00 sec)

mysql>


Return list of columns in user-provided subquery

Just pull the DataTable object(s) and enumerate the DataColumnCollection.  Here is some sample code to show how you can enumerate the collection and dump the column names:

DataTable queryResults = // get your data

foreach (DataColumn column in queryResults.Columns)
    Console.WriteLine("Column Name :: {0}", column.ColumnName);

Of course this is an extremely rudimentary illustration, but it shows what you need:


Are PostgreSQL cursor bound to connection?

Yes, cursors are not shared across sessions, and their namespace is private to their session as well.

Moreover, if the cursor is not declared "holdable" trough the WITH HOLD clause, it's automatically closed at the end of the transaction.

The relationship between cursor lifespan and session is detailed in the docpage for CLOSE:

  Every non-holdable open cursor is implicitly closed when a transaction
  is terminated by COMMIT or ROLLBACK. A holdable cursor is implicitly
  closed if the transaction that created it aborts via ROLLBACK. If the
  creating transaction successfully commits, the holdable cursor remains
  open until an explicit CLOSE is executed, or the client disconnects.


What is the correct name and significance of EAV table pivot?

The process of pivoting data is often called transposition, as it is similar to the matrix operation of that name in mathematics (http://en.wikipedia.org/wiki/Transpose), though that isn't really what you are doing in your example.

In your output you still have the EAV pattern but stored less efficiently. The columns "Entity, A_Name1, A_Value1" is one attribute in the EAV pattern, "Entity, A_Name2, A_Value2" is another, and so forth. Each of your value columns could contain a range of probably unrelated attribute values so you are not in any sort of normal form and reporting is likely to be even more difficult than the original layout.

A proper transposition in this case would be:

Entity  | Attribute1 | Attribute2 | Attribute2
----------------------------------------------
Entity1 | Value1     | NULL       | NULL
Entity2 | Value2     | Value3     | Value4
Entity3 | Value5     | Value6     | NULL

This way each column contains data for only one attribute.


MongoDB Replica Set Can't be connected To

It turns out that the host wasn't properly added to /etc/hosts. Adding it seemed to solve the problem.


I think you should parse the text/Elements ,so that you can obtain the desired string.

SELECT
 Tab.Col.value('id[1]', 'varchar(50)') AS Id,
 Tab.Col.value('text[1]', 'nvarchar(MAX)') AS TextAsNVarChar
,(SELECT CHAR(t.u.value('.','nvarchar(max)'))
        FROM Tab.Col.nodes('text/Element') AS t(u)
        FOR XML PATH('')
 )TextAsNVarChar_New
FROM @testxml.nodes('/ProgramWzgSignStates/WzgSignStates/Element') AS Tab(Col)

I used FOR XML PATH to obtain 2Hallo

Id                 TextAsNVarChar    TextAsNVarChar_New
AQ_A07_1_280_F3~ZA 507297108108111   2Hallo


First: as noted by joanolo, the code you posted (when added to the set-up in your SQLFiddle link) generates a completely different error.

I obviously cannot guarantee that my changes are correct, but it appears that you were having problems with your JOIN clauses. The ON conditions for the JOINs weren't lined up correctly.

I've modified them as follows:

    FROM `___BillableDatas` d
    JOIN ___Bookings b
    ON d.BIL_BookingId = b.BOO_Id
    JOIN ___Rates r
    ON d.BIL_RateId = r.RAT_Id
    WHERE r.RAT_HotelId = 'cus_AjGG401e9a840D'
    AND d.BIL_Date BETWEEN "2017-01-01" AND "2017-12-31"
    AND d.BIL_Type="Night"

Note: I moved the d.BIL_Date and d.BIL_Type checks to the WHERE clause. It's not necessarily going to generate incorrect results to put them in the JOIN, but I generally recommend that only the checks that are explicitly tied to how the two tables' rows relate to each other be included in the ON clause; it helps clarify your intentions, and makes changing the query later easier.

And here's a SQLFiddle link with the modified code included. It returns a single row.

Now: based on the error you report, I don't think it's necessarily a MySQL issue. It seems likely that you're running the SQL command from somewhere else (PHP code, for instance), and that you've got a problem with how you're passing in your parameters to the statement. I've seen :hotel_id used in such code to indicate a parameter to be replaced, and the fact that you need to use the same value at two points in the code may be causing your problem. It's a direction to look, at least.

Hope this helps.


In your code, you are locking the following records in the c1 cursor result set for the column sal:

     EMPNO ENAME             SAL DNAME
---------- ---------- ---------- --------------
      7782 CLARK            9451 ACCOUNTING
      7839 KING            12001 ACCOUNTING
      7934 MILLER           8301 ACCOUNTING

For example, from another session this query

SELECT sal FROM emp FOR UPDATE;

will block until the next commit or rollback statement will be issued (check out v$session).

I guess the problem is that you are updating the sal column values of table emp1 (and not emp one), but these records are not locked. You plan on updating emp1 records that have not been referenced by the previous SELECT FOR UPDATE statement, so their ROWIDs become invalids.
From here: The WHERE CURRENT OF statement allows you to update or delete the record that was last fetched by the cursor.

In fact, if you change the code like this:

...
IF c2.dname = 'ACCOUNTING' THEN
    UPDATE emp1
    SET    sal = sal + 1000;
END IF;
...

or if you replace emp1 with emp:

...
IF c2.dname = 'ACCOUNTING' THEN
    UPDATE emp
    SET    sal = sal + 1000;
    WHERE  CURRENT OF c1;
END IF;
...

it would work.


SQL Server Partitioning vs Indexes on Separate Filegroups

First and foremost: those logical disks better be backed by at least 8 different physical disks. If you're asking about load balancing 8 logical disks created on the same physical storage (the same spindles) then you're wasting time.

The best (and simplest!) option is to create a single filegroup with 8 files (equal in size and pregrown), each on a spindle, and then place the table and the indexes in this filegroup. SQL Server will balance the data equally among the files.

Partitioning is a feature for ETL switch in and switch out. It should not be used for performance, as the best you can hope for is equal performance with the original table. For performance use a well designed clustered index, one that matches the typical load.

If your data is really known upfront and the index usage characteristics are very well understood then you may try to balance them explicitly on their own filegroups. But trying to wrestle manual control over this is more likely to cause harm than benefit. The simpler option of single file group with 8 files balances IO better than manual explicit control 99% of the times.


You are right, the columns in a SELECT at the same level can't refer to each other. Use a subquery instead:

SELECT
  sum_a as a
  sum_b as b
  round(sum_a / sum_b) as a_over_b
FROM (
  SELECT
    sum(column_a) as sum_a,
    sum(column_b) as sum_b
  From
    table
  Group by column
) as subq


Create view with group by and extra fields

If you want to filter rows before the aggregation, I would use an inline table-valued function instead of a view:

CREATE FUNCTION dbo.PlaceStatisticsForDate
    (@DateFilter AS date)
RETURNS TABLE
AS
RETURN
    SELECT COUNT(PS.TypeID) AS TypeCount, PS.TypeID
    FROM dbo.PlaceStatistics AS PS
    WHERE PS.mydatefield = @DateFilter
    GROUP BY PS.TypeID;

Usage:

SELECT PSD.TypeCount, PSD.TypeID
FROM dbo.PlaceStatisticsForDate ('20160506') AS PSD;


You are facing a bug. You need to gather more information using RDA in order to perform an in-depth analysis.

These are known bugs related to your ORA-600 message.

10043021    11.2.0.4, 12.1.0.1  ORA-600 [kcbnew_3] after select / analyze on RECYCLEBIN object

17586955    11.2.0.4.4, 11.2.0.4.BP11   ORA-600 [ktspfmdb:objdchk_kcbnew_3] in RAC

12747437    11.2.0.3.8, 11.2.0.3.BP21, 11.2.0.4, 12.1.0.1   ORA-600 [ktspfmdb:objdchk_kcbnew_3] after purging single consumer queue table

12323180    11.2.0.2.7, 11.2.0.2.BP17, 11.2.0.3, 12.1.0.1   ORA-600 [ktspfmdb:objdchk_kcbnew_3] due to re-used block read into cache

11902008    11.2.0.4, 12.1.0.1  SMON may crash with ORA-600 [kcbgcur_3] or ORA-600 [kcbnew_3] during Transaction recovery

10137834    11.2.0.2    ORA-600 running DBMS_XDB.MOVEXDB_TABLESPACE

9456964     11.2.0.2    OERI[kcbnew_3] after shrinking an IOT and reusing the blocks freed by shrink

Look if any of the above descriptions matches your case.

But the short answer would be to open a Service Request with Oracle Support.


How to retrieve a large text from inside a table?

There's another way similar to Aaron's, using the little-documented processing-instruction... thing.
Using this gives you unchecked XML, which will won't replace predefined entities.
Also, the column will have XML identifying marks at the beginning and end, which may be another drawback depending on how you want to use the output.
But it'll be clicky and show you the whole thing!
CREATE TABLE dbo.LongText (
    Id INT IDENTITY,
    LongText_N NVARCHAR(MAX),
    LongText_X XML
    );

INSERT dbo.LongText ( LongText_N, LongText_X )
SELECT asm.definition, (SELECT asm.definition AS [processing-instruction(_)] FOR XML PATH(''))
FROM sys.all_sql_modules AS asm

SELECT * FROM dbo.LongText AS lt


As Mark Sinkinson said storing comma separated list in the table is the bad idea.Reason there is a better structure to store list that is 'TABLE' :).

Instead you can create third table to store belonging club information.
However you can get your result using the following query.

SELECT tbl1.id,
TRIM(REGEXP_SUBSTR(tbl1.belongto, '[^,]+',1, lines.column_value)) Belongto
FROM tbl1,
TABLE(CAST(MULTISET(SELECT level FROM dual CONNECT BY INSTR(tbl1.belongto,',',1, level-1)>0) AS SYS.odciNumberList))lines;

And look at the plan of the above query its quite expensive.:(

| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU) |Time     |

--------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      | 16336 |   430K|    65  (11)| 00:00:01 |

|   1 |  NESTED LOOPS               |      | 16336 |   430K|    65  (11)| 00:00:01 |

|   2 |   TABLE ACCESS FULL         | TBL1 |     2 |    50 |     2   (0)| 00:00:01 |

|   3 |   COLLECTION ITERATOR SUBQUERY FETCH|      |  8168 | 16336 |    32  (13)| 00:00:01 |

|*  4 |    CONNECT BY WITHOUT FILTERING     |      |       |       ||      |

|   5 |     FAST DUAL               |      |     1 |       |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

So consider to change your design for betterment.
Thanks


"Merge" two rows in a Postgres table, with foreign keys

Assuming you just want to delete duplicates in books after merging duplicate authors.
BEGIN;
LOCK books, authors;

CREATE TEMP TABLE dupes ON COMMIT DROP AS (SELECT 2 AS dupe, 1 AS org);

DELETE FROM books b  -- delete duplicate books
USING  dupes d
WHERE  b.author_id = d.dupe
AND    EXISTS (
   SELECT 1
   FROM   books
   WHERE  title = b.title
   AND    author_id = d.org
   );

UPDATE books b  -- now we relink all remaining books
SET    author_id = d.org
FROM   dupes d
WHERE  b.author_id = d.dupe;

DELETE FROM authors a  -- now we can delete all dupes
USING  dupes d
WHERE  a.id = d.dupe;

COMMIT;

The temp table could hold many rows to remove many dupes at once.
Repeat the first two steps for every tables referencing authors.id. If there are many I would create and execute the statements dynamically ...
I lock the tables explicitly to avoid concurrent disturbances.
Automation
A basic function could look like this:
CREATE OR REPLACE FUNCTION f_remove_dupe(_tbl text, _col text, _dupe int, _org int)
  RETURNS void  AS
$func$
DECLARE
   _ftbl text;
   _fcol text;
BEGIN
   FOR _ftbl, _fcol IN
      -- table and column name behind all referencing FKs
      SELECT c.conrelid::regclass::text, f.attname
      FROM   pg_attribute  a
      JOIN   pg_constraint c ON a.attrelid = c.confrelid AND a.attnum = c.confkey[1]
      JOIN   pg_attribute  f ON f.attrelid = c.conrelid AND f.attnum = c.conkey[1]
      WHERE  a.attrelid = _tbl::regclass
      AND    a.attname  = _col
      AND    c.contype  = 'f'
   LOOP
      EXIT WHEN _ftbl IS NULL;  -- skip if not found
      EXECUTE format('
         UPDATE %1$s
         SET    %2$I = $2
         WHERE  %2$I = $1'
       , _ftbl, _fcol)
      USING _dupe, _org;
   END LOOP;

   EXECUTE format('
      DELETE FROM %I WHERE %I = $1'
    , _tbl, _col)
   USING _dupe;
END
$func$ LANGUAGE plpgsql;

Call:
SELECT f_remove_dupe('authors', 'id', 2, 1);

This simple version ...

... only works for a single dupe.
... ignores UNIQUE constraints in referencing tables.
... assumes all FK constraints only use the one column, ignoring multi-column FKs
... ignores possible interference from concurrent transactions.

Adapt to your requirements.
Related:

Find referenced field(s) of foreign key constraint
Can't execute dynamic DDL, argument is NULL
SQL injection in Postgres functions vs prepared queries


use explain on your query (it will provide output on your execution)
add index on your table


MySQL DB Causing Spike-up on Windows Server CPU

If not impacting business, have you tried to kill the connection app-side?

We had an issue with locked/active sessions from an app to a MSSQL DB, however the only way we could bypass was to break the active connection from the app to the DB via failover. That was only because it shifted resources from node 1 to node 2. We attempted to kill the connection via MSSQL queries but couldn't get it to stop that way, so failover ended up being the solution for us.

Note: I am still new to DBA, but figured it's worth a try to share what I've experienced in my short time in this role.


How to clone a table schema (including indices) in SQL Server?

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

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

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


Most efficient way to insert data into archiving table without influencing production?

You probably cannot find any articles on the topic because it is so simple:

WITH old_rows AS (
   DELETE FROM t
   WHERE ...
   RETURNING *
)
INSERT INTO tarchive
SELECT * FROM old_rows;

This won't lock table t very much — concurrent SELECTs and data modifications won't be affected (unless they are trying to modify one of the deleted rows).

Your main problems are:

This statement may take a long time, holding locks and blocking autovacuum as it runs.
Afterwards, the table will not be smaller, but emptier. This hurts sequential scans and wastes cache space.

Particularly the second problem hurts, and the remedies all require making the table unavailable for a time:

VACUUM (FULL) will reorganize the table, but blocks any access to the table while it runs.
Replacing the table with a different one will block data modifications for a while:

BEGIN;
LOCK TABLE bloated IN SHARE ROW EXCLUSIVE MODE;
CREATE TABLE bloated_copy (LIKE bloated INCLUDING ALL);
INSERT INTO bloated_copy SELECT * FROM bloated;
DROP TABLE bloated;
ALTER TABLE bloated_copy RENAME TO bloated;
COMMIT;

Because of all that, it is a good idea to plan how you want to get rid of old data right when you design your system, but in my experience this is almost always forgotten. The least painful way to get rid of old data is to partition the tables by time.


OK, I've refactored (hopefully for the last time) for your new data and to use a calendar table.  In this simple demo I create and populate the calendar table (for years 2000 to 2030), then do the day|month|year code.  This version honours the time component and will scale well.  The calendar table is one I use in my dev.

Please note this is intended as a demo, so please double-check the results:

USE tempdb
GO

IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = 'calendar' )
EXEC('CREATE SCHEMA calendar')
GO

-- DROP TABLE calendar.main
IF OBJECT_ID('calendar.main') IS NULL
BEGIN

    CREATE TABLE calendar.main (
        calendarId      INT IDENTITY(1,1) NOT NULL,
        calendarDate    DATE NOT NULL,
        calendarDay     INT NOT NULL,
        calendarMonth   INT NOT NULL,
        calendarYear    INT NOT NULL,
        [dayOfYear]     INT NOT NULL,
        [week]          INT NOT NULL,
        [quarter]       INT NOT NULL,
        datenameDay     VARCHAR(9) NOT NULL,
        datenameMonth   VARCHAR(9) NOT NULL,
        academicMonth   INT NOT NULL,
        academicYear    INT NOT NULL,
        isWeekday       BIT NULL,
        isWeekend       BIT NULL,
        lastDayOfMonth  BIT NULL,

    CONSTRAINT PK_calendar__main PRIMARY KEY ( calendarDate ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    CONSTRAINT UK_calendar__main UNIQUE NONCLUSTERED ( calendarId ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    -- Add indexes
    CREATE INDEX idx_calendar__main__calendarYear ON calendar.main ( calendarYear )
    CREATE INDEX idx_calendar__main__calendarMonth ON calendar.main ( calendarMonth )
    CREATE INDEX idx_calendar__main__calendarDay ON calendar.main ( calendarDay )
    CREATE INDEX idx_calendar__main__calendarYearMonthDay ON calendar.main ( calendarYear, calendarMonth, calendarDay )

    CREATE INDEX idx_calendar__main__academicYear ON calendar.main ( academicYear )
    CREATE INDEX idx_calendar__main__academicMonth ON calendar.main ( academicMonth )
    CREATE INDEX idx_calendar__main__academicYearMonth ON calendar.main ( academicYear, academicMonth )

    --!!TODO add any indexes you need

    ALTER TABLE calendar.main ADD  DEFAULT ((0)) FOR isWeekday
    ALTER TABLE calendar.main ADD  DEFAULT ((0)) FOR isWeekend
    ALTER TABLE calendar.main ADD  DEFAULT ((0)) FOR lastDayOfMonth
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((academicMonth>=(1) AND academicMonth<=(12)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((academicYear>=(1999) AND academicYear<=(2030)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((calendarDay>=(1) AND calendarDay<=(31)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((calendarMonth>=(1) AND calendarMonth<=(12)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((calendarYear>=(2000) AND calendarYear<=(2030)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  (([dayOfYear]>=(1) AND [dayOfYear]<=(366)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  (([quarter]>=(1) AND [quarter]<=(4)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  (([week]>=(1) AND [week]<=(54)))

END
GO

-- Populate calendar table once only
IF NOT EXISTS ( SELECT * FROM calendar.main )
BEGIN

    -- Populate calendar table
    WITH cte AS
    (
    SELECT 0 x
    UNION ALL
    SELECT x + 1
    FROM cte
    WHERE x < 11323 -- Do from year 1 Jan 2000 until 31 Dec 2030 (extend if required)
    )
    INSERT INTO calendar.main ( calendarDate, calendarDay, calendarMonth, calendarYear, [dayOfYear], [week], [quarter], datenameDay, datenameMonth, academicMonth, academicYear, isWeekday, isWeekend, lastDayOfMonth )
    SELECT
        calendarDate,
        DAY( calendarDate ) cDay,
        MONTH( calendarDate ) cMonth,
        YEAR( calendarDate ) cYear,
        DATENAME( dayOfYear, calendarDate ) cdayOfYear,
        DATENAME( week, calendarDate ) cWeek,
        DATENAME( quarter, calendarDate ) cQuarter,
        DATENAME( dw, calendarDate ) cdw,
        DATENAME( month, calendarDate ) cMonthName,
        MONTH( academicDate ) aMonth,
        YEAR( academicDate ) aYear,
        CASE WHEN DATENAME( dw, calendarDate ) Not In ( 'Saturday', 'Sunday' ) THEN 1 ELSE 0 END,
        CASE WHEN DATENAME( dw, calendarDate ) In ( 'Saturday', 'Sunday' ) THEN 1 ELSE 0 END,
        CASE WHEN DAY( DATEADD( day, 1, calendarDate ) ) = 1 THEN 1 ELSE 0 END

    FROM
        (
        SELECT
            DATEADD( day, x, '1 Jan 2000' ) calendarDate,
            DATEADD( month, -7, DATEADD( day, x, '1 Jan 2000' ) ) academicDate
        FROM cte
        ) x
    WHERE calendarDate < '1 Jan 2031'
    OPTION ( MAXRECURSION 0 )

    ALTER INDEX ALL ON calendar.main REBUILD

END
GO

IF OBJECT_ID('dbo.records') IS NOT NULL DROP TABLE dbo.records
CREATE TABLE dbo.records (
    Id              INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    period          VARCHAR(10) NOT NULL,
    startDateTime   DATETIME NOT NULL,
    endDateTime     DATETIME NOT NULL
)
GO

INSERT INTO dbo.records ( period, startDateTime, endDateTime )
VALUES
    ( 'day', '2014-01-01 06:10:10', '2014-01-05 18:20:30' ),
    ( 'day', '2014-01-10 04:40:20', '2014-01-15 02:50:10' ),
    ( 'day', '2014-01-11 04:10:10', '2014-01-11 08:00:10' ),

    ( 'month', '2014-01-05 14:20:30', '2014-06-05 12:30:40' ),
    ( 'month', '2014-10-10 06:50:40', '2014-12-15 04:10:20' ),
    ( 'month', '2014-10-10 06:10:50', '2014-10-10 06:30:10' ),

    ( 'year', '2015-03-05 14:10:30', '2018-02-06 05:20:40'),
    ( 'year', '2019-02-04 12:32:23', '2022-03-09 22:34:12'),
    ( 'year', '2020-04-05 06:05:04', '2020-06-03 09:07:45')

GO

DECLARE @period VARCHAR(10) = 'year'    -- 'day'|'month'|'year'

IF @period = 'day'
BEGIN

    SELECT
        r.Id,
        r.startDateTime,
        ISNULL( CAST( c.calendarDate AS DATETIME ), r.endDateTime ) AS endDateTime
    FROM dbo.records r
        OUTER APPLY ( SELECT TOP 1 * FROM calendar.main c WHERE c.calendarDate Between r.startDateTime And r.endDateTime ) c
    WHERE r.period = 'day'

    UNION ALL

    SELECT
        r.Id,
        CAST ( c.calendarDate AS DATETIME ) startDateTime,
        CASE WHEN c2.calendarDate > endDateTime THEN endDateTime ELSE c2.calendarDate END
    FROM dbo.records r
        INNER JOIN calendar.main c ON c.calendarDate Between r.startDateTime And r.endDateTime
            CROSS APPLY ( SELECT TOP 1 * FROM calendar.main c2 WHERE c2.calendarDate > c.calendarDate ) c2
    WHERE r.period = 'day'
    ORDER BY Id, startDateTime, endDateTime

END
ELSE IF @period = 'month'
BEGIN

    SELECT
        r.Id,
        r.startDateTime,
        ISNULL( CAST( c.calendarDate AS DATETIME ), r.endDateTime ) AS endDateTime
    FROM dbo.records r
        OUTER APPLY (
            SELECT TOP 1 *
            FROM calendar.main c
            WHERE c.calendarDate Between r.startDateTime And r.endDateTime
              AND c.calendarDay = 1
              ) c
    WHERE r.period = 'month'

    UNION ALL

    SELECT
        r.Id,
        CAST ( c.calendarDate AS DATETIME ) startDateTime,
        CASE WHEN c2.calendarDate > endDateTime THEN endDateTime ELSE c2.calendarDate END
    FROM dbo.records r
        INNER JOIN calendar.main c ON c.calendarDate Between r.startDateTime And r.endDateTime
            CROSS APPLY ( SELECT TOP 1 * FROM calendar.main c2 WHERE c2.calendarDate > c.calendarDate AND c2.calendarDay = 1 ) c2
    WHERE r.period = 'month'
      AND c.calendarDay = 1
    ORDER BY Id, startDateTime, endDateTime

END

ELSE IF @period = 'year'
BEGIN

    SELECT
        r.Id,
        r.startDateTime,
        ISNULL( CAST( c.calendarDate AS DATETIME ), r.endDateTime ) AS endDateTime
    FROM dbo.records r
        OUTER APPLY (
            SELECT TOP 1 *
            FROM calendar.main c
            WHERE c.calendarDate Between r.startDateTime And r.endDateTime
              AND c.dayOfYear = 1
              ) c
    WHERE r.period = 'year'

    UNION ALL

    SELECT
        r.Id,
        CAST ( c.calendarDate AS DATETIME ) startDateTime,
        CASE WHEN c2.calendarDate > endDateTime THEN endDateTime ELSE c2.calendarDate END
    FROM dbo.records r
        INNER JOIN calendar.main c ON c.calendarDate Between r.startDateTime And r.endDateTime
            CROSS APPLY ( SELECT TOP 1 * FROM calendar.main c2 WHERE c2.calendarYear > c.calendarYear
                AND c2.dayOfYear = 1 ) c2
    WHERE r.period = 'year'
      AND c.dayOfYear = 1
    ORDER BY Id, startDateTime, endDateTime

END

ELSE
    RAISERROR( 'Invalid period specified (%s)', 16, 1, @period )
GO


If you only want to update data, I'm not sure what the INSERT statement is for in your question.

If you just want to update several rows with a single statement, you might be looking for this:

with update_values (ID,PARENT_ID,BOUGHT_IN_FORM_TYPE_ID,PRIORITY,NAME,HEADING,DESCRIPTION,ICON,BOUGHT_IN_CONTROL_PANEL_FILE_ID) as
(
  VALUES
     (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null),
     (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
)
update bought_in_control_panel
   set parent_id = ud.parent_id,
       bought_in_form_type_id = ud.bought_in_form_type_id,
       ....
from update_values ud
where ud.id = bought_in_control_panel.id;


How to find how many number of transactions are happening in SQL Server/SQL Server database in a day?

The Perfmon counters for Transactions/Sec and Batch Requests/Sec are cumulative counters, meaning they keep going up after the SQL Server starts up.

To get a count per day since startup, divide the counter output by the number of days that the server has been up. An easy way to do that is to check the creation date of TempDB - here's an example query, but keep in mind that rounding will be a problem if the server's been up for less than a day or two:

SELECT DATEDIFF(dd, create_date, GETDATE())
FROM sys.databases
WHERE database_id = 2;

To get daily numbers going forward, consider saving the contents of that counter to a table. Then you can take a day's metric, subtract the day before, and you'll get the counter delta for that one day. (I use sp_BlitzFirst and the Power BI Dashboard for DBAs to do this with 15-minute granularity. (Disclaimer: those are open source tools that I wrote.))

Unfortunately, if you try to do daily sampling, it can be inaccurate due to:

The server restarting or clustered SQL Server service failing over
The Availability Group or database mirror transitioning roles (failing over) does not reset those counters, but you'll need to add up the transactions or batches from both replicas
If you're combining metrics from multiple replicas, keep in mind that you've also probably got monitoring software, and the monitoring software's queries are also going to be counted


DB2 database -> does it continue to be platform-specific? (iSeries, LUW, z/OS)

I don't think your assumption about "different drivers to connect to DB2 for iSeries and DB2 for Linux, UNIX and Windows" is correct. There is a single IBM Data Server Client that is used to connect to DB2 for LUW, DB2 for System i, DB2 for z/OS, and Informix servers.

However, what you quote seems to reference DB2 server software, not drivers, and there are still different versions of DB2 servers for LUW, System i, and z/OS. They used to have quite different SQL dialects, but these differences are being reduced.


Source for extended-event category and channel information?

That information is available in the sys.dm_xe_* DMVs, but I had some trouble finding it.

Every event has a special, read-only column called "CHANNEL" in the sys.dm_xe_object_columns DMV.  The column is of type "etw_channel", and its value is in the column_value column of the DMV.  The column's value can be looked up in the sys.dm_xe_map_values DMV to get the channel name.

The category can be retrieved in a similar fashion, but with two wrinkles: the read-only column is called "KEYWORD", and 27 columns (in SQL Server 2014, at least) don't have that column.

The query below demonstrates how to get the information.  In particular, it returns the same information you see in the GUI when first choosing events.  The events are ordered by name, events from the "Debug" channel are filtered out, and 27 of them have a NULL category.

SELECT
        dxo.name,
        'Category' = camv.map_value,
        'Channel' = chmv.map_value
    FROM sys.dm_xe_objects AS dxo
    LEFT JOIN sys.dm_xe_object_columns AS cac ON 1=1
        AND cac.object_package_guid = dxo.package_guid
        AND cac.object_name = dxo.name
        AND cac.name = 'KEYWORD'
    LEFT JOIN sys.dm_xe_map_values AS camv ON 1=1
        AND camv.object_package_guid = cac.type_package_guid
        AND camv.name = cac.type_name
        AND camv.map_key = cac.column_value
    JOIN sys.dm_xe_object_columns AS chc ON 1=1
        AND chc.object_package_guid = dxo.package_guid
        AND chc.object_name = dxo.name
        AND chc.name = 'CHANNEL'
    JOIN sys.dm_xe_map_values AS chmv ON 1=1
        AND chmv.object_package_guid = chc.type_package_guid
        AND chmv.name = chc.type_name
        AND chmv.map_key = chc.column_value
    WHERE 1=1
        AND dxo.object_type = 'event'
        AND chmv.map_value <> 'Debug'
    ORDER BY
        dxo.name


node.js needs something like this to start with:

var connection = mysql.createConnection({ ... , charset : 'utf8'});

Yeah, ασδασδ looks Greek to me.  What you have is "Mojibake".  See https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored for discussion of what causes Mojibake.

Do not use any form of encode/decode/conv -- it only compounds the problem. If that link does not help you, provide the extra details that it requests.

See also https://www.npmjs.com/package/utf8

The form should mention utf-8:

<form method="post" action="/your/url/" accept-charset="UTF-8">

Analysis...

'Κ' (Greek Kappa) is the 2 bytes, hex 'CE9A', in utf8.
   'Îš' in latin1 is hex 'CE9A' (2 bytes)
   'Îš', if converted to utf8 is hex 'C38E C5A1'
   'ÃŽÅ¡' is that that looks like when interpreted as latin1.

Note: this last hex is what you show in your Comment.

So, you seem to have "double encoding".

SELECT CONVERT(BINARY(CONVERT(CONVERT(BINARY(CONVERT('ÃŽÅ¡' USING latin1)) USING utf8mb4) USING latin1)) USING utf8mb4);  --> 'Κ'

What is missing is where "latin1" is occurring in the flow of the data.


Will mysql_secure_install script block master updates to slave?

If by disable remote login you mean it added skip-networking, you'll want to remove that from your my.cnf and restart mysql


Oracle: how to query a table if I know only its (non-default) tablespace?

Given your DBA_TABLES query, you have the following:

Owner (Schema Name): EXAMPLEOWNER

Table Name: MYTABLE

The SYSTEM user will not just be able to select from MYTABLE unless there is a Public Synonym pointing to it.

So, to select from the table you have to fully qualify the name by prefixing the table with its schema:

SELECT * FROM EXAMPLEOWNER.MYTABLE;

Note that tablespaces are simply the place the object is stored in, and have no bearing in DML queries.


There are three ways to accomplish this. Since you've already mentioned Always Encrypted, I must ask why you would need to give the certificate to developers in the production environment. Without the certificate, the column encryption key can't be decrypted.

Second, you can set up column level encryption and drop the key on the certificate or asymmetric key so data can only be encrypted and not decrypted, then replicate it to a more secure server that has the private key.

The option that will most likely apply is a hardware security module. Starting in SQL Server 2008 R2 Enterprise Edition, Microsoft included interfaces to integrate Extensible Key Management systems. This is a hardware module that offloads all encryption and decryption functions, including the storage of keys. If the DBA and developers do not have permissions to this system, then they will not be able to decrypt.


Upgrade from Postgres 11 to Postgres 12 made some queries 300x slower, probably due to new CTE handling

As pointed out in the answer to my other, more specific question, the reason is Just-in-time compilation.

SET jit = false; solves all performance problems in my case.


Well I would like to comment on further aspect of using if-else with exists.

It's best to avoid using if-else with exists. Why?

You need to make sure at least one record exists in your table
This method does not allow for concurrency (specially in insert, updates transactions, it could end up creating deadlock)

Instead you may use WHERE EXISTS

Reference materials that explains well: avoid if else with exists in sql

And to prove your colleagues wrong ;) check this article out too. I am not going to re-write Author's data here. But the tag line is worth re-writing again and again.

" The other benefit of EXISTS is that once it finds a single record that matches it stops processing. This doesn't have a huge impact if you're checking on a primary key. It does have a big impact if you're checking for existance based on another field. "

e.g. from article

if exists (select *
    from authors
    where state = 'ca')
  Print 'Record exits'
ELSE
  Print 'Record doesn''t exist'

if (select count(*)
    from authors
    where state = '172-32-1176') > 0
  Print 'Record exits'
ELSE
  Print 'Record doesn''t exist'

In the pubs database there are only 23 records in the authors table. Even with that small number of records, the IF EXISTS version runs 4 times faster than selecting a count. This is because it stops as soon as it finds a single record that matches the criteria. The second statement must process all the rows that match. --<<--taboo :D

Reference : using if exists vs if count(*)


MySQL: self-administration for multiple users

I think using a MySQL gui tool like SQLyog would solve your problem. It has User manager tool where you can set privilege to different users.


switch TOP clause between percentage and rows by parameter

I'd have 2 separate queries/stored procedures just to avoid dynamic SQL.

An IF statement in one stored proc would be enough too


How to drop java source from Oracle database properly?

Each element of a name must be quoted individually:

drop java source "SCHEMA"."JAVASOURCENAME";

The identifier "SCHEMA.JAVASOURCENAME" refers to a java source name SCHEMA.JAVASOURCENAME in the current schema, e.g. "VORIAND"."SCHEMA.JAVASOURCENAME"


SQL Server Replication Agent Profile -Output Parameter

That link is for logread.exe.  Checking the page for the stored procedure, see:

http://msdn.microsoft.com/en-us/library/ms190313%28v=SQL.100%29.aspx

I'm wondering if the hyphen is needed, as it is not listed like that on the stored procedure's page.


Try

UPDATE Tab1 JOIN Tab2 ON Tab1.b = Tab2.b
SET Tab1.a = '3'
WHERE Tab1.c = '4'
;

Check the documentation for further information (look for 'multiple-table syntax').  An important point is:

  If you use a multiple-table UPDATE statement involving InnoDB tables
  for which there are foreign key constraints, the MySQL optimizer might
  process tables in an order that differs from that of their
  parent/child relationship. In this case, the statement fails and rolls
  back. Instead, update a single table and rely on the ON UPDATE
  capabilities that InnoDB provides to cause the other tables to be
  modified accordingly. See Section 14.3.5.4, “FOREIGN KEY Constraints”.

You may check a similar example on SQLFiddle.


Beginner Question: Trouble turning a field with comma separated values into a multi-valued field

I think your best bet would be to use a many to many relationship. Generally this is done with a table for the records (like you have now), a table for the comma delimited field and a primary key field with rows for each of the entries (one for x, one for y and one for z) and a third table with nothing but that keys from the other 2 tables.

So, in your join table, you might have a section of 3 rows with the same key on the main table but different entries for the reference table.  This link explains it reasonably well.


adcfgclone.pl dbconfig fails when trying to configure the database to use logical host names

Vip name from /etc/hosts was being used for the setup and causing the error.
Corrected the context file to use the name specifies with physical name and the issue was resolved.


Is it poor practice to aggregate data from different tables into one?

If I understood you correctly,

you have a large third-party system,
you don't have much control over it,
you make complex reports that read data directly from this third-party database,
your queries depend on the internal structure of the third-party database.

I would approach it like this:

Set up my own separate database, which I have full control of.
Set up a sync process that reads data from relevant tables and columns from the third-party database and inserts/updates into mine.
Develop my complex reports based on the stable structure of my database.

In this case you can fine-tune structure and indexes of your database to improve performance of your reports, without affecting third-party system.
Unless the original data structure changes dramatically, the logic of your queries for your reports would not change if third-party database changes. You would have to adjust only the sync process.

The sync process is effectively the conversion process - you convert data from third-party database into the structure that you need. Part of this conversion process could be fixing any normalization problems that original third-party database may have. Only this part of the system has to know and depend on the internal structure of third-party system. Your main reports and main queries would depend only on your database.

So, the main point is - separate and limit the part of your system that depends on internals of third-party system.

update

Regarding real-time requirement. BTW, I always thought that definition of "real-time" is "guaranteed response time", not "some small response time". It depends on your application, of course. In my practice it is enough if I sync two databases within a minute of detected change. If a user sees a report on screen and some underlying data changes, the report has to be somehow re-run to reflect this change. You can poll for changes or listen to some event/message, still the report query has to be executed again to show the latest changes.

You already intend to write triggers to capture changes in the original tables and write these changes to one generic table. So, capture changes as you intended, but write them to properly normalized tables, not a single one.

So, this is extreme case - conversion of third-party data structure into your internal data structure is performed in the triggers that fire on INSERT/UPDATE/DELETE of third-party tables. It can be tricky. The code of triggers would depend on internal structure of both systems. If conversion is non-trivial it may delay original INSERT/UPDATE/DELETE to the point of their failure. If there is a bug in your trigger it may affect original transaction to the point of their failure. If third-party system changes it may break your trigger, which would cause transactions of third-party system to fail.

Less extreme case. To make the code of your triggers simpler and less prone to errors write all captured changes to some staging/audit/diff table(s), set some flag/send a message that there are changes pending and launch the main conversion process that would go through these intermediary tables and perform conversion. The main thing here is that potentially heavy conversion process should happen outside the scope of the original transaction.

At a second glance it looks pretty much like your original suggestion in the question. But, the difference is: the capture-all tables hold data only temporarily; the amount of data is small - just what has changed; it doesn't have to be a single table; eventually the data will be stored in separate properly normalized permanent tables, which you have full control of, which are independent from third-party system and which you can tune for your queries.


"Cannot open database version 706. Upgrade the database to the latest version" - when trying to use availability database for read-only access

What do you suggest on above ?
I need to keep primary SQL server replica as 2012 for now, and I need to make >async replica (SQL server 2016) to be available for read-only access

There is nothing you can do, this is a known item:

During a version upgrade, readable secondaries cannot be read after an
upgrade of the readable secondary and before either the primary
replica is failed over to an upgraded secondary or the primary replica
is upgraded.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/upgrading-always-on-availability-group-replica-instances


Retrieve total available days in date range compared against multiple records

First solution

Well, I tried a solution. It works but it is pretty ugly. But it works...

SELECT count(*)
FROM (
    SELECT code, dates.selected_date
    FROM appartments
    INNER JOIN (select * from
      (select adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3) v
    WHERE selected_date BETWEEN '2015-04-16' AND '2015-04-28') dates
    WHERE (code, selected_date) NOT IN (
        SELECT code, dates.selected_date
        FROM appartments
        INNER JOIN (select * from
          (select adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
           (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
           (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
           (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
           (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3) v
          ) dates ON dates.selected_date between date_arrival and date_departure)
    GROUP BY code, dates.selected_date) available_dates_by_code

Change the date period in the line WHERE selected_date BETWEEN '2015-04-16' AND '2015-04-28') dates.

Remove the first level SELECT FROM to get all dates of unoccupied apartments for dates between '2015-04-16' AND '2015-04-28'.

You may want to change the '2015-01-01' dates to something earlier (ie. CURDATE() if you're only working with future dates). This query will only return next 30 years dates past '2015-01-01', so change it to something like CURDATE() - '1 YEAR'

I'm very curious to see if someone have a better solution...

How it works

From the bottom to the top :

The first SELECT gets all occupied dates for all appartments.
The second SELECT gets all dates wanted and removes all appartment/date couples that are occupied.
The third select count the number of appartement/date couple available between the provided dates.

Second solution

SELECT
  (
    SELECT adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date
    FROM apartments
    INNER JOIN (SELECT * FROM
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3) v
    WHERE selected_date BETWEEN '2015-04-16' AND '2015-04-28'
  ) -
  (
    SELECT count (code, dates.selected_date)
    FROM apartments
    INNER JOIN (SELECT * FROM
      (select adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3) v
      ) dates ON dates.selected_date between date_arrival and date_departure
    WHERE selected_date BETWEEN '2015-04-16' AND '2015-04-28'
  ) AS 'days_of_availability'

This one is much more simpler. The second SELECT counts the number of days existing for the two dates multiplied by the number of apartments. The third SELECT counts the number of occupied days for all the apartments. The top SELECT does (number of days) minus (number of occupied days).

Fun fact: it took me almost 30 minutes to get this query working. That's a shame.


What type of files are C-9999-yyyymmdd-00 in Oracle

They're control file backups.

The reason for the file size discrepencies is due to the fact that they're for different databases. Order the list by filename and it'll be more obvious. Just for your information, the first number after the C- prefix is the DBID (database ID).


Error in SSMS when attempting to restore a database

From your reply to my question, then it does appear to be too much backup history. For a one off cleardown, something along these lines will do it:

USE msdb
GO
DECLARE @days DATETIME
SET @days = DATEADD(day,-30,current_timestamp)
EXEC sp_delete_backuphistory @days
GO

That example gets rid of everything over 30 days old, so you may need to change the -30 for a value appropriate for your needs. But remember that you'll then have to build up any restores for backups over 30 days old from hand (may or may not be a problem for you, but thought I'd better mention it).

Going forward you can schedule that to run via a scheduled job  to keep you small.


How to store tax for a Product when tax rate for a Product can change and tax-exempted Products

Don't put tax exempt entries in the Product_Tax table.

Design is good (already endorced by me).


Oracle 11g see transaction or session start time

Franz

The view V$TRANSACTION likely gives you the information you require. The transaction id is the XID column,


Deadlock on inserts to a single table with a single key. (Mysql)

I guess you have following issue.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row.
http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html


How can I identify queries that are producing cache misses in Postgres?

The pg_stat_statements extension does exactly what you want, giving the block hits and misses for each statement.

However, I usually don't find this information all that useful.  Many of the block misses are actually served by the file system cache and not actually read from disk.  PostgreSQL provides no direct way to discern these types of misses.

I think the best thing to do would be to turn on track_io_timing and then look at the time spent servicing those misses, rather than the raw number of misses.


How can I get partition name from value in MySQL/MariaDB

information_schema.PARTITIONS.PARTITION_DESCRIPTION

Consider naming the partitions in an obvious way, such as

p20200419

to contain data for the 19th (and defined LESS THAN ('2020-04-20'))

More tips:  http://mysql.rjweb.org/doc.php/partitionmaint and its companion:  http://mysql.rjweb.org/demo_part_maint.pl.txt

Example

Given:

SHOW CREATE TABLE pmdemo.pmdemo\G
*************************** 1. row ***************************
       Table: pmdemo
Create Table: CREATE TABLE `pmdemo` (
  `dt` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(dt))
(PARTITION `start` VALUES LESS THAN (0) ENGINE = MyISAM,
 PARTITION from20121008 VALUES LESS THAN (735150) ENGINE = MyISAM,
 PARTITION from20121009 VALUES LESS THAN (735151) ENGINE = MyISAM,
 PARTITION from20121010 VALUES LESS THAN (735152) ENGINE = MyISAM,
 PARTITION from20121011 VALUES LESS THAN (735153) ENGINE = MyISAM,
 PARTITION future VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
1 row in set (0.01 sec)

This select:

mysql> SELECT * FROM information_schema.`PARTITIONS`
      WHERE table_name = 'pmdemo'
        AND partition_description = TO_DAYS('2012-10-10')\G

*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: pmdemo
                   TABLE_NAME: pmdemo
               PARTITION_NAME: from20121009  -- the partition name
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 3
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: TO_DAYS(ft) -- a convenient formula for time-series
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 735151  -- the TO_DAYS() in the query
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 0
              MAX_DATA_LENGTH: 1970324836974591
                 INDEX_LENGTH: 1024
                    DATA_FREE: 0
                  CREATE_TIME: 2017-01-09 13:49:01
                  UPDATE_TIME: 2017-01-09 13:49:01
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
1 row in set (0.00 sec)


Redshift: Performance wise Is storing delimited values in a single column ever preferable to normalizing them out to another table?

In a word, NO!

You should not breach Codd's Rules unless you have no choice but to do so.

I fail to see what performance gains you will obtain by having to parse strings over having an Associative Entity (AKA joining, bridging tables or mapping - amongst others) and using SQL JOINs.

Most importantly from an RDBMS perspective, this allows you to enforce DRI (Declarative Referential Integrity) constraints on your data. DRI is your friend.

From the link:

  There are old-wives' tales about FKs being unnecessary overhead. This
  notion was kept alive by the fact that (for good, but probably now
  legacy, notably space, reasons) some RDBMSs did not/don't
  automatically create indexes on Foreign Key fields (this should be done in the majority of cases). This led to the mistaken view that Foreign Keys were overhead without benefit which is not
  true for the vast bulk of use-cases!

p.s. you mention that I have seen various posts that suggest that this is not the way to go- where are these references? I doubt if they'll stand up to much scrutiny!


Developing an Azure Sql Database backup strategy

There are a number of ways to schedule jobs for Azure SQL DB such as elastic jobs or azure automation. They can be setup to export your database at the intervals you require so it provide similar coverage as backups. Not the same experience and will be pretty klunky if your databases grow to massive sizes but in the few to tens of GB, you won't miss the super powers of local SQL Server backup (e.g. compression).

The team is trying to provide a solution for the long term retention requirement for backups so if it's important to you, add the request to https://feedback.azure.com/forums/217321-sql-database. I don't know what state that investigation is in but always helpful to get users chiming in with requirements so be very explicit on how long you really need to keep the backups around.


Copying the database from SQL Server to PostgreSQL

For the person who's done this before I've looked at many tools and there are many pitfalls in doing that.  The cheapest way I found to do this was to do a text dump, convert, and load.

My setup was MSSQL 2005 to PostgreSQL 8.x on Linux.  The reason I say convert is because in the bulk bcp dump there is no way to distinguish between '' and NULL so the COPY command I used to load was complaining about NULL value for a table that had '' as a part of the primary key.

Dumping the schema also required conversion since the types are not exactly compatible, so it's been a bit of work but no buying of data migration tools which can be expensive for what may be just one time use.

There are other tools like this one but from what I have tested(2 years ago) has limitations.


MS SQL Server Restore: Methods Without Exclusive Access

As far as I know there is no way with SQL Server to keep the database online while it’s restoring.  Restores are a point in time version of the database and without having exclusive access to perform the restore it can’t guarantee the state of the data if data was to get changed mid restore.

In the case of oops deletes and not a corrupted database.  If you have the space to do a full restore you can restore the needed database with a different name and file name(s) which will allow you to query the needed data and reinsert or update the live database.

There is a product called LiteSpeed from Quest that I use (not connected to them) to perform backups and compression that will allow you to read the backup files without having to do a real restore. You will then be able to query the tables in the backup and store the results into temp tables in another database on the server. This option requires that you have backed up your database with the software so some advanced DR planning is required.


Report-builder: How can I show the labels in a List when the dataset is empty?

Occurs to me that I can do this by adding an empty record if the select returns no row; is that the best option?


I did a little more research and tested on my local database, found out that the child tables just need to cascade delete and it works from the top down perfectly.

You do this by adding the ON CASCADE DELETE option to the foreign keys where you want this to happen.


Get the id_user_app if not exists in id_user_rq

SELECT DISTINCT A.*, p.*, u.email FROM
              (SELECT * FROM default_relation_users WHERE id_user_rq = 1) A
              LEFT JOIN
              (SELECT * FROM default_relation_users WHERE id_user_ap = 1) B
              ON (A.id_user_rq=B.id_user_ap AND B.id_user_rq=A.id_user_ap)
              LEFT JOIN default_users u ON (u.id = A.id_user_rq)
              LEFT JOIN default_profiles p ON (p.user_id = A.id_user_rq)
              WHERE B.id_user_ap IS NULL
              GROUP BY A.id_user_rq

I changed A and B to be subqueries using 1 for id_user_rq and id_user_ap

If you want the opposite, just reverse A and B subqueries to something like this:

SELECT DISTINCT A.*, p.*, u.email FROM
              (SELECT * FROM default_relation_users WHERE id_user_ap = 1) A
              LEFT JOIN
              (SELECT * FROM default_relation_users WHERE id_user_rq = 1) B
              ON (A.id_user_rq=B.id_user_ap AND B.id_user_rq=A.id_user_ap)
              LEFT JOIN default_users u ON (u.id = A.id_user_rq)
              LEFT JOIN default_profiles p ON (p.user_id = A.id_user_rq)
              WHERE B.id_user_rq IS NULL
              GROUP BY A.id_user_rq


How do I stop a master from being a master in mariadb

There's RESET MASTER, but that just removes the binary logs and resets the binlog file number to 1. It doesn't prevent replicas from connecting. But they will be very confused if they connect and the binlog file number has been reset.
You could revoke REPLICATION SLAVE privilege from the username used by any of the replicas that are currently connecting.
Or you could disable binary logs, which is accomplished by removing the log_bin option from your server options file and restarting. AFAIK, you cannot disable the binary log dynamically.


How do I recover from Mariadb(10.4.18) binlog corruption

Once it became clear that the cluster had been working properly right until the reboot, I restored the node to the last backup and let Galera bring the node back up to date.
In the end it was came back very quickly.


MySQL connection fails with one database and not the other

There was an extra entry in mysql.user table with host as '' that was interfering. Deleted it manually and running FLUSH PRIVILEGES worked for me.


ERROR 1034 (HY000) : Wrong aligned block, while importing in mysql

At first glance, I would say you have some corruption. But, this an import. That scares me. Take your mysqldump and run this:

head -20 SQLDump.sql

Does the dump contain DROP TABLE and CREATE TABLE ? My guess would be no.

Did you change the table structure, i.e.,

added columns to the table
removed columns from the table
changed charset/collation to utf8

after doing that mysqldump ? My guess would be yes.

If neither of these are the case, then your table has some corruption in the .frm.

Try installing MySQL on another DB Server and loading the SQLDump there. If nothing errors out, the mysqldump is fine. This verifies some corruption.

Try dropping the problem table, create it from scratch. Then, load it back. You could run myisamchk against the MyISAM table.

http://bugs.mysql.com/bug.php?id=44571
http://grokbase.com/t/mysql/mysql/123aqcrswa/help-the-dreaded-incorrect-key-file-for-table-xxxxxxx-try-to-repair-it-error


What exactly mean refused in Oracle listener?

lsnrctl does show the number of connections refused since the listener was started. Connections can be refused for several reasons, usually because some part of the connect string is incorrect, missing, or malformed, like a bad service_name setting. A connection might also be rejected if the server has Valid Node Checking enabled and the client IP address isn't white-listed. Here is a list of common errors and some troubleshooting tips: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/netag/troubleshooting-oracle-net-services.html#GUID-F521AB0A-B67C-432B-B899-8F856EB51CAE

TNS-12500/ORA-12500: TNS: listener failed to start a dedicated server process
ORA-12514: TNS:listener does not currently know of service
requested in connect descriptor
ORA-12520: TNS:listener could not find available handler for requested type of server
ORA-12521: TNS:listener does not currently know of instance requested in connect
descriptor
ORA-12525: TNS:listener has not received client's request in time allowed
ORA-12533: TNS:illegal ADDRESS parameters
TNS-12540/ORA-12540: TNS:internal limit restriction exceeded and TNS-00510: Internal limit restriction exceeded
TNS-12541/ORA-12541: TNS:no listener
TNS-12549/ORA-12549: TNS:operating system resource quota exceeded and TNS-00519: Operating system resource quota exceeded
TNS-12560/ORA-12560: TNS:protocol adapter error occurred


How can I pg_dump my database while excluding extensions?

It looks like you've installed the ossp-uuid extension from the sql scripts directly, rather than with create extension.

You should CREATE EXTENSION "uuid-ossp" FROM unpackaged; on your local DB, before creating the dump. That'll convert it into an extension, which is dumped as CREATE EXTENSION ... instead of individual CREATE FUNCTION ... commands.