MySQL help do I use Where not exist or Join? - Laravel

SELECT
    u1.id,
    u1.domain,
    t1.end_date

FROM urls u1
INNER JOIN
        (SELECT
            l1.urls_id,
            MIN(end_date) as end_date
         FROM urls u2
         INNER JOIN links l1
             ON u2.id = l1.urls_id
            AND l1.status = "Approved"
            AND (l1.end_date > now() OR l1.end_date IS NOT NULL)
         GROUP BY l1.urls_id) t1
ON u1.id = t1.urls_id

Source requirements (not 100% sure about p4, so this could be changed):

1). end_date NOT NULL  AND
2). end_date > now()
3). status is "Approved" AND
4). return latest end_date closer to present or now.


Restore ALL databases with sqlcmd in SQL Server (Express, Docker, 2017)

Based on what you want I would do the following:

Create two files - List.txt (with the list of DBs you want to
restore) and DBRestore.bat with sqlcmd command to run a stored
procedure 'DBRestore' inside SQL Server
Create a stored procedure 'DBRestore' which loads into SQL Server temp table the list from the LIST.txt and processes it - reads DB name one by one and restores them


The issue here is that SQL Server can only open the database master key if it has been encrypted with the service master key. In your scenario, the primary server can open the DB automatically because the DMK is encrypted by the SMK. When you failover, the new servers SMK was not used to encrypt the DMK so can't open it automatically.

When you run your script, it is decrypting the DMK using the password, re-generating it, then re-encrypting it using the service master key of the new server. The solution to this is quite straightforward:

sp_control_dbmasterkey_password

This system stored procedure is used to create a CREDENTIAL object in SQL Server that contains the DMK password. On start-up (or failover) SQL Server will attempt to use the SMK to decrypt the DMK. If that fails, it locates all credentials with the same family GUID as the DB being decrypted and tries each credential in turn until one is successful or no credentials are left.

Execute this stored procedure (providing the values as appropriate) on your secondary node and the next failover should see the DMK decrypted automatically.


That makes total sense.

Since you said the base_id is VARCHAR(255), your first query

SELECT sid FROM rf_fo.scald_atoms WHERE base_id = 493457;

really behaves like this

SELECT sid FROM rf_fo.scald_atoms WHERE CAST(base_id as unsigned) = 493457;

This causes every base_id to experience a CAST and a comparison. Full table scan required.

Your second query is a pure string comparison and the index has string-based values.


Instead of using the slow log (which are queries that have finished and logged), you may want to poll mysql for long running queries while they are still running.

You may need to try using mk-query-digest or pt-query-digest and poll the processlist.

I learned how to use mk-query-digest from this youtube video as a replacement for the slow log: http://www.youtube.com/watch?v=GXwg1fiUF68&feature=colike

Here is the script I wrote to run the query digest program

#!/bin/sh

RUNFILE=/tmp/QueriesAreBeingDigested.txt
if [ -f ${RUNFILE} ] ; then exit ; fi

MKDQ=/usr/local/sbin/mk-query-digest
RUNTIME=${1}
COPIES_TO_KEEP=${2}
DBVIP=${3}

WHICH=/usr/bin/which
DATE=`${WHICH} date`
ECHO=`${WHICH} echo`
HEAD=`${WHICH} head`
TAIL=`${WHICH} tail`
AWK=`${WHICH} awk`
SED=`${WHICH} sed`
CAT=`${WHICH} cat`
WC=`${WHICH} wc`
RM=`${WHICH} rm | ${TAIL} -1 | ${AWK} '{print $1}'`
LS=`${WHICH} ls | ${TAIL} -1 | ${AWK} '{print $1}'`

HAS_THE_DBVIP=`/sbin/ip addr show | grep "scope global secondary" | grep -c "${DBVIP}"`
if [ ${HAS_THE_DBVIP} -eq 1 ] ; then exit ; fi

DT=`${DATE} +"%Y%m%d_%H%M%S"`
UNIQUETAG=`${ECHO} ${SSH_CLIENT}_${SSH_CONNECTION}_${DT} | ${SED} 's/\./ /g' | ${SED} 's/ //g'`

cd /root/QueryDigest
OUTFILE=QP_${DT}.txt
HOSTADDR=${DBVIP}
${MKDQ} --processlist h=${HOSTADDR},u=queryprofiler,p=queryprofiler --run-time=${RUNTIME} > ${OUTFILE}

#
# Rotate out Old Copies
#

QPFILES=QPFiles.txt
QPFILES2ZAP=QPFiles2Zap.txt
${LS} QP_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9].txt > ${QPFILES}

LINECOUNT=`${WC} -l < ${QPFILES}`
if [ ${LINECOUNT} -gt ${COPIES_TO_KEEP} ]
then
        (( DIFF = LINECOUNT - COPIES_TO_KEEP ))
        ${HEAD} -${DIFF} < ${QPFILES} > ${QPFILES2ZAP}
        for QPFILETOZAP in `${CAT} ${QPFILES2ZAP}`
        do
                ${RM} ${QPFILETOZAP}
        done
fi

rm -f ${QPFILES2ZAP}
rm -f ${QPFILES}
rm -f ${RUNFILE}

Make sure

you a user called queryprofiler whose password is queryprofiler and who only has the PROCESS privilege
you put */20 * * * * /root/QueryDigest/ExecQueryDigest.sh 1190s 144 10.64.95.141
 in the crontab to run every 20 minutes (Each profile is 20 min less 10 seconds, Keeps the last 144 copies, and only runs if specfifc DBVIP is present [Alter script to bypass checking for DBVIPs])

The output produces a file with the 20 worst running queries based on the number of times the query was called X avg sec per query.

Here is the sample output of the query profiling summary of mk-query-digest

# Rank Query ID           Response time    Calls   R/Call     Item
# ==== ================== ================ ======= ========== ====
#    1 0x812D15015AD29D33   336.3867 68.5%     910   0.369656 SELECT mt_entry mt_placement mt_category
#    2 0x99E13015BFF1E75E    25.3594  5.2%     210   0.120759 SELECT mt_entry mt_objecttag
#    3 0x5E994008E9543B29    16.1608  3.3%      46   0.351321 SELECT schedule_occurrence schedule_eventschedule schedule_event schedule_eventtype schedule_event schedule_eventtype schedule_occurrence.start
#    4 0x84DD09F0FC444677    13.3070  2.7%      23   0.578567 SELECT mt_entry
#    5 0x377E0D0898266FDD    12.0870  2.5%     116   0.104199 SELECT polls_pollquestion mt_category
#    6 0x440EBDBCEDB88725    11.5159  2.3%      21   0.548376 SELECT mt_entry
#    7 0x1DC2DFD6B658021F    10.3653  2.1%      54   0.191949 SELECT mt_entry mt_placement mt_category
#    8 0x6C6318E56E149036     8.8294  1.8%      44   0.200667 SELECT schedule_occurrence schedule_eventschedule schedule_event schedule_eventtype schedule_event schedule_eventtype schedule_occurrence.start
#    9 0x392F6DA628C7FEBD     8.5243  1.7%       9   0.947143 SELECT mt_entry mt_objecttag
#   10 0x7DD2B294CFF96961     7.3753  1.5%      70   0.105362 SELECT polls_pollresponse
#   11 0x9B9092194D3910E6     5.8124  1.2%      57   0.101973 SELECT content_specialitem content_basecontentitem advertising_product organizations_neworg content_basecontentitem_item_attributes
#   12 0xA909BF76E7051792     5.6005  1.1%      55   0.101828 SELECT mt_entry mt_objecttag mt_tag
#   13 0xEBE07AC48DB8923E     5.5195  1.1%      54   0.102213 SELECT rssfeeds_contentfeeditem
#   14 0x3E52CF0261A7C3FF     4.4676  0.9%      44   0.101536 SELECT schedule_occurrence schedule_occurrence.start
#   15 0x9D0BCD3F6731195B     4.2804  0.9%      41   0.104401 SELECT mt_entry mt_placement mt_category
#   16 0x7961BD4C76277EB7     4.0143  0.8%      18   0.223014 INSERT UNION UPDATE UNION mt_session
#   17 0xD2F486BA41E7A623     3.1448  0.6%      21   0.149754 SELECT mt_entry mt_placement mt_category mt_objecttag mt_tag
#   18 0x3B9686D98BB8E054     2.9577  0.6%      11   0.268885 SELECT mt_entry mt_objecttag mt_tag
#   19 0xBB2443BF48638319     2.7239  0.6%       9   0.302660 SELECT rssfeeds_contentfeeditem
#   20 0x3D533D57D8B466CC     2.4209  0.5%      15   0.161391 SELECT mt_entry mt_placement mt_category

Above this output are histograms of these 20 top worst-performing queries

Example of the first entry's histogram

# Query 1: 0.77 QPS, 0.28x concurrency, ID 0x812D15015AD29D33 at byte 0 __
# This item is included in the report because it matches --limit.
#              pct   total     min     max     avg     95%  stddev  median
# Count         36     910
# Exec time     58    336s   101ms      2s   370ms   992ms   230ms   393ms
# Lock time      0       0       0       0       0       0       0       0
# Users                  1      mt
# Hosts                905 10.64.95.74:54707 (2), 10.64.95.74:56133 (2), 10.64.95.80:33862 (2)... 901 more
# Databases              1     mt1
# Time range 1321642802 to 1321643988
# bytes          1   1.11M   1.22k   1.41k   1.25k   1.26k   25.66   1.20k
# id            36   9.87G  11.10M  11.11M  11.11M  10.76M    0.12  10.76M
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ###
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `mt1` LIKE 'mt_entry'\G
#    SHOW CREATE TABLE `mt1`.`mt_entry`\G
#    SHOW TABLE STATUS FROM `mt1` LIKE 'mt_placement'\G
#    SHOW CREATE TABLE `mt1`.`mt_placement`\G
#    SHOW TABLE STATUS FROM `mt1` LIKE 'mt_category'\G
#    SHOW CREATE TABLE `mt1`.`mt_category`\G
# EXPLAIN
SELECT `mt_entry`.`entry_id`, `mt_entry`.`entry_allow_comments`, `mt_entry`.`entry_allow_pings`, `mt_entry`.`entry_atom_id`, `mt_entry`.`entry_author_id`, `mt_entry`.`entry_authored_on`, `mt_entry`.`entry_basename`, `mt_entry`.`entry_blog_id`, `mt_entry`.`entry_category_id`, `mt_entry`.`entry_class`, `mt_entry`.`entry_comment_count`, `mt_entry`.`entry_convert_breaks`, `mt_entry`.`entry_created_by`, `mt_entry`.`entry_created_on`, `mt_entry`.`entry_excerpt`, `mt_entry`.`entry_keywords`, `mt_entry`.`entry_modified_by`, `mt_entry`.`entry_modified_on`, `mt_entry`.`entry_ping_count`, `mt_entry`.`entry_pinged_urls`, `mt_entry`.`entry_status`, `mt_entry`.`entry_tangent_cache`, `mt_entry`.`entry_template_id`, `mt_entry`.`entry_text`, `mt_entry`.`entry_text_more`, `mt_entry`.`entry_title`, `mt_entry`.`entry_to_ping_urls`, `mt_entry`.`entry_week_number` FROM `mt_entry` INNER JOIN `mt_placement` ON (`mt_entry`.`entry_id` = `mt_placement`.`placement_entry_id`) INNER JOIN `mt_category` ON (`mt_placement`.`placement_category_id` = `mt_category`.`category_id`) WHERE (`mt_entry`.`entry_status` = 2  AND `mt_category`.`category_basename` IN ('business_review' /*... omitted 3 items ...*/ ) AND NOT (`mt_entry`.`entry_id` IN (53441))) ORDER BY `mt_entry`.`entry_authored_on` DESC LIMIT 4\G

