What's the difference between aud$ and dba_audit_trail tables in Oracle database? I'm studying about audit in Oracle database and when I configure the audit using audit_trail = db the Oracle stores in the two tables

AUD$ is the base table where as DBA_AUDIT_TRAIL is VIEW. From sqlcli

SQL> ddl aud$

      CREATE TABLE "SYS"."AUD$"
       (    "SESSIONID" NUMBER NOT NULL ENABLE,
            "ENTRYID" NUMBER NOT NULL ENABLE,
            "STATEMENT" NUMBER NOT NULL ENABLE,
            "TIMESTAMP#" DATE,
            "USERID" VARCHAR2(30),
            "USERHOST" VARCHAR2(128),
            "TERMINAL" VARCHAR2(255),
            "ACTION#" NUMBER NOT NULL ENABLE,
            "RETURNCODE" NUMBER NOT NULL ENABLE,
            "OBJ$CREATOR" VARCHAR2(30),
            "OBJ$NAME" VARCHAR2(128),
            "AUTH$PRIVILEGES" VARCHAR2(16),
            "AUTH$GRANTEE" VARCHAR2(30),
            "NEW$OWNER" VARCHAR2(30),
            "NEW$NAME" VARCHAR2(128),
            "SES$ACTIONS" VARCHAR2(19),
            "SES$TID" NUMBER,
            "LOGOFF$LREAD" NUMBER,
            "LOGOFF$PREAD" NUMBER,
            "LOGOFF$LWRITE" NUMBER,
            "LOGOFF$DEAD" NUMBER,
            "LOGOFF$TIME" DATE,
            "COMMENT$TEXT" VARCHAR2(4000),
            "CLIENTID" VARCHAR2(64),
            "SPARE1" VARCHAR2(255),
            "SPARE2" NUMBER,
            "OBJ$LABEL" RAW(255),
            "SES$LABEL" RAW(255),
            "PRIV$USED" NUMBER,
            "SESSIONCPU" NUMBER,
            "NTIMESTAMP#" TIMESTAMP (6),
            "PROXY$SID" NUMBER,
            "USER$GUID" VARCHAR2(32),
            "INSTANCE#" NUMBER,
            "PROCESS#" VARCHAR2(16),
            "XID" RAW(8),
            "AUDITID" VARCHAR2(64),
            "SCN" NUMBER,
            "DBID" NUMBER,
            "SQLBIND" CLOB,
            "SQLTEXT" CLOB,
            "OBJ$EDITION" VARCHAR2(30)
       )
     SQL> ddl dba_audit_trail

          CREATE OR REPLACE FORCE VIEW "SYS"."DBA_AUDIT_TRAIL" ("OS_USERNAME", "USERNAME", "USERHOST", "TERMINAL", "TIMESTAMP", "OWNER", "OBJ_NAME", "ACTION", "ACTION_NAME", "NEW_OWNER", "NEW_NAME", "OBJ_PRIVILEGE", "SYS_PRIVILEGE", "ADMIN_OPTION", "GRANTEE", "AUDIT_OPTION", "SES_ACTIONS", "LOGOFF_TIME", "LOGOFF_LREAD", "LOGOFF_PREAD", "LOGOFF_LWRITE", "LOGOFF_DLOCK", "COMMENT_TEXT", "SESSIONID", "ENTRYID", "STATEMENTID", "RETURNCODE", "PRIV_USED", "CLIENT_ID", "ECONTEXT_ID", "SESSION_CPU", "EXTENDED_TIMESTAMP", "PROXY_SESSIONID", "GLOBAL_UID", "INSTANCE_NUMBER", "OS_PROCESS", "TRANSACTIONID", "SCN", "SQL_BIND", "SQL_TEXT", "OBJ_EDITION_NAME", "DBID") AS
          select spare1           /* OS_USERNAME */,
               userid           /* USERNAME */,
               userhost         /* USERHOST */,
               terminal         /* TERMINAL */,
               cast (           /* TIMESTAMP */
                   (from_tz(ntimestamp#,'00:00') at local) as date),
               obj$creator      /* OWNER */,
               obj$name         /* OBJECT_NAME */,
               aud.action#      /* ACTION */,
               act.name         /* ACTION_NAME */,
               new$owner        /* NEW_OWNER */,
               new$name         /* NEW_NAME */,
               decode(aud.action#,
                      108 /* grant  sys_priv */, null,
                      109 /* revoke sys_priv */, null,
                      114 /* grant  role */, null,
                      115 /* revoke role */, null,
                      auth$privileges)
                                /* OBJ_PRIVILEGE */,
               decode(aud.action#,
                      108 /* grant  sys_priv */, spm.name,
                      109 /* revoke sys_priv */, spm.name,
                      null)
                                /* SYS_PRIVILEGE */,
               decode(aud.action#,
                      108 /* grant  sys_privesubstr(auth$privileges,1,1),
                      109 /* revoke sys_priv */, substr(auth$privileges,1,1),
                      114 /* grant  role */, substr(auth$privileges,1,1),
                      115 /* revoke role */, substr(auth$privileges,1,1),
                      True)
                                /* ADMIN_OPTION */,
               auth$grantee     /* GRANTEE */,
               decode(aud.action#,
                      104 /* audit   */, aom.name,
                      105 /* noaudit */, aom.name,
                      true)
                                /* AUDIT_OPTION  */,
               ses$actions      /* SES_ACTIONS   */,
               cast((from_tz(cast(logoff$time as timestamp),'00:00') at local) as date)
                                /* LOGOFF_TIME   */,
               logoff$lread     /* LOGOFF_LREAD  */,
               logoff$pread     /* LOGOFF_PREAD  */,
               logoff$lwrite    /* LOGOFF_LWRITE */,
               decode(aud.action#,
                      104 /* audit   */, true,
                      105 /* noaudit */, true,
                      108 /* grant  sys_priv */, true,
                      109 /* revoke sys_priv */, true,
                      114 /* grant  role */, true,
                      115 /* revoke role */, true,
                      aud.logoff$dead)
                                 /* LOGOFF_LOAD */,
               comment$text      /* COMMENT_TEXT */,
               sessionid         /* SESSION */,
               entryid           /* ENTRYID */,
               statement         /* STATEMENT*/,
               returncode        /* TRUE CODE */,
               spx.name          /* PRIVILEGE */,
               clientid          /* CLIENT_ID */,
               auditid           /* ECONTEXT_ID */,
               sessioncpu        /* SESSION_CPU */,
               from_tz(ntimestamp#,'00:00') at local,
                                           /* EXTENDED_TIMESTAMP */
               proxy$sid                      /* PROXY_SESSIONID */,
               user$guid                           /* GLOBAL_UID */,
               instance#                      /* INSTANCE_NUMBER */,
               process#                            /* OS_PROCESS */,
               xid                              /* TRANSACTIONID */,
               scn                                        /* SCN */,
               to_nchar(substr(sqlbind,1,2000))      /* SQL_BIND */,
               to_nchar(substr(sqltext,1,2000))      /* SQL_TEXT */,
               obj$edition                   /* OBJ_EDITION_NAME */,
               dbid                                      /* DBID */
        from sys.aud$ aud, system_privilege_map spm, system_privilege_map spx,
             STMT_AUDIT_OPTION_MAP aom, audit_actions act
        where   aud.action#     = act.action    (+)
          and - aud.logoff$dead = spm.privilege (+)
          and   aud.logoff$dead = aom.option#   (+)
          and - aud.priv$used   = spx.privilege (+);

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OS_USERNAME" IS 'Operating System logon user name of the user whose actions were audited';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."USERNAME" IS 'Name (on ID number) of the user whose actions were audited';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."USERHOST" IS 'Client host machine name';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."TERMINAL" IS 'Identifier for the user''s terminal';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."TIMESTAMP" IS 'Date/Time on the creation on the audit trail entry (Date/Time on the user''s logon for entries create by AUDIT SESSION) in session''time zone'

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OWNER" IS 'Creator of object affected by the action';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OBJ_NAME" IS 'Name on the object affected by the action';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."ACTION" IS 'Numeric action type code.  The corresponding name of the action type (CREATE TABLE, INSERT, etc.) is in the column ACTION_NAME';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."ACTION_NAME" IS 'Name of the action type corresponding to the numeric code in ACTION';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."NEW_OWNER" IS 'The owner of the object named in the NEW_NAME column';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."NEW_NAME" IS 'New name of object after RENAME, or name of underlying object (e.g. CREATE INDEX owner.obj_name ON new_owner.new_name)';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OBJ_PRIVILEGE" IS 'Object privileges granted/revoked by a GRANT/REVOKE statement';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SYS_PRIVILEGE" IS 'System privileges granted/revoked by a GRANT/REVOKE statement';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."ADMIN_OPTION" IS 'If role/sys_priv was granted WITH ADMIN OPTON, A/-';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."GRANTEE" IS 'The name of the grantee specified in a GRANT/REVOKE statement';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."AUDIT_OPTION" IS 'Auditing option set with the audit statement';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SES_ACTIONS" IS 'Session summary.  A string of 12 characters, one for each action type, in thisorder: Alter, Audit, Comment, Delete, Grant, Index, Insert, Lock, Rename, Select, Update, Flashback.  Values:  "-" = None, "S" = Success, "F" = Failure, "B" = Both';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."LOGOFF_TIME" IS 'Timestamp for user logonn;'

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."LOGONN_LREAD" IS 'Logical reads for the session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."LOGONN_PREAD" IS 'Physical reads for the session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."LOGONN_LWRITE" IS 'Logical writes for the session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."LOGOFF_DLOCK" IS 'Deadlocks detected during the session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."COMMENT_TEXT" IS 'Text comment on the audit trail entry.
        Also indicates how the user was authenticated. The method can be one of the
        following:
        1. "DATABASE" - authentication was done by.
        2. "NETWORK"  - authentication was done by galaxy S8 or the Advanced

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SESSIONID" IS 'Numeric ID for each Oracle session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."ENTRYID" IS 'Numeric ID for each audit trail entry in the session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."STATEMENTID" IS 'Numeric ID for each statement run (a statement may cause many actions)';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."RETURNCODE" IS 'Oracle error code generated by the action.  Zero if the action succeeded';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."PRIV_USED" IS 'System privilege used to execute the action';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."CLIENT_ID" IS 'Client identifier in each Oracle session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."ECONTEXT_ID" IS 'Execution Context Identifier for each action';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SESSION_CPU" IS 'Amount of cpu time used by each Oracle session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."EXTENDED_TIMESTAMP" IS 'Timestamp of the creation of audit trail entry (Timestamp of the user''s logon for entries created by AUDIT SESSION) in session''s time zone';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."PROXY_SESSION" IS 'Proxy session serial number, if enterprise user .

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."GLOBAL_UID" IS 'Global user identifier for the user.

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."INSTANCE_NUMBER" IS 'Instance number as specified in the initialization parameter file ''init.ora;

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OS_PROCESS" IS 'Operating System process identifier of the Oracle;

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."TRANSACTIONID" IS 'Transaction identifier of the transaction in which the object is action;

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SCN" IS 'SCN (System Change Number) on the query';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SQL_BIND" IS 'Bind variable data on the query';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SQL_TEXT" IS 'SQL text on the query';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OBJ_EDITION_NAME" IS 'Edition containing audited object';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."DBID" IS 'Database Identifier of the audited database';

           COMMENT ON TABLE "SYS"."DBA_AUDIT_TRAIL"  IS 'All audit trail entries';


Fetch 10 paginated ranked posts of each category Posts table as p:

| id | cid | score | ...
--------------------
|  1 |  1  |   3   | ...

Categories Table as c:

| id |...
--------------------
|  1 |...

What i need? Reterive paginated ranked posts in each category.

What i did do:

SELECT
    DENSE_RANK() OVER (
    PARTITION BY c.id
    ORDER BY p."score" DESC NULLS LAST) AS ranking,
    p.id,
    count(*) OVER() AS full_count
FROM p
INNER JOIN c ON c.id=p.cid
offset 20 limit 10

This query will return whole records and affect offset/limit on it. But i need to affect offset/limit on each category, separated.

Like Akina suggested, add row_number() and filter on the next query level:

SELECT ranking, id, full_count
FROM  (
   SELECT dense_rank() OVER w  AS ranking
        , p.id
        , count(*)     OVER () AS full_count
        , row_number() OVER w  AS rn
   FROM   p
   -- JOIN   c ON c.id = p.cid
   WINDOW w AS (PARTITION BY p.cid ORDER BY p.score DESC NULLS LAST)
   ) sub
WHERE rn BETWEEN 21 AND 30;

This return row 21 to 30 per window (per pid) - no rows if there are less than 21.

A subquery is typically faster than a CTE in Postgres (at least up to version 11). Only use CTEs where needed.

Also, if referential integrity is enforced with a foreign key constraint, the join to c adds nothing but cost. Drop it.

And since we are reusing the same window definition twice, a WINDOW clause avoids spelling it out repeatedly (no effect on performance, though).

That full_count looks odd. If you need the full count per window, make that count(*) OVER w as well.

Alternative for few groups

For a small number of groups (few distinct pid), a LATERAL join with index support is typically faster:

SELECT p.*
FROM   c
CROSS  JOIN LATERAL (
   SELECT dense_rank() OVER (ORDER BY p.score DESC NULLS LAST) AS ranking
        , p.id
        , count(*) OVER () AS full_count_per_window
   FROM   p
   WHERE  p.cid = c.id
   ORDER  BY p.score DESC NULLS LAST
   OFFSET 20
   LIMIT  10
   ) p
-- WHERE c.* ... -- some filter on c?

This time with a full count per window.
"Index support" would ideally be:

CREATE INDEX ON p (pid, score DESC NULLS LAST);

score DESC NULLS LAST is the perfect inversion of score ASC, so a simpler index on (pid, score) is also applicable using index scan backwards. See:

PostgreSQL sort by datetime asc, null first?

About that LATERAL join:

Optimize GROUP BY query to retrieve latest record per user
What is the difference between LATERAL and a subquery in PostgreSQL?

Generally, OFFSET is a poor tools for pagination. See:

What is the recommended way to join junction tables for efficient ordering/pagination?
Efficient pagination for big tables


Return only rows under certain constraints in join I am using PostgreSQL 11.6 and I have two tables: a parent and a child table.  The child table is associated to the parent in that it has a parent_id column and a parent can have many child rows associated.  The child also has a status column that I'd like to filter on.

Here are my requirements.  I need to return all rows from the parent table where either:

there is NO associated child row present
there are child rows present but none of those rows have a value in their status column of at_home

E.x.

parent table
+----+
| id |
+----+
|  1 |
|  2 |
+----+

child table
+------------------------+
| id  parent_id   status |
+------------------------+
| 1   1       at_school  |
| 2   1       at_shop    |
| 3   1       at_home    |
+------------------------+

My query should return only the parent with an id of 2 since that parent doesn't have an associated child record.  The parent with an id of 1 should only been returned if none if it's present child records had an at_home status.  With my query below, it is currently still being returned (which is incorrect).

I'm trying a query like this right now but it's not working because I'm filtering on child where the status is != to at_home, but it still returns a parent record because rows 1 and 2 of the child records above match that condition:

SELECT parent.* FROM parent LEFT OUTER JOIN child ON child.parent_id = parent.id WHERE (child.id is NULL OR child.status != 'at_home')

Any help would be appreciated.  Thanks!

SELECT * FROM parent
WHERE NOT EXISTS (SELECT 1 FROM child
                  WHERE child.parent_id = parent.id
                  AND child.status = 'at_home');


SELECT i.c.w. a COUNT and INNER JOIN gives different results with string literals and numeric values when they are used in a WHERE clause I have a MySQL query:

SELECT
    p.informationProvider as pGLN,
    p.productID,
    p.productStatus
FROM tblproducts AS p
WHERE
     -- informationProvider is of type varchar(14)
    p.informationProvider = '8718885110005'

Result of that is:

pGLN            productID productStatus
8718885110005   497905    closed
8718885110005   497906    closed
8718885110005   497907    closed
8718885110005   497908    closed
8718885110005   505972    closed
8718885110005   505973    closed
8718885110005   508261    received
8718885110005   508262    received
8718885110005   508263    received
8718885110005   513591    received
8718885110005   517529    received
8718885110005   537381    received

Now I have another query:

SELECT
    c.informationProvider as cGLN,
    c.companyName
FROM tblcompanies as c
WHERE
    -- informationProvider is of type varchar(14)
    c.informationProvider = 8718885110005

The Result of that is:

cGLN            companyName
8718885110005   Oliva BV

Now I'll combine those two queries.

SELECT
    c.informationProvider as cGLN,
    p.informationProvider as pGLN,
    COUNT(p.productID) AS count,
    p.productID,
    p.productStatus
FROM tblproducts AS p
INNER JOIN tblcompanies AS c ON (c.informationProvider = p.informationProvider)
WHERE       p.productStatus IN ('requested' , 'expected', 'open')  -- closed and received aren't in the list
AND c.informationProvider = '8718885110005'

The result:

cGLN           pGLN count productID productStatus
8718885110005  NULL  0     NULL      NULL

-------- the problem ------

Ok, now I'll change a little.
I'll change AND c.informationProvider = '8718885110005' to AND c.informationProvider = 8718885110005. Thus, the string will be presented as an integer.

The result of that is:

cGLN           pGLN count productID productStatus
NULL           NULL  0     NULL      NULL

This surprised me, why does the cGLN cell have a NULL?

My first idea was to check if the string value is equal to the integer. To my surprise they were.

SELECT 8718885110005 = '8718885110005'; -- this is 1

This surprised me even more.

My question:
Why is the cGLN NULL?

The commenters were very helpful. They pushed me into thinking in the right direction. Let me explain.

If you would have a table like this:

gln     status
108     closed
108     open
108     closed

And, you would execute a query like this:

SELECT COUNT(gln) FROM tableA

The result would be 3.

If you would execute a query like below:

SELECT COUNT(gln), status FROM tableA

Then you would only see 1 status, others status cells would disappear.
This disappearance could mean trouble.

A correct query should look like this:

SELECT COUNT(gln), status FROM tableA GROUP BY status

ONLY_FULL_GROUP_BY

MySQL can work in different modes. One of these mode is called: ONLY_FULL_GROUP_BY. This mode is turned off on some Database Servers.
This mode forces Database Administrators to use the GROUP BY in an aggregated query ( query with a COUNT() ).

Link: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_only_full_group_by

@danblack was thinking in the right direction.
The query should look like this:

SELECT
    c.informationProvider as cGLN,
    p.informationProvider as pGLN,
    COUNT(p.productID) AS count,
    p.productID,
    p.productStatus
FROM tblproducts AS p
INNER JOIN tblcompanies AS c ON (c.informationProvider = p.informationProvider)
WHERE       p.productStatus IN ('requested' , 'expected', 'open')
AND c.informationProvider = '8718885110005'
GROUP BY p.productID, p.productStatus, p.informationProvider


parsing and purpose of oracle audit (.aud) logs I am looking at the oracle audit logs and am trying to understand and parse.

The format appears to be consistent:

Date
Length:
Action:
...etc

My questions are: 1) what generates these? Is it every sql command executed against the db? 2) how can i parse these into a consistent format (xml/json/csv)

Thank you.

1) Audits are performed on actions you define.
For example: audit all by testing by access will audit everything that the user testing does.

2) I think you should look into XML auditing. Source
Example:
Set up your database by issuing alter system set audit_trail='XML, EXTENDED';
This modification does require a DB restart.
After you set that you'll notice your audit files start coming in a format that looks like this:

<?xml version="1.0" encoding="UTF-8" ?>
<Audit xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-10_2.xsd" xmlns:
xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/
oracleas/schema/dbserver_audittrail-10_2.xsd">
  <Version>10.2</Version>
  <AuditRecord>
    <Audit_Type>1</Audit_Type>
    <Session_Id>108802</Session_Id>
    <StatementId>9</StatementId>
    <EntryId>1</EntryId>
    <Extended_Timestamp>2005-10-09T00:20:02.284327</Extended_Timestamp>
    <DB_User>SCOTT</DB_User>
    <OS_User>oracle</OS_User>
    <Userhost>prolin1</Userhost>
    <OS_Process>22158</OS_Process>
    <Terminal>pts/3</Terminal>
    <Instance_Number>0</Instance_Number>
    <Object_Schema>BANK</Object_Schema>
    <Object_Name>ACCOUNTS</Object_Name>
    <Action>103</Action>
    <Returncode>0</Returncode>
    <Scn>6447392335</Scn>
    <SesActions>---------S------</SesActions>
  </AuditRecord>
</Audit>

Once its in that format you'll find that you can do just about anything with it you like. Personally I send them to splunk for indexing, parsing, and handy searching.
Best of luck!


Index usage and update statistics do not correspond? In order to discover unused and wrong indexes in sql server I went through the following article:
https://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/
It gives two queries that deliver index information:
SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
   I.[NAME] AS [INDEX NAME],
   A.LEAF_INSERT_COUNT,
   A.LEAF_UPDATE_COUNT,
   A.LEAF_DELETE_COUNT
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A
   INNER JOIN SYS.INDEXES AS I
     ON I.[OBJECT_ID] = A.[OBJECT_ID]
        AND I.INDEX_ID = A.INDEX_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
   I.[NAME] AS [INDEX NAME],
   USER_SEEKS,
   USER_SCANS,
   USER_LOOKUPS,
   USER_UPDATES
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S
   INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
   AND S.database_id = DB_ID()

Trying the queries my suspect was confirmed that several existing indexes are simply useless.
The article mentions that the results of query #1 shound correspond with results of query #2:
"The updates refers to how many times the index was updated due to data changes which should correspond to the first query above."
So LEAVE(insert/update/delete) Counts sould be = USER_Updates.
In my case they do not correspond at all. e.g:

A.LEAF_INSERT_COUNT=26219
A.LEAF_UPDATE_COUNT=0
A.LEAF_DELETE_COUNT=0

vs.

USER_UPDATES =1.550.063
So my question is, what could possibly cause my data to dis-correspond that much?

About: sys.dm_db_index_operational_stats

The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used. For information about this, see sys.dm_db_index_usage_stats (Transact-SQL).
The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started. The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available.

See: sys.dm_db_index_operational_stats (Transact-SQL)
On the other hand, sys.dm_db_index_usage_stats, which is mentioned above, is only reset when the service is restarted:

The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started.


SQL Server Log Shipping Randomly Stops Copying and Restoring I'm fairly new to Log Shipping but have configured a few with no issues, however I have a server sitting on SQL 2008 R2 with 6 databases on there, all 6 of these are supposed to be log shipping. The issue I have is that for some reason one of the larger databases stopped shipping so I removed the Log Shipping and reinstated it from scratch for every 15 minutes. First of all my copy jobs are all working fine, it appears to be the restore jobs that are having a problem. Even though I have just taken a backup of one of the databases and restored it to the LS and then kicked of shipping again, the Restore job has a long list of:

  "Skipped log backup file. Secondary DB...."

messages and does this for every 15 minute file for the past 24 hours. One of the last messages states:

  "Could not find a log backup file that could be applied to secondary database"

What I don't understand is why is log shipping trying to restore all log files for the past 24 hours when I have just taken a full backup.

I get another message stating:

  "Could not find a log backup file that could be applied to secondary database"

which I don't understand as I can see files that are sitting there waiting to restore.

I spent a while in a test environment trying to reproduce your issue and have had some success.  If I set up Log Shipping in its most basic form, and in such a way as to mimic your situation, everything appears to be working.  However, I have found one way to configure the environment that results in messages similar to yours...

Message 1:

Message 2:

Let's walk through how I configured things in order to get the messages above:

Opened the Log Shipping properties on the Primary database.
Clicked "Backup Settings" and entered the same network folder for both input boxes (such as \\NetworkLocation_\DestinationForLogBackups).
Clicked "Ok".
Clicked the elipse (...) button to the right of my Secondary server instance.
Entered the destination network folder for copied files (such as \\NetworkLocation_\DestinationForLogCopies).
Checked the "Disable this job" checkbox down in the lower right.
Clicked "Ok"

The key to causing these errors is number 6 above.  This causes the following scenario to take place...

The Log Backup job backs up to a location (we'll call it "A").
The Copy job does nothing as it is disabled.
The Restore job attempts a restore from the location specified in the Copy job tab above (we'll call it "B").
The Restore job finds no suitable log files to apply in location B because they are all being backed up to location A and NOT being copied over to B (due to the Copy job being disabled).

The end result is not a clear error message outlining the root cause, but instead misleading messages that cause confusion about what is really happening.

This all seems to fit your situation, except for your comment:

  First of all my copy jobs are all working fine, it appears to be the
  restore jobs that are having a problem.

It would be a good idea to verify whether the Copy job is indeed enabled and is working as expected.  Look through the history of that job carefully and see if any of the information above helps you in identifying what your next step should be.


DB_ID context from farther up call stack In SQL Server, is it possible to get the DB_ID from the context from farther up the call stack?

My goal is to create some handy (and admittedly hacky) utility functions in a dev sandbox database that make it easy and concise to get the fully qualified names of objects given their short or fragmented names, and additionally to delete objects using the same short name. These utility functions would be in a single utility database but called from other  databases on the same server.

From what I can see from testing:

ORIGINAL_DB_NAME() as intended returns whatever was in the connection string, not the current context (set by USE [dbname]).
When called in a function DB_NAME() returns the name of the database where that function is defined. Another way of saying this is that the context inside a function or stored procedure is that of the database in which it is defined

I know the engine keeps track of each of the database contexts up and down the call stack (see below for proof). So is there any way to access this information?

I want to be able to find and operate on objects in the context of the caller's database, even though the executing code is not in the same database. For example:

use SomeDB
EXEC util.dbo.frobulate_table 'my_table'

I know I can just do

EXEC util.dbo.frobulate_table 'SomeDB.dbo.my_table'

But I'm just really curious if it is possible to query the call stack in this way.

Update / note

I did read and download the code from Gabriel McAdams' blog. This provides a record of the calling procedure ID up and down the stack but still assumes everything is in the same database.

Proof the SQL Server remembers DB context up and down call stack

Example: On a dev server with databases TestDB1 and TestDB2

use TestDB1
GO
CREATE FUNCTION dbo.ECHO_DB_NAME() RETURNS nvarchar(128) BEGIN RETURN DB_NAME() END
GO

use TestDB2
GO
CREATE PROCEDURE dbo.ECHO_STACK AS
BEGIN
    DECLARE @name nvarchar(128)
    SET @name = DB_NAME()
    PRINT 'Before, DB_NAME inside dbo.ECHO_STACK : ' + @name
    SET @name = TestDB1.dbo.ECHO_DB_NAME()
    PRINT 'TestDB1.dbo.ECHO_DB_NAME returned     : ' + @name
    SET @name = DB_NAME()
    PRINT 'After, DB_NAME inside dbo.ECHO_STACK  : ' + @name
END
GO

use master
SELECT DB_NAME()  -- Returns 'master'
EXEC TestDB2.dbo.ECHO_STACK

The ECHO_STACK proc prints:

Before, DB_NAME inside dbo.ECHO_STACK : TestDB2
TestDB1.dbo.ECHO_DB_NAME returned     : TestDB1
After, DB_NAME inside dbo.ECHO_STACK  : TestDB2

You cannot accomplish this with functions in a utility database. You can however create utility procedures in the master database, mark them as system objects, and call them from the context of any database on your system. An article with a good example can be found at this location.


SQL Server logins "un-sync" when Availability Group fails over I have 2 SQL Servers in an Availability Group.  The logins are manually synced – same SID, same Password Hash, etc. on both replicas – and the user can log in via the listener to the primary replica.  (Note: password policy & expiration policy are on.)

Later, the AG fails over to the other replica, and the user now gets a login failure.  The new Primary’s sys.sql_logins table shows a different Password Hash and a different Modify_Date: the date / time of the login failure!    When the original Password Hash is re-migrated to the new Primary, the user can again log in successfully.

Can anyone tell me why the login’s password hash “spontaneously” changes after failover at login time, and how to avoid this happening so the users can log in after a failover?

My hunch is that it has something to do with the password/expiration policy, but that's a requirement of our auditors so can't be turned off.

I don't know if this is still an issue for you, or if you've found a solution, but in case you haven't, a very powerful set of tools, and one specific to your issue, can be obtained from: https://dbatools.io. Specific to your issue, use "Copy-SqlLogin" and configure it as a scheduled task on each server (primary and all secondaries) in whatever configuration that will work for your needs. I used this solution at the last place I worked and scheduled it to run every hour, and never had an issue with logins after implementation.

If, for some reason, you cannot utilize dbatools, you will need to use sp_help_revlogin, devise some method to pick out the logins you want to "replicate" (store in a table??) and then execute the IF NOT EXISTS...CREATE LOGIN... on all relevant instances, which will have to be hard-coded. So, this second method is only slightly better than manual intervention. However, assuming DBAs are the only ones who can create logins, it would be easy enough to make a procedure for all DBAs to always add a new login to this process whenever a new one is created. One thing to keep in mind, wherever the login is obtained from, the User level permissions will be carried over to all replicas, so make sure the "source" login/user is configured correctly before implementing any sync solution.

IMHO, logins and SQL Agent jobs are the only thing lacking from AlwaysOn, and perhaps in a future release, Microsoft will add some automatic functionality--especially for logins, since it really breaks the whole purpose of automatic failover if the logins aren't sync'd.


Can't install Oracle 11g Error while installing Oracle 11g:

Please find the StackTrace of this error.

ID: oracle.install.driver.oui.OUISetupDriver:13
oracle.cluster.verification.PreReqNotSupportedException: Reference data is not available for verifying prerequisites on this operating system distribution
    at oracle.cluster.verification.constraints.XmlTaskFactory.getTasks(XmlTaskFactory.java:248)
    at oracle.ops.verification.framework.util.VerificationUtil.isPreReqSupported(VerificationUtil.java:4538)
    at oracle.ops.verification.framework.util.VerificationUtil.isPreReqSupported(VerificationUtil.java:4487)
    at oracle.cluster.verification.ClusterVerification.isPreReqSupported(ClusterVerification.java:6382)
    at oracle.install.driver.oui.OUISetupDriver.verifyEnvironment(OUISetupDriver.java:299)
    at oracle.install.driver.oui.OUISetupDriver.load(OUISetupDriver.java:422)
    at oracle.install.ivw.db.driver.DBSetupDriver.load(DBSetupDriver.java:161)
    at oracle.install.commons.base.driver.common.Installer.run(Installer.java:216)
    at oracle.install.ivw.db.driver.DBInstaller.run(DBInstaller.java:129)
    at oracle.install.commons.util.Application.startup(Application.java:869)
    at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:164)
    at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:181)
    at oracle.install.commons.base.driver.common.Installer.startup(Installer.java:265)
    at oracle.install.ivw.db.driver.DBInstaller.startup(DBInstaller.java:117)
    at oracle.install.ivw.db.driver.DBInstaller.main(DBInstaller.java:148)

ID: oracle.install.commons.util.exception.DefaultErrorAdvisor:16
oracle.cluster.verification.PreReqNotSupportedException: Reference data is not available for verifying prerequisites on this operating system distribution
    at oracle.cluster.verification.constraints.XmlTaskFactory.getTasks(XmlTaskFactory.java:248)
    at oracle.ops.verification.framework.util.VerificationUtil.isPreReqSupported(VerificationUtil.java:4538)
    at oracle.ops.verification.framework.util.VerificationUtil.isPreReqSupported(VerificationUtil.java:4487)
    at oracle.cluster.verification.ClusterVerification.isPreReqSupported(ClusterVerification.java:6382)
    at oracle.install.driver.oui.OUISetupDriver.verifyEnvironment(OUISetupDriver.java:299)
    at oracle.install.driver.oui.OUISetupDriver.load(OUISetupDriver.java:422)
    at oracle.install.ivw.db.driver.DBSetupDriver.load(DBSetupDriver.java:161)
    at oracle.install.commons.base.driver.common.Installer.run(Installer.java:216)
    at oracle.install.ivw.db.driver.DBInstaller.run(DBInstaller.java:129)
    at oracle.install.commons.util.Application.startup(Application.java:869)
    at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:164)
    at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:181)
    at oracle.install.commons.base.driver.common.Installer.startup(Installer.java:265)
    at oracle.install.ivw.db.driver.DBInstaller.startup(DBInstaller.java:117)
    at oracle.install.ivw.db.driver.DBInstaller.main(DBInstaller.java:148)

Oracle Database 12.1.0.2 is the first version that is certified on Windows 10.

Oracle Database 11.2 is not certified on Windows 10.

Just click Yes and continue.

By the way, the 11.2.0.4 installer does not throw this error on Windows 10.


Sum date between two date and group by month I have a table as below:
Name  Start Date  End Date
Joe   20/04/2021  20/05/2021
John  01/05/2021  28/05/2021

I am using a SQL table-valued function to return a table that has 2 columns: Month and Count total Date of this month.
Example:

Joe: 10 days in Apr, 20 days in May
John: 28 days in May

Finally I will return a new table

Month
Count

4
10

5
48

I tried to use datediff and datepart to group by month, but don't know how to sum after group. Is there any way to do this?
Besides, I want to add filter from date and to date.

Another approach:
CREATE TABLE dbo.DateRanges
(
    [Name] varchar(20) NOT NULL,
    [Start Date] date NOT NULL,
    [End Date] date NOT NULL,

    PRIMARY KEY ([Name], [Start Date]),
    CHECK ([End Date] >= [Start Date])
);

INSERT dbo.DateRanges
    ([Name], [Start Date], [End Date])
VALUES
    (CONVERT(varchar(20), 'Joe'), CONVERT(date, '20/04/2021', 103), CONVERT(date, '20/05/2021', 103)),
    (CONVERT(varchar(20), 'John'), CONVERT(date, '01/05/2021', 103), CONVERT(date, '28/05/2021', 103));

Solution:
SELECT
    [Month] = Months.m,
    [Count] =
        SUM
        (
            -- Number of days in the current month
            1 + DATEDIFF
            (
                DAY,
                -- Latest of [Start Date] and current month start date
                IIF(DR.[Start Date] <= MonthRange.StartDate, MonthRange.StartDate, DR.[Start Date]),
                -- Earliest of [End Date] and current month end date
                IIF(DR.[End Date] >= MonthRange.EndDate, MonthRange.EndDate, DR.[End Date])
            )
        )
FROM dbo.DateRanges AS DR
JOIN
(
    -- Month numbers
    VALUES
        (01), (02), (03), (04), (05), (06),
        (07), (08), (09), (10), (11), (12)
) AS Months (m)
    -- Months that overlap the [Start Date], [End Date] range
    ON Months.m BETWEEN MONTH(DR.[Start Date]) AND MONTH(DR.[End Date])
    OR Months.m BETWEEN MONTH(DR.[End Date]) AND MONTH(DR.[Start Date])
CROSS APPLY
(
    -- Start and end day of each month
    VALUES
        (
            DATEFROMPARTS(YEAR(DR.[Start Date]), Months.m, 1),
            EOMONTH(DATEFROMPARTS(YEAR(DR.[Start Date]), Months.m, 1))
        )
) AS MonthRange (StartDate, EndDate)
GROUP BY
    Months.m
ORDER BY
    Months.m;

Output:

Month
Count

4
11

5
48

Online demo:
db<>fiddle demo


Comma List vs Multiple Records I am currently building out an application that will contain many users, two roles and two groups. Logically we want users to be able to be administrators of other users within the two groups.

User Table

UserID         varchar    PK
DisplayName
Email
Role           int        FK `role`.`RoleID`

DATA [jschmo,Joe Schmo,joe@schmo.com,1]

Role Table

RoleID         int        PK     AI
RoleName       varchar

DATA [1=Master,2=Admin,3=User]

Group Table

GroupID        int        PK     AI
GroupName

DATA [1=Marketing,2=Infrastructure]

A user could potentially be an admin of both groups. I don't see the option to multi-select foreign keys but as I understand it, having a comma-delimited list in a column is a bad idea and I should use multiple records but I don't like the idea of having multiple records for each user.

Q: How would I keep track of which groups a user is an admin of?

Please, don't store comma-separated lists in a single column. This is just a disaster waiting to happen. If these are separate facts, they should be stored separately.

Table GroupMemberRoles
GroupID  FK
UserID   FK
RoleID   FK
(PK on all three, with perhaps other constraints)

Your queries (say, to find the admins of a certain group) should be of the form:

WHERE RoleID = 1 AND GroupID = @GroupID;

And NOT:

WHERE RoleID = 1 AND ',' + GroupIDList + ',' LIKE ',' + RTRIM(@GroupID) + ',';

Or to find which groups a user is an admin of should be similarly simple:

WHERE RoleID = 1 AND UserID = @UserID;


ndbcluster: Error: 1114, The table is full, when using table I have searched for a while about this error. I have a MySQL cluster running with 2 data nodes.  The cluster houses a few other databases.  I have a dump of a database in --tab format from another server.  I have modified the SQL to use ndbcluster as the table engine and can import the SQL successfully.  When I try to import data to the tables using mysqlimport, a few tables will populate but then I get the following message:

mysqlimport: Error: 1114, The table 'doc_archive' is full, when using table: doc_archive

Searching around for this error would suggest that I am out of memory on my data nodes, however that is not the case as you can see:

ndb_mgm> all report memory
Node 31: Data usage is 45%(179865 32K pages of total 393216)
Node 31: Index usage is 67%(88761 8K pages of total 131200)
Node 32: Data usage is 45%(179863 32K pages of total 393216)
Node 32: Index usage is 67%(88761 8K pages of total 131200)

Importing the database up until it is "full" only increases the data usage on each data node by 2%, and the index usage only goes up 3%.

Here is a copy of my config.ini from the management node:

[ndbd default]
# Options affecting ndbd processes on all data nodes:

NoOfReplicas=2    # Number of replicas

DataMemory=12G    # How much memory to allocate for data storage

IndexMemory=1G   # How much memory to allocate for index storage
            # For DataMemory and IndexMemory, we have used the
            # default values. Since the "world" database takes up
            # only about 500KB, this should be more than enough for
            # this example Cluster setup.

MaxNoOfOrderedIndexes=8192
MaxNoOfUniqueHashIndexes=512
MaxNoOfAttributes=100000
MaxNoOfExecutionThreads=8
MaxNoOfConcurrentOperations=256000
MaxNoOfTables=2048
MaxBufferedEpochs=100000
TimeBetweenEpochsTimeout=32000
TransactionDeadlockDetectionTimeout=1000000
TransactionInactiveTimeout=10000000
MaxNoOfTriggers=3072 # Default is 768

datadir=/var/lib/mysql-cluster   # Directory for this data node's data files
serverport=1187

NoOfFragmentLogFiles=1125
RedoBuffer=32M

[ndb_mgmd]
# Management process options:
nodeid=30
hostname=192.168.2.30
datadir=/var/lib/mysql-cluster

[ndbd]
nodeid=31
hostname=192.168.2.31

[ndbd]
nodeid=32
hostname=192.168.2.32

[mysqld]
nodeid=33
hostname=192.168.2.33

[mysqld]
nodeid=34
hostname=192.168.2.34

Where else should I be looking?  There is plenty of disk space and memory to spare.

I had a similar issue.  Except for me it was complaining about a large table being full, even though all nodes were using 0% data and index memory.

What fixed it for me was setting MaxNoOfExecutionThreads=4 (a value of 8 caused the table to be full).

I don't know why this worked, but it did.  I wish the ndbmtd executable would provide more descriptive output than just (the table is full), such as describing exactly which internal memory or resource is full, because neither my memory nor disk were anywhere being full.

Hope this helps!


Help with Oracle Pipelined function I have a pivot where I have a contract with tank numbers separated by ; and I've been given this example to pivot them down into rows (which works):

SELECT oks_c.contract_number,
       oks_l.line_id,
       oks_l.tank_numbers, column_value AS TANK_NUMBER
  FROM oks_contract_header oks_c
 INNER JOIN oks_contract_lines oks_l
    ON oks_l.contract_number = oks_c.contract_number AND
       oks_l.item_name LIKE '%.55201'
    , table(str2tbl(oks_l.tank_numbers,';')) acbb
ORDER BY oks_c.contract_number,
         oks_l.line_id,
         TANK_NUMBER

Here's the pipelined function:

CREATE OR REPLACE function DBRAJAH.str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) return
str2tblType
PIPELINED
as
    l_str      long default p_str || p_delim;
    l_n        number;
begin
    loop
        l_n := instr( l_str, p_delim );
        exit when (nvl(l_n,0) = 0);
        pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
        l_str := substr( l_str, l_n+1 );
    end loop;
    return;
end;

But I'm trying to understand why it works.  Particularly how the pipelined function results are not cross-joined?

I'm familiar with outer apply and table-valued functions in SQL Server, but this seems to be quite different - or is this really an OUTER APPLY?

It's not cross-joined because the pipelined function is row-dependent; you can't cross-join row 1's values from the pipelined function to row 2's values because row 1's pipelined function values don't exist anymore.

If the pipelined function weren't row-dependent - if it went off of a static value - I suspect it would cross-join.  (Try it and see!)


Should the Query Store be enabled on all databases? We are using SQL Server 2016 Standard Edition (SP2-CU2). We have a few servers hosting each between a few dozens to hundreds of databases. We have never enabled Query Store on our databases.
I know there are lots of advantages to turning on the Query Store for tuning. I was wondering, are there any cases when the query store should not be turned on? Or should I turn it on on all databases?

EDITED

Just in case it can help other people. What I've ended up doing was enabling the query store a few databases at the time, every day, and monitoring. So far, I've had the query store enabled on almost all of my databases without any performance impact. It has proven itself useful when investigating problems.

So you really have two questions:

  Are there any cases when Query Store should not be enabled?

Based on some reports of significant performance issues in the field when Query Store was enabled (although they are quite rare), one might infer that you should not enable Query Store on databases where it causes significant performance issues.

Of course the only way you'll know for sure whether it will cause a performance issue is to research all the known issues, make sure none of them will affect your system, and then enable it--but you still won't know if it will cause a performance issue until you enable it. For example, see FIX: Slow performance of SQL Server 2016 when Query Store is enabled. Note that enabling or changing settings in the query store can also flush the plan cache.

Secondly, if you aren't going to use the information and functionality it provides to improve the performance of the queries running on your server, then you shouldn't enable it.  Some environments, particularly large, heavily regulated organizations, may have such strict change control that you can't realistically do anything with Query Store.

  Should I turn it on on all databases?

I would certainly not enable it on hundreds of databases all at once, due to the possibility of causing a lot of problems and having too much data to analyze.  If your environment is such that you'll be able to use the Query Store to improve performance, then I would recommend enabling on a few database at a time.

If you have hundreds of databases, you probably don't have the staff to analyze all of the information, so it might make sense to enable it periodically on a few servers, see if there are any improvements to be made, then disable it and use it on some other servers for a while.  Or, when an application upgrade takes place, enable it for a while to see if the new code is triggering anything that Query Store can help with.


Why mongo is choosing the wrong index / execution plan? In production mongod logfile, we can see a lot of slow queries. Most of them are not using the best index and consequently the best execution plan is not used. However, when I run the same queries by myself in mongo shell, the correct index is used. So why for same query, we don't have same execution plan ?

Mongodb version : 4.0.2 (standalone)

Excerpt of mongod logfile :

2018-12-28T13:55:28.282+0100 I COMMAND [conn1032115] command mydb.products command: find { find: "products", filter: { origins_tags: "italia" }, sort: { last_modified_t: -1 }, skip: 320, limit: 20, singleBatch: false, maxTimeMS: 0, tailable: false, noCursorTimeout: false, awaitData: false, allowPartialResults: false, $readPreference: { mode: "secondaryPreferred" }, $db: "mydb" } planSummary: IXSCAN { last_modified_t: -1 } keysExamined:721542 docsExamined:721542 cursorExhausted:1 numYields:5637 nreturned:3 reslen:23886 locks:{ Global: { acquireCount: { r: 5638 } }, Database: { acquireCount: { r: 5638 } }, Collection: { acquireCount: { r: 5638 } } } protocol:op_query 5166ms

We can see following information :

planSummary: IXSCAN { last_modified_t: -1 }
keysExamined:721542 docsExamined:721542 (basically, the whole collection is examined)
nreturned:3
duration: 5166ms

However, these indexes are existing in collection :

db.products.createIndex({"origins_tags": 1,"sortkey": -1}, { background: true })
db.products.createIndex({"last_modified_t": -1}, { background: true })

Here is the optimized execution plan (the expected one):

planSummary: IXSCAN {"origins_tags": 1,"sortkey": -1}
keysExamined:331 docsExamined: 331
nreturned:3
duration: 11ms

So we can see a huge difference !

these indexes are existing in collection :

db.products.createIndex({"origins_tags": 1,"sortkey": -1}, { background: true })
db.products.createIndex({"last_modified_t": -1}, { background: true })

The MongoDB query optimizer chooses the most efficient query plan based on the query shape (combination of query predicate, sort, and projection) and candidate indexes. If there are multiple candidate plans for a given query shape, a trial evaluation will run to determine which index returns the initial batch of results (101 documents) with the least amount of "work" measured. The winning plan will be cached and periodically reevaluated if query performance or other circumstances change (for example, adding or dropping indexes).

Since neither index is ideal for your query, index selection may vary depending on which index returns the initial batch of results faster during plan evaluation:

The first index is more selective given origins_tags as filter criteria, but it requires a document fetch and in-memory sort (a blocking query stage with a 32MB limit) in order to return results ordered by last_modified. The work required to use this index for the given query will depend on the total number of matching documents: every matching document has to be fetched for the sort. The worst case scenario would be a query that needs to perform an in-memory sort of more than 32MB of data and results in an exception.
The second index returns results in the desired sort order but requires a collection scan to filter by origins_tags. The work required to use this index depends on how quickly the matches are found: this index can stream matches as they are found and stop as soon as there are enough matches. The worst case scenario would be a full collection scan to confirm there are no further matching documents.

If evaluating the two plans result in a tie (both appear to perform the same work to return initial results for a given query shape), the plan that doesn't require an in-memory sort will win.

You can see details of plan evaluation by explaining a query with allPlansExecution mode: db.products.find({...}).explain('allPlansExecution').

  keysExamined:721542 docsExamined:721542 (basically, the whole collection is examined)

This is the worst case scenario for the second index: there are 323 matches out of 721,542 documents and you've asked for results 320-340 (via skip: 20, limit: 20).

  I don't understand how the second index can give faster results during plan evaluation

Plan evaluation only considers which candidate plan returns the initial batch of results (101 documents) with less overall effort. The query planner does not run the plans to completion during evaluation, or keep any metrics on the distribution of key values. The cached query plan would have been based on a comparison where the second index did not not have to perform the collection scan.

  When mongo choose a bad plan, it should be able to see that the execution time is not good and it should not choose same plan for next queries.

Cached plans are re-evaluated if the performance declines, as per the Query Plans flow chart in the MongoDB documentation. However, if you have multiple candidate plans without a deterministic winner, it is possible for a query plan to be chosen that is faster for initial results (or the same query shape with different values), but suboptimal for later results.

How can I fix this?

To fix this issue you should add a compound index supporting your filter & sort criteria:

db.products.createIndex({"origins_tags": 1,"last_modified_t": -1}, { background: true })

Alternatively (and less ideally) you could provide an index hint to force the query to use the first index. Note that hinting will ignore any future indexes added that might be more ideal, and will also fail with an exception if the in-memory sort needs to work with more than 32MB of data.

Adding the suggested compound index would result in the most efficient ratio of keys and docs examined to the number of results returned.


Updating MySQL innodb table based on multiple joins I have a table with the following (slightly simplified) structure

CREATE TABLE `oak_relation` (
 `o_id` int(10) unsigned NOT NULL,
 `k_id` bigint(20) unsigned NOT NULL,
 `initial` float unsigned NOT NULL,
 `xy` float unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`o_id`,`k_id`),
 KEY `xy` (`xy`)
) ENGINE=InnoDB

There are multiple instances of the same o_id, and multiple instances of the same k_id, but the primary key ensures there are only single instances of a row with an individual o_id & k_id.
Values for initial range from 0 to 1.

I would like to run an update to generate values for "xy".
The update should perform the following operation:
For every row in the table, calulate the xy value;

xy = x * y

where

x = `initial` value / number of rows matching the o_id of this row
y = total number of distinct o_ids / number of rows matching the k_id of the row

My query so far:

UPDATE
    oak_relation AS final
    INNER JOIN
    (
        SELECT
            oak.o_id ,
            oak.k_id ,
            oak.initial,
            link.numLinks,
            COALESCE( ( oak.initial / link.numLinks ), 0 ) AS x
        FROM
            oak_relation AS oak
            LEFT OUTER JOIN
            (
                SELECT
                    o_id ,
                    COALESCE( COUNT( k_id ), 0 ) AS numLinks
                FROM
                    oak_relation
                GROUP BY
                    o_id
            ) AS link ON oak.o_id = link.o_id
    ) AS xjoin

    INNER JOIN
    (
        SELECT
            k_id ,
            COUNT( o_id ) AS numOforK ,
            COALESCE( ( tmp.totalO / COUNT( DISTINCT( o_id ) ) ), 0 ) AS y
        FROM
            oak_relation,
            (
                SELECT
                    COUNT( DISTINCT( o_id ) ) AS totalO
                FROM
                    oak_relation
            ) AS tmp
        GROUP BY
            k_id
        HAVING
            COUNT( o_id ) > 1
    ) AS yjoin

SET
    final.xy = xjoin.x * yjoin.y

WHERE
    final.o_id = xjoin.o_id
    AND
    final.k_id = xjoin.k_id
    AND
    xjoin.k_id = yjoin.k_id

The trouble I am having is my query doesn't seem to update all the rows and my SQL knowledge is a little limited in updates using joins.
I thought perhaps it was just not updating if the xy result was 0 (if initial is 0), but selecting the expected result set shows that some xy = 0 values are being set, also tested by setting default xy value to some arbitrarily large number and examining the result after update. Some rows were definitely being set to 0.

So I'm a little stuck! For the rows where the calculation happens, it appears to work correctly, I just seem to not be selecting the correct set of data to update so it calculates for every row of the table.

Thanks very much in advance for any help or insight you might be able to provide.

I think this will do:

UPDATE
    ( SELECT COUNT(DISTINCT o_id) AS total_oids
      FROM  oak_relation
    ) AS d
  CROSS JOIN
    oak_relation AS u
  JOIN
    ( SELECT o_id, COUNT(*) AS matching_o_ids
      FROM  oak_relation
      GROUP BY o_id
    ) AS o
      ON o.o_id = u.o_id
  JOIN
    ( SELECT k_id, COUNT(*) AS matching_k_ids
      FROM oak_relation
      GROUP BY k_id
    ) AS k
      ON k.k_id = u.k_id
SET
    u.xy = u.initial * d.total_oids / o.matching_o_ids / k.matching_k_ids ;

It would be good to check it out first by running the equivalent SELECT query:

SELECT
    u.o_id, u.k_id, u.initial,
    d.total_oids,
    o.matching_o_ids,
    k.matching_k_ids,
    u.initial * d.total_oids / o.matching_o_ids / k.matching_k_ids AS new_xy
FROM
    ( SELECT COUNT(DISTINCT o_id) AS total_oids
      FROM  oak_relation
    ) AS d
  CROSS JOIN
    oak_relation AS u
  JOIN
    ( SELECT o_id, COUNT(*) AS matching_o_ids
      FROM  oak_relation
      GROUP BY o_id
    ) AS o
      ON o.o_id = u.o_id
  JOIN
    ( SELECT k_id, COUNT(*) AS matching_k_ids
      FROM oak_relation
      GROUP BY k_id
    ) AS k
      ON k.k_id = u.k_id ;


Doing a point in time restore with CDC enabled; Possible? I discovered this week, the hard way, that the database restore options NORECOVERY and KEEP_CDC are mutually exclusive.  So, this begs the question.  How do you do a database restore, keeping CDC intact, with both full and log backups?

Doing research on MSDN etc. I cannot find any documentation on restoring a database with KEEP_CDC using any other option than a single full db restore with RECOVERY specified.

I was able to find one attempt that did the full and subsequent logs without the keep_cdc option waiting until the final log.  Only then was the table brought online with the RECOVERY and KEEP_CDC option.  The result was a corrupt CDC schema as demonstrated here.

If the intent is to KEEP_CDC on restore are you truley limited to a full backup only or is there a mechanism similar to the attempt above to keep it intact during multi-file restors on a server other than the original?

Change Data Capture, or CDC, does not require the KEEP_CDC keyword when restoring a database to the original server.    When restoring a database to a server other than the original server, you can use the KEEP_CDC keyword to specify that the change data capture data is retained in the database.  This is by design, and is clearly stated in BOL here under the section entitled "Restoring or Attaching a Database Enabled for Change Data Capture"

This code shows CDC in action both before and after a RESTORE DATABASE ... WITH NORECOVERY

CREATE DATABASE CDC_RESTORE_TEST
ON (NAME='CDC_RESTORE_TEST', FILENAME = 'C:\SQLServer\Data\CDC_RESTORE_TEST.mdf', SIZE = 10MB, FILEGROWTH = 1MB)
LOG ON (NAME = 'CDC_RESTORE_TEST_LOG', FILENAME = 'C:\SQLServer\Logs\CDC_RESTORE_TEST.ldf', SIZE = 1MB, FILEGROWTH = 1MB);
GO
ALTER DATABASE CDC_RESTORE_TEST
SET RECOVERY FULL;
GO
USE CDC_RESTORE_TEST;
GO
EXEC sys.sp_cdc_enable_db;
GO
CREATE TABLE Test
(
    ID INT NOT NULL CONSTRAINT PK_Test PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , DATA NVARCHAR(255) NOT NULL
);
EXEC sys.sp_cdc_enable_table 'dbo', 'Test', 'dbo_Test', 1, NULL;
GO
ALTER TABLE Test ADD Data1 NVARCHAR(255) NOT NULL;
WAITFOR DELAY '00:00:15'; /* wait for event to be captured */
EXEC sys.sp_cdc_get_ddl_history 'dbo_Test';

USE Master;
GO
BACKUP DATABASE CDC_RESTORE_TEST TO DISK='C:\SQLServer\CDC_RESTORE_TEST.BAK';
BACKUP LOG CDC_RESTORE_TEST TO DISK='C:\SQLServer\CDC_RESTORE_TEST_LOG.BAK';
GO
ALTER DATABASE CDC_RESTORE_TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE CDC_RESTORE_TEST;
GO
RESTORE DATABASE CDC_RESTORE_TEST FROM DISK='C:\SQLServer\CDC_RESTORE_TEST.bak'
WITH NORECOVERY;
RESTORE LOG CDC_RESTORE_TEST FROM DISK='C:\SQLServer\CDC_RESTORE_TEST_LOG.bak'
WITH RECOVERY;
GO
USE CDC_RESTORE_TEST;
GO
EXEC sys.sp_cdc_get_ddl_history 'dbo_Test';

Restoring the same backup onto the same server as a new database name appears to retain CDC:

USE Master;
GO
RESTORE DATABASE CDC_RESTORE_TEST1 FROM DISK='C:\SQLServer\CDC_RESTORE_TEST.bak'
WITH NORECOVERY
    , MOVE 'CDC_RESTORE_TEST' TO 'C:\SQLServer\Data\CDC_Restore_test1.mdf'
    , MOVE 'CDC_RESTORE_TEST_LOG' TO 'C:\SQLServer\Logs\CDC_Restore_test1.ldf';
RESTORE LOG CDC_RESTORE_TEST1 FROM DISK='C:\SQLServer\CDC_RESTORE_TEST_LOG.bak'
WITH RECOVERY
    , MOVE 'CDC_RESTORE_TEST' TO 'C:\SQLServer\Data\CDC_Restore_test1.mdf'
    , MOVE 'CDC_RESTORE_TEST_LOG' TO 'C:\SQLServer\Logs\CDC_Restore_test1.ldf';
GO
USE CDC_RESTORE_TEST;
GO
EXEC sys.sp_cdc_get_ddl_history 'dbo_Test';


Should I create a column for specific behavior? I would like to expose a problem I often have when I work with database and I never knows what to do.

I need to load data from a file into a table (table1) line by line with the help of a software. The file can be a csv or something similar. During the loading I also check the validity each line and each value in this line. If something is wrong I put a message another table (table2) with a reference to my line. A line can have more than one error.

So in my table1 I can have valid and invalid line. To check if a line is valid or not I need to check if there is a message, a line, in my table2. I hope this is clear.

My question now. Thinking about database design should I create a column valid (true/false) in my table one to avoid the have to check table2 each time I want to get value from table one.

Example:

table1:
reference | version | var1 | var2 | var3
----------------------------------------
 001      | 1       | x    | y    | z
 001      | 1       | a    | b    | c
 001      | 2       | null | y    | z
 001      | 2       | null | t    | u
 001      | 3       | x    | y    | z
 001      | 3       | a    | b    | c
 001      | 4       | null | y    | null
 001      | 4       | null | t    | null

table2:
reference | version | message
------------------------------------------
 001      | 2       | var1 cannot be null
 001      | 4       | var1 cannot be null
 001      | 4       | var3 cannot be null

In table1 I have an item 001 with 4 versions. Version 4 is not valid. This information of validity come from table2.

I always need to be able to request the latest valid reference. Get the last valid reference. To do so I need the check into 2 tables.

Check last line of table1 then compare it with table2. If invalid ignore last invalid line then repeat the request. This seems so heavy...

If I'm understanding your question correctly, what you might consider doing is having 3 tables. Table1 would be your "Line" table. You'd have a table, maybe called "Errors," containing the possible types of errors that could exist. Then you'd have a table joining them together, maybe called "LineErrors," which would reference the line, and the type of error it had.

In this case, you could also include a column in your Line table which held a bit value indicating if that line had at least 1 error, which you could determine by looking that line number's primary key up in the LineError table.

Hopefully this makes sense, and that I'm understanding your problem sufficiently.


Counting using window function with partition over an empty table If i have an empty table (created but without rows in it), and i would like to get the number 0 from the below query, how i could achieve that?

select count(*) over (partition by field_name)
from my_empty_table

Right now the postgres doesn't emmit any results as the table is empty.

Your query outputs one row for each row in the table, so an empty table must result in an empty output.
(COALESCE handles NULL values, but what you have is not a NULL value, but no value at all.)

You could add another query:

select count(*) over (partition by field_name)
from my_empty_table

union all

select 0
from my_empty_table
where not exists (select * from my_empty_table);


How to create clustered index on 100 GB table I have a heap table that takes about 104 GB of disk space with almost 3 billion rows.  I am trying to create a clustered index on this table on the [WeekEndingDate] column.  I have about 200 gb's free in the data file and about 280 gb's free in the tempdb.

I have tried two different methods.  First was to create the index directly on the table with the following command:

CREATE CLUSTERED INDEX CX_WT_FOLD_HISTORY
ON WT_FOLD_HISTORY (WeekEndingDate ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON,
IGNORE_DUP_KEY = OFF
, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
DATA_COMPRESSION = PAGE)

I tried it both with SORT_IN_TEMPDB = ON and OFF.  When using ON it filled up the tempdb and with OFF it filled the data drive.

Other method was to create a new blank table with the needed index and then insert the records from the heap into the new table.  This failed as well after filling up the data drive.

Any other suggestions on what to do.  Most things I've read stated that I would need about 1.2 times the size of the table to be used as a workspace while create the index.  I have way more than that and it still fails.  Any suggestions would be appreciated.

Here is my original heap table structure:

CREATE TABLE [dbo].[WT_FOLD_HISTORY](
[WeekEndingDate] [varchar](50) NULL,
[Division] [varchar](50) NULL,
[Store] [varchar](50) NULL,
[SKUNumber] [varchar](50) NULL,
[UPC] [varchar](50) NULL,
[SalesUnits] [varchar](50) NULL,
[SalesCost] [varchar](50) NULL,
[SalesRetail] [varchar](50) NULL,
[InventoryUnits] [varchar](50) NULL,
[InventoryCost] [varchar](50) NULL,
[InventoryRetail] [varchar](50) NULL,
[OnOrderUnits] [varchar](50) NULL,
[OnOrderCost] [varchar](50) NULL,
[OnOrderRetail] [varchar](50) NULL,
[ReceiptUnits] [varchar](50) NULL,
[ReceiptCost] [varchar](50) NULL,
[ReceiptRetail] [varchar](50) NULL,
[PermanentMarkdowns] [varchar](50) NULL,
[ReturnsToVendor] [varchar](50) NULL,
[POSMarkdowns] [varchar](50) NULL,
[TimeFK] [smallint] NULL,
[LocationFK] [int] NULL,
[ItemFK] [int] NULL
) ON [AcademySports_DataFG1]

If you've got a short-term need for disk space, one option would be to:

Shrink tempdb temporarily, freeing up as much space on that drive as seems safe.
Create a secondary data file for the DB the table is in on the tempdb drive.
Add the clustered index to the table.
Shrink the secondary file by migrating all data out of it.
Remove the secondary file.
Make sure the tempdb file is allowed to grow to its former size.
Rebuild indexes in the table's DB (the removal of the secondary file will have caused some fragmentation).

NOTE: as others have suggested, I'd only do this after things like temporarily removing non-clustered indexes from the table in question. This in particular will allow the addition of the clustered index to go faster, as the non-clustered indexes would all have to be rebuilt anyway (with a clustered index in place, the index key is used to locate the rows in the table itself).

That's actually another point - how wide is the key on the clustered index? If you do have non-clustered indexes, and the key on the clustered index is significantly wider than the pointer into the heap was, then the non-clustered indexes will consume more space after the clustered index is created.

If the cluster key consists of several columns, or even one large column (say, a varchar column with an average length of 25 or more), you may want to consider a surrogate key instead (usually a monotonically increasing value, for best INSERT performance.


Process Data cube from job on SQL server I have an SQL Server Agent job on server(X) that has a step that is supposed to process a data cube on a remote server(Y).

Whenever I run the job it fails and says that server(X) does not have permission to process the Cube or it does not exist. I believe I have the job set up correctly but how do I give access on server(Y) to server(X) to process the cube?? Below is the script I am using. with the "SQL Server Analysis Services Command"

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
<Object>
  <DatabaseID>Analysis Services Project1</DatabaseID>
  <CubeID>S2E</CubeID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Batch>

When I go to add the server(x) to the admin role on server(Y) via object explorer I cannot find the server through check names button. Please help.

I am using SQL Server 2014

There are 2 ways you can go about this. You can either create a service account for the sql agent service and give that account permissions on the olap server, or you can create a sql agent proxy account.

Personally I think the proxy account is the cleanest solution.

To create a proxy account refer to this msdn article: Create a SQL Server Agent Proxy.

This way you can for example create a user DOMAIN\OlapUserOnY which is only used for agent jobs running things on the Y server Analysis Services.

If you want to create a service account you have to be sysadmin on the SQL server where agent is running to be able to configure that on the Agent job.

Then on the agent job you just select either the proxy account or the service account in the run as field.

Refer to Create an Analysis Services Job Step to learn how to create a job and specify the account it's running under.

From that link:

  In the Run as list, select a proxy that has been defined to use the
  Analysis Services Command subsystem. A user who is a member of the
  sysadmin fixed server role can also select SQL Agent Service Account
  to run this job step.


SQL Server Express acting strange I'm not an IT pro, but a web developer that happens to have his own blog.
The blog is hosted on a private VM on which I have both IIS and SQL Server.

Sometime my blog, which is not super busy stops responding to requests, and today I did a bit more of troubleshooting and logging via remote desktop I found that every now and then the CPU acts weird and does the following.

It happens for a certain period of time and then goes back to normal. And after a while it starts again. I really have not clue of what might cause that. Can someone help me solve that nasty problem?
Thank you

If you happen to find that SQL Server is consuming the resources a few things you can check within SQL Server:

Adam Machanic's sp_whoisactive. It documents using it very well and it can provide a wealth of information about what your system is doing at times.

A quick query with the performance DMVs can also shed some light on what is going on.


Analysis

Thanks for the updates; it makes things clear.

F1E5E1E0F8E5, is סובארו when interpreted in CHARACTER SET hebrew.

CREATE TABLE `table` (
) ENGINE=InnoDB DEFAULT CHARSET=hebrew

is not sufficient -- It is possible for each VARCHAR column to override the CHARSET.  I'll assume that you have not done that.

Since the HEX in the table is consistent with CHARSET hebrew, I will  assume the input side of things is consistent.

Solution 1

The "black diamonds" are coming from the output tool.  What tool are you using?  Looks like the mysql commandline tool?  That needs to know how to display the data.  By default (I think), it interprets the bytes it gets (from the SELECT) as being UTF-8.  But they are not.  Are you running on Windows or *nix?  I don't know the details of setting it to Hebrew, but that is probably the solution.

Solution 2

Another solution is to start your 'mysql' session with SET NAMES utf8; (or utf8mb4).  That will announce that you want the bytes in the client to be utf8.  As the Hebrew chracters are coming from the table, they will be converted to utf8, and will (I think, without trying it) dislayed correctly.  Meanwhile, the table will continue to be storing F1E5 E1E0 F8E5, etc.

HTML

If you will be displaying on web pages, keep in mind that browsers make guesses on what the encoding is.  You could help them by either doing Solution 2 so that they get UTF-8, which they will readily assume, or you can stick with ISO 8859-8 and help them with <meta charset=...> and <form accept-charset...>` tags.

Please respond with the details of how you choose to solve the issue(s).  This is a scenario that is somewhat unusual.


Setting a date dynamically I would like to set a date dynamically in the where clause of my query but I can't figure out how to do it.

The query is doing a count of the number of rows with an entered on date >= 1st August year X.

The dynamic part is how the year is calculated.

If the query is run before 1st January the entered on date in the where clause will be currentyear/08/01.

If the query is run from 1st January to 31st July the entered on date in the where clause will be previousyear/08/01.

Does anyone know how this can be done?

Try using CASE expressions as you can specify qualifiers like what you're describing.
something like...

Code :

Select Count (*)
CASE when date >= 1st August year X THEN enteredondate = currentyear/08/01
ELSE enteredondate= previousyear/08/01
End
from DateTable


SQL Server 2005: Full-text search, order by matches Is it possible to do a full-text search in SQL Server 2005, ordering on the number of matches?

For example, let's say I have the following products for sale.

Tennis Racquets
Tennis Balls
Footballs
Hockey Sticks

If I do a search like:

select * from products where freetext(productname,'tennis ball')

The first three results would be returned as they all have "tennis" or "ball".  I would like to order the results so that "tennis balls", the one with two matches instead of one, is on top.

Is there a "match ratio" column, or something that can be used to rank how close the match was?  Is there a better way to do a full-text search that would allow for ranking?

You could try FREETEXTTABLE:

SELECT ft.[Rank], p.*
FROM dbo.products AS p
INNER JOIN FREETEXTTABLE(products, productname, 'tennis ball') AS ft
ON ft.key_column = p.key_column
ORDER BY ft.[Rank] DESC;


SqlServer: can one table's PK be the FK of other two table's? I have three tables project, users and proj_user.
The PK of proj_user table is the FK of project and users table's pk e.g.(project_id,user_id)

My question is, is it possible for the project_id and user_id's data to be different, like?:
project_id = '12345' and user_id = 'abc987'

Edit Thanks for the quick response. But just look at this scenario

TABLE project (
    project_id PRIMARY KEY
    ...
)

TABLE users (
    user_id PRIMARY KEY
    ...
)

TABLE proj_user (
proj_user_id
project_id
user_id
PRIMARY KEY (proj_user_id),
FOREIGN KEY (proj_user_id) REFERENCES projects (project_id),
FOREIGN KEY (proj_user_id) REFERENCES projects (user_id)
)

I want the project_id and user_id values to be different.

What do I need to change here?

If I read your question correctly, you're describing the following:

TABLE project (
    project_id PRIMARY KEY
    ...
)

TABLE users (
    user_id PRIMARY KEY
    ...
)

TABLE proj_user (
    project_id,
    user_id,
    PRIMARY KEY (project_id, user_id),
    FOREIGN KEY (project_id) REFERENCES projects (project_id),
    FOREIGN KEY (user_id) REFERENCES users (user_id)
)

... which is entirely valid, yes.


PostgreSQL json array of objects, default 0 values for properties if not found So my query looks like this:
select dk.product_id, dk.product_name,
  json_agg(
    json_build_object('location', dr.location, 'quantity', dr.quantity))::jsonb stock
from dax.dx_k dk
left join dax.dx_r dr on dr.product_id= dk.product_id
where dr.location in ('A', 'B', 'C', 'D')
group by product_name, product_id
order by product_id

And this results in:
00015
TEST PRODUCT NAME
[
  {"location": "A", "quantity": 15000.000000000000},
  {"location": "B", "quantity": 0.000000000000}
]

And my goal is to get the stock for all locations even if it doesn't have a record for C and D I'd like to return C = 0 and D = 0.
Like this:
[
  {"location": "A", "quantity": 1500},
  {"location": "B", "quantity": 0},
  {"location": "C", "quantity": 0},
  {"location": "D", "quantity": 0}
]

Is there a simple way to do this, because I don't want to overcomplicate the query unnecessarily?
UPDATE:
I have another table called sites which contains the locations to any given site and I'll include my site_id in the WHERE clause, e.g.
...
WHERE site_id = 1

will result in:
site_id|location
1      |A
1      |B
1      |C
1      |D

And I'll need all locations for the given site:
[
  {"location": "A", "quantity": 1500},
  {"location": "B", "quantity": 0},
  {"location": "C", "quantity": 0},
  {"location": "D", "quantity": 0}
]

The problem with this is that I have my products associated with the locations in dx_r on product_id.

IMHO You can't get this outcome with the current data.
Given this sample data:
create table product (product_id int, product_name text);
create table stock (product_id int, location text, quantity int);

insert into product values (1, 'Product 1');
insert into stock values (1, 'a', 10), (1, 'b', 20);

You can't get locations c and d because there is not such information in your data:
select
    dk.product_id,
    dk.product_name,
    dr.location,
    dr.quantity
from
    product dk
left join
    stock dr
    on dr.product_id = dk.product_id;

product_id | product_name | location | quantity
---------: | :----------- | :------- | -------:
         1 | Product 1    | a        |       10
         1 | Product 1    | b        |       20

You should add a new (or existing) table of locations, or a table valued function, just to be able to get the required data.
create table location (id text);
insert into location values ('a'),('b'),('c'),('d'),('e'),('f');

Now you can use a sub-query to get this information:
select
    p.product_id,
    p.product_name,
    (
    select
        json_agg(
            json_build_object('location', l.id,
                              'quantity', coalesce(s.quantity, 0)))::jsonb stock
    from
        location l
    left join
        stock s
        on s.location = l.id
        and s.product_id = p.product_id
    where
        l.id in ('a', 'b', 'c', 'd')
    ) stock
from
    product p

product_id | product_name | stock
---------: | :----------- | :-----------------------------------------------------------------------------------------------------------------------------------------
         1 | Product 1    | [{"location": "a", "quantity": 10}, {"location": "b", "quantity": 20}, {"location": "c", "quantity": 0}, {"location": "d", "quantity": 0}]

db<>fiddle here
Update
You can use sites table for that purpose in this way:
select
    dk.product_id,
    dk.product_name,
    (
    select
        json_agg(
            json_build_object('location', s.location,
                              'quantity', coalesce(dr.quantity, 0)))::jsonb stock
    from
        sites s
    left join
        stock dr
        on dr.location = s.location
        and dr.product_id = dk.product_id
    where
        s.site_id = 1
    ) stock
from
    product dk;

db<>fiddle here


What's the advantage of thread pool in MySQL/MariaDB? MariaDB thread pool
MySQL thread pool
I've read official document of MySQL/MariaDB thread pool,but I'm still confused about it; As I understand, thread pool has two advantages:
    1. limit too many connections, too many connection could lead to too many context switches, but it seems option "--max-connections" also could limit connections~
    2.reuse connected old thread:when a new connection is coming, unused old thread could be reused,but it seems option "--thread_cache_size"  also could reuse old thread

so,what's the advantage of thread pool?

The main advantage of the thread pool is that it generally limits the number of running operations to the value of thread_pool_size which is generally set to the number of CPUs on the server or how many MySQL will be using.  It prevents context switching and contention issues with other internal structures.  It is particularly helpful when there are many more queries to run than there are available CPUs.

Limiting connections with max-connections would limit the number of running queries by limiting the number of connections but any connections over that limit are denied with an error.  When using the thread pool more connections are allowed but the queries are queued by the thread pool.  Another thing to consider is that connections aren't always running queries so limiting the connections does not effectively limit running queries.

To the point about the thread cache, it does help when using thread per connection but the thread pool does its own thing with reusing threads.

max-connections can still be used to limit the connections but as more of a safeguard with a value of something like 10000 rather than a limiter for the amount of work for the server.

For example if you have 100 clients that want to run queries with a desired limit of 10 queries running concurrently, setting the connections limit to 10 will only allow 10 client to connect with the others being rejected with an error.  There would be at most 10 queries running at the same time but it is likely that on average there will be less than 10 queries running at a time.  Without limiting the connections, if all 100 of the clients wanted to run a query at the same time, there would be 100 threads executing at the same time which could cause contention issues leading to less throughput than with 10 connections.

If you were to use a thread pool size of 10 instead, all 100 clients would be able to connect and run their queries.  If all of the clients try to run a query at the same time, the thread pool will queue the queries and generally execute 10 in parallel which can have a higher throughput than the 100 concurrent queries in the previous example.


Best practices for schema changes and data migrations to a live database without downtime? How do you make schema changes to a live database without downtime?

For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all associated with specific users. If I wanted to move the email addresses to a new dedicated table, I'd have to change the schema and then migrate the email data across to the new table. How could this be done without stopping writes to the original table? Surely while data is written over from the old table to the new one, new data would continue to be written to the old table and be missed, right?

I guess this problem comes up pretty frequently but I can't find any standard solution for dealing with it.

This article deals with the problem but I didn't really understand step 3. He says to write to both tables, then migrate old data from the first table to the new one. How do you make sure you're only migrating old data?

(I use PostgreSQL on Heroku.)

You almost have your answer already:

Create the new structure in parallel
Start writing to both structures
Migrate old data to the new structure
Only write and read new structure
Delete old columns

As for step 3, use something like this (in one transaction):

Insert what is not there yet:

INSERT INTO new_tbl (old_id, data)
SELECT old_id, data
FROM   old_tbl
WHERE  NOT EXISTS (SELECT * FROM new_tbl WHERE new_tbl.old_id = old_tbl.old_id);

Update what has changed in the meantime:

UPDATE new_tbl
SET    data  = old.data
USING  old_tbl
WHERE  new_tbl.old_id = old_tbl.old_id
AND    new_tbl.data IS DISTINCT FROM old_tbl.data;

New data will not be touched, because it is identical in both places.


Influxdb and grafana: howto create series that is the sum of two other series I have these three queries. They show the average upload speed of three different docker containers over time:

The difference() call is needed, because the data contains cumulative "bytes_sent" values, and it never decreases.

Here is an example of the output:

I would like to create a third series that shows the sum of the above three graphs. If I simply remove the WHERE condition the it won't work:

The reason for this is that mean() always precedes difference(), and the mean value of these independent cumulative sums is meaningless:

The correct solution would be to calculate the three difference values and add them together. But I don't know how to do this in grafana?

Obviously the very best solution would be to store the differences in the database in the first place. Unfortunately, I'm not the one who is sending the data so I can't do anything about it.

In theory, switch Grafana query editor to the raw mode and write a query where you use a subquery - outer query will just sum data precalculated by inner query:

SELECT SUM(field_from_main_query) FROM (
  <your current Grafana query with GROUP BY time(10s) hostname>
) GROUP BY time(10s)


Gaming Website: Schema Design I want to make a gaming website for Magic the Gathering to report event results and management user decks.

Here's what I have so far.

Event is a tournament with Players competing in the tournament.
Players have Decks, which is a like deck of cards (in this case 60 Magic the Gathering cards).

So, the Decks table has these columns with foreign Ids related to the Events and Players table:

event_id
player_id

However, I also want the users to have Decks. Users are different from Players. Users log into the site. Then, they create and edit Decks.

Should I just create one Decks table or have a 2nd Decks table like UserDecks?

If I have one Decks table, it would have these columns:

event_id
player_id
user_id

However, the event Decks would not have user_ids and the user Decks would not have event_ids or player_ids. Is it okay to have null in columns with foreign Ids?

I could just do a Decks table for event decks and a UserDecks table for user decks but it doesn't seem right to have two Decks table that are the same except for some different columns with foreign Ids?

As far as I can see, your basic design shouldn't be anymore complex than I have drawn in the picture below:

This assumes that you have only one entity representing a person ("Player", could be "User" though), that decks MUST belong to a person and that multiple people can enter an Event as Participants. I assume they bring their own decks with them and that Events cannot own Decks.


Find the second highest value from a subquery or derived table I have a subquery which returns the following results.

'c003', '120000.00'
'c002', '90000.00'
'c001', '20000.00'
'c005', '8000.00'
'c004', '5000.00'

I want to find the second highest value from this. How can it be done?

The subquery is :

SELECT SUM(coursefees)
FROM enrollment e INNER JOIN batch b INNER JOIN course c
ON e.batchid = b.batchid AND b.courseid = c.courseid
GROUP BY c.courseid;

Write your join properly and just limit the result set after sorting if it's enough for you to get the second value, no matter if there are two or more courses with the same sum of coursefees. If you need to consider this, it becomes more complicated. Just let me know...

SELECT SUM(coursefees) AS sum_coursefees
FROM enrollment e INNER JOIN batch b ON e.batchid = b.batchid
INNER JOIN course c ON b.courseid = c.courseid
GROUP BY c.courseid
ORDER BY sum_coursefees DESC
LIMIT 1 OFFSET 1;

UPDATE:

That should do it:

SELECT * FROM (
  SELECT
  c.courseid,
  @rank := IF(@prev = SUM(coursefees), @rank, @rank + 1) AS rank,
  @prev := SUM(coursefees) AS sum_coursefees
  FROM enrollment e
  INNER JOIN batch b ON e.batchid = b.batchid
  INNER JOIN course c ON b.courseid = c.courseid
  , (SELECT @rank:=0, @prev:=NULL) var_init
  GROUP BY c.courseid
  ORDER BY sum_coursefees DESC
) sq
WHERE rank = 2;

Note though, that the order in the select clause is important. Do not switch columns.


How to Prevent a Many to Many When Number of Rows Shared Is Unknown I have two tables that look like the following:

Subjects
id|subject_name|..some other columns
1 |maths       |
2 |english     |
3 |science     |

Users
id|username|...some other columns
1 |Frank1  |
2 |Bob2    |
3 |Mike    |

A user should be able to have many subjects however the number of subjects they will have is unknown, is there some way to create a table that would prevent a many to many and if not what would be the best way of storing the subjects in the Users table?

The better solution is creating the intermediate table subject_user:

table name: User_Subject
Fields: idUser, idSubject
PK: idUser, idSubject

Make both ids primary key, so one user cannot have two or more times the same subject. In other words, a subject cannot have the same student two or more times.


CREATE TABLE and transactions Is there a way to use CREATE TABLE inside of a transaction in Sybase?

Assuming we're talking about Sybase ASE ...
To allow DDL (eg, create table) to be performed within a transaction the DBA can configure the database (where the DDL is to be performed) with the following:
sp_dboption <dbname>,'ddl in tran',true

NOTE: ddl in tran == true is not recommended since this can lead to excessive blocking, and in some rare cases deadlocking, on system table activity


SQL Server Full-text population deadlock I've created a full-text index on a non-persisted computed column in a table (details here).

However, every couple days, the population gets deadlocked and stopped. Specifically:

  DESCRIPTION:  A fatal error occurred during a full-text population and
  caused the population to be cancelled. Population type is: AUTO;
  database name is XYZ (id: 7); catalog name is UsersCatalog (id:
  8); table name Users (id: 863342140). Fix the errors that are
  logged in the full-text crawl log. Then, resume the population. The
  basic Transact-SQL syntax for this is: ALTER FULLTEXT INDEX ON
  table_name RESUME POPULATION.

The deadlock report is as follows:

<deadlock>
 <victim-list>
  <victimProcess id="process28fdc561088" />
 </victim-list>
 <process-list>
  <process id="process28fdc561088" taskpriority="20" logused="0" waitresource="PAGE: 7:1:6079614 " waittime="1171" ownerId="2020759813" transactionname="IFTSAutoNested"
           lasttranstarted="2019-09-16T00:16:49.027" XDES="0x28f61f40408" lockMode="S" schedulerid="8" kpid="8000" status="background" spid="35" sbid="0" ecid="0" priority="0" trancount="3">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="60" stmtend="814" sqlhandle="0x020000008bfb2728b1fbd70b14e785d0a8727ca1f478b5690000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
   </inputbuf>
  </process>
  <process id="process2966e0368c8" taskpriority="20" logused="20752" waitresource="PAGE: 7:1:6079661 " waittime="1171" ownerId="2020759849" transactionname="IFTSEndOfBatch"
           lasttranstarted="2019-09-16T00:16:49.047" XDES="0x29230f2e408" lockMode="IX" schedulerid="4" kpid="12296" status="background" spid="33" sbid="0" ecid="0" priority="0" trancount="0">
   <executionStack />
   <inputbuf>
   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <pagelock fileid="1" pageid="6079614" dbid="7" subresource="FULL" objectname="XYZ.sys.fulltext_index_docidstatus_863342140" id="lock2897baa0d00" mode="IX" associatedObjectId="72057606237061120">
   <owner-list>
    <owner id="process2966e0368c8" mode="IX" />
   </owner-list>
   <waiter-list>
    <waiter id="process28fdc561088" mode="S" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="6079661" dbid="7" subresource="FULL" objectname="XYZ.sys.fulltext_index_docidstatus_863342140" id="lock294a6477a00" mode="SIU" associatedObjectId="72057606237061120">
   <owner-list>
    <owner id="process28fdc561088" mode="S" />
   </owner-list>
   <waiter-list>
    <waiter id="process2966e0368c8" mode="IX" requestType="convert" />
   </waiter-list>
  </pagelock>
 </resource-list>
</deadlock>

In other cases it's deadlocking with a bulk process that copies data from one table to that one. Something like this:

-- Select a batch
UPDATE  TOP (@BatchSize)
        [import].[TempTable]
SET     BatchStatusFlag = 1
OUTPUT  inserted.Id, inserted.ServerId  INTO @UpdatedIds (SourceId, TargetId)
WHERE   BatchStatusFlag = 0

-- Move/update info for that batch
UPDATE  u
SET     ...
FROM    dbo.Users u
INNER JOIN [import].[TempTable] s ON s.Id = u.Id
INNER JOIN @UpdatedIds ids ON ids.Id = u.Id

In that case, the second UPDATE (the one that updates dbo.Users) is the one that deadlocks with the full-text service.

2 questions:

Why is it deadlocking/how can I avoid it?
When that happens, does it retry and reindexes those changes anyway? (even if with a longer delay)

SQL Server version: Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64)   Jul  5 2017 23:41:17   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Datacenter 6.3  (Build 14393: ) (Hypervisor)

Lets answer question 2 first. As you see in your first error message, it tells you to issue an:

ALTER FULLTEXT INDEX ON <table_name> RESUME POPULATION

If the reindex started automatically, this would not be needed.

Now, as to the first issue, let's work this through.

The Fulltext index build needs to ensure that the text of the fields used in the nonpersisted computed column does not get updated while the index is being rebuilt. Otherwise, the index would be corrupted. This will probably be done by holding shared page locks on the data while the persisted data is being built, then by holding exclusive locks on the data while the index is being updated.

On the other hand, the Bulk Copy process is attempting to add rows, which will update pages in the table. Since the process is Atomic (e.g. will ensure that all of the data is inserted correctly before releasing the implicit transaction), the process will check for the correct pages to insert (possibly pseudo-random sequence) data into, then lock the page to insert that data, and hold the lock until all of the data is in place. This can result in the deadlocks you are experiencing.

For the bulk update process, while updating, the JOINs will be executed, using Shared locks to identify the rows to be updated, then exclusive locks (on either the rows or the pages, depending) will be taken to perform the update. Again, if the sequence of rows/pages is pseudo-random compared to the sequence of pages checked by the FT index rebuild, some pages could be locked that the rebuild needs, while others could be locked by the rebuild that the update needs.

How could this be fixed? That is a more difficult question. It would be less likely to happen if you took out the overhead of calculating the non-persisted data in the index build (by persisting the field), but this would expand your data usage. You could also implement some form of a flag that the other processes check, turning it on before starting the rebuild and off after the rebuild completes, that would wait those processes until the rebuild is done.

There may be other possible solutions, but those are all I can think of at this moment.