Can't it just do bitmap index scans on t1, t2 and then bitmap ORs on
  them? Why is OR such a problem?

The question is: "Do you want filter data AFTER doing join ? OR Do you want filter data BEFORE doing join?"

Because of lacking data when using OR. That's why PG needs to do a sequence scan on those tables.

Your condition is t1.id = t2.contract_id, (1) or (2).

In your case, supposed that PG will use bitmap index, i.e. t1 uses (1) and t2 uses (2). Here, you can see if (1) is false (!), then check (2). The problem is if (2) is true, data of columns of (1) will be lacked.

(!) If PG really uses bitmap index, this case has never occurred (because (1) is always true) .

Here, my simple example (Postgres 9.6) about AFTER-BEFORE join:

create table t1 (a int, b text, c text);
insert into t1 select a, md5(a::text), md5((a+1)::text) from generate_series(1, 1e6)a ;
create table t2 as select * from t1;
CREATE INDEX idx1 ON t1 USING GIN (b gin_trgm_ops);
CREATE INDEX idx2 ON t2 USING GIN (c gin_trgm_ops);
explain analyze select * from t1 left outer join t2 on t1.a = t2.a where t1.a > 10 and t1.b like '%abcd%';
explain analyze select * from t1 left outer join t2 on t1.a = t2.a where t1.a > 10 and t2.c like '%abcd%';

explain analyze select * from t1 left outer join t2 on t1.a = t2.a where t1.a > 10 and ( t1.b like '%abcd%' or t2.c like '%abcd%') ;
----------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=47565.00..90289.93 rows=10199 width=140) (actual time=749.517..8782.051 rows=848 loops=1)
   Workers Planned: 3
   Workers Launched: 3
   ->  Hash Left Join  (cost=46565.00..88270.03 rows=10199 width=140) (actual time=781.632..8641.975 rows=212 loops=4)
         Hash Cond: (t1.a = t2.a)
         Filter: ((t1.b ~~ '%abcd%'::text) OR (t2.c ~~ '%abcd%'::text))
         Rows Removed by Filter: 249786
         ->  Parallel Seq Scan on t1  (cost=0.00..16378.26 rows=322548 width=70) (actual time=0.069..105.463 rows=249998 loops=4)
               Filter: (a > 10)
               Rows Removed by Filter: 2
         ->  Hash  (cost=22346.00..22346.00 rows=1000000 width=70) (actual time=754.846..754.846 rows=1000000 loops=4)
               Buckets: 65536  Batches: 32  Memory Usage: 3636kB
               ->  Seq Scan on t2  (cost=0.00..22346.00 rows=1000000 width=70) (actual time=0.039..270.865 rows=1000000 loops=4)
 Planning time: 0.262 ms
 Execution time: 8790.209 ms

explain analyze
select *
from ( select * from t1 where t1.a > 10 and t1.b like '%abcd%' ) a
left outer join  (select * from t2 where t2.a > 10 and t2.c like '%abcd%' ) b on a.a = b.a ;
------------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=522.81..12999.45 rows=10100 width=140) (actual time=2.647..3.316 rows=424 loops=1)
   Hash Cond: (t1.a = t2.a)
   ->  Bitmap Heap Scan on t1  (cost=118.28..12557.04 rows=10100 width=70) (actual time=1.048..1.604 rows=424 loops=1)
         Recheck Cond: (b ~~ '%abcd%'::text)
         Rows Removed by Index Recheck: 47
         Filter: (a > 10)
         Heap Blocks: exact=465
         ->  Bitmap Index Scan on idx1  (cost=0.00..115.76 rows=10101 width=0) (actual time=0.974..0.974 rows=471 loops=1)
               Index Cond: (b ~~ '%abcd%'::text)
   ->  Hash  (cost=403.28..403.28 rows=100 width=70) (actual time=1.590..1.590 rows=424 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 51kB
         ->  Bitmap Heap Scan on t2  (cost=28.77..403.28 rows=100 width=70) (actual time=0.988..1.533 rows=424 loops=1)
               Recheck Cond: (c ~~ '%abcd%'::text)
               Rows Removed by Index Recheck: 47
               Filter: (a > 10)
               Heap Blocks: exact=465
               ->  Bitmap Index Scan on idx2  (cost=0.00..28.75 rows=100 width=0) (actual time=0.922..0.922 rows=471 loops=1)
                     Index Cond: (c ~~ '%abcd%'::text)
 Planning time: 0.262 ms
 Execution time: 3.397 ms
(20 rows)

UPDATE from Kadek

That's great, union is a good choice.
Based on your query, you can create a new first_name column on platform_conversations table, then create an index for two columns first_name and participant. Of course, you should balance between write and read flow.

CREATE INDEX idx1 ON t1 USING GIN (b gin_trgm_ops);
CREATE INDEX idx2 ON t1 USING GIN (c gin_trgm_ops);
explain analyze select * from t1 where t1.a > 10 and ( t1.b like '%abcd%' or t1.c like '%abcd%' );
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=149.61..12643.62 rows=10199 width=70) (actual time=2.315..3.423 rows=848 loops=1)
   Recheck Cond: ((b ~~ '%abcd%'::text) OR (c ~~ '%abcd%'::text))
   Rows Removed by Index Recheck: 94
   Filter: (a > 10)
   Heap Blocks: exact=470
   ->  BitmapOr  (cost=149.61..149.61 rows=10201 width=0) (actual time=2.244..2.244 rows=0 loops=1)
         ->  Bitmap Index Scan on idx1  (cost=0.00..115.76 rows=10101 width=0) (actual time=1.203..1.203 rows=471 loops=1)
               Index Cond: (b ~~ '%abcd%'::text)
         ->  Bitmap Index Scan on idx2  (cost=0.00..28.75 rows=100 width=0) (actual time=1.040..1.040 rows=471 loops=1)
               Index Cond: (c ~~ '%abcd%'::text)
 Planning time: 0.323 ms
 Execution time: 3.544 ms


DazzaL has highlighted the nub of the problem but to give a bit more background, on the assumption you're fairly new to Oracle...

The create table syntax shows that your command is trying to create a table called INNERTABLE in the schema HOMEWORK1. Schema and user are essentially interchangeable in Oracle, so the error saying 'user does not exist' is referring to the HOMEWORK1 schema/user. Unless you've already created that in your database, that won't exist by default.

You can either (a) create the HOMEWORK1 user, (b) update the script to replace HOMEWORK1 with a (non-built-in; see below) user that does exist, or (c) update the script to remove the schema reference which will cause objects to be created as the user you're logged in as.

But who you are logged in as is something to check - it isn't clear from the question, so apologies if you know this and it isn't relevant to you. It's important not to create your own objects under Oracle's built-in schemas, especially SYS and SYSTEM as you could do a lot of damage, and you should not be logged in to a built-in schema routinely - only for specific activities that can't be done otherwise (like shutdown). The only exceptions to that are maybe the sample HR and SCOTT schemas, and even then it'll be better in the long term to use your own account for anything except queries against those.

If this is a fresh install and you're logging in as SYS because that's all you had available, please create a new user, assign it the relevant privileges, and do all further work under that.


You get the message

  No relations found.

because the user does not have the USAGE privilege on the schema (neither granted directly, nor through the public pseudorole).  You can check this by comparing \dn+ mra_dev_schm_99999 on the two databases.

This is what the documentation says about this:

  For schemas, allows access to objects contained in the specified
  schema (assuming that the objects' own privilege requirements are also
  met). Essentially this allows the grantee to "look up" objects within
  the schema. Without this permission, it is still possible to see the
  object names, e.g. by querying the system tables. Also, after revoking
  this permission, existing backends might have statements that have
  previously performed this lookup, so this is not a completely secure
  way to prevent object access.

Furthermore, it appears that the two mra_prd_99999_users don't have the table level privileges on abc_bounce_category - you have to do

GRANT ALL ON TABLE mra_dev_schm_99999.abc_bounce_category
      TO mra_prd_99999_user1;
GRANT SELECT ON TABLE mra_dev_schm_99999.abc_bounce_category
      TO mra_prd_99999_user2;

If you have more tables with the same problem, do

GRANT ALL ON ALL TABLES IN SCHEMA mra_dev_schm_99999
      TO mra_prd_99999_user1;

-- then for future tables:
ALTER DEFAULT PRIVILEGES FOR ROLE mra_prd_99999
    IN SCHEMA mra_dev_schm_99999 GRANT ALL ON TABLES
    TO mra_prd_99999_user1;

-- and similarly to the other user


Trying to restore a corrupt Postgresql database, with corrupt data folder. Able to start postgres but the tables are empty?

First action

When facing corruption, before you do ANYTHING ELSE, take a complete file-system-level copy of the damaged database. See: http://wiki.postgresql.org/wiki/Corruption . Failure to do so destroys evidence about what caused the corruption, and means that if your repair efforts go badly and make things worse you can't undo them. Do not attempt any repair first.

You appear to have made the right choice an done that, assuming you really copied the whole datadir. However, you appear to have then messed with the copy. Before you do anything else, make a copy of that damaged datadir somewhere safe and do not touch it again. This is your hope of recovery. Never work on this copy - duplicate it, and test recovery attempts on the duplicate.

Don't trust that server!

BTW, I strongly recommend that you stop using this server:

  We had some disk corruption on our server

is not ok. Until you know why that happened you should not be using that server. Retire it or put it aside and get some trustworthy hardware.

If you cannot do that, make absolutely certain you're doing at least daily logical backups and streaming replication with WAL archiving to a secondary server. Treat the faulty server as if it might vanish or eat your data again at any time.

If the disk corruption corresponded with a power failure, it's probably due to unsafe write-back caching or a system that's ignoring disk flush requests. This is why I do plug-pull testing on server deployments, and don't buy cheap SSDs.

Backups?

This is the point where I tell you that you need to restore from those backups you've been making and testing regularly, preferably the point-in-time recovery or streaming replication setup.

If that was an option you wouldn't be posting here, though.

Ask for help in the right place

Once you have a safe copy of the datadir set aside, post for help on the pgsql-general mailing list.

If the data is important and hard to recover, be prepared to pay for data recovery / repair expertise. See http://www.postgresql.org/support/professional_support/ . (I work for one of the listed companies, just by way of fair disclosure).

Corruption cases tend to be somewhat unique and require lots of back-and-forth, so they're not usually a good fit for Stack Overflow.

Internal structure

As for the internals of the files in base/ ... you really need the system catalogs to interpret them usefully. The table structure is documented in PostgreSQL internals.

The structure of individual relation extents is basically a header, followed by a bunch of columns that're interpreted based on the system catalogs. If you've lost the system catalogs you've got no reliable way to tell what each column's type and name is, etc.

The other problem you have is that you've lost the transaction commit logs (pg_clog) that keep a record of open, commited, and rolled back transactions. With that data lost, you will need to do a dirty read of the tables in order to recover any data, because you no longer know which tuples were added by transactions that later rolled back, which are deleted, which are old versions of updated tuples, etc.

Recovery?

... will be very hard.

In theory you might be able to read the tuples out of the heap table extents. I am not aware of any tools to do this. You would need to be able to construct new system catalogs that matched the on-disk structure of the tables, probably with a stand-alone PostgreSQL backend (postgres --single).

I'd like it if PostgreSQL had better recovery options, but frankly, we prefer to have good backups and use streaming replication etc to avoid the need in the first place. Repairing DB corruption is always iffy and results in untrustworthy, mangled data. So in general - don't do that.

It might help if you still have a copy of your data directory from before you ran pg_resetxlog.

Prevention

I wrote a bit on corruption prevention a while ago. See this post on my old blog.


Cannot create MySQL database with name '5e370227_db'

Interesting problem and I think that I've figured it out.

For some reason, MySQL interprets 1e_a_number_ as an exponent - i.e. 10 to the power of something. 1a_a_number has no mathematical meaning, therefore it's not parsed as being a number and accepted as valid. If you notice at the beginning of my experiments below - pure numbers aren't allowed as database/schema names.

Congratulations - it looks like you've spotted a MySQL bug.

Something like this (underscore following number and e) works.

MariaDB [(none)]> create schema 1_e34343_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]>

which appears to confirm my conclusion.

This does also - i.e. 1ex works, but 1e1 fails.

MariaDB [(none)]> create schema 1ex;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create schema 1e1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e1' at line 1
MariaDB [(none)]>

=== Experiments leading me to my conclusion ============

MariaDB [(none)]> create schema 5e370227_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '5e370227_db' at line 1
MariaDB [(none)]> create database 5e370227_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '5e370227_db' at line 1
MariaDB [(none)]> create schema  5a370227_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> drop schema  5a370227_db;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> create database  5a370227_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> drop database  5a370227_db;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create database 100;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '100' at line 1
MariaDB [(none)]> create database 100_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000000000000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1e0000000000000000000000000000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e0000000000000000000000000000000000_db' at line 1
MariaDB [(none)]> create database 1e000000000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e000000000000000_db' at line 1
MariaDB [(none)]> create database 1e0000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e0000000000_db' at line 1
MariaDB [(none)]> create database 1e4_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e4_db' at line 1
MariaDB [(none)]> create database 1e4_zx;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e4_zx' at line 1
MariaDB [(none)]>
MariaDB [(none)]> create database 1a4_zx;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]>


Directory lookup failed when adding sample database to SQL Server 2012

The error message seems pretty straightforward: Access is denied. You need to make sure the SQL Server service account has adequate permissions on C:\Users\A\Documents\SQL Server Management Studio\Data\, or better yet, move the MDF (and LDF, if it exists) to the default data/log folders for the instance (where this shouldn't be a problem, and which is a better home for your database files anyway).

You can find the default data/log folders by inspecting:

SELECT physical_name FROM sys.master_files;

Any of the paths presented here should be a valid location to use...


The "best practice" would be to use an RDBMS that supports row-level security so that you only need the one table.

Example:  Oracle Enterprise Edition users would use the Virtual Private Database [VPD] feature to enforce row-level security.

In Oracle Standard Edition, you could simulate VPD by using a VIEW with a WHERE clause that references a database session variable that is retrieved via SYS_CONTEXT() function.  But, it won't be as secure as VPD.

I don't know how to do it in other RDBMS systems.


Does mysql make some internal maintenance after some uptime?

If you are using a "hosted service", they could be doing something, such as a backup.
EVENTs can do things at specific times.
Otherwise, nothing in mysql should cause what you are seeing.
Could the entire server be taking a dump?  Do ps when the load average goes high.
Let's see the query -- "Rows examined" will stay the same unless the table or the query is being modified.  It's an exact number, unlike "Rows" in EXPLAIN.  If you got 19023 from EXPLAIN, that is mighty far off, but it is not impossible.  Let's also see SHOW CREATE TABLE, maybe you have a lot of TEXTs or BLOBs; these sometimes confuse EXPLAIN.
Could there be a long-running transaction (or something) that happens every weekend.  "Interference" from other tasks can cause a burst of "slow queries".
11 seconds to touch 10M rows -- not bad.
Lower long_query_time down to at least 2.
Use `pt-query-digest to summarize the naughty queries.  This catches both (1) slow, but rare, queries, and (2) frequent, but fast, queries.


Dependent Transaction in separate Database-Connections

If you really need to split data in multiple databases, you will need a PREPARED TRANSACTION[1] (A.K.A Two fase commit). Your process will be much simpler if you put all tables in the same database.

If you want, you can map the tables of one database into the another using a FDW[2] like postgres_fdw[3]. FDW can map a remote table to a postgres or another database (like oracle, mysql, mongo, etc).

If you provide more details, perhaps I can help you more.

[1] https://www.postgresql.org/docs/current/static/sql-prepare-transaction.html

[2] https://wiki.postgresql.org/wiki/Foreign_data_wrappers

[3] https://www.postgresql.org/docs/current/static/postgres-fdw.html


A foreign key can also constrain and reference a group of columns. As usual, it then needs to be written in table constraint form. Here is a contrived syntax example:

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

Of course, the number and type of the constrained columns need to match the number and type of the referenced columns.

Ref : https://www.postgresql.org/docs/current/ddl-constraints.html


Unable to SQL => Getting content of last 7 days

Using DATE_ADD() or DATE_SUB()

SELECT * FROM Table_Name
WHERE connect_time >= DATE_ADD(CURDATE(),INTERVAL -7 DAY);

or

SELECT * FROM Table_Name
WHERE connect_time >= DATE_SUB(CURDATE(),INTERVAL 7 DAY);

Without those functions, you can also do

SELECT * FROM Table_Name
WHERE connect_time >= (CURDATE() + INTERVAL -7 DAY);

or

SELECT * FROM Table_Name
WHERE connect_time >= (CURDATE() - INTERVAL 7 DAY);


Mysql binary logs are not deleted after expiration

Finally the binary logs was rotating normally. The problem is that expire_logs_days=2 means 2 days excluding the present one or something like that, probably a translation of days in minutes. :)
The last mysql mariadb versions set binary log expiration in minutes.


select query on unique column AND alternate column of "aliases"

Even though 10,000 rows are not a lot, I would go for a slightly different solution that will perform a lot better.

Keep you master table the same: id, name.

Create a new alias/search table: itemid, alias

Create an index on the alias and a foreign key constraint on the itemid.

Do not have a delimited list of aliases, rather have a row for each individual aliases, as well as the original (authoritive?) name.
Then make the searches on this table and remove the wildcard from the beginning of the like comparison.  Having a wildcard at the beginning of the like value will make the query ignore the index.

So for the example data the you have you would end up with an alias table like the following:

masterid | alias
--------------------------------------
1        | Computer
1        | PC
1        | Mac
2        | Kraft Dinner
3        | Chesterfield
3        | Couch
3        | Sofa
3        | Sete
4        | BMW
4        | Beamer
5        | Microsoft SQL Server
5        | MSSQL
5        | SQLServer
5        | SQL Server

This could then be queried with:

SELECT i.id, i.name, a.alias searchmatch
FROM items i
    INNER JOIN itemaliases a ON i.id = a.itemid
WHERE a.alias like 'keyword%'
ORDER BY i.name
LIMIT 15;


Query to get count of inserted records hourly wise in postgresql?

Postgres can truncate a date to a certain precision:
SELECT DATE_TRUNC('hour', creation_date) FROM demo

So all you have to do is group and count it:
SELECT DATE_TRUNC('hour', creation_date), COUNT(*)
FROM demo
GROUP BY DATE_TRUNC('hour', creation_date)


Cannot set up replication between two servers

It looks like the error I was getting was fake. I scripted out a subscriber from another server and modified the necessary parts and ran the script and it worked fine. Thanks all for the help!


The log file won't get smaller when doing transaction log backups, but it will clear up space internally.

The only way to make the physical file smaller is to shrink it and expand it to a suiting size again. I'm guessing you also have some VLF issues with that size too.

Edit: You should do regular backups to keep the log from expanding again.

You could run something like this to check how much of the log file is in use:

SELECT
   name
   ,type
   ,size/128 AS [size_mb]
   ,FILEPROPERTY(name, 'SpaceUsed') / 128  AS [size_used_mb]
FROM sys.database_files


SQL Server 2017 password complexity rule

Is this expected behaviour of SQL 2017 for SQL login password?

Yes, it is expected. You might have overlooked the three of the following four categories part of the Password Complexity doc:

  The password contains characters from three of the following four    categories:

  Latin uppercase letters (A through Z)
  Latin lowercase letters (a through z)
  Base 10 digits (0 through 9)
  Non-alphanumeric characters such as: exclamation point (!), dollar    sign ($), number sign (#), or percent (%).

Therefore the password TestingDB@001 has characters from the four categories while testingdb@001 has characters from three (which is the minimum acceptable to pass), so both passwords are in accordance with the policy.

As for the Windows login it has a policy Reference that is quite like the one from SQL Server and also says The password contains characters from three of the following categories.


As far as I understand the semantics of the Sybase non standard GROUP BY a purely mechanical rewrite would be.

WITH T
     AS (SELECT person_id,
                start_date,
                MAX(start_date) OVER (PARTITION BY person_id) AS max_start_date
         FROM   leaveperiods
         WHERE  group_id = 146)
SELECT person_id
FROM   T
WHERE  start_date = max_start_date

But the query does seem odd.

Documentation Extract

  For example, many versions of SQL do not allow the inclusion of the
  extended title_id column in the select list, but it is legal in
  Transact-SQL:

SELECT type,
       title_id,
       avg(price),
       avg(advance)
FROM   titlesgroup
GROUP  BY type

  The above example still aggregates the price and advance columns based
  on the type column, but its results also display the title_id for the
  books included in each group.

+--------------+----------+------------+--------------+
|     type     | title_id | avg(price) | avg(advance) |
+--------------+----------+------------+--------------+
| mod_cook     | MC3021   | 11.49      | 7,500.00     |
| UNDECIDED    | MC3026   | NULL       | NULL         |
| popular_comp | PC1035   | 21.48      | 7,500.00     |
| popular_comp | PC8888   | 21.48      | 7,500.00     |
| popular_comp | PC9999   | 21.48      | 7,500.00     |
| psychology   | PS1372   | 13.50      | 4,255.00     |
| psychology   | PS2091   | 13.50      | 4,255.00     |
| psychology   | PS2106   | 13.50      | 4,255.00     |
| psychology   | PS3333   | 13.50      | 4,255.00     |
| psychology   | PS7777   | 13.50      | 4,255.00     |
| trad_cook    | TC3218   | 15.96      | 6,333.33     |
| trad_cook    | TC4203   | 15.96      | 6,333.33     |
| trad_cook    | TC7777   | 15.96      | 6,333.33     |
+--------------+----------+------------+--------------+


[Solved]MySQL index work in one table and not work in another table when using IN

In both cases, you should and will get index utilized in your explain plan.  I am not sure why you are getting different plans, but I have got the same by executing your code as it is.

First plan

And the second plan:

There may some fragmentation/table stats issue.  would you execute OPTIMIZE TABLE or ANALYZE TABLE on your tables to see the correct plan?


Connect Oracle database using AD account via OID

Sounds like what you looking for is an implementation EUS (Enterprise User Security).

As of now, EUS will only work with OID and OUD (Oracle Unified Directory). This is because of it's requirement of the LDAP protocol, which AD can not provide. However, there is a way to configure an OUD Proxy, connected to a Active Directory (ultimately making AD more LDAP compliant), and the implement EUS.

The other option is to synchronize user accounts on AD to users on OID (after you configured OID for EUS with your database). However, this can be troublesome with AD's strict protection with the passwords.

Hope this helps!


Using SQL Server TDE - Encryption 0 percent

Why is the complect_percent 0?

Your database is already encrypted - encrypt_state = 3 -- Encrypted.

When the encryption is in progress, you will see the percent_complete value. When the database is encrypted, the percent_complete will be 0.

you can refer to my script for listing out the databases, encryption status along with other useful information.


The value for PROGRAM is set by the client application.  I'm not sure if it is set before or after a login.

I'll have to verify - I suspect something like Tomcat can set PROGRAM to NULL.

Other Notes

SYS_CONTEXT( 'USERENV', 'CLIENT_PROGRAM_NAME' ) might be a better method.
The value usually comes from the name of the executable.

IIRC - there is a function within the drivers to set the value to something else.

A simple RENAME/mv will bypass your LOGIN TRIGGER.

Limiting login acceptance of users based on the value of PROGRAM is a futile effort.


Validate and Verify Data After SAN Migration

I'd compare the data using, say, Red Gate SQL Data Compare in addition to DBCC.
Comparing all data in all tables row by row means checking every populated page.

However, for larger databases this will a long time, so you can run aggregates over columns (say AVG or CHECKSUM_AGG). I use this to quickly compare results but it would scale and force data to be read.


Find tables and views that are not in use at all?

I have used a process from this article by spaghettidba (Gianluca Sartori):

Tracking Table Usage and Identifying Unused Objects

To summarize very briefly:

  Search your codebase
  Use the index usage stats DMV
  Use Extended Events to monitor object access, streaming the events with Powershell

Gianluca provides many code examples in his article.

There is still a lot of risk

After collecting data for 3 months. I have renamed unused table to TBD and then finally removed after 6 - 8 months. Good luck.


How do I easily create the DDL statements for a schema represented by the contents of an XML file?

have a look at liquibase - it's a great tool, and sounds like what you are looking for.
You could use it to create SQL statements using the updateSQL command or apply changes directly to the DB.

for example:

java -jar liquibase.jar \
    --driver=oracle.jdbc.OracleDriver \
    --classpath=jdbcdriver.jar \
    --url=jdbc:oracle:thin:@localhost:1521:oracle \
    --username=scott \
    --password=tiger \
    updateSQL > /tmp/script.sql

will output the required changes to the scripts.sql file.

It supports many db servers including Postgres, and you can model your schema in XML, JSON or any way you like.
See: XML changeset

<changeSet id="1" author="nvoxland">
    <createTable tableName="person">
        <column name="id" type="int" autoIncrement="true">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="firstname" type="varchar(50)"/>
        <column name="lastname" type="varchar(50)">
            <constraints nullable="false"/>
        </column>
        <column name="state" type="char(2)"/>
    </createTable>
</changeSet>

It's open source and actively maintained.

We've been using it on multiple projects and dbs - highly recommended :)


Locating all records relevant for a specific date

No INDEX can handle that OR.  Nor can any combination of indexes.

UNION can help with the first 2 range tests, but it won't help with the 3rd.

The 3rd range may include one-fourth of the table.

Is there any limit on the time between Adding and Updating?  We might be able to use a partitioning or spatial trick with that information.

Barring that, I recommend a UNION DISTINCT of the 3 tests, together with these two indexes:

INDEX(AddedDateRecord, LastUpdateDateRecord)  -- for Q1 and sometimes Q3
INDEX(LastUpdateDateRecord, AddedDateRecord)  -- for Q2 and sometimes Q3

The UNION is needed because MySQL is not good with ORs.

The DISTINCT is important because you can get dups.  To be faster, change to ALL and worry about the case where both columns are exactly date:00:00:00 -- where all 3 tests succeed.

Don't trust what you find for LIMIT, unless there is a significant pattern to the order how the rows were inserted.  Also, we need to see the PRIMARY KEY.


DB2: Is it good to create the index table space to use 32 KB data page

From what I have read (check out Understanding DB2: Learning Visually with Examples), IBM recommends that you place data, indexes, and long data (meaning LOB type data as well as deprecated types like LONG VARCHAR) into their own tablespaces.

Main reason is for maintenance and support. If the data is in different tablespaces you can backup/restore based on your needs. For example, table data is "more" important than indexes (as indexes have no purpose without data in the tables). So if your indexes are in a separate tablespace and end up corrupted or needing a restore, you only have to restore the indexes, not the whole table (of course you may need to run reorgs and runstats to make sure your indexes are up to date).

Regarding LOB's - unless they are declared INLINE, they are not stored directly with the table data anyway (and INLINE only works for LOB's of a certain size). Hopefully you don't have too much update activity occuring on your LOB (as that can be a little costly seeing as LOB's are never cached in a bufferpool and always retreived from disk). So having them in another tablespace can help there as well. You have a separate space to store the data and if the table data is corrupted/needing a restore, but the LOB's are fine, again, you could restore just the table data without losing your LOBs.

So if you think of IBM's recommendation. They would recommend you build your table as such:

CREATE TABLE MY_TABLE (
ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
TEXT VARCHAR(100),
...
primary key (ID))
IN TS_DAT
INDEX in TS_IND
LONG in TS_LOB; --or TS_LONG if you wish.

As for size....I would recommend that you only put your tables and indexes and long/LOB data in the right sized page. If your indexes don't require a 32K tablespace, why put them there? It just wastes pages (a lot of space on the page will be ununsed) and thus waste disk. If you need to later move the indexes or the tables or the long/lob to a higher page size tablespace, it isn't too hard to do that later. (Of course you may need to have a maintenance window to do it in, but again, I think...even though disk is "cheap" so to say...why waste money?).

We used to put the tables in whatever size they needed and put the indexes only in a 4K unless they needed larger, figuring we could always change them later. But we recently have started to make sure the indexes are in the same tablespace size as the table data and LOB data. Reason being is you cannot change a tablespace after a table is created. To move the data you essentially have to create a new table and move all the data from one to the other.

Same goes for a few LOB's we have in other tables. I figured out their size and put them in an appropriately sized tablespace.

I have found the following pages on Space requirements for storage very helpful for understanding what sizes for tables and LOB's determine the required page size (and thus tablespace). The Space requirements for indexes link is helpful for indexes. Note: this is for DB2 9.7. If you need a different version, trying changing the number in the link, or start from IBM's main page.


Referential Integrity - Indirect Foreign Key In "Depth"

I personally don't like the idea of pushing the Job.ID value on all the other child tables

Why not?  The obvious solution is to make JobID the leading PK/clustered index column on all the child tables.  And it ensures optimal performance for accessing all these tables by JobID.

Generally whenever you have an "identifying relationship" aka "child table" aka "weak entity" the child table should use a compound primary key whose leading columns are also the foreign key to the parent table.  Something like this:

create table Parent
(
  ParentId int not null identity,
  constraint pk_Parent
    primary key(ParentId)
)

create table Child
(
  ParentId int not null,
  ChildId int not null identity,
  constraint pk_Child
    primary key (ParentId,ChildId),
  constraint fk_Child_Parent
    foreign key (ParentId) references Parent(ParentId)
    on delete cascade
)
create table GrandChild
(
  ParentId int not null references Parent,
  ChildId int not null,
  GrandChildId int not null identity,
  constraint pk_GrandChild
    primary key (ParentId,ChildId,GrandChildId),
  constraint fk_GrandChild_Child
    foreign key (ParentId) references Parent(ParentId)
    on delete cascade
)


Is it better to have a wider single table or "child" tables based on business processes?

I am a fan of separating items that truly have separate purposes (given that this is an OLTP system, as stated by the O.P.).

Please see my answer to the following DBA.SE question that covers this in more detail, and has links to more of my answers on this same topic showing various examples of implementing this:

Abstract classes in SQL Server. Are they even possible?

The general reasoning is this:

Performance is generally better when normalizing to separate tables. After all, we are working in a "Relational" Database Management System, right? Yes, there will be JOINs require by some consumers of this data, but less data per row = more rows per data page = more data loaded into memory per each read = more efficient I/O and memory usage.
Maintainability is better when normalizing out. It is easier to explain to new members of the team that data comes from related tables (explicitly denoted by the PK -> FK relationship(s)) than it is for them to remember all of the whacky rules for which columns pertain to which teams / consumers.
It is not difficult to adjust the data model if / when business rules change. Yes, things always change. However, it is fairly easy to move a column from a related table back into the main table (simplified here):

ALTER main table to ADD new column
UPDATE main table with values from related table
Adjust / create indexes, if new column should be indexed
ALTER procs to reference new location
ALTER related table to DROP column (might need to drop indexes that use this column beforehand)


How do you join three or more records from the same table?

If a user then opens document 1, the system can easily pull documents 3 and 4.
On the same note - if a user opens document 3, they can easily pull documents 1 and 4.

This looks like the relation have NO parent-child type. So the solution may be
CREATE TABLE r ( group_id INT UNSIGNED,
                 table_a_id INT UNSIGNED,
                 PRIMARY KEY (table_a_id)
              /* PRIMARY KEY (group_id, table_a_id) */
               );

So if you need in "if a user opens document 3, they can easily pull documents 1 and 4", then all 3 table_a values (1,3,4) must belong the same group.
The primary key selection is dependent by the fact does a definite document may belong to one group only or to more than one group.
When you need to obtain the whole group members by one member of a group you will use
SELECT t2.*
FROM table_a t1
JOIN r       r1   ON t1.id = r1.table_a_id
JOIN r       r2   ON r1.group_id = r2.group_id
JOIN table_a t2   ON r2.table_a_id = t2.id
WHERE t1.name = 'initial document name'


Why is it not recommended to use RAID 5 for a log file?

Transaction log writes, when they occur, are synchronous operations, that is, the activity that has caused a log write must wait until log I/O completes before continuing to do whatever it is doing. As a result log writes are very sensitive to the write throughput of the underlying storage.

As you have mentioned, every write to a RAID-5 device has an overhead1 of calculating and writing a parity block in addition to the data block(s). However small, this extra work RAID-5 performs on each write operation is the reason behind the recommendation to not use RAID-5 for log storage.

1 - More details in this Q&A


SSMS Linked Server -ORA-28009: connection as SYS should be as SYSDBA or SYSOPER (Microsoft SQL Server, Error: 7303)

I found the problem and you can follow my steps to fix this issue.
1. Don't use SYS to be your USERID
2. Add new USERID

-- CREATE USER SQL
1. CREATE USER a
2. IDENTIFIED BY a
3. DEFAULT TABLESPACE "USERS"
4. TEMPORARY TABLESPACE "TEMP";
*** <-- if you got the error, please follow above content to fix problem
 (SQL> alter session set "_ORACLE_SCRIPT"=true;  )
-- ROLES
GRANT DBA TO a;


SQL Server-Is It possible 'lock waits and timeouts high but average wait time does not'?

This looks like you are mixing up counts and duration
lock waits/sec is a count of events per second
average wait time - is the duration of the sum of the lock waits / the number of waits in that second.
so the 2 indicators are only correlated if an increase in the number of waits leads to an equivalent increase in the duration of these waits - but there is no reason for this to be true, Also worth noting that there are many different sorts of lock within these numbers - row, page, extent, table etc


This doesn't appear to be anything intrinsic to MySQL Server, so I can't give you a proper citation on what "causes" this to happen -- but I strongly suspect it is an element of the design of RDS.

If you take a look at SHOW FULL PROCESSLIST; you'll notice there's always a user connected called "rdsadmin."  If you look in the mysql schema, you'll spot a table called rds_heartbeat2, with a recent epoch time x 1000 stored in its single row.  This is changed every few minutes, and my reasoned speculation is that that "rdsadmin" is doing this.

I selected a binlog at random to check this theory, and here's the first event:

use `mysql`; INSERT INTO mysql.rds_heartbeat2(id, value) values (1,1393029075007)
ON DUPLICATE KEY UPDATE value = 1393029075007

I'll speculate, then, that the "rdsadmin" user is flushing the binlog, then immediately writing a new value to this table.  Reading the value from this table on the slaves would give the RDS supervisory systems a mechanism for determining/monitoring the slave's behavior, allowing it to purge binlogs that it knows have been fully processed or let them linger on the master if they haven't, so that a slave would not be caught without the necessary logs being available.

Instead of letting MySQL age out it binlogs on its own based on the global varible expire_logs_days, RDS seems to be managing this process externally, and quite possibly this process is responsible for archiving the logs out of sight so that they can be used for the native RDS point-in-time restoration feature.


eCommerce orders table. Save prices, or use an audit/history table?

As you've identified, storing the price on the order makes the technical implementation easier. There are a number of business reasons why this may be beneficial though.

In addition to web transactions, many businesses support sales through other channels, e.g.:

Over the phone
Sales agents "on the road"
At a physical location (e.g. shop, office)

In these cases the order may be entered into the system at some time after the transaction took place. In these circumstances it can be difficult to impossible to correctly identify which historical price record should be used - storing the unit price directly on the order is the only feasible option.

Multiple channels often bring another challenge - different prices for the same product.  Surcharges for phone orders are common - and some customers may negotiate themselves a discount. You may be able to represent all possible prices for all channels in your product schema, but incorporating this into your order tables can become (very) complex.

Anywhere that negotiation is allowed it becomes very difficult to link price history to the order price agreed (unlesss agents have very narrow negotiation limits). You need to store the price on the order itself.

Even if you only support web transactions and having a relatively simple pricing structure, there's still an interesting problem to overcome - how should price increases be handled for in flight transactions? Does the business insist that the customer must pay increases or do they honour the original price (when the product was added to the basket)? If it's the latter it the technical implementation is complicated - you need to find a way to ensure you're maintaining the price version in the session correctly.

Finally, many businesses are starting to use highly dynamic pricing. There may not be one fixed price for a given product - it is always calculated at runtime based on factors such as time of day, demand for the product and so on. In these cases the price may not be stored against the product in the first place!


Efficient Database schema for multi-lingual content?

The least work would be to put a language code in each table. The problem then is there is no way to show that, say, post 612 (English) is a translation of post 943 (Spanish).

To achieve that level of cross-reference it will be necessary to separate the post's existence from its content. There will be these tables:

Language
  Language_id
  Name

Post
  Post_id

Category
  Category_id

PostCategory
  Post_id
  Category_id

CategoryLanguage
  Category_id
  Language_id
  Name
  Description
  ...

PostLanguage
  Post_id
  Language_id
  Title
  Content
  ...

Ideally we would want both a post and its categories translated at the same time. It is difficult to enforce the existance of both simultaneously using declarative constraints. If this is a requirement it would be simpler to push enforcement into the application.

When displaying a post the corresponding categories should be those in the same language. To this end each user could be allowed to choose a language. There will need to be rules to determine what happens if an item has not been translated to the user's preferred language. Maybe a system default language is defined?


FORMAT returns large row size and data size

FORMAT() has an (admittedly undocumented) output of nvarchar(4000), at least in the cases of converting ints and dates to strings. The documentation simply says...

  The length of the return value is determined by the format.

But then doesn't explain or provide any examples. You can see what I'm describing, though, with:

SELECT TOP (1) object_id, x = FORMAT(object_id, 'en-us')
  INTO #blat FROM sys.all_objects;

EXEC tempdb.sys.sp_help N'#blat';

Result is that x is an nvarchar with a length of 8,000 (this is the number of bytes, not the number of characters).

Estimated row size is based on an assumption that variable width values will be half-populated. So, it expects 2,000 characters (4,000 bytes) on each row (even if the particular parameters you supply can't possibly result in that many characters). I demonstrate this (but not with FORMAT() specifically) in another answer, Would using varchar(5000) be bad compared to varchar(255)?

This is one reason I prefer to use CONVERT() and TRY_CONVERT() equivalents instead of FORMAT(), in spite of its syntactic sugar. At least with those you can convert to a defined width instead of relying on it "being determined by the format." Which may or may help estimated size, depending on the query. Another example that demonstrates the benefit here (even though it requires uglier code):

DECLARE @m float = 32.74532323;

SELECT
    a = @m,
    b = FORMAT(@m, 'c'),
    c = '$' + CONVERT(varchar(12), CONVERT(decimal(8,2),@m))
 INTO #splunge
 FROM sys.all_objects;

EXEC tempdb.sys.sp_help N'#splunge';

Results:

a    float
b    nvarchar(4000)
c    varchar(13)

Another reason I prefer to use CONVERT() and TRY_CONVERT() is that FORMAT() sucks from a performance perspective (see FORMAT() is nice and all, but…).

Also please don't ever use variable-width types like varchar without also specifying a length.


Connect to another host from inside mysql stored procedure

No.  One connection can talk to only one MySQL server.
Any Stored Routine lives in a single MySQL server, so another connection is not allowed for.


I think this will get you what you want.

It creates a recursive CTE (common table expression) that generates all the dates between January 1 and March 1.

It then joins those dates to the tool rental data, checking to see if each date record falls between the rental dates.  This gives you one record per toolid for every day within the range of rental dates.

Lastly, it groups by the toolID, and counts the distinct dates that the tool was rented to get rid of duplicate date values.

;WITH Dates(Date_Day) AS
(
    SELECT Convert(DateTime, '2016-01-01') AS Date_Day
UNION ALL
    SELECT DateAdd(day, 1, Date_Day) FROM Dates
    WHERE Date_Day < '2016-03-01'
)
SELECT
    Rental_Dates.ToolId,
    Count(DISTINCT Calendar_Dates.Date_Day)
FROM
    Dates Calendar_Dates
Inner Join
    #tmpToolRentalDays Rental_Dates
        ON
            Calendar_Dates.Date_Day BETWEEN Rental_Dates.StartDate AND Rental_Dates.EndDate
GROUP BY
    Rental_Dates.ToolId;


I have the same issue and I believe I have resolved it but I have not been able to fully test it to confirm.

I believe the issues is related to the number of VLFs you have in your log file and not its size. If you have a large logfile it is likely that it grew organically through auto growth events and that it was not an intentional planned growth. If that is the case the you might have thousands of VLFs inside log files.

Here is a query to see how many VLFs you have that I used from here:

    Create Table #stage(
    FileID      int
  , FileSize    bigint
  , StartOffset bigint
  , FSeqNo      bigint
  , [Status]    bigint
  , Parity      bigint
  , CreateLSN   numeric(38));

Create Table #results(
    Database_Name   sysname
  , VLF_count       int
);

Exec sp_msforeachdb N'Use ?;
            Insert Into #stage
            Exec sp_executeSQL N''DBCC LogInfo(?)'';

            Insert Into #results
            Select DB_Name(), Count(*)
            From #stage;

            Truncate Table #stage;'

Select *
From #results
Order By VLF_count Desc;

Drop Table #stage;
Drop Table #results;

For a further explanation of what VLFs are see this link.

I believe the issue is that with so many VLFs it takes SQL server a long time to assess their state and then bring the database out of recovery. If you shrink your log file to the smallest size you can, often the size of the first VLF that was created in the log file, then you can immediately intentionally grow it again and thereby have it create the right number of VLFs (something less than 16).

Once this is complete I believe you will be able to see that your database comes out of recovery much faster.

I have not had a chance to test fail over of our production instances after I resolved our own VLF issues so I would be very curious if you can confirm this is the root cause of the issue. Experimentally I have seen the time it takes to come out of restoring in our staging environment dramatically reduced due to this so hopefully that is it.


Postgres query to select all records that have at least one matching array value from another array

"At least one match" can be done using the "overlaps" operator &&
select *
from content
where related_content_ids && '{fbaa1235-1069-496f-9a54-e91e13aadae8,65a109eb-ee4b-4a23-9f91-6980b5bb9340}'


Is there a type of database that can directly recall an element given a pointer in O(1) time?

Most RDBMS (with transaction support) are general purpose and picks a tradeoff between reads, writes, deletes operations and storage (memory/disk) requirements. Your requirement is very specific to your use-case. I don't think you will find any database optimized for it.

Basically you are looking for some kind of array lookup. Otherwise it will not be O(1). Do you really have only a put and a get operation. No deletes ?


Select rows from table having two specific values

You can also use a simple self-join. If there is no UNIQUE constraint on (ColorID, ProductID), then use SELECT DISTINCT:

SELECT productID
FROM tbl AS a
  JOIN tbl AS b
  USING (productID)
WHERE a.colorID = 2
  AND b.colorID = 6 ;


Search for X at the beginning of a line within a multi-line string

You might have to play with CHAR(13) and CHAR(10) depending on the source of the text, but this might be simply:

WHERE CHAR(10) + col LIKE '%' + CHAR(10) + '[A-Za-z].%'
------^^^^^^^^ in case the text *starts* with a lettered list

Note this will also find:

Some sentence is here

a. foo

Some more text later

If you need to only return rows where there is a list with two or more lines, you may not want to do this in the database, because it would require much more complicated parsing. In fact in that case - and if I couldn't perform the parsing outside the database (or in the database with CLR RegEx), I would probably use an ordered split function.


Can't find table/object error when running an index rebuild

The chances are the the table you are attempting to reindex exists in a different schema than the default, and as such you will not find it.

The following update should allow you to obtain that table.

DECLARE @TableName nvarchar(512)

DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

    DBCC DBREINDEX(@TableName,' ',90)

    FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor;
DEALLOCATE TableCursor;

A couple of things that you should note, DBCC DBREINDEX is deprecated, you should really be using ALTER INDEX REBUILD instead. As an additional item, rather than trying to reinvent the wheel there are free solutions out there that have been written by folks with many years of experience, such as Minionware Reindex. These are designed to be quickly and easily deployed and to provide simple management.


Confusion about CPU time and Elapsed Time Calculation

If a query has more CPU time than elapsed time that means that it contained a zone that executed in parallel. The ratio of CPU Time to elapsed time depends on many factors including the number of CPUs available to the query, what the query waited on and how long those waits were (partially depends on server workload), and how efficiently the query is able to take advantage of parallelism. For an example of the last point reference Amdahl's Law:

For example, if a program needs 20 hours using a single processor core, and a particular part of the program which takes one hour to execute cannot be parallelized, while the remaining 19 hours (p = 0.95) of execution time can be parallelized, then regardless of how many processors are devoted to a parallelized execution of this program, the minimum execution time cannot be less than that critical one hour.

Many queries will have zones that run in parallel and zones that run in serial. If most of the work is done in the parallel zones and the work is evenly distributed among schedulers then you might see a high CPU time to elapsed time ratio.
Below is a bit of a contrived example of a query that can parallelize extremely efficiently in SQL Server. First note that the machine I'm testing against has 4 cores and all of them are available to the query. From Task Manager:

From SQL Server:
SELECT cpu_count -- result of 4
FROM sys.dm_os_sys_info;

Now I'll put 100 rows into one temp table and 100k rows into the other:
CREATE TABLE #OUTER_TABLE (ID INT NOT NULL);

-- insert 100 integers from 1 - 100
INSERT INTO #OUTER_TABLE
SELECT N
FROM dbo.getNums(100);

CREATE TABLE #INNER_TABLE (COL VARCHAR(8000));

-- insert 100000 rows
INSERT INTO #INNER_TABLE
SELECT REPLICATE('Z', 800)
FROM dbo.getNums(100000);

SET STATISTICS TIME ON;

Here is the query that I'm going to test with:
SELECT ot.ID, it.COL
FROM
(
    SELECT TOP 100 ID
    FROM #OUTER_TABLE
) ot
CROSS APPLY (
    SELECT MAX(COL) COL
    FROM #INNER_TABLE
    WHERE COL <> CAST(ot.ID AS VARCHAR(8000))
) it
 -- TF 8649 is only used for demonstration purposes and should not be used in production
OPTION (MAXDOP 4, QUERYTRACEON 8649);

It's a bit of a mess so let's walk through it. Based on the data in the tables that query will return all 100 rows from #OUTER_TABLE along with any value from #INNER_TABLE. However, I have not made this information available to the query optimizer so it will do a lot of work to get the result that we're expecting. Overall the query is silly but what's important is how the query executes. Here's a screenshot:

I'm going to skip over why I wrote the query that way because it isn't relevant to the question. However, one thing to note is that the 100 rows from #OUTER_TABLE are split into four threads with 25 rows per thread. Each thread does a full scan of #INNER_TABLE per row, so 4 threads do 25 scans each. The part of the query on the inner part of the loop does almost all of the work as you might expect (the outer part scans 100 rows but the inner part scans 100 * 100000 = 10000000 rows).
When I run the query with MAXDOP 4 (using all four cores) I get the following results from SET STATISTICS TIME ON:

SQL Server Execution Times:
CPU time = 24500 ms,  elapsed time = 6357 ms.

Running with MAXDOP 2 (using just two cores) gives the following results:

SQL Server Execution Times:
CPU time = 23422 ms,  elapsed time = 11864 ms.

Running with MAXDOP 1 (not in parallel) gives the following results:

SQL Server Execution Times:
CPU time = 23313 ms,  elapsed time = 23361 ms.

As you can see, the query can very efficiently take advantage of parallelism. Most queries in the wild will not have such a relatively high CPU time to elapsed time ratio (after factoring in DOP).
For an example of when the elapsed time can be greater than CPU time consider the time that it takes to send the results to a client, such as to a SQL Server Management Studio results grid. The time to send those results will be included in the elapsed time but won't factor that heavily into CPU time. The work that the server does to read the results from memory or disk will contribute to both CPU time and elapsed time. The time spent sending results to the client is one type of wait event (usually ASYNC_NETWORK_IO). There are many different reasons why a SQL Server query could be waiting and not all of them affect CPU utilization in the same way.
Suppose I run this query:
SELECT * FROM INNER_TABLE;

Getting the results to the client results in a CPU time of 94 ms and an elapsed time of 749 ms. I can use sys.dm_exec_session_wait_stats to see the wait events for the session:
╔═══════════════════════╦═════════════════════╦══════════════╦══════════════════╦═════════════════════╗
║       wait_type       ║ waiting_tasks_count ║ wait_time_ms ║ max_wait_time_ms ║ signal_wait_time_ms ║
╠═══════════════════════╬═════════════════════╬══════════════╬══════════════════╬═════════════════════╣
║ ASYNC_NETWORK_IO      ║               19444 ║          662 ║               12 ║                  67 ║
║ MEMORY_ALLOCATION_EXT ║                  37 ║            0 ║                0 ║                   0 ║
╚═══════════════════════╩═════════════════════╩══════════════╩══════════════════╩═════════════════════╝

I can also run the query in a different session without returning the result set to the client. In one test this took 47 ms of CPU time and 60 ms of elapsed time. Here are the wait sessions from that query which are quite different from the other one:
╔═══════════════════════╦═════════════════════╦══════════════╦══════════════════╦═════════════════════╗
║       wait_type       ║ waiting_tasks_count ║ wait_time_ms ║ max_wait_time_ms ║ signal_wait_time_ms ║
╠═══════════════════════╬═════════════════════╬══════════════╬══════════════════╬═════════════════════╣
║ ASYNC_NETWORK_IO      ║                 185 ║            2 ║                0 ║                   1 ║
║ SOS_SCHEDULER_YIELD   ║                   7 ║            0 ║                0 ║                   0 ║
║ MEMORY_ALLOCATION_EXT ║                  40 ║            0 ║                0 ║                   0 ║
╚═══════════════════════╩═════════════════════╩══════════════╩══════════════════╩═════════════════════╝


One of the easiest ways to use a full backup to prepare, a differential to migrate.

First off, you do a full backup and restore to prepare the files on the new server disks, but use the RESTORE .. WITH NORECOVERY option to allow more restores.

At the time of migration, you do a differential backup/restore. This is far quicker and you use RESTORE .. WITH RECOVERY this time.


MySQL forbids referencing outer-level columns deeper than one level of nesting. Your query, however, is referencing users.id three levels deep.

What you need, therefore, is to rewrite the correlated subquery in such a way that, even if it uses nested queries, the correlation with the outer level is not nested, something like this:

(
  SELECT
    COUNT(*)
  FROM
    (
      SELECT ...
    ) AS mutualConnection
  WHERE
   ... = users.id
)

The task is quite a challenge because of the way your subquery appears to be correlated with the main query. If I understand it correctly, the logic goes like this:

  For every user, find the number of distinct connections (users) who are also connected to a certain other user (in this case user ID='4').

So you are retrieving a column collected from either of two connections columns, sender_id and receiver_id, depending on whether the other matches users.id. After retrieval, you are checking that the retrieved sender_id or receiver_id be among the connections of User 4. Finally, you are counting all distinct occurrences of the resulting column (which, I will repeat, is a mix of sender_id and receiver_id).

This is how you could do that without the many nesting levels you have attempted and keeping all the correlations at the same level:

SELECT
  u.*,
  (
    SELECT
      COUNT(DISTINCT CASE u.id WHEN c.sender_id THEN c.receiver_id ELSE c.sender_id END)
    FROM
      connections AS c
    WHERE
      c.status = '2'
      AND u.id IN (c.sender_id, c.receiver_id)
      AND (CASE u.id WHEN c.sender_id THEN c.receiver_id ELSE c.sender_id END)
      IN  (
            SELECT sender_id AS user_id
            FROM   connections
            WHERE  receiver_id = '4' AND status = '2'
            UNION
            SELECT receiver_id AS user_id
            FROM   connections
            WHERE  sender_id = '4' AND status= '2'
          )
  ) AS mutual_connection_count
FROM
  users AS u
;

The CASE expression is the user_id column of your query's tempUser derived table. It is used in the COUNT function as well as in the WHERE clause (the IN predicate). Usually such repetition of code is eliminated by nesting. But nesting cannot be used here because of the MySQL limitation mentioned at the beginning of this post. So, repetition of code is the price you have to pay to work around it. Luckily, there is not much of it in this specific case.


How much overhead is involved with querying a DB on the same server?

If you just want a readable copy of the data in table A, then there are ways to make this efficient without worrying about whether the copy is in the same database or a different database. Or a different server, for that matter, as long as you can reliably point your application differently depending on whether it is reading or writing, and as long as there isn't a bunch of referential integrity tied to A.

Let's keep things simple and have everything in the same database. Create two new schemas:

CREATE SCHEMA fake   AUTHORIZATION dbo;
CREATE SCHEMA shadow AUTHORIZATION dbo;

Now create a copy of A in the dbo and shadow schemas:

SELECT * INTO shadow.B FROM dbo.A;
SELECT * INTO dbo.B    FROM dbo.A;

(Then add all of the appropriate indexes, etc. to both tables.)

Now, create a stored procedure that does the following:

TRUNCATE TABLE shadow.B;

INSERT shadow.B SELECT * FROM dbo.A;

-- optionally add an UPDATE STATISTICS command here

BEGIN TRANSACTION;

  ALTER SCHEMA fake TRANSFER     dbo.B;
  ALTER SCHEMA dbo  TRANSFER  shadow.B;

COMMIT TRANSACTION;

ALTER SCHEMA shadow TRANSFER fake.B;

-- optionally truncate the now stale copy, though I keep
-- this around for debugging / comparisons:
TRUNCATE TABLE shadow.B;

Then schedule it to run in a job (frequency will depend on your tolerance for how long this process takes and how fresh the copy of the data needs to be). You can always call it manually if you want it to be refreshed in between runs.

As I mentioned above, unless you use the Auto-Close feature, the overhead of accessing a table in a different database on the same instance is negligible compared to accessing the same table in the same database. So the process or any related efficiency doesn't really change that much if you put B in a different database (just an extra prefix on the INSERT/SELECT, and the pointing of the application as mentioned above). The process also doesn't change that much if you move the copy to a different server (though linked servers, distributed transactions etc. make this a little more cumbersome). You will see the process might be a little slower across servers, but users shouldn't notice unless they also now have to join data between the two servers - if they can query the other server independently to pull data from B they won't be able to tell the difference, unless the other server is severely underpowered.

If you have referential integrity you may find that you will need to drop/re-create and/or disable/re-enable constraints in order to schema transfer (I've almost always used this trick for relatively static lookup data and, as a copy primarily used for reporting, did not have DRI). Also both copies of A will have their own statistics, so half the time you'll have one set of statistics, and half the time you'll have the other set. I haven't done extensive testing of what happens with query plans etc. after the schema transfer.

Of course if you store B in a different database then perhaps the DRI issues etc. are not all that important anyway, but I thought I would mention them.

I have, however, blogged a bit more about how I arrived at this technique:

http://www.sqlperformance.com/2012/08/t-sql-queries/t-sql-tuesday-schema-switch-a-roo


How can I add timestamp into my table when I use Write-DbaDbTableData?

When I was arranging a similar setup in the past, I did the following:

Use -AutoCreateTable to generate the basic schema for any commands I wanted to write to the database
Modify the tables to use better data types + added a column with a default GETDATE() value
Pipe any results into the newly modified tables.

This is also helpful since any updates to the command output will break due to schema differences and force you to adjust the code and table as needed.


One MySQL database or many SQL Server databases

If hosting externally is an option, you can get a MSSQL Database on Azure for as little as $5/MO.  http://azure.microsoft.com/en-us/pricing/details/sql-database/

The Basic tier has a 2GB limit, but you can get 250 GB for $15/MO.

Azure plans also come with:

  built-in backup support, with the ability to point-in-time restore
  your data from any transaction up to 35 days. Additionally you can
  automatically replicate to another Azure region of your choice. This
  enables you to easily implement a disaster recovery policy designed
  for your business needs.

Might be worth checking out for just a few $$ per month.


You should do it in two passes:

First create the tables:

create table Table1 (..., constraint pk_table1 primary key (...));
create table Table2 (..., constraint pk_table2 primary key (...));
..
create table TableN (..., constraint pk_tableN primary key (...));

Then add the foreign key constraints:

alter table Table1 add constraint fk_table1_tablex
    foreign key (...)
    references TableX (...);
alter table Table2 add constraint fk_table2_tabley
    foreign key (...)
    references TableY (...);
...

This way the there is no order dependency.


View collation are incomplete joins in couchdb

Ok, I found the solution, I have to use the reduce function for that, like:

function(keys, values, rereduce){
var result = null;
var orders = []
for(var i =0; i< values.length; i++){
if(values[i].type=='customer') result = values[i];
    else{
          orders.push(values[i])

    }
}
result.orders = orders;

return result;
}


mysql, changing tables from myisam to ndb

Maybe you could try the following:

ALTER TABLE piwik_log_profiling RENAME piwik_log_profiling_old;
CREATE TABLE piwik_log_profiling SELECT * FROM piwik_log_profiling_old WHERE 1=2;
ALTER TABLE piwik_log_profiling ADD COLUMN query100 CHAR(100) NOT NULL AFTER query;
ALTER TABLE piwik_log_profiling ADD UNIQUE INDEX (query100);
ALTER TABLE piwik_log_profiling ENGINE=NDBCLUSTER;

If it works up to now, them do this

INSERT INTO piwik_log_profiling (query)
SELECT query,LEFT(query,100),`count`,sum_time_ms
FROM piwik_log_profiling_old;

Based on this, your queries will have to use query100 instead of query in its WHERE clauses.

Please let us all know if this worked !!!


Unknown table 'table' in field list on referencing temporary table inside a trigger

The solution is now found by Akina, I made a mistake on IF NEW.barang_kode not in (kode_barang_tbl.kode_barang) THEN which should be IF NEW.barang_kode not in (SELECT kode_barang FROM kode_barang_tbl) THEN. I also made changes to my script which makes the same error on cast(dummy1.rvid as char). So here's my full script in the end. Thanks to Akina.
DELIMITER //
DROP TRIGGER IF EXISTS pob_exists_barang_kode//
CREATE TRIGGER pob_exists_barang_kode BEFORE INSERT
ON `PURCHASE_ORDER_BARANG` FOR EACH ROW
BEGIN
    declare msg varchar(128);
    CREATE TEMPORARY TABLE kode_barang_tbl
    SELECT ven.`KODE_BARANG` as kode_barang
    FroM  (
            SELECT po.`REQ_VENDOR_ID` as rvid
            FROM `PURCHASE_ORDER` po
            WHERE NEW.`PURCHASE_ORDER_ID` = po.`ID`
          ) `asdf`,
          `VENDOR_BARANG` ven
    WHERE ven.`VENDOR_ID` = asdf.rvid;

    SELECT po.`REQ_VENDOR_ID` as rvid
            FROM `PURCHASE_ORDER` po
            WHERE NEW.`PURCHASE_ORDER_ID` = po.`ID` INTO @rvid;

    IF NEW.barang_kode not in (SELECT kode_barang FROM kode_barang_tbl) THEN
        set @errmsg = concat('Kesalahan! Tidak ada kode barang ',
                          cast(new.barang_kode as char),
                          ' pada vendor id ',
                          cast(@rvid as char)
                        );
            DROP TEMPORARY TABLE kode_barang_tbl;
        signal sqlstate '45000' set message_text = @errmsg;
    END IF;

    DROP TEMPORARY TABLE kode_barang_tbl;
END;//

DELIMITER ;


What does it mean "no longer needed"? Archive redologs have "only one" usage, they can be applied onto the last full/incremental database backup to recover the database.

So you either backup your development database and then you can delete them via RMAN during database backup. Or you do not backup your database and then you can run in NO ARCHIVELOG mode.


What are the risks of building a database outside IT control?

There are a variety of factors that would cause IT organizations to be cautious about creating databases and giving business users the level of access to those systems that you are, presumably, asking for when you talk about wanting to "play with" the data.

First off, since you're in a company that does trading, that implies that there are dozens of laws and agencies that regulate the company.  In order to comply with those laws, your organization undoubtedly has put together dozens if not hundreds of policy documents that undoubtedly include policy statements that say things like only members of the DBA team have superuser privileges to modify data in databases, only changes that go through the official software development lifecycle are promoted to production, a separation of duties ensures that people that administer the database are different from people that code against the database which are different from people that input and maintain the data.  The first step of the many, many audits that such an organization goes through is to check to see whether the policies in those documents matches the actual practice.  Finding "rogue" databases that exist outside of these policies is a huge red flag in such an audit.  Even if your database didn't actually create any real risk, it would create an audit finding because it violated the agreed-upon policies.  Theoretically, the IT organization could work with their regulators to amend the dozens of policies to allow this sort of thing in special cases, but that would likely be a tremendous amount of work.

It is unlikely, though, that regulators would view the sort of risk analysis that you're talking about as the sort of thing that might get a pass from the burden of regulation.  The process of building risk models and analyzing risk in a trading business is about as core a concern of regulators as you're going to find.  Regulators are going to want someone to be able to explain exactly what sort of analysis is being done, what data sources are being used, how that data is being manipulated, etc.  They're going to want to know who has access to that data, how it is backed up, etc.

Second, systems like the one you describe have a tendency to grow over time and often get dumped on IT organizations once they become unwieldy or when the original owners move on.  A system that you set up today just for yourself to play with can quickly become a system that your department depends on and then a system that is integrated with all sorts of reporting and workflow processes as time goes on.  At some point, though, you'll move on to another company or the system will get slower or someone will realize that some critical bit of the business depends on a system that is running with no backups on some guy's machine and then IT will get the call to own the system.  Those projects are generally train wrecks.  These systems aren't generally designed well (risk analysts generally come up with better software designs than software developers come up with risk models but both pursuits benefit from expert attention up front), they're not properly documented, etc.  They also tend to rely on tons of manual processes that scale acceptably when one business user owns the process and understands in detail what the system is doing and why but that scales incredibly poorly when IT folks are trying to simultaneously manage many different systems that they don't understand in deep detail.  IT organizations generally do everything they can to prevent these systems from sprouting in the first place because they turn into such hassles in the end.

Third, if the system that you're building is necessary for you to do your job, then it's likely that it needs the attention of IT.  It probably needs to be backed up, for example.  It probably needs to be added to various proactive and reactive monitoring scripts/ systems that the DBAs monitor in order to ensure that sufficient disk space is available, that the workload of your system isn't crushing other systems, etc.  It probably needs to be part of the disaster recovery plan (if only to note that it's a low priority system to restore).  It needs to get added to the set of systems that the DBAs patch when security updates come out or when the enterprise upgrades to new versions of the database.  It needs to get added to the support contract so that it doesn't raise issues when the company gets audited by the vendor.  These things are generally much harder when IT doesn't own the system.  But if IT owns the system, it needs to behave like all the other IT systems-- it needs to be developed by developers, used by users, administered by administrators, it needs to have different environments (dev, test, staging, prod) to support the standard development models, etc.

In most IT organizations, if you need this sort of setup, you would need a project to create it (which would include things like assigning development resources, a project manager, etc.)  That project obviously needs to be prioritized, implemented, etc.

If you really want "free reign" to do whatever you want without any of the controls that are put on developers, it's possible that you, your manager, and IT could get together and come up with a reasonable compromise.  Perhaps you could be given slightly elevated privileges in some database/ schema somewhere along with restrictions in the types of things you're allowed to do (i.e. nothing from what you're doing can feed any other system, nothing can be used in a production process, etc.)  It's not uncommon for analysts to have a "playground" where they can create tables to support ad hoc reporting, for example, with the understanding that anything that needs to become part of a production report needs to get built following the full development lifecycle process.


Replication fails with "Transaction log for database is full due to LOG_BACKUP"

there is no clear indication of what may be causing this upsetting in the transaction log.

but I would check it out what is happening in the distribution.

run the following procedure:

sp_helpdistributor

that will show you where the distribution database and server is.

Assuming the name of the distribution database is distribution:

 SELECT * FROM distribution.dbo.MSrepl_Commands

have a look at what you get and use the last transaction to see what is in it:

check it out:

 SELECT * FROM distribution.dbo.MSrepl_Commands
--- commands for all transactions for replication for seqno or all
  WHERE   xact_seqno = 0x000002470000F7D30021

if all good or at least nothing obviously bad:
use that xact_seqno here (I did not use the end one on this example - but if your replication is very busy you can limit it):

    exec distribution.dbo.sp_browsereplcmds @xact_seqno_start =  '0x000002470000F7D30021',  -- returns all/seqno info (sql query) on replication
   -- @xact_seqno_end  =  '0x00001C030000F4540018'

This allows you to see what has been replicated - what commands to each article (see on the right hand side of the picture below)

This is not a comprehensive answer but would give you a start.
Well after the obvious thing as looking at the replication monitor.

Check these scripts to give you some extra vision.

You can also check Some useful commands and sp for Transaction Replication


Try this query:

select * from items where feed_id NOT IN (select id from feeds)

Also set foriegn key relationship with delete cascade. So it will get deleted from dependent tables as well.


PostgreSQL 8.4: How to tell if a procedural language is installed or not?

In PostgreSQL 9.0 and later, PL/pgSQL is pre-installed by default.
Version 9.0 also introduced CREATE OR REPLACE LANGUAGE:

  CREATE OR REPLACE LANGUAGE will either create a new language, or
  replace an existing definition. If the language already exists, its
  parameters are updated according to the values specified or taken from
  pg_pltemplate ...

To avoid raising an exception on older versions you can check the catalog table pg_language. I quote the manual once more:

  The system catalog pg_language [...] records information
  about the currently installed languages.

SELECT EXISTS (
  SELECT 1
  FROM   pg_language
  WHERE  lanname = 'plpgsql');

Or use the client application createlang, which has an option to check for existing languages:

createlang -l [connection parameters]


How is this called in a snowflake schema?

In Kimball terminology it would be a ﬁxed depth hierarchy. Using Inmon I think just a normalised dimension.


Delete Trigger - Linking to a table with a PK made up of three columns

CREATE TABLE A
 (
     ID1 INT,
     ID2 INT,
     ID3 INT,
     FOO INT,
     BAR INT,
     PRIMARY KEY (ID1, ID2, ID3)
 );

 CREATE TABLE B
 (
     ID1 INT,
     ID2 INT,
     ID3 INT,
     FOO INT,
     BAR INT,
     PRIMARY KEY (ID1, ID2, ID3)
 );
 GO

 CREATE TRIGGER TRG_A_INSDEL
 ON [A]
 AFTER INSERT, DELETE
 AS
 BEGIN

     /* Insert new rows */
     INSERT INTO B
         SELECT * FROM inserted;

     /* Delete those rows that match PK */
     DELETE B
     FROM
         B
     JOIN
         deleted d
         ON d.ID1 = B.ID1
         AND d.ID2 = B.ID2
         AND d.ID2 = B.ID2;
 END
 GO

 INSERT INTO A VALUES (1, 1, 1, 1, 1);
 INSERT INTO A VALUES (2, 2, 2, 2, 2);
 INSERT INTO A VALUES (3, 3, 3, 3, 3);
 GO

SELECT * FROM B;
GO

ID1 | ID2 | ID3 | FOO | BAR
--: | --: | --: | --: | --:
  1 |   1 |   1 |   1 |   1
  2 |   2 |   2 |   2 |   2
  3 |   3 |   3 |   3 |   3

DELETE FROM A WHERE ID1 >= 2;
GO

SELECT * FROM B;
GO

ID1 | ID2 | ID3 | FOO | BAR
--: | --: | --: | --: | --:
  1 |   1 |   1 |   1 |   1

db<>fiddle here


Set based query to group data ordered data

Start by making a column like CASE WHEN MinutesSinceLastRequest < 20 THEN 0 ELSE 1 END AS IsOver20

Then wrap that in a rolling sum - this will change every time there's a 1 for IsOver20: SUM(IsOver20) OVER (ORDER BY [TimeStamp] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupNum

So now you can group by this easily. Include PARTITION BY UserId if you need to break it down by UserId too.


MongoDB Data loss following machine PowerOff with writeConcernMajorityJournalDefault true

How much time your query was running?? and from where you get next count as you mentioned after stopping primary you get this count is lesser?? also If you can share your script.

It will be obvious that If you are stopping the Script the data will not going to be insert as you expected.


How to automatically shrink a SQL Server transaction log only if it exceeds a certain limit (e.g. 20 GB)

Our database is set to FULL recovery mode and we take a database
  backup and a log backup every night.

I think you need to take log backups much more often than every night. Try every hour to see if that keeps your file growth in check. If not you may need to run them more frequently.

You could also deploy an SQL Agent Alert that triggers when the log percent used reaches a certain threshold. This alert could send an email or fire an agent job that runs a log backup job.

Also I wouldn't ever recommend automatically shrinking files.


Repopulating Oracle tables using data from Excel/CSV file

For reusable data loads, I prefer defining static external tables that read from CSV files. You'll need an Oracle DIRECTORY object to read the file.

This example creates a five-column external table called FOO_TAB that reads the FOO_TAB.CSV file from the FOO_DIR directory.

CREATE TABLE foo_tab
(
   a1   VARCHAR2(4000),
   a2   VARCHAR2(4000),
   a3   VARCHAR2(4000),
   a4   VARCHAR2(4000),
   a5   VARCHAR2(4000),
)
ORGANIZATION EXTERNAL
(
   TYPE ORACLE_LOADER
   DEFAULT DIRECTORY foo_dir
   ACCESS PARAMETERS
   (
      RECORDS DELIMITED BY newline
      BADFILE 'foo_tab.bad'
      DISCARDFILE 'foo_tab.dis'
      LOGFILE 'foo_tab.log'
      SKIP 1 /* first row just has column headers */
      FIELDS TERMINATED BY ","  OPTIONALLY ENCLOSED BY '"'
      LTRIM
      MISSING FIELD VALUES ARE NULL
      (
         a1   CHAR(4000),
         a2   CHAR(4000),
         a3   CHAR(4000),
         a4   CHAR(4000),
         a5   CHAR(4000),
      )
   )
   LOCATION (foo_dir:'foo_tab.csv')
)
REJECT LIMIT UNLIMITED;


There is no magic; if you have 8 columns to output then you'll need to have 8 expressions to change the output in any way. (Have you considered normalization?)

A slightly less verbose approach would be:

SELECT
  Hail_N = COALESCE(NULLIF(hail_n, ''),'0'),
  Hail_S = COALESCE(NULLIF(hail_s, ''),'0'),
  ...
FROM dbo.vw_hail_wind_directions; -- always use schema prefix and statement terminators


How do you approach refactoring an ugly procedure/query?

Yes, create unit tests.  This is really the only way to ensure that a modified version meets the same requirements as the original.

To refactor the procedures look for any repeating patterns of code that could be extracted into separate procedures or nested procedures.  If the procedures are still too long break parts of it into separate procedures that each complete one task.  As you break off new procedures you should add unit tests for it.

Seven or eight levels of sub-selects does sound excessive, but you may find that some or even most of these are necessary to produce the data required.  I would focus on the procedure initially and then tackle the SQL.

To do unit testing in Oracle, the foremost commercial product for testing is Quest's Code Tester.  Oracle has Unit Testing built into it's free SQL Developer product.  The question Unit testing for PL/SQL on StackOverflow has some other options or you can just write your own tests.


I use set events 10046, for the explain plan and 10053 for the waits.
Once you run the queries that you want timings for, assuming that you are using 10g/11g/12c go to the diag trace folder, find your trace files and run tkprof against the trace files.

ALTER SESSION SET tracefile_identifier = 'test_plan1';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

/*
run your queries here
*/

ALTER SESSION SET EVENTS '10053 trace name context OFF';
ALTER SESSION SET EVENTS '10046 trace name context OFF';

go to the <diag>/rdbms/<sidname>/<sidname>/trace directory
tkprof <in file name> <out file name> waits=yes explain=syste/<pwd>

This will give really good information about how the queries ran, how long,
how much logical/physical IO, explain plan and waits. Plus you don't need
any optional components to get the information.

-- use logging package to log and not reraise errors
CREATE OR REPLACE TRIGGER application_logon_trace
       AFTER LOGON ON DATABASE
DECLARE
   v_db_user VARCHAR2(64) := SYS_CONTEXT ('SESSION_USERID', 'PROXY_USER' );
   v_host    VARCHAR2(64) := SYS_CONTEXT ('USERENV',        'HOST'    );
   v_ddl_cmd VARCHAR2(2000);
BEGIN
-- You can have an IF clause that will only turn on logging for certain users if you like
-- or you can only log sessions coming from certain servers/pcs if you want.
   IF INSTR(v_host, '.') > 0
   THEN
      v_host := SUBSTR( v_host, 1, INSTR(v_host, '.')-1);
   END IF;

   v_ddl_cmd := 'ALTER SESSION SET tracefile_identifier = ''application_logon_trace_'|| v_db_user || '_' || v_host ||'''';
   DBMS_OUTPUT.PUT_LINE( v_ddl_cmd );
   EXECUTE IMMEDIATE v_ddl_cmd;

   v_ddl_cmd := 'ALTER SESSION SET EVENTS=''10046 trace name context forever, level 12''';
   DBMS_OUTPUT.PUT_LINE( v_ddl_cmd );
   EXECUTE IMMEDIATE v_ddl_cmd;

   v_ddl_cmd := 'ALTER SESSION SET EVENTS=''10053 trace name context forever, level  1''';
   DBMS_OUTPUT.PUT_LINE( v_ddl_cmd );
   EXECUTE IMMEDIATE v_ddl_cmd;
EXCEPTION WHEN OTHERS THEN NULL; -- feel free to call you favorite error logging package here.
END;
/


Is it possible for a properly set up MySQL to return invalid UTF-8?

I don't think so.  But I have come close:

mysql> CREATE TABLE invalid (c VARBINARY(22));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO invalid (c) VALUES (UNHEX('65c3c3c366'));
Query OK, 1 row affected (0.01 sec)

mysql> SELECT HEX(c) FROM invalid;
+------------+
| HEX(c)     |
+------------+
| 65C3C3C366 |
+------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE invalid MODIFY COLUMN c VARCHAR(22) CHARACTER SET utf8mb4;
Query OK, 1 row affected, 1 warning (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------+
| Level   | Code | Message                                                         |
+---------+------+-----------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xC3\xC3\xC3f' for column 'c' at row 1 |
+---------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(c) FROM invalid;
+--------+
| HEX(c) |
+--------+
| 65     |
+--------+
1 row in set (0.00 sec)

By starting with BINARY, then converting to CHAR, I can get invalid data into the column.  However, the ALTER gives a warning when I try to convert it.

Note in the final SELECT, the data is truncated.  The truncation happened in the ALTER, not the SELECT, where your Question wanted it to happen.  This can be verified by converting back to BINARY:

mysql> ALTER TABLE invalid MODIFY COLUMN c VARBINARY(22);
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT HEX(c) FROM invalid;
+--------+
| HEX(c) |
+--------+
| 65     |
+--------+
1 row in set (0.00 sec)

If you have seen the problem happen, please provide details; perhaps I can dissect the problem.


How to move geographical data from MS Access to SQL Server

I would use VBA to construct & export your spatial data into the Well Known Text (WKT) format. SQL Server supports importing data from this format & as it is text based it is fairly easy syntactically.


Why seq-scan can be much faster than index-scan and index-only-scan in this simple query?

This is quite a common query (pardon the pun! :-) ) from people running queries which perform full table scans (FTS), when the poster feels that the system should make use of the index(es).

Basically, it boils down to the explanation given here. If the tables are so small, the optimiser will say that "it's not worth the bother of going to the index, doing a lookup and then fetching the data, instead, I'll just slurp in all the data and pick out what I need", i.e. perform an FTS.

[EDIT in answer to @txsing's comment]

For an MVCC (multi-version concurrency control) database, you have to traverse every record for a count at a given moment - that's why, for example, a COUNT(*) is much more expensive for MySQL's InnoDB rather than MyISAM.

An excellent explantion (for PostgreSQL) is available here. The guy who wrote this post is a "major contributor" to PostgreSQL (thanks to @dezso for leading me to that post).


how to use mysql to delete only the first ten table records?

I can understand you saying "stable". There is no clean mechanism in MySQL to implicitly govern the order in which MySQL can delete rows using LIMIT. It is actually not transaction-safe for MySQL Replication.
The only way to guarantee an orderly deletion of rows is to delete by a PRIMARY KEY or UNIQUE KEY that does not rely on an auto_increment column.
EXAMPLE OF STABLE DELETE
Suppose you want to delete the first 10 employees in a company table (layoffs do hurt, this is only an example)
CREATE TABLE emp
(
    id INT NOT NULL AUTO_INCREMENT,
    empid INT NOT NULL,
    ...
    PRIMARY KEY (id),
    UNIQUE KEY (empid)
);

You can safely delete the 10 employees as follows:
CREATE TABLE empids_to_delete
SELECT empid FROM emp ORDER BY empid LIMIT 10;
ALTER TABLE empids_to_delete ADD PRIMARY KEY (empid);
DELETE B.* FROM empids_to_delete A INNER JOIN emp B USING (empid);
DROP TABLE empids_to_delete;

This would be great as long as you create the empid outside this table. This would not present a Replication Slave with a problem even if the auto_increment sequence  on a Master is completely different from that of the Slave.


Here's a simple analogy as to why transaction logs vs simple backups are vastly different sizes.

Imagine you have the number 10. Each day for the next week you add 1 to it. This gives you the number 17 after a week.

If you're recording every single transaction in a complex logging scenario you're essentially storing:

10+1+1+1+1+1+1+1

Although in this case each +1 is actually dozens of lines of code!

Wouldn't it be easier just to store the result (17). Now obviously this is less flexible and won't allow you to roll-back cleverly if things go wrong - but that's the fundamental trade-off here!


You need to have VIEW SERVER STATE permission on the user account.

GRANT VIEW SERVER STATE TO <username>;

This will need to be granted by someone with sufficient privileges and will allow you to view all sessions when you run commands like sp_who or sp_whoisactive.

If you are worried about security implications of this permission, this post may help Security and Performance implications of "View Server State"

Once you have the permissions to view server state, you can then restrict you just your user by passing that in as a parameter

EXEC sp_who [Domain\mark.sinkinson];
EXEC sp_whoisactive @filter_type = 'login', @filter = [Domain\mark.sinkinson];


Database Design - Employee Clocking In and Out System

One thing you might want to consider changing is making clocking.clock_out_date nullable.  Until the employee clocks out there isn't a reasonable value to use here, so NULL is the best choice.

You can tell the difference between someone who is still working (theoretically) and someone who forgot to clock out by checking their previous clocking record when they clock in.  If the previous record has a NULL clock_out_date then they obviously forgot to clock out.

You could also create a report that checks for employees who haven't clocked out for more than (some configurable number of hours) since they last clocked in.  You should be able to get a reasonable number for the longest expected shift.  It may be once in a while that you report an employee to management for not clocking out when they are actually still working, but that should be rare and the manager will know it - or find it out quickly enough, so the few false positives you might generate aren't serious.

One other thing you might want to think about is not keeping the hourly rate right on your staff table.  It might be better to keep this in a child table which includes date ranges since you want to be able to let a wage go up or down over time and it would be good to (a) have a history and (b) be able to enter future-dated changes to relieve time pressure for data maintenance.


SQL Server injection - how much damage in 26 chars?

Easy:

GRANT EXECUTE TO LowlyDBA

Or, I guess in this case it'd be

grant execute to lowlydba

Take your pick of variations on this.

In all likelihood you may be able to test this now against your current system, but any number of small changes in the database over time could invalidate your testing. The character string could change, someone could create a lower case stored procedure that has destructive potential - anything. You can never say with 100% confidence that there isn't a destructive 26 character attack someone could construct.

I suggest you find a way to make the developer follow basic industry standard best security practices, if only for your own sake as someone who I presume is at least partially responsible should security breaches happen.

Edit:

And for maliciousness/fun, you could try enabling every trace flag. This would be interesting to observe. Feels like a blog post Brent Ozar would make...

DBCC TRACEON(xxxx, -1)


How can I identify the number of times a View is called in SQL Server?

Here is one not-overly-scientific way to do it, it is quite possible that it will miss references due to, say, indexed view expansion, and get you false positives if the batch text contains your view name in comments or as part of another entity name or string. Also won't work on earlier versions of SQL Server (which is why it is important to tag your question with the minimum version of SQL Server you need to support).

SELECT t.text,s.execution_count,s.last_execution_time
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
WHERE t.text LIKE '%your-view-name%';

Also note this only contains metrics going back to the last service restart, RECONFIGURE, etc.


For this scenario, the database server should not be publicly exposed, for obvious security reasons.

You should expose a public interface (API) of your application, probably by using something like web services. The mobile application instances then only communicate directly with the API.

This separation (decoupling) allows for all sorts of good things to happen, such as easier scale-out/load balancing.


Changes to Oracle EM 11G Database Control "Availability >> Backup Settings" don't stick

Enterprise Manager 11.2.0.4 Database Control Backup Settings are not Retained, Scheduled Backups Fail to Run (Doc ID 2185136.1)

  SOLUTION

  Download and apply one-off Patch 19316728 to the 11.2.0.4 database
  Oracle Home according to the instructions in the associated README
  file.

If you want to set the above setting manually, just log in to the server, and:

$ rman target /
RMAN> configure controlfile autobackup on;


select l.*
from location as l                             --- find all locations
where not exists                               --- where there isn't
      ( select *
        from location_flag as lf               --- a flag
        where lf.location_id = l.id
          and lf.flag_id = 1                   --- with 1
          and lf.value = 'YES'                 --- and YES
          and not exists                       --- without
              ( select *
                from location_flag as lf2      --- another flag
                where lf2.location_id = l.id
                  and lf2.flag_id = 2          --- with 2
                  and lf2.value = 'YES'        --- and YES
              )
      ) ;

or:

select l.*
from location as l
where not exists
      ( select *
        from location_flag as lf
        where lf.location_id = l.id
          and lf.flag_id = 1
          and lf.value = 'YES'
      )
   or exists
      ( select *
        from location_flag as lf2
        where lf2.location_id = l.id
        and lf2.flag_id = 2
        and lf2.value = 'YES'
      ) ;


How to query for certain text in log messages from Google App Engine that are exported to BigQuery?

The reason why this query failed is because you have a table with nested repeated field protoPayload.line (datatype RECORD).

You can try implicit unnesting, it would be something like:

SELECT x
FROM `gae_log.appengine_googleapis_com_request_log_20180323` as t,
      t.protoPayload.line AS x
WHERE x.logMessage  like '%14994838999176%'

You can also have a look on how to query STRUCT elements in an ARRAY here.


Why does pg_basebackup use so much less resources than pg_dump?

pg_dump doesn't use more I/O that pg_basebackup. If anything, then fewer, since it doesn't have to read indexes. What could be is that you have a lot of TOASTed overlength values, and reading from the TOAST table causes random and maybe more I/O.

Typically, what is more used by pg_dump is CPU, because it has to parse all the table data and format them for output. So I'd guess that CPU is the resource which is scarce on your system.

As a first measure, you should identify the bottleneck, and then optimize your workload. If you have reached the limit there, consider adding more of the scarce resource.

Another, quite different cause for what you observe would be if your application misguidedly takes ACCESS EXCLUSIVE locks on tables. Such locks would be blocked until pg_dump is done and are a bad idea for other reasons as well (autovacuum). Check pg_locks for entries where granted is FALSE.