Give it a Try !!!


Partitioning will not improve speed. Valid partitioning scenarios include storage management (via. hot-cold filegroups), data archival and ETL fast partition switch load. Not query performance. Here is a more detailed answer at How To Decide if You Should Use Table Partitioning which goes into details.

If you want to improve speed then you need to identify the bottlenecks and address them. You can follow How to analyse SQL Server performance for details. The answers will depend on your findings.

If you want a 'magic bullet' for data growing and ad-hoc analytical workloads problems then I would recommend looking into columnstores. I specifically say ad-hoc analytical because transactional OLTP workloads can and should be addressed with traditional indexes.


Indexes are not visible in a MongoDB replica set

Default _id Index

As per MongoDB BOL Here MongoDB creates a unique index on the _id field during the creation of a collection. The _id index prevents clients from inserting two documents with the same value for the _id field. You cannot drop this index on the _id field.

  Note: In sharded clusters, if you do not use the _id field as the shard key, then your application must ensure the uniqueness of the
  values in the _id field to prevent errors. This is most-often done by
  using a standard auto-generated ObjectId.

Create an Index

To create an index, use  db.collection.createIndex()

db.collection.createIndex( <key and index type specification>, <options> )

The db.collection.createIndex() method only creates an index if an index of the same specification does not already exist.

  Note: MongoDB indexes use a B-tree data structure.

> use StackExchange
switched to db StackExchange
> show collections
position
test
> db.createCollection("score")
{ "ok" : 1 }
> db.score.createIndex( { score: 1 } )
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
> db.score.getIndexes()
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "StackExchange.score"
        },
        {
                "v" : 2,
                "key" : {
                        "score" : 1
                },
                "name" : "score_1",
                "ns" : "StackExchange.score"
        }
]

Build Indexes on Replica Sets

For replica sets, secondaries will begin building indexes after the primary finishes building the index. In sharded clusters, the mongos will send  createIndex() to the primary members of the replica set for each shard, which then replicate to the secondaries after the primary finishes building the index.

To minimize the impact of building an index on your replica set, you can use the following procedure to build indexes.

  Important : To create unique indexes using the following procedure, you must stop all writes to the collection during this
  procedure.

  If you cannot stop all writes to the collection during this procedure,
  do not use the procedure on this page. Instead, build your unique
  index on the collection by:

  issuing db.collection.createIndex() on the primary for a replica set,
  or

  issuing db.collection.createIndex() on the mongos for a sharded cluster.

Ensure that your oplog is large enough to permit the indexing or re-indexing operation to complete without falling too far behind to catch up. See the oplog sizing documentation for additional information.
This procedure does take one member out of the replica set at a time. However, this procedure will only affect one member of the set at a time rather than all secondaries at the same time.
Before version 2.6  Background index creation operations become foreground indexing operations on secondary members of replica sets. After 2.6, background index builds replicate as background index builds on the secondaries.

For your further ref Here and Here


What type of locks does BEGIN TRANSACTION cause against a specific table that's being read from?

5 million rows is a lot, theoretically it would cause shared lock escalation for TableA to table-level lock in any case (explicit or implicit transaction)

But you can find this out experimentally, you just need to setup Extended Events session and monitor for "lock_escalation" event

Then run your query in explicit and implicit transaction and check out XE session logs to see the lock_escalation events


MySQL 5.6 Bug Amazon RDS with replica. AFTER INSERT TRIGGER is not working. Solutions?

According Paragraphs 1-3 of MySQL Documentation Replication and Triggers

  With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.

  This behavior is by design. If under row-based replication the slave applied the triggers as well as the row changes caused by them, the changes would in effect be applied twice on the slave, leading to different data on the master and the slave.

  If you want triggers to execute on both the master and the slave—perhaps because you have different triggers on the master and slave—you must use statement-based replication. However, to enable slave-side triggers, it is not necessary to use statement-based replication exclusively. It is sufficient to switch to statement-based replication only for those statements where you want this effect, and to use row-based replication the rest of the time.

MySQL default for binlog_format in 5.6 is STATEMENT and ROW in 5.7. You will have to set that in the DB Parameter Group. You may need to convert the binlog_format to STATEMENT on RDS Master, RDS Slave, and the External Slave. Only the can you configure a trigger on the External Slave.

Interestingly, MariaDB has in its Documentation Running Triggers on the Slave for Row-based Events. You may have to switch to MariaDB.


Lock or disable connections to Postgres while performing maintenance

You can set connection limit for role:

ALTER ROLE x SET CONNECTION LIMIT 0;

or for databases:

ALTER DATABASE db SET CONNECTION LIMIT 0;

After your maintenance chores are done, reset your limit to its previous value such as -1 for unlimited connections.


Azure SQL data warehouse - do transaction limits apply to CTAS opperations?

Ref:
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-develop-best-practices-transactions

The transaction safety limits only apply to fully logged operations. CTAS is capable of being minimally logged.  So answer depends, if CTAS is minimally logged safety limit does not apply and vice versa.

  Unlike fully logged operations, which use the transaction log to keep
  track of every row change, minimally logged operations keep track of
  extent allocations and meta-data changes only. Therefore, minimal
  logging involves logging only the information that is required to
  rollback the transaction in the event of a failure or an explicit
  request (ROLLBACK TRAN). As much less information is tracked in the
  transaction log, a minimally logged operation performs better than a
  similarly sized fully logged operation. Furthermore, because fewer
  writes go the transaction log, a much smaller amount of log data is
  generated and so is more I/O efficient.


SSMS cannot determine if remote computer is connected to domain

This is an issue with the picker for selecting accounts when it cannot connect to the SMB port (or if NetBIOS isn't enabled, but you state that it works if you disable the firewall).
You can either try to open port 445 on the firewall or create the login using T-SQL.
There used to be a Microsoft KB Article for older versions of Windows but I believe this still applies.


Your objective is slightly ambiguous:

  all records with a question mark (?)

Columns that are not character data types may or may not contain a question mark, depending on their text representation.

Also, this could mean to filter for any columns equaling '?' or just containing '?'.

A dead simple solution for the "containing" interpretation (and ignoring my first concern - which seems safe enough for '?') is to cast the whole row to text - generating the default text representation of everything, plus delimiters and decorators - none of which include the question mark (?) per default:

SELECT * FROM tbl t WHERE t::text LIKE '%?%';

You would run this for every (relevant!) table in the DB. (You'll want to exclude system tables at least.) I added an example implementation to the related question on SO:

How to search a specific value in all tables (PostgreSQL)?

Related answer on SE Code Review:

SQL: Search for a keyword in several columns of a table


Find which account SQL Server service is running BACKUP under

BACKUP will access the UNC under an impersonation context. This is a 'double-hop' situation and requires Kerberos constrained delegation to be configured.

This is already answered at https://stackoverflow.com/questions/18749224/restore-database-from-a-shared-folder


How much "Padding" do I put on my indexes?

If you're referring to the fill factor of the index, the only legitimate answer that you can get is "it depends." The fill factor of an index can be varied based on how much you'll be inserting into the existing leaf level pages of the index.

It's going to be best to create your indexes using the default settings and monitor the level of fragmentation over a period of time. You should also be routinely performing index maintenance using an index maintenance script. These scripts will automate the process of keeping your indexes in good operating condition.


Config settings in MySQL so that it saves data on disk monthwise/yearwise

You can use MySQL Partitioning.
You can partition your data on the basis of month/year.

Example: I have a table which I have partitioned by year

CREATE TABLE `RTestAll` (
  `recordDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `networkId` bigint(11) NOT NULL DEFAULT '0',
  `publisherId` bigint(11) NOT NULL DEFAULT '0',
  `feedId` bigint(11) NOT NULL DEFAULT '0',
  `subPublisherId` bigint(11) NOT NULL DEFAULT '0',
  `subId` varchar(100) NOT NULL DEFAULT '',
  `searches` bigint(20) DEFAULT NULL,
  `matches` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`recordDate`,`networkId`,`publisherId`,`feedId`,`subPublisherId`,`subId`),
  KEY `K_networkId` (`networkId`),
  KEY `K_publisherId` (`publisherId`),
  KEY `K_feedId` (`feedId`),
  KEY `K_subPublisherId` (`subPublisherId`)
) ENGINE=InnoDB CHARSET=latin1
 PARTITION BY RANGE (YEAR(recordDate))
(
 PARTITION p0 VALUES LESS THAN (2011) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2012) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN MAXVALUE ENGINE = InnoDB
) ;

For reference please have a look at Partitioning Types in MySQL.

For use of partitioning, your SELECT queries should be designed in such a way that they use the partition key.

Going ahead please also have a look at Limitations of partitioning.


Will removing a foreign key constraint affect performance?

At time of writing (PostgreSQL 9.4 and below), PostgreSQL's optimiser doesn't use foreign key constraints to prove anything or allow for extra optimisations. The only impact removing one can have on performance is a performance improvement due to the removal of foreign key checks.

It's a data integrity enforcement tool, not a performance tool.


6GB Table - 37Mi Rows - 'System.OutOfMemoryException' - SQL 2008 R2

System.OutOfMemory exception is a .NET exception which is basically telling you that the grid control experienced an OOM condition. I had blogged about this in detail in the past.

You could try the recommendations to trim down the result set which is definitely required in the intermediate levels of the procedure. But to circumvent the OOM condition in SSMS, you could either try with output to TEXT or output to a file or use SQLCMD to execute the same T-SQL procedure/command.


How to configure Oracle11g to send out Password Expiry Notifications vide SMS or e-mail?

With all the possibilities of not expired, in grace period and expired this can be quite tricky.  As well many databases have service accounts that there is no point in sending emails to as they do not have an email address. In order to get this right you need:

a database profile for the people whose passwords expire and you wish to advise
a table to hold their database username, email, phone
a procedure or external process so that when a new user is added you add them to your email table.  Do not put a trigger on dba_users!
a logging table so when someone's password expires and they say they were not notified you can show when the email was sent
a table/package to hold constants like the name of the email domain

I had this code created by someone on our team to answer the same problem. It works but could be improved.  I leave the profile, table and some package names as an exercise

PROCEDURE EmailExpiringPassword IS

      pEmailBody       VARCHAR2(32767);
      pEmailTitle      VARCHAR2(32767);
      pCount           INTEGER;
      pDBName          VARCHAR2(50);
      pDomain          VARCHAR2(50);
      --email of group issuing this warning
      --so they can be contacted IF the user is locked out
      pDBNameSupportEmail        VARCHAR2(255);
begin
      pDomain := <call a package to get the constant value for the domain name>
      pDBNameSupportEmail := <call a package to get the constant value for the email address of the group/team responsible for account management>

      SELECT d.name
      into pDBName
      FROM v$database d;

       FOR missed IN (  SELECT
                        du.username,
                        du.expiry_date,
                        to_char(coalesce(abs(cast(du.expiry_date - sysdate as integer)),0)) as days,
                        du.account_status,
                        du.profile
                        FROM dba_users du
                        left outer join <Your custom table to hold user email,phone> ud
                           on ud.db_username = du.username
                        where   ud.db_username is null
                        and du.profile in ('<profiles of interest>')
                      )
         LOOP
          pCount := pCount + 1;
          pEmailTitle := pEmailTitle || missed.username || ' missing record, ';

          pEmailBody := pEmailBody ||
                        '
' ||
                        'Username: ' || missed.username || '
' ||
                        'Profile: ' || missed.profile || '
' ||
                        'Expiry Date: ' || missed.expiry_date || '
' ||
                        'Account Status: ' || missed.account_status ||  '
' ||
                        'Database: ' || pDBName  || '
' ||
                        'Table: <Your custom table to hold user email,phone>' || '
' ||
                        'Missing record. Please add the user to the table <Your custom table to hold user email,phone>.'   || '
' ||
                        '' || '
'
                        ;

          IF (length(pEmailBody) > 30000)THEN
            EXIT;
          END IF;
         END LOOP;

         IF (pCount > 0)
           THEN
           <Your custom package to send email>(
           strFROM    => pDBName || '_missing_record@' ||  pDomain ,
           strsubject => pEmailTitle,
           strbody    => pEmailBody,
           strto      => pDBNameSupportEmail
           );

           INSERT into <Your custom table to hold user email,phone>_PASSWORD_EMAIL_LOG
           (user_data_pwd_email_log_id ,sentdate, username, EmailAddress, EmailBody)
           SELECT (SELECT coalesce(max(user_data_pwd_email_log_id),0)+1
           FROM <Your custom table to hold user email,phone>_PASSWORD_EMAIL_LOG),
           sysdate,
           pEmailTitle,
           pDBNameSupportEmail,
           pEmailBody
           FROM dual;
           commit;

         END IF;

         pCount := 0;

        FOR email IN (  SELECT
                        du.username,
                        du.expiry_date,
                        ud.firstdotlastname || '@' || pDomain as email,
                        cast(du.expiry_date - sysdate as integer) as days_number,
                        to_char(coalesce(abs(cast(du.expiry_date - sysdate as integer)),0)) as days,
                        account_status, CASE WHEN p.limit = 'DEFAULT' THEN 10 ELSE 10 END AS Limit
                        FROM <Your custom table to hold user email,phone> ud
                        inner join dba_users du
                             on ud.db_username = du.username
                        inner join dba_profiles p
                             on du.profile = p.profile
                             and resource_name = 'PASSWORD_GRACE_TIME'
                        where
                        du.profile in ('<profiles of interest>')
                        and (cast(du.expiry_date - sysdate as integer) between -10 and 10
                            or account_status = 'EXPIRED (GRACE)')
                        and ud.active_ind = 1
                      )
         LOOP

          pCount := pCount + 1;

          pEmailTitle := email.username || ' password expiring';

          pEmailBody := 'Username: ' || email.username || '
' ||
                        'Database: ' || pDBName  || '
' || '
' ;

          IF NOT (email.account_status = 'EXPIRED (GRACE)' and email.expiry_date is null) and email.days_number>0
              THEN
                pEmailBody :=
                'Your password will expire in ' ||  email.days || ' days. ' ||
                'Please reset your password. It will expire after ' ||
                email.expiry_date ||
                ' and locked after ' ||
                to_char(email.expiry_date + email.limit) || '.' ||
                'Contact ' || pDBNameSupportEmail || ' for details.' ||'
' ||'
';

          ELSE IF email.days_number <= 0 and email.expiry_date >= sysdate
               THEN
                pEmailBody :=
                'Your password expired ' ||  email.days || ' days ago. ' ||
                'Please reset your password. It will be locked after ' ||
                to_char(email.expiry_date + email.limit) || ' days.' ||
                ' Contact ' || pDBNameSupportEmail || ' for details.' ||'
' ||'
' ;

           ELSE
                pEmailBody := pEmailBody ||
                'Please login into ' || pDBName || ' to reset your password. ' || '
' ||'
';
           END IF;
        END IF;

          pEmailBody := pEmailBody ||
          '' || 'Please do not reply to this email, this address is not monitored.'   ||
          '
';

         INSERT into <Your custom table to hold user email,phone>_PASSWORD_EMAIL_LOG
         (user_data_pwd_email_log_id ,sentdate, username, EmailAddress ,  EmailBody)
         SELECT (SELECT coalesce(max(user_data_pwd_email_log_id),0)+1 FROM <Your custom table to hold user email,phone>_PASSWORD_EMAIL_LOG),
         sysdate,
         email.username,
         email.email,
         pEmailBody
         FROM dual;
         commit;

         <Your custom package to send email>(
         strFROM    => pDBName || '_password_expiring@' ||  pDomain ,
         strsubject => pEmailTitle,
         strbody    => pEmailBody,
         strto      => email.email
         );

         END LOOP;

-- delete logs 60+ days old
delete FROM PASSWORD_EMAIL_LOG
where cast(sysdate - sentdate  as integer) >=60;

end EmailExpiringPassword;


Cannot update parent rows with trigger on the same table

It looks like what you want to do is actually have a summary of the sum of used bytes in each folder, right? I believe that triggers in Oracle are not the best choice of technology to provide you this functionality. This could be better modeled using a view, or even better, a materialized view.

The problem with triggers is that they will serialize the DML to the table and will not scale too well. Imagine a situation where you are updating and inserting lots of rows... for each one of them you'll have an update to the folder table.

If you model your problem with a view instead you would not incur the overhead of the extra DML on the folder table. A simple query like this should do the trick:

SELECT fd.name AS folder_name,
       SUM(f.bytes) AS folder_size
  FROM folders fd, files f
 WHERE fd.id = f.folderId
 GROUP BY fd.name;

You may then make this query persistent by creating a view (the sum will run every time you query the view) or a materialized view with fast refresh on commit (in this case the materialized view stores the calculation as a pre-computed table, useful if the table is huge and the sum is costly).

The materialized view "fast refresh on commit" feature delegates to Oracle the task of recalculating the agregation every time the underlying table(s) changes.

The Data Warehousing Guide has an in dept explanation on this feature:
https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG015


Practical application of EXISTS vs. LEFT JOIN to find addresses for delivery

While you can use EXISTS to determine whether, say, a dropship address is present, you can't use it to tell what the actual address is. EXISTS doesn't return any data. For that, you'll need the join.

A better design might be well-served here, for example a single Addresses table with a type. But as I understand it your model is currently not flexible.


Where do caching servers conceptually fit in my architecture?

Your caching layer sits between Model and View Controller. You should not hit database for needless requests. These requests include in my opinion.

Almost all lookup tables. You read city and state list already. Why go to database again.
Facts used in almost every page. if you show User's detail every page. Hit to Database once and cache it.
Slow queries you can afford to show with some delay. For example you can cache How many orders your customer have and show it without going to database.

Note that Caching does not need to be for database only. For example you may cache messages from mail server, or expensive file parsing operation result also.

Caching is the number one layer which increases speed of application.


Replace all rows in a table based on date

This is a potential solution, just based on your sample data only, if your data sample has more complexity, such as in ONE day, you have multiple records of different extract_datetime for both prod and staging tables, we may need to find other ways (but in such scenario, your business rules may need to be modified to remove confusions, currently your business rules and your sample data are excellently matched, i.e. no confusion or grey area)

use tempdb
--drop table dbo.Prod, dbo.Staging, #tmp;
--go

-- create sample tables
create table dbo.Prod  (datefield datetime, field1 varchar(20), extract_datetime datetime);
create table dbo.Staging (datefield datetime, field1 varchar(20), extract_datetime datetime);
create table #tmp (datefield datetime) -- holds the deleted [DateField] value form dbo.prod

go
-- populate sample tables
insert into dbo.Prod (datefield, field1, extract_datetime) values
('20160701', 'P11', '2016-08-02 00:10:00')
,('20160701', 'P12', '2016-08-02 00:10:00')
,('20160701', 'P12', '2016-08-02 00:10:00')
,('20160701', 'P14', '2016-08-02 00:10:00')
,('20160702', 'P21', '2016-08-05 00:10:00')
,('20160702', 'P22', '2016-08-05 00:10:00')
,('20160703', 'P31', '2016-08-05 00:10:00')
,('20160703', 'P32', '2016-08-05 00:10:00')
,('20160703', 'P33', '2016-08-05 00:10:00')
go
insert into dbo.Staging (datefield, field1, extract_datetime) values
('20160701', 'S11', '2016-08-25 00:10:00')
,('20160702', 'S21', '2016-08-25 00:10:00')
,('20160702', 'S22', '2016-08-25 00:10:00')
,('20160702', 'S23', '2016-08-25 00:10:00')
,('20160702', 'S24', '2016-08-25 00:10:00')
,('20160703', 'S31', '2016-07-05 00:10:00')
,('20160703', 'S32', '2016-07-05 00:10:00')
,('20160703', 'S33', '2016-07-05 00:10:00')
,('20160703', 'S34', '2016-07-05 00:10:00')
,('20160703', 'S35', '2016-07-05 00:10:00')
,('20160707', 'S41', '2016-07-05 00:10:00') -- new record as per new requiremnt, 2016/08/15
go

-- delete records from Prod table as per business rules, but log the deleted [DateField] into #tmp for next step use
merge dbo.Prod as p
using dbo.Staging as s
on (p.DateField = s.DateField)
when matched and p.Extract_dateTime < s.Extract_datetime
then
   delete
output deleted.datefield into #tmp;

-- re-populate the prod table with records from staging table
insert into dbo.prod (datefield, field1, extract_datetime)
select datefield, field1, extract_datetime
from dbo.Staging
where Datefield in (select distinct datefield from #tmp)
or Datefield not in (select datefield from dbo.Prod) -- added as per new requirement, 2016/08/15

-- check final result
select * from dbo.prod
order by  Datefield


Using pgsql_fdw in a function result “ERROR: cache lookup failed for type 0”

Someone recently submitted a formal bug report that looks identical to your question. Without any follow-up so far.

If the problem is a mismatch between pgsql_fdw implementation and the way the plpgsql interpreter prepares its queries, using EXECUTE might help:

EXECUTE 'insert into test_tf (id,name) select id,name from ft_test';

This forces plpgsql to avoid pre-processing the query, and it's somehow the generic workaround against this kind of errors.

But anyway it looks like pgsql_fdw is quite experimental at the moment, it's not included in the PostgreSQL contrib directory, and its source code is suprisingly not available for download at: http://sourceforge.net/projects/interdbconnect/files/ where we would expect it.
Currently, you may have more success with dblink, the "old way".


Improving statistics on a large "WHERE IN" SQL Server query

The issue may not be with improving statistics, but rather a problematic indexing strategy.

Indexing all (or many) columns is usually not effective. Indexes were intended to be built from a small number of high-cardinality columns using small data types (int, bigints, small varchars).

You may consider hashing any huge columns like the varbinary(max) as part of the insert or ETL process and then indexing them:

How to handle uniqueness of many large columns in SQL Server?

How many rows in your main table? What percent of those rows will be returned by your query?

I ask because if your query returns ~20% of the data then nonclustered indexes will probably be eliminated as a retrieval path because scanning NC indexes can result in hitting the same datapage at different times during the scan to retrieve multiple rows on that page.


How to Join two tables with the join being regexp_replace

You can not directly use the alias in ON clause or in WHERE condition

So simply use your expression in ON clause:

SELECT t.indicator,
       regexp_replace(t.indicator,'[^0-9]') as numbers
from temp t
Left join temp2 t2
    on (regexp_replace(t.indicator,'[^0-9]') = t2.<Join column name>);

Cheers!!


Create INSERT INTO from SELECT in PostgreSQL

You can use pg_dump with the --inserts option. You should also check the manual of your SQL client. Some can export data as INSERT statements.

Another option is to just copy the rows that you delete into a new table:

create table _backup
as
with deleted as (
  delete from the_table
  where .... --<< put your condition here
  returning *
)
select  *
from deleted;

The above will delete rows from the_table and all rows that are deleted are copied into the table _backup


MySQL under what scenario suitable to use WHERE EXISTS

Not a fair test.  Your example is about as close as you can get between using JOIN to using EXISTS.

Also, it probably gave a different result -- What if there are 2 Johns?  The JOIN would count both; the EXISTS would count only once.

EXISTS is a "semi-join".  It is better than JOIN in cases where it can stop short of scanning for all rows that match.  Instead, it stops with the first match.  If there is a useful index, semi-join does not necessarily help much.  If not, it could be a big speed up.

In the past, IN ( SELECT ... ) was notoriously inefficient.  EXISTS (SELECT * ) is often a better choice.

Search around stackoverflow.com for more instances of EXISTS.  I think you will find a variety.


I don't have your SSAS database so I can't test the syntax, but I believe you would need to write your MDX as follows (notice the THIS= addition):

SCOPE ([Measures].[Total]);
THIS = SUM([Data Source].[Interpolation Method].&[3],[Measures].[Total]);
END SCOPE;

See the documentation for syntax details.


How to detach corrupt database in SQL Server

Like gbn commented, if the database is on storage that is not accessible, it is going to be set to suspect and the attempt to set it to single-user will always fail. The call to sp_dbremove will properly remove the entry from the database list, and it won't be attempted to be brought online next time you start the SQL service.

Be aware that sp_dbremove is deprecated, and DROP DATABASE should work just as well if the database is suspect.


MySQL `where...in` statement - does it remove duplicates?

Assuming that user_id is not a PRIMARY KEY, then MySQL will need to de-duplicate the subquery to apply the correct semantics. The DISTINCT keyword should not be required here since it doesn't change the semantics.

MySQL actually has more than one strategy for how to remove duplicates: https://dev.mysql.com/doc/refman/8.0/en/subquery-optimization.html

To see the strategy used, you need to paste the output from EXPLAIN FORMAT=JSON (it does not appear in the regular tabular EXPLAIN). You will see something like:

     "transformation": {
                   "select#": 2,
                   "from": "IN (SELECT)",
                   "to": "semijoin",
                   "chosen": true
                 }


SQL Server 2014 Always On .. can I have replication without a listener?

You can configure multiple listeners but what I think you want to do is just configure the other IP (for the 3rd replica) at the cluster level so that your AG resource can access it. If your cluster is configured for the multi-subnet then you should have the ability to add the IP for that 3rd replica to your current listener.

If I recall you might have to create the role in the WSFC for your listener as a client access point. I know this is the required configuration when I have built AGs in Azure environments, but those may be special circumstances compared to dealing with all on-premise setups.


What does remote database have to do for DB link in Oracle

What about remote database side, what does it have to do?

There must have Oracle Net installed on both local and remote database servers.
And the user being used should have CREATE SESSION system privilege on the remote database.

  On fixed user link, local database need to know ID and password set. Is the ID is a usual account with which someone can access to the remote database without DB LINK or the ID is only for DB LINK?

You can have to options to connect to the remote database,  one is using current user and another is fixed user which can be defined explicitly after CONNECT TO clause. That user is a remote user which must be exist on the remote database server and must have CREATE SESSION privilege . If you omit this then currently logged in user is used to access tables on the remote server.

[oracle@localhost admin]$ sqlplus scott/scott

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 30 16:39:08 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create database link dblink1 using 'account';

Database link created.

SQL> create database link dblink2 connect to jay identified by jay using 'account';

Database link created.

SQL> select * from emp@dblink1;
select * from emp@dblink1
                  *
ERROR at line 1:
ORA-28000: the account is locked
ORA-02063: preceding line from DBLINK1

SQL>

Look at the above example. I have connected to the local database as SCOTT user and create two dblinks to connect to the remote server called 'account' using 'account' TNSNAME.

First dblink dblink1 uses current user which is SCOTT in our case is used in order to select * from emp at the remote server 'account'. In the example the select statement has returned an error stating that the SCOTT user at the remote site is locked.

The second dblink dblink1 uses the fixed user called jay hence this dblink can be used to access tables owned by Jay schema as well as other tables according to the privileges the user has got.

For details please refer to the Oracle Documentation.
CREATE DATABASE LINK


I suspect the combined query is not doing what you think it is doing.

Add parentheses to make it clear whether the second ORDER BY belongs to the second SELECT or to the UNION.  Ditto for the LIMIT.

( SELECT ...
)
UNION
( SELECT ...
     -- Do the ORDER BY and LIMIT belong here?
)
-- Or here (for the UNION)?

For further discussion, Please provide

EXPLAIN ...
SHOW CREATE TABLE ...
how big are the tables


Exclude procedure call from transaction rollback

You can make the procedure have its on transaction - called an autonomous transaction, but be careful with this one, you can easily get yourself into deadlocks if you use it carelessly without knowing your transactions.

create or replace procedure p1 (...) as
  pragma autonomous transaction
begin
  ...
end;

Usually this is used for logging purposes.


Setting up SQL Server to handle queries from external domains

It depends on what you mean by a proper user name and password.

Windows Authentication

You will need to setup the Domain trust relationship between each domain. Your domain hosting the database server will need to trust the domain holding the web application. This should allow the domain accounts access the web application can connect to the database server, without having to use a separate account.

SQL Login

If you do not want to deal with the domain relationship you can simple configure SQL Server in mixed mode. You can find those instructions here. You would just create a SQL Login and grant the required access to the database(s) for the web application.


INSERT in a remote server is far too slow, how to speed it up?

Since  most mysql versions turn on autocommit, use a transaction to have the server sync data to disk only once and not on every row.

Add at the top of your .sql:

BEGIN TRANSACTION;

and append at the end:

COMMIT;

This will have the added advantage that either all will be inserted or none at all. Same for your php script. Or you may add begin/commit every say 50000 rows to commit in batches.


Restore the latest backup on the database

If you don't have access to the original server and all you have is the backup file, then you'll need to restore the headers from the backup file first to see what's inside it.

You can do this by:

create table #headers
(
BackupName              nvarchar(128),
BackupDescription       nvarchar(255),
BackupType              smallint,
ExpirationDate          datetime,
Compressed              bit,
Position                smallint,
DeviceType              tinyint,
UserName                nvarchar(128),
ServerName              nvarchar(128),
DatabaseName            nvarchar(128),
DatabaseVersion         int,
DatabaseCreationDate    datetime,
BackupSize              numeric(20,0),
FirstLSN                numeric(25,0),
LastLSN                 numeric(25,0),
CheckpointLSN           numeric(25,0),
DatabaseBackupLSN       numeric(25,0),
BackupStartDate         datetime,
BackupFinishDate        datetime,
SortOrder               smallint,
[CodePage]              smallint,
UnicodeLocaleId         int,
UnicodeComparisonStyle  int,
CompatibilityLevel      tinyint,
SoftwareVendorId        int,
SoftwareVersionMajor    int,
SoftwareVersionMinor    int,
SoftwareVersionBuild    int,
MachineName             nvarchar(128),
Flags                   int,
BindingID               uniqueidentifier,
RecoveryForkID          uniqueidentifier,
Collation               nvarchar(128),
FamilyGUID              uniqueidentifier,
HasBulkLoggedData       bit,
IsSnapshot              bit,
IsReadOnly              bit,
IsSingleUser            bit,
HasBackupChecksums      bit,
IsDamaged               bit,
BeginsLogChain          bit,
HasIncompleteMetaData   bit,
IsForceOffline          bit,
IsCopyOnly              bit,
FirstRecoveryForkID     uniqueidentifier,
ForkPointLSN            numeric(25,0),
RecoveryModel           nvarchar(60),
DifferentialBaseLSN     numeric(25,0),
DifferentialBaseGUID    uniqueidentifier,
BackupTypeDescription   nvarchar(60),
BackupSetGUID           uniqueidentifier,
CompressedBackupSize    bigint,
Containment             tinyint
);
go

insert into #headers exec('restore headeronly from disk=''c:\DEV\UnitTest.bak''');
go

You now have a temporary table(#headers) which contains details of all the backups contained within that device file.

To find the most recent full database backup we filter for only full db backups (backupType=1) and then look for the most recent backup in the device, which will be the one with the largest LSN. This gives us the position value, which is used in the RESTORE command to specify to SQL Server which backup we want to restore:

declare @filepos int;
select top 1 @filepos=Position from #headers where BackupType=1 order by lastlsn desc;
restore database unittest from Disk=N'c:\DEV\UnitTest.bak' with file=@filepos, recovery, replace;

As you say you will still have access to the original server, we can then query the tables in msdb that SQL Server uses to store this information.

The following queries are assuming you are running them in a SQL session on the original server. If you're not allowed to do that, then you'll need to look into using a linked server and 4 part naming.

There are 2 ways you can specify the set of backups you want to look at. One is via the path to the backup file, the other is by referencing the name on the backup device. I've included both in this query, with some notes about which lines to comment out depend on how you're doing it.

This will produce a RESTORE statement which you can run in a seperate SSMS session. I've also included an example at the bottom of how you could automate this, though you'd want to do some testing beforehand to make sure it's doing what you want.

declare @filepos int, @dbname varchar(50), @devname varchar(50)

/*
dbname is the name of the database you want to restore. Need this, as it's possible for backups of more than one database to be present in the same backup file or device

devname is either the name of the backup device, or the path to the .bak file
*/

select @dbname='Unittest', @devname='c:\DEV\unittest.bak'

select @filepos=max(c.position)
from
msdb.dbo.backupmediafamily a
inner join msdb.dbo.backupmediaset b on a.media_set_id=b.media_set_id
inner join msdb.dbo.backupset c on b.media_set_id=c.media_set_id
where
a.physical_device_name=@devname  --use this line if you know the path to the backup
--a.logical_device_name=@devname --this line if you only know a device name.
and c.type='D' and c.database_name=@dbname

--This will build the restore statement if you know the device name
select 'restore database ['+@dbname+'] from '+@devname+' with file='+cast(@filepos as varchar(4))+', recovery, replace'

--This will build the restore statement if you know the backup file path
select 'restore database ['+@dbname+'] from disk=N'''+@devname+''' with file='+cast(@filepos as varchar(4))+', recovery, replace'

/*
This section only if feeling brave and confident, and you've checked the output previously, as it will execute whatever the generated restore command is, so this could cause a major problem if not checked first, or even overwrite production if run on the wrong box.
*/

declare @restore_cmd varchar(250)
select @restore = 'restore database ['+@dbname+'] from disk=N'''+@devname+''' with file='+cast(@filepos as varchar(4))+', recovery, replace'
exec(@restore)


Oracle: Physically reorganizing rows of a huge table by a different key

Create an index on (record_key, record_id). This way oracle will most likely use the index only to answer your slow running query. You can add more columns to that index, so that you also get the needed content. Hopefully you don't need all columns.

Be careful about, what impact this index has on your load performance.


By default, most packages in most distributions in Linux, including official and distributions ones on Ubuntu set the default MySQL datadir on /var/lib/mysql. You can check the actual datadir by connecting to mysql and executing:
mysql> SHOW GLOBAL VARIABLES like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.02 sec)

Technically, there could be files outside of the datadir (per-table configuration, innodb files, etc.), but not with the default configuration.
Please note that, in general, that directory is owned by the mysql user, so you will not be able to read or write it as a regular user. Only the mysql system user or a user with administration privileges will be able to access there.
If you are using Filezilla, you probably will be using ftp/sftp/ssh. Please note that copying the files directly has the following restrictions:

You can only do it consistently if the server has been stopped. Otherwise you will end up with a corrupt set of files.
The files you will copy can only be used as a backup, or to clone the current setup, you cannot access the tables unless you set up a new mysql instance on your local pc
You may want to copy other files like logs (/var/log/mysql*) and the configuration files (/etc/my*)

If you want simply to access the mysql service/data remotely and operate with it, you do not need filezilla, only a mysql client, like the mysql monitor command line, MySQL Workbench or phpmyadmin, by setting them to connect to the remote host (assuming the service is available from the outside).
Update: if what you want is a text-based representation of your database, and you do not want to use a client like Workbench or phpmyadmin, please follow these steps:

In Ubuntu, execute on a command line prompt:
 mysqldump -u root --all-databases > ~/database.sql

Copy the database.sql file found on your home directory to your local pc using filezilla

If you want to export a single table and open it in an application like Excel, you can export it also in CSV format.


Do not use the "-startoption mount". Mount state is only for making some configuration changes, backup/recovery operations, and a handful of other administrative functions. Just use this:

srvctl start database -db myservice1
srvctl start database -db myservice2

Note that "tnsping" won't actually test whether a service is running, only whether the listener is running on the specified hostname and port number from the connection string. You could use any made up service_name and still get an "ok" response as long as the listener is up.


Why/when does creating a mysql trigger require SUPER privileges?

With regard to SUPER and TRIGGER privileges, you need the TRIGGER privilege to create triggers on tables you have rights for.

If you play binlogs, there is the possibility of seeing commands to create triggers on tables you do not have rights for. This is implied from the MySQL Documentation:

  CREATE TRIGGER requires the TRIGGER privilege for the table associated with the trigger. The statement might also require the SUPER privilege, depending on the DEFINER value, as described later in this section. If binary logging is enabled, CREATE TRIGGER might require the SUPER privilege, as described in Section 19.7, “Binary Logging of Stored Programs”.

With regard to your second point

  trusted not to create stored functions that will cause unsafe events to be written to the binary log.

There are occasions when certain SELECTs on a Master may yield different results on a Slave. For example, let's say you ran this query on a a MASTER

SELECT * FROM tbl WHERE name='...' LIMIT 10;

How could a SLAVE produce different results?

If you have a several hundred INSERTs commands occur on a Master, they get serialized in the Binary Logs. Even if a DB Connection 1 completes an INSERT before DB Connection 2, it is possible for DB Connection 2 to write its event in the Master's Binary Logs before DB Connection 1. This is particularly true in a write-intensive, heavy OLAP environment. This would make a Slave's copy of tbl physically out-of-sync with its Master. Running queries can hide this.

OK, you are probably thinking it's just a SELECT.

SELECTs are not in Binary Logs. INSERTs, DELETEs, and UPDATEs are.

Did you know you could do a query like this ?

DELETE FROM tbl LIMIT 10;

Replicating this can really make tbl out of sync.

Stored Functions can also have this headache if it the code within it is not DETERMINISTIC. This is why log_bin_trust_function_creators was invented. This alleviates mysqld of the responsibility of figuring out if data operations are synchronized in terms of replication. Yes, they fudged their way around downstream replication issues.


How to fix double-encoded UTF8 characters in postgres

That is pretty similar in PostgreSQL:
convert_from(convert_to(textcol, 'LATIN1'), 'UTF8')


Query doesn't respond when adding two columns

Summary

The main problems are:

The optimizer's plan selection assumes a uniform distribution of values.
A lack of suitable indexes means:

Scanning the table is the only option.
The join is a naive nested loops join, rather than an index nested loops join. In a naive join, the join predicates are evaluated at the join rather than being pushed down the inner side of the join.

Details

The two plans are fundamentally pretty similar, though performance may be very different:

Plan with the extra columns

Taking the one with the extra columns that doesn't complete in a reasonable time first:

The interesting features are:

The Top at node 0 limits the rows returned to 100. It also sets a row goal for the optimizer, so everything below it in the plan is chosen to return the first 100 rows quickly.
The Scan at node 4 finds rows from the table where the Start_Time is not null, State is 3 or 4, and Operation_Type is one of the listed values. The table is fully scanned once, with each row being tested against the predicates mentioned. Only rows that pass all the tests flow on to the Sort. The optimizer estimates that 38,283 rows will qualify.
The Sort at node 3 consumes all the rows from the Scan at node 4, and sorts them in order of Start_Time DESC. This is the final presentation order requested by the query.
The optimizer estimates that 93 rows (actually 93.2791) will have to be read from the Sort in order for the whole plan to return 100 rows (accounting for the expected effect of the join).
The Nested Loops join at node 2 is expected to execute its inner input (the lower branch) 94 times (actually 94.2791). The extra row is required by the stop parallelism exchange at node 1 for technical reasons.
The Scan at node 5 fully scans the table on each iteration. It finds rows where Start_Time is not null and State is 3 or 4. This is estimated to produce 400,875 rows on each iteration. Over 94.2791 iterations, the total number of rows is almost 38 million.
The Nested Loops join at node 2 also applies the join predicates. It checks that Operation_Type matches, that the Start_Time from node 4 is less than the Start_Time from node 5, that the Start_Time from node 5 is less than the Finish_Time from node 4, and that the two Id values do not match.
The Gather Streams (stop parallelism exchange) at node 1 merges the ordered streams from each thread until 100 rows have been produced. The order-preserving nature of the merge across multiple streams is what requires the extra row mentioned in step 5.

The great inefficiency is obviously at steps 6 and 7 above. Fully scanning the table at node 5 for each iteration is only even slightly reasonable if it only happens 94 times as the optimizer predicts. The ~38 million per-row set of comparisons at node 2 is also a large cost.

Crucially, the 93/94 row row goal estimation is also quite likely to be wrong, since it depends on the distribution of values. The optimizer assumes uniform distribution in the absence of more detailed information. In simple terms, this means that if 1% of the rows in the table are expected to qualify, the optimizer reasons that to find 1 matching row, it needs to read 100 rows.

If you ran this query to completion (which might take a very long time), you would most likely find that many more than 93/94 rows had to be read from the Sort in order to finally produce 100 rows. In the worst case, the 100th row would be found using the last row from the Sort. Assuming the optimizer's estimate at node 4 is correct, this means running the Scan at node 5 38,284 times, for a total of something like 15 billion rows. It could be more if the Scan estimates are also off.

This execution plan also includes a missing index warning:

/*
The Query Processor estimates that implementing the following index
could improve the query cost by 72.7096%.

WARNING: This is only an estimate, and the Query Processor is making
this recommendation based solely upon analysis of this specific query.
It has not considered the resulting index size, or its workload-wide
impact, including its impact on INSERT, UPDATE, DELETE performance.
These factors should be taken into account before creating this index.
*/

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Batch_Tasks_Queue] ([Operation_Type],[State],[Start_Time])
INCLUDE ([Id],[Parameters])

The optimizer is alerting you to the fact that adding an index to the table would improve performance.

Plan without the extra columns

This is essentially the exact same plan as the previous one, with the addition of the Index Spool at node 6 and the Filter at node 5. The important differences are:

The Index Spool at node 6 is an Eager Spool. It eagerly consumes the result of the scan below it, and builds a temporary index keyed on Operation_Type and Start_Time, with Id as a non-key column.
The Nested Loops Join at node 2 is now an index join. No join predicates are evaluated here, instead the per-iteration current values of Operation_Type, Start_Time, Finish_Time, and Id from the scan at node 4 are passed to the inner-side branch as outer references.
The Scan at node 7 is performed only once.
The Index Spool at node 6 seeks rows from the temporary index where Operation_Type matches the current outer reference value, and the Start_Time is in the range defined by the Start_Time and Finish_Time outer references.
The Filter at node 5 tests Id values from the Index Spool for inequality against the current outer reference value of Id.

The key improvements are:

The inner side scan is performed only once
A temporary index on (Operation_Type, Start_Time) with Id as an included column allows an index nested loops join. The index is used to seek matching rows on each iteration rather than scanning the whole table each time.

As before, the optimizer includes a warning about a missing index:

/*
The Query Processor estimates that implementing the following index
could improve the query cost by 24.1475%.

WARNING: This is only an estimate, and the Query Processor is making
this recommendation based solely upon analysis of this specific query.
It has not considered the resulting index size, or its workload-wide
impact, including its impact on INSERT, UPDATE, DELETE performance.
These factors should be taken into account before creating this index.
*/

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Batch_Tasks_Queue] ([State],[Start_Time])
INCLUDE ([Id],[Operation_Type])
GO

Conclusion

The plan without the extra columns is faster because the optimizer chose to create a temporary index for you.

The plan with the extra columns would make the temporary index more expensive to build. The [Parameters] column is nvarchar(2000), which would add up to 4000 bytes to each row of the index. The additional cost is enough to convince the optimizer that building the temporary index on each execution would not pay for itself.

The optimizer warns in both cases that a permanent index would be a better solution. The ideal composition of the index depends on your wider workload. For this particular query, the suggested indexes are a reasonable starting point, but you should understand the benefits and costs involved.

Recommendation

A wide range of possible indexes would be beneficial for this query. The important takeaway is that some sort of nonclustered index is needed. From the information provided, a reasonable index in my opinion would be:

CREATE NONCLUSTERED INDEX i1
ON dbo.Batch_Tasks_Queue (Start_Time DESC)
INCLUDE (Operation_Type, [State], Finish_Time);

I would also be tempted to organize the query a little better, and delay looking up the wide [Parameters] columns in the clustered index until after the top 100 rows have been found (using Id as the key):

SELECT TOP (100)
    BTQ1.id,
    BTQ2.id,
    BTQ3.[Parameters],
    BTQ4.[Parameters]
FROM dbo.Batch_Tasks_Queue AS BTQ1
JOIN dbo.Batch_Tasks_Queue AS BTQ2 WITH (FORCESEEK)
    ON BTQ2.Operation_Type = BTQ1.Operation_Type
    AND BTQ2.Start_Time > BTQ1.Start_Time
    AND BTQ2.Start_Time < BTQ1.Finish_Time
    AND BTQ2.id != BTQ1.id
    -- Look up the [Parameters] values
JOIN dbo.Batch_Tasks_Queue AS BTQ3
    ON BTQ3.Id = BTQ1.Id
JOIN dbo.Batch_Tasks_Queue AS BTQ4
    ON BTQ4.Id = BTQ2.Id
WHERE
    BTQ1.[State] IN (3, 4)
    AND BTQ2.[State] IN (3, 4)
    AND BTQ1.Operation_Type NOT IN (23, 24, 25, 26, 27, 28, 30)
    AND BTQ2.Operation_Type NOT IN (23, 24, 25, 26, 27, 28, 30)
    -- These predicates are not strictly needed
    AND BTQ1.Start_Time IS NOT NULL
    AND BTQ2.Start_Time IS NOT NULL
ORDER BY
    BTQ1.Start_Time DESC;

Where the [Parameters] columns are not needed, the query can be simplified to:

SELECT TOP (100)
    BTQ1.id,
    BTQ2.id
FROM dbo.Batch_Tasks_Queue AS BTQ1
JOIN dbo.Batch_Tasks_Queue AS BTQ2 WITH (FORCESEEK)
    ON BTQ2.Operation_Type = BTQ1.Operation_Type
    AND BTQ2.Start_Time > BTQ1.Start_Time
    AND BTQ2.Start_Time < BTQ1.Finish_Time
    AND BTQ2.id != BTQ1.id
WHERE
    BTQ1.[State] IN (3, 4)
    AND BTQ2.[State] IN (3, 4)
    AND BTQ1.Operation_Type NOT IN (23, 24, 25, 26, 27, 28, 30)
    AND BTQ2.Operation_Type NOT IN (23, 24, 25, 26, 27, 28, 30)
    AND BTQ1.Start_Time IS NOT NULL
    AND BTQ2.Start_Time IS NOT NULL
ORDER BY
    BTQ1.Start_Time DESC;

The FORCESEEK hint is there to help ensure the optimizer chooses an indexed nested loops plan (there is a cost-based temptation for the optimizer to select a hash or (many-many) merge join otherwise, which tends not to work well with this type of query in practice. Both end up with large residuals; many items per bucket in the case of the hash, and many rewinds for the merge).

Alternative

If the query (including its specific values) were particularly critical for read performance, I would consider two filtered indexes instead:

CREATE NONCLUSTERED INDEX i1
ON dbo.Batch_Tasks_Queue (Start_Time DESC)
INCLUDE (Operation_Type, [State], Finish_Time)
WHERE
    Start_Time IS NOT NULL
    AND [State] IN (3, 4)
    AND Operation_Type <> 23
    AND Operation_Type <> 24
    AND Operation_Type <> 25
    AND Operation_Type <> 26
    AND Operation_Type <> 27
    AND Operation_Type <> 28
    AND Operation_Type <> 30;

CREATE NONCLUSTERED INDEX i2
ON dbo.Batch_Tasks_Queue (Operation_Type, [State], Start_Time)
WHERE
    Start_Time IS NOT NULL
    AND [State] IN (3, 4)
    AND Operation_Type <> 23
    AND Operation_Type <> 24
    AND Operation_Type <> 25
    AND Operation_Type <> 26
    AND Operation_Type <> 27
    AND Operation_Type <> 28
    AND Operation_Type <> 30;

For the query that does not need the [Parameters] column, the estimated plan using the filtered indexes is:

The index scan automatically returns all qualifying rows without evaluating any additional predicates. For each iteration of the index nested loops join, the index seek performs two seeking operations:

A seek prefix match on Operation_Type and State = 3, then seeking the range of Start_Time values, residual predicate on the Id inequality.
A seek prefix match on Operation_Type and State = 4, then seeking the range of Start_Time values, residual predicate on the Id inequality.

Where the [Parameters] column is needed, the query plan simply adds a maximum of 100 singleton lookups for each table:

As a final note, you should consider using the built-in standard integer types instead of numeric where applicable.


Possible pitfalls of condensing 2 SQL Server instances into 1

I believe consolidation like you are planning to do is a good thing.  Depending on how the linked servers are queried, you may find a huge jump in performance (cross server joins are usually quite horrible when compared to cross database joins on the same server).

The downside is that your entire system is now sitting on one machine.  When this machine is down, all your apps are down.

Here is what I suggest you do to prepare:

Run the SQL Server Upgrade Advisor against each database.  It will tell you of any incompatible code that may exist (e.g. "*=" join syntax, no longer supported).

Identify DTS packages running against the old systems.  DTS is no longer supported.  You will have to rewrite them using SSIS.

Make sure the new system is powerful enough to handle the workoad.


Scheduled Job Does not work in oracle SQL*PLUS

Try this:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'delete_old_rows',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'delete_trips;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=SECONDLY',
        end_date        => add_months(sysdate,12),
        auto_drop       => FALSE,
        enabled         => TRUE
    );
END;
/

You don't need the EXECUTE part in job_action.

You can check DBA_SCHEDULER_JOBS.NEXT_RUN_DATE to check when it's next due to be executed. Obviously you can also check the other relevant columns to see if it's actually executed and/or failed (LAST_START_DATE,FAILURE_COUNT etc). Table doc link.


Prevent THREADPOOL waits due to idle worker thread trimming

The trace flag is 8061.
It is undocumented, so should only be enabled when suggested by Microsoft support.
The flag needs to be enabled globally (or on start up):
-- Enable globally
DBCC TRACEON (8061, -1);

SQL Server checks to see if it should trim idle workers in:
sqldk!SchedulerMonitor::CheckScheduler

It skips routine excess worker trimming if trace flag 8061 is set.
Worker trimming is performed by:
sqldk!WorkDispatcher::TrimIdleWorkers

Worker trimming will still be performed if the following memory value is greater than zero:
sqldk!SOS_OS::sm_WorkerPressureCount

Quite rightly, the trace flag will not prevent trimming under all scenarios, but it does work for the purpose expressed in the question.


MongoDB new secondary replica set node has much smaller data folder (dbPath)

Running rs.printSlaveReplicationInfo() on the Primary is not helping me figure whether the new node contains a full copy of the replica set.

Running rs.printReplicationInfo() is not giving me what I'm looking for either.

The answer lies in db.stats(); by comparing the number of objects and dataSize, of the primary and the new node, the new node is smaller.

However, when comparing the new node with another secondary, the values very closely match! Real question answered! Except for that dbPath size difference.


What is the best way to mirror an ASPState database?

Mirroring tempdb, that's a no. Have you checked Step-by-step SQL Mirroring the ASPState database?

  If the -sstype:p parameter is not specified, then by default, the
  ASP.NET sessions will be put into the TempDB database and not the
  ASPState database. This confused me for a while. This is fine for a
  normal non-mirrored environment because the ASP sessions will be
  cleared if the server is restarted. But, this is not fine for the
  mirrored environment, because we want to mirror the ASP session data
  itself, not just the Stored Procedures! Also, mirroring is not
  possible on the TempDB database. The -sstype:p parameter makes ASP.NET
  install the session tables into the ASPState database, and they'll be
  persisted if the server is restarted. This is exactly the behaviour we
  want for a mirrored environment.


Deterministic encryption will produce the same value as long as the inputs are the same. In database terms it means that:

  Queries can perform equality comparison on columns encrypted using deterministic encryption, but no other operations (for example, greater/less than, pattern matching using the LIKE operator, or arithmetical operations).

Source

So yes you can see if you have duplicates. How? By comparing the stored value of one row with the others in the column.

A theoretical example would be like this:

Select EncryptedColumn, COUNT(EncryptedColumn) from yourtable
GROUP BY EncryptedColumn
    HAVING COUNT(EncryptedColumn) > 1

This will tell you which values within your EncryptedColumn are duplicated. If you can provide the table schema a better example can be given.


Implementing Pub/Sub initiated from the database

Sounds like your looking for query notifications.

Exactly as you describe, this feature allows you to subscribe to notifications which are generated when the results of a query change. Typically more efficient than repeatedly polling the database for changes.

Under the hood, notifications rely on the mechanisms used to maintain indexed views. Because of this, notification queries are constrained by the same restrictions as apply to indexed views. It's a lengthy set of requirements that you should review in detail to determine if it would make this a non-starter for your scenario.


MyISAM table keeps crashing. What are my options?

In most cases, crashing a MyISAM table is nothing more than throwing mud on the MyISAM file header. It keeps a running count of the number of times the file has been opened. It increments upon opening and decrements upon closing.
You may want to look at the value for open-files-limit. This is an option that you cannot dynamically set with mysqld running. It can be configured at startup. What is interesting is its explanation in the MySQL Documentation:

Changes the number of file descriptors available to mysqld. You should
try increasing the value of this option if mysqld gives you the error
Too many open files. mysqld uses the option value to reserve
descriptors with setrlimit(). If the requested number of file
descriptors cannot be allocated, mysqld writes a warning to the error
log.
mysqld may attempt to allocate more than the requested number of
descriptors (if they are available), using the values of
max_connections and table_open_cache to estimate whether more
descriptors will be needed.

By default, mysqld sets this values based on the OS and how many maximum open file handles mysqld believes the OS will give to it.
You can take a risk and raise that number in /etc/my.cnf and restart mysql. As mentioned in the MySQL Documentation, a warning will be posted.
Talk to your sysadmin to see if your OS can have its file handle limit raised.
Once you can get it raised, restart mysql and see if the open_files_limit was raised as well.


Trigger is failing with no errors at all, why?

Try with:

USE `veeva_new`;

DELIMITER $$

DROP TRIGGER IF EXISTS veeva_new.userterritory_ai$$
USE `veeva_new`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `veeva_new`.`userterritory_ai` AFTER INSERT ON `userterritory` FOR EACH ROW
BEGIN
    # [ VARIABLES DECLARATION ]
    DECLARE vUserID VARCHAR(18) DEFAULT '';
    DECLARE vTerritoriRep VARCHAR(45) DEFAULT '';
    DECLARE vTerrytoriLastMod DATETIME DEFAULT '0000-00-00 00:00:00';
    # [ VARIABLES ASIGNATION ]
    SET vUserID         =NEW.userid;
    SET vTerritoriRep   =(select IFNULL(reps.territories_id,'N/A') from pdone.reps where reps.veeva_rep_id=vUserID LIMIT 0,1);
    SET vTerrytoriLastMod=(SELECT reps.lastSyncAt FROM pdone.reps WHERE reps.veeva_rep_id=vUserID LIMIT 0,1);

IF (NEW.territoryid != vTerritoriRep) THEN
    UPDATE pdone.reps
    SET reps.territories_id = NEW.territoryid,
        reps.lastSyncAt = IF(NOW()>vTerrytoriLastMod,NOW(),reps.lastSyncAt),
        reps.updatedAt=now()
    WHERE reps.veeva_rep_id=vUserID;
END IF;

END$$
DELIMITER ;

Assuming veeva_rep_id = NEW.userid (from userterritory).

This will update reps.territories_id when the NEW.territoryid is updated, reps.lastSyncAt when reps.lastSyncAt be greater than @TerritoryLastMod and reps.updatedAt = now(), current timestamp.


left joining and combining the results where they are not null

Without an actual query attempt from you, some thing are difficult to understand.

From what you've said, you're looking to group by date only, not on any information from c. That being the case, it's not clear that c needs to be in the query at all.

The following query should give you the totals you want, for all dates found in either c_r or c_s:

SELECT c_date
      ,COALESCE(SUM(r_amount),0) as r_amount
      ,COALESCE(SUM(s_amount),0) as s_amount
  FROM (
        SELECT c_date, 0 as r_amount, something_numeric as s_amount
          FROM c_s
        UNION ALL
        SELECT c_date, something_numeric as r_amount, 0 as s_amount
          FROM c_r
       ) sq
 GROUP BY c_date
;

If you need to limit rows by some value in c, INNER JOIN each of the tables in the subquery to c separately, with identical WHERE clauses. You could alternately include the values from c in the subquery's SELECT list, and filter in the main query, but that's probably less efficient.


Get all rows from table where JSON column contains a certain value

Double quotation marks are name delimiters. They are reserved for delimiting names (column names, table names etc.) that contain non-standard characters or those that you want to explicitly make case-sensitive (because that is their effect in PostgreSQL, which is according to the standard, too).

So, that is why all attempts with "192.168.1.1" fail: PostgreSQL indeed interprets those as names (specifically column names in each of those contexts).

The one case without quotes fails simply because 192.168.1.1 is an invalid token sequence. Numbers and some other constants can be represented in PostgreSQL without quotation marks but the tokens you specify there cannot be interpreted either as a number or anything else.

Finally, the one where you are using single quotation marks around the IPs fails because PostgreSQL is trying to interpret those as JSON literals. Why? Because the ip column is of type json – that is the type of column values returned by json_array_elements.

So, in order for that second attempt of yours to succeed, you should first of all represent the IPs as valid JSON string items. That means you need to enclose them in double quotation marks and then in single quotation marks, like this:

where ip in ('"192.168.1.1"','"192.168.1.2"')

However, that will give you this error:

  operator does not exist: json = json

Your options are:

convert ip to text:

where ip::text in ('"192.168.1.1"','"192.168.1.2"')

convert ip to jsonb

where ip::jsonb in ('"192.168.1.1"','"192.168.1.2"')

Either should get you going.

Note, though, that filtering your data like that can give you duplicates in the output. The issue is, the json_array_elements function turns the specified json value into a row set, repeating the source row's columns for each transposed item. So, for your example the FROM clause effectively produces the following row set:

       email       |          known_ips            |      ip
-------------------+-------------------------------+---------------
 user1@example.com | ["192.168.1.1","192.168.1.2"] | "192.168.1.1"
 user1@example.com | ["192.168.1.1","192.168.1.2"] | "192.168.1.2"
 user2@example.com | ["192.168.1.3"]               | "192.168.1.3"
 user3@example.com | ["192.168.1.2"]               | "192.168.1.2"

Since for user1 each ip will match the IN predicate, you will get the corresponding email returned twice.

To resolve that, instead of this:

...
from users, json_array_elements(known_ips) as ip
where ip::jsonb in ('"192.168.1.1"','"192.168.1.2"')

you can do something like this:

...
from users
where exists
(
  select *
  from json_array_elements(known_ips) as ip
  where ip::jsonb in ('"192.168.1.1"','"192.168.1.2"')
)


Concatenate multiple columns into one column (,) separated

I think you need to use GROUP CONCAT (only available in MySQL). If you are using MS SQL Server you can try this project: https://groupconcat.codeplex.com/


Adding Secondary Data file in SQL Always on

Just add it as you would normally do on the primary node. Make sure that all the nodes have the same disk configuration and enough disk space.


Use AS. For example:

mysql> select 'data' as mycolumnname;
+--------------+
| mycolumnname |
+--------------+
| data         |
+--------------+
1 row in set (0.00 sec)

mysql>

If you actually want to name the column after the date operation you're performing (your question isn't clear), you can do it by enclosing it in backticks. For example:

mysql> select 'data' as `Time + interval '2012-12-18 13:36:47' minute` ;
+----------------------------------------------+
| Time + interval '2012-12-18 13:36:47' minute |
+----------------------------------------------+
| data                                         |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql>

I suspect what you mean is the following:

select *,(Time + interval '2012-12-18 13:36:47' minute) AS mytime from chatnotiservice;


Is there a way to determine automatic storage table spaces?

Try the monitoring function:

select
  tbsp_name,
  case TBSP_USING_AUTO_STORAGE
    when 1 then 'YES'
    else 'NO'
  end auto_storage
from table (sysproc.MON_GET_TABLESPACE('',-1))


Is there a way to make pg_dump ignore errors instead of stopping?

This functionality is missing from pg_dump and the right solution would be to enhance it by adding a flag with something like --ignore-errors, the comments miss the point that backing up some tables is better than none.
In a scenario where you only want to restore some tables this would be helpful.

There are few options using which you can implement a workaround:

Use the parameter below to reduce timeout to 10 sec, log the table and dynamically exclude that table from a new pg_dump command.
--lock-wait-timeout=timeout
--exclude-table=table
If you have a standby DB, use that to do your backups and you will have lesser chance of this issue happening.
If the issue is due to some unlogged stage tables, you can use the parameter below to ignore them ( I have not tested this so not 100% sure)
--no-unlogged-table-data
Write a wrapper script and backup each schema or table, this way if the failure happens you will miss one schema or table, you will have to make sure to implement a similar wrapper for restore as doing it by hand would be problematic, also this would not be consistent at a DB level.


Aggregate function to return any (first) encountered value

There are various ways to do this, with different performance, depending on the distribution of data (number of distinct object_id values, etc).

The easiest query to write - but not necessary the most efficient, is of course using an aggregate, MIN() or MAX():

SELECT object_id, MIN(data2) AS data2
FROM cache
GROUP BY object_id ;

If you have an index on (object_id, data2) this will not be too bad in recent versions of Postgres that can use index-only-scan for the execution plan.

Another way would be using DISTINCT ON syntax. The same index as above would help:

SELECT DISTINCT ON (object_id)
    object_id, data2
FROM cache
ORDER BY object_id ;

If, compared to the table size, there is a small number of object_id values, a different approach would be much more efficient. Provided you also have another tables (say objects) that has object_id as its primary key:

SELECT o.object_id, c.data2
FROM objects AS o
  CROSS JOIN LATERAL
     ( SELECT data2
       FROM cache AS c
       WHERE c.object_id = o.object_id
       ORDER BY c.data2
       LIMIT 1
     ) AS c ;

The same index would be needed. The ORDER BY is not required but with the index in place, it won't hurt efficiency. If you don't have an objects table then that part would have to be replaced with:

---
FROM ( SELECT DISTINCT object_id FROM cache) AS o
  CROSS JOIN LATERAL
---

But you'd lose some efficiency, especially in older versions. In that case, you could replace this subquery with a complicated recursive query that traverses the object_id index efficiently. See the Posgres docs for more details: Loose Index Scan.

Read also these great answers by Erwin in the related questions:

How do I efficiently get “the most recent corresponding row”?.
Optimize GROUP BY query to retrieve latest record per user
Best performance in sampling repeated value from a grouped column

And last but not least, the main reason for the problems is this:

  I have denormalized data and I want to avoid ... (I know that all values in that group are equal booleans).

Normalizing the table would lead to much more efficient queries.


REORG command does not work in IBM Data studio but same works in Command Line Processor

I guess you tried to run it in an editor window.
YOu have to switch to

  DB2 server Command Line Processor

in the Configuration tab of the editor.
Do not forget to explicitly connect to your database

connect to sample ;
reorg table suppliers;

Alternatively you could switch to "Administer Databases" and run the reorg from the contect menu of a table:


Test

Select case when sc.ars = 0
            then 0
            else SUM(sc.ars / NULLIF(SUM(ic.lit) OVER (PARTITION BY NULL),0))
            end
FROM salesconfig sc
Left Join income ic
On sc.empID = ic.empID


The following works but there's probably some more clever version. Explanation of the query's logic:

We first find how many "resets" have been done up to and including the current row by counting the not nulls of the reset_val column, so we can separate the rows into subgroups.

We also use another window function LAST_VALUE() with IGNORE NULLS, so we can find the last reset_value.

Note that both these window functions COUNT() and LAST_VALUE() have an ORDER BY, therefore the default window ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Omitted in the query, to make the code more clear.

Assuming that the val is not nullable, the other window function could also be shortened, from:

       COALESCE(SUM(val) OVER
         (PARTITION BY group_id, reset_count
          ORDER BY order_val
          ROWS BETWEEN UNBOUNDED PRECEDING
                   AND 1 PRECEDING), 0)

(avoiding the COALESCE() as well) to:

       SUM(val) OVER
         (PARTITION BY group_id, reset_count
          ORDER BY order_val)
       - val

Finally, in the second cte, we use the above found subgroups (using PARTITION BY group_id, reset_count) to find the cumulative sums.

WITH x AS
  ( SELECT temp.*,
           COUNT(reset_val) OVER
               (PARTITION BY group_id
                ORDER BY order_val)
             AS reset_count,
           COALESCE(LAST_VALUE(reset_val IGNORE NULLS) OVER
               (PARTITION BY group_id
                ORDER BY order_val), 0)
             AS reset_value
    FROM temp
  ) ,
y AS
  ( SELECT x.*,
           COALESCE(SUM(val) OVER
             (PARTITION BY group_id, reset_count
              ORDER BY order_val
              ROWS BETWEEN UNBOUNDED PRECEDING
                       AND 1 PRECEDING), 0)
           + reset_value AS cumsum
    FROM x
  )
SELECT *
FROM y ;

Test at SQLfiddle.

Another variation, based on @Chris' recursive answer. (slightly improved, works with non-consecutive order_val, avoids the final GROUP BY).
Also works in case the first row of a group has a reset_val:

WITH row_nums AS
  ( SELECT id, group_id, order_val, reset_val, val,
           ROW_NUMBER() OVER (PARTITION BY group_id
                              ORDER BY order_val)
             AS rn
    FROM temp
  ) ,
updated_temp (id, group_id, order_val, reset_val, val, rn, cumsum) AS
  ( SELECT id, group_id, order_val, reset_val, val, rn,
           COALESCE(reset_val, 0)
    FROM row_nums
    WHERE rn = 1
  UNION ALL
    SELECT curr.id, curr.group_id, curr.order_val, curr.reset_val, curr.val, curr.rn,
           COALESCE(curr.reset_val, prev.val + prev.cumsum)
    FROM row_nums  curr
      JOIN updated_temp  prev
        ON  curr.rn-1 = prev.rn
        AND curr.group_id = prev.group_id
  )
SELECT id, group_id, order_val, reset_val, val, cumsum
FROM updated_temp
ORDER BY group_id, order_val ;

Test at SQLfiddle-2.

One more variation, using the older (proprietary) CONNECT BY syntax for recursive queries. More compact but I find it more difficult to write and read than the CTE version:

WITH row_nums AS
  ( SELECT id, group_id, order_val, reset_val, val,
           ROW_NUMBER() OVER (PARTITION BY group_id
                              ORDER BY order_val)
             AS rn,
           COALESCE(reset_val, 0) AS cumsum
    FROM temp
  )
SELECT id, group_id, order_val, reset_val, val, rn,
       COALESCE(reset_val, PRIOR val + PRIOR cumsum, 0) AS cumsum
FROM row_nums
START WITH rn = 1 OR reset_val IS NOT NULL
CONNECT BY  rn-1 = PRIOR rn
        AND group_id = PRIOR group_id
        AND reset_val IS NULL
ORDER BY group_id, order_val ;

Tested at SQLfiddle-3.


Parent/child relationship or not if the name of the field implies the relationship?

What you describe is a variant of "Materialized Path", see for example https://communities.bmc.com/docs/DOC-9902 for an example. I say variant because you have no separator token, but are instead using a fixed number of positions for each different level.

In Materialized Path you usually don't have an explicit parent/child relationship represented, instead it is encoded into the "path" string. To determine sub-categories, use like as in your example. For ancestor-categories you need some procedural part that splits the path-string. Direct parent is the first ancestor. Direct children can be located by using a combination of like and length() function. Root and leaf predicates are easy to define as well.

As a starting point, why would you like to add a parent-child relationship (if it is not for the operations described above)? If the answer is referential integrity it will be a bit backward since you already have the domain at hand (your existing table). Adding a separate relation (table) to represent the parent/child relationship would be something like:

create table ...
( category varchar(...) not null
,     constraint ...
          foreign key ... references ...
, parent_category varchar(...) not null
,     constraint ...
          foreign key ... references ...
)

You could have a trigger populate this table after insert of a new "category"
But it does not really protect you from anything, it would act more like a report of your encoded parent child relation.

The other option is to add a nullable attribute to your existing table, your "top-categories" would have null as a parent, and the others would have substr(code, 1, length(code)-2) as a parent. This would be some kind of mix between "adjacency list" and "materialized path". You would still have to protect against anomalies like ('01020304', NULL) and ('010203','0202'). It is possible to add triggers that validates this.

If you want to keep your current table as is it is probably easiest to add validation code, either in triggers or in your application that makes sure that there exists a "super-category".

If you are willing to split the code in parts you could have a look at "Nested Sets". It is described in the link I gave above, or as an additional transitive closure relation, see for example http://karwin.blogspot.se/2009/04/sql-antipatterns-strike-back-slides.html. A slight variation of the same method is described at:  http://dustbite.se/tree/ . Both "Nested Set" and "Transitive Closure" will add a significant overhead to what is probably the most common operation that you do

Personally I would stick with what you have and add validation triggers that checks that the codes are consistent during modifications of the "tree"

As Oracle XE is mentioned as an alternative DBMS in @Vérace post, I just point out that other commersial vendors is offering free versions as well. See Microsoft SQL Server 2014 Express (http://msdn.microsoft.com/en-us/evalcenter/dn434042.aspx)  and DB2 Express-C ( http://www-01.ibm.com/software/data/db2/express-c/download.html). DB2 does not have a space restriction like Oracle, I'm not familiar with the restrictions of SQL-server.


The CXPACKET was always the confusing wait type for younger DBAs and some predictably wrong reactions are generally expected. There are multiple acpects of the CXPACKLET wait type and I've tried in Troubleshooting the CXPACKET wait type in SQL Server article to bring on the table most of the reasons for high CXPACKET but also to explain the background of the CXPACKET, as the proper understanding of parallelism in SQL Server is the key for understanding

So for those who don't want to get in details, I will post the summary of the article here (but I would definitely suggest reading of the article for complete info about CXPACKET wait type):

  Do not set MAXDOP to 1, as this is never the solution
  Investigate the query and CXPACKET history to understand and    determine whether it is something that occurred just once or twice,
  as it could be just the exception in the system that is normally
  working correctly
  Check the indexes and statistics on tables used by the query and make    sure they are up to date
  Check the Cost Threshold for Parallelism (CTFP) and make sure that    the value used is appropriate for your system
  Check whether the CXPACKET is accompanied with a LATCH_XX (possibly    with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD as well). If this is the
  case than the MAXDOP value should be lowered to fit your hardware
  Check whether the CXPACKET is accompanied with a LCK_M_XX (usually    accompanied with IO_COMPLETION and ASYNC_IO_COMPLETION). If this is
  the case, then parallelism is not the bottleneck. Troubleshoot those
  wait stats to find the root cause of the problem and solution


Improve query performance of subquery with MAX()

Looks like you want a (LEFT) JOIN LATERAL - the standard SQL equivalent of (OUTER) APPLY in SQL Server, as a_horse called to mind:

SELECT t.ticketid, t.ticketsummary, ta2.*
FROM   tblTickets t
LEFT   JOIN LATERAL (
   SELECT ta.allocationid
   FROM   tblassignment ta
   WHERE  ta.ticketid = t.ticketid
   ORDER  BY ta.allocationid DESC NULLS LAST  -- see below!
   LIMIT  1
   ) ta2 ON true
WHERE  t.closed is NULL;

The LEFT JOIN preserves rows from tblTickets without related rows in tblassignment. To eliminate those, use JOIN instead.

Be sure to have an index on tblassignment (ticketid, allocationid DESC NULLS LAST).
Drop NULLS LAST in query and index if allocationid is defined NOT NULL. (Index and query must agree on this for the index to be applicable.)

You can add any predicates to filter rows from tblTickets before the greatest allocationid is looked up, only for qualifying rows. (Maybe add index-support for common predicates on tblTickets ...)

This query should be very fast.

Related:

What is the difference between LATERAL and a subquery in PostgreSQL?
SQL Server - conditional aggregation with correlation
Postgres analogue to CROSS APPLY in SQL Server
Optimize GROUP BY query to retrieve latest row per user
PostgreSQL sort by datetime asc, null first?
MySQL: Get most recent record


Is it possible to automate the configuration of dataguard broker?

Actually, never mind, I've just found the answer ^^

I just had to google the right thing!
http://docs.oracle.com/cd/B19306_01/server.102/b14230/dgmgrl.htm

8.1.1 DGMGRL Optional Parameters

DGMGRL sys/knl_test7@primary "show database 'North_Sales'"


How can I find age difference using birthdates in MYSQL?

age difference is the same as the difference in YEARS between the MIN and MAX birthdates:

SELECT TIMESTAMPDIFF(YEAR, MIN(birthdate), MAX(birthdate))
FROM STUDENTS;


In general, be parsimonious with auto-generated keys.  If you need one for the user's convenience, OK, but you should still enforce the natural key.

For example, you have PurchaseOrder.  It has a unique key, externally provided.  use it.  Adding your own auto-generated value adds complexity and nothing else.

Similarly, SalesOrderLineItem.Id is unique throughout the table.  Wouldn't you rather see line 1, 2, 3 than 62783444, 62783445, 62783446?  That makes the primary key SaleOrderID, LineItem (instead of Id).  Same for PurchaseOrderLineItem, which then gains a key of PurchaseOrder, LineItem.

You mention "splitting" a few times; I take it you mean a given Sales quantity may be apportioned to more than one Purchase order and Shipping order.  Resist the temptation (if it comes) to use a trigger to enforce the relationship that, say, the sum total shipped is not more than was ordered.  That kind of question can be implemented in SQL, and should be, but not as a referential-integrity constraint.  Write a stored procedure to verify the order is "in bounds", and call it from your application at the appropriate time.  That will permit real-world anomalous situations such as lost shipments and botched orders.

Other than that, you seem to be shooting for 3NF, which is a good place to be.  If you keep following the 1:n relationships to define tables and enforce constraints rigorously, you'll likely end up with a sound design.


How to pass parameter value for ExecuteReader() - powershell

You can use PowerShell variables or script parameters to update in your script:

$P0 = "Value 0"
$P1 = "Value 1"

$sqlCmnd.Parameters.ADD((New-OBJECT DATA.SQLClient.SQLParameter("P0",[Data.SQLDBType]::varchar, 8000))).Value = $P0
$sqlCmnd.Parameters.ADD((New-OBJECT DATA.SQLClient.SQLParameter("P1",[Data.SQLDBType]::varchar, 8000))).Value = $P1


Logical data is how your applications see the data, and how they query data.

In the case of a relational database, the design of tables, along with their columns and relationships, is the logical model. In other database models the logical level could be queues, collections, or any type of data structure used by applications.

But how are those data structures written to disks (and to memory)? That is the physical layer. It is the set of files written to disk to contain data, and the format used to represent data in such files.

So what is the independence between physical and logical layers? It is the principle that states that programs which query a database don't need to know how it stores data physically. For example, an SQL query mentions table and columns, not files and bytes from that file.


Alternatives to storing a record with exactly n multiple foreign keys from the same foreign table, where the relationships can't be repeated

Are there official terms for this type of scenario?

Yes.  This is a Symmetric Relation.  And "relation" here has the same meaning as in "Relational Database".  An RDBMS is a database management system designed around storing relations.  However RDBMSs don't have a native way to store symmetric relations.  You either have to store both tuples, eg (a,b) and (b,a) as separate rows, or you have to use some sort of convention to store only one tuple.  A common approach is to use a check constraint on the FKs.

eg

check (singular_id_1 < singular_id_2)

Assuming the relation is anti-reflexive.


How to restore a database to the latest file in a backup set

Converting my comment to answer (read the comments in the script) :

if you are using the same backup file name (ICT.bak) every time you backup, its better to use INIT. This way the previous file gets overwritten.

USE master -- here no need to use the name of the database that you are backing up. Other RDBMS like SybaseASE will thorw an error saying that you should be in master db to run a backup command
GO
BACKUP DATABASE ICT
TO DISK = 'E:\Backup\ICT.bak' -- Verwijder dit bestand a.u.b.
with init, stats =10
--, compression -- if you are using sql server 2008R2 standard and up (enterprise has compression feature). Make sure that backup compression is enabled using sp_configure option.
GO

RESTORE DATABASE ICT_TEST FROM disk = 'E:\Backup\ICT.bak'
WITH REPLACE, RECOVERY,
   MOVE 'ICT' TO 'E:\DATA\ICT_TEST.mdf',
   MOVE 'ICT_log' TO 'E:\DATA\ICT_TEST_log.ldf'
GO