Which of these queries is best for performance?

Sometimes I wonder if SHORT scripts really is the best thing to focus on.

The size of a script has little to do with how efficiently the query will execute. A more compact statement will likely consume fewer resources in terms of compilation, but (re)compilation is usually a rare occurrence in a live system.

Fewer table accesses is usually desirable, though, and this does lead to more compact code.

Very generally speaking, a smaller execution plan will yield better results, and a lower estimated cost will yield better results. Again, though, it's highly situational. Cost estimates in particular can be way off in some cases. It's important to measure the actual execution time, because at the end of the day, that's what matters.

  With left joins i can achieve what i want with just a few lines. But then I tried with a longer script, using unions. Which is the best method?

First of all, we need to know how much data will be in these tables in a real system. Right now there's so little it will be difficult to use the STATISTICS TIME performance metrics to figure out a winner -- the results that come back will be dominated by factors other than the query execution. With more data, it's likely the plans will change, thus rendering the comparison here moot.

Having said that, by looking at the query plans as they are now from a logical point of view, the first one is the winner.

You can see that the Clustered Index Scan of quantities appears once in the first plan, while it appears four times in the second one. The second plan also contains an expensive Distinct Sort as a result of using UNIONs (this operator could be eliminated by using UNION ALLs instead, which won't change the results).

The first query could also probably be improved, by getting index seeks on the colors and sizes tables, instead of table scans. It might be worth trying a hash match plan as well (which is what you'll probably see when quantities and products are larger), but for tables this small, the startup cost may be too much overhead to be of benefit.

What I would suggest you do is run each of the statements you want to test 10,000+ times in a loop, figure out the average execution time, and then compare.


"truncate log on checkpoint" option in SQL Server

You can still see (atleast in SQL Server 2008R2) trunc. log on chkpt. using sp_dboption, and you can set the database option as well

In SQL Server 2012 and up, you will get a nice error

  Msg 2812, Level 16, State 62, Line 1
  Could not find stored procedure 'sp_dboption'.

Its there lying for backward compatibility ONLY. In SQL Server 2000 and up, SQL Server has a concept of recovery model that defines how the log truncation is honored e.g. In simple recovery - only a checkpoint will truncate the log where as in full recovery mode - only a log backup will truncate the log.

If you want to understand more about Transaction Log Management then refer to Stairway to Transaction Log Management

As s side note, SybaseASE even in newer version Sybase 15.7 has this option (As a matter of fact it has duplicate trunc log on chkpt and trunc. log on chkpt. the later is the one that SQL Server got in SQL 7.0 through SQL Server 2008R2 - seen in sp_dboption):


Create an explain plan for query over a global temporary table

For testing purposes, you can insert data in your GTT, gather statistics, and lock statistics. That will let you get an explain plan in another session as if your GTT had data there. You should unlock statistics after you're done with testing.

I do wonder why you have this request. As of Oracle 12c GTTs have session-private statistics by default. You can simply gather statistics after the insert. On Oracle 11g you can still safely gather statistics on the GTT if you're guaranteed to only have a single session querying the GTT at once. Otherwise you can delete statistics, lock them, and rely on dynamic sampling to get statistical information about the table. If your default level of dynamic sampling isn't sufficient you can set a level just for the GTT at the query level with the /*+ dynamic_sampling({alias} {level}) */ hint. Another option is to load a representative amount of data and to lock the statistics.


Your LEFT JOIN is converted to a INNER join because the WHERE contains conditions about the columns of the right (linkcats) table. Then, since the conditions are contradicting, the query will always return 0 rows:

WHERE linkcats.cat_id IN (17,1,35,33,50)
AND linkcats.cat_id NOT IN (28)

You need a semijoin and an anti-semijoin, which can be written with EXISTS and NOT EXISTS subqueries, like this:

SELECT u.*
FROM shortenified_urls AS u                        -- all links
WHERE u.is_social = '1'
  AND u.active = '1'
  AND EXISTS
      ( SELECT 1
        FROM linkcats AS lc                        -- that are related
        WHERE lc.link_id = u.id                    -- to at least one of
          AND lc.cat_id IN (17,1,35,33,50)         -- these categories
      )
  AND NOT EXISTS                                   -- but not to
      ( SELECT 1
        FROM linkcats AS lc
        WHERE lc.link_id = u.id
          AND lc.cat_id IN (28)                    -- any of these
      )  ;


Specify a single libpq style connection string:

psycopg2.connect("host=db-0,db-1 user=user password=password dbname=data")


How to handle concurrent write access to two related tables

An UPDATE takes a write lock on the row automatically, which prevents concurrent transactions from doing the same until the lock is released (your transaction has finished).

So this should do the trick:

WITH upd AS (
   UPDATE t1
   SET    ct1 = true
   WHERE  t1.id = $t1_id  -- your input here
   AND    ct1 = false
   RETURNING t1_id
   )
INSERT INTO t2(t1_id, col1)
SELECT t1_id, 'foo'  -- or your input for t2 here
FROM   upd;          -- only if UPDATE found a row

This assumes a PK t1.id to allow multiple rows in t1. Your example makes it seem like there is a single row in t1. The same solution would work for that simple case, just remove t1.id from the query.

t1.ct1 must be defined NOT NULL.

If the UPDATE finds no row (row in t1 with t1.id = $t1_id is already true or does not exist) then nothing happens.

If concurrent transactions wait to update the same row, they will wake up once this transaction has finished.

If your transaction commits, ct1 is true now, and the recheck for others will return no qualifying row, i.e. concurrent transactions are finished, too.

If your transaction rolls back, the next one in line gets to update t1 (and hence also insert rows in t2).

Note: Normally, queries in CTEs can execute in any order. But since the outer INSERT references the UPDATE, a sequence of operations is established.


Multiple Instances Of Oracle Or One Instance With Multiple Schemas

Even if you don't have name conflicts and could easily combine all Dev environments into a single instance, I still prefer multiple instances for my Dev environments due to the following:

You really should have your Dev environments closely mimic your Live environments in terms of hardware, resources, and volume of data as much as possible.  You will be in a much better position to:

1)  Stress test new code before large (or even small) projects go live.

2)  Your Dev teams are far more likely to catch performance issues before they go live (even if you don't stress test).

Performance issues should be caught long before you go live.  The best way to do that is to test in an environment that is as close to your live environment as you can possibly make it (even before that code is uploaded to the QA environment, if you have one).


Number of times PostgreSQL database restarted

Based on the pg_log or any PG log files, using grep to match how many times of shutting down the database.

grep -rwn postgresql-2018-04-12.log -e "database system is shut down"

The output looks like as below

  postgresql-2018-04-12.log:9:2018-04-12 16:07:25 CEST LOG:  database
  system is shut down postgresql-2018-04-12.log:18:2018-04-12 16:08:18
  CEST LOG:  database system is shut down


How many partitions should I set for my MySQL table?

Do not PARTITION unless you have actions that would benefit from it.  Let's see your queries.  And, will you be purging 'old' data?

Rule of Thumb:  Don't even consider PARTITION unless there is more than a million rows.

Rule of Thumb:  Have 20-50 partitions; no more.

Do not create next month's partition until you are about to need it.  Keep an empty 'future' partition in case you forget to create the next one.

For real performance, use Summary tables.

More on partitioning.
More on Summary tables.

SHOW CREATE TABLE -- There may be some simple changes to make things more efficient.  Also provide a sample SELECT.


This is a question address canonicalization and parsing. Essentially what you're talking about is handled through a gazetteer (geographical rule set). There are two ways to do this right,

address_standardizer from the PostGIS project and certainly better if you're only using United States addresses.
pgsql-postal may be a better method for international addresses.

I'll show the address standardizer version for the address,

Chicken Ranch
10511 Homestead Rd
Pahrump, NV 89061

Using standardize_address from address_standardizer, returns a composite type of stdaddr. First we install it,

CREATE EXTENSION address_standardizer;
CREATE EXTENSION address_standardizer_data_us;

And, then we can use it like this.

SELECT * FROM standardize_address('us_lex',
   'us_gaz', 'us_rules', '10511 Homestead Rd, Pahrump, NV 89061');
 building | house_num | predir | qual | pretype |   name    | suftype | sufdir | ruralroute | extra |  city   | state  | country | postcode | box | unit
----------+-----------+--------+------+---------+-----------+---------+--------+------------+-------+---------+--------+---------+----------+-----+------
          | 10511     |        |      |         | HOMESTEAD | ROAD    |        |            |       | PAHRUMP | NEVADA | USA     | 89061    |     |
(1 row)

So you can see, ROAD is pulled out in suftype

Likewise,...

SELECT * FROM standardize_address('us_lex',
   'us_gaz', 'us_rules', '1600 Pennsylvania Avenue, NW Washington, D.C. 20500');
 building | house_num | predir | qual | pretype |     name     | suftype |  sufdir   | ruralroute | extra | city | state | country | postcode  | box |     unit
----------+-----------+--------+------+---------+--------------+---------+-----------+------------+-------+------+-------+---------+-----------+-----+--------------
          | 1600      |        |      |         | PENNSYLVANIA | AVENUE  | NORTHWEST |            |       |      |       | USA     | D C 20500 |     | # WASHINGTON


SQL Server replication: can I modify contents of a snapshot?

I've experimented quite extensively with the mentioned situation and it looks like there are different scenarios in which snapshot is re-created: sometimes they involve bulk data being transferred, sometimes they don't. I suppose that the logic on the Publication side is clever enough to see when re-initialization of a subscription will require data being loaded again, and when - not.

Sometimes it brings disaster though, like when you try to re-create a filter, aren't able to and then would have to drop the article and create it again with a filter. The data is actually good on both sides, even if it's not matching the filter on the subscription - well, so what - it will after some updates. The problem is that then the snapshot will be re-generated and subscription marked for re-init. But the table is full of (good) data already and more likely than not you get an error trying to apply the snapshot.

So then, as in my question, I took all the involved .bcp files and emptied them (with a powershell script as they were many). Deleting them didn't help, because snapshot realizes something's been stolen from it. But empty bulk data files are harmless and then everything goes through, thankfully!

I suppose you could find a scenario when your application would suffer from some data being temporary present that doesn't match the replication filter, but in my case I think everything is working - so far so good.


Reshape table by converting columns to rows

There is no way to have the column names being evaluated as columns, as pointed out in this question https://stackoverflow.com/questions/15800367/select-columns-with-particular-column-names-in-postgresql

The way to do it, is using to_jsonb, as suggested in unnest all columns from a given table, like:

select foo.id, x.*
from foo, jsonb_each_text(to_jsonb(foo)) as x(colname,colvalue)
where x.colname ~ '^[a-z]$';

In my specific case, I was trying to convert all data related to Corona Virus. The original data is available at: https://github.com/CSSEGISandData/COVID-19/blob/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv. I've imported it as table covid.

It has many columns named after the date, like: '1/22/20','1/23/20','1/24/20','1/25/20','1/26/20', .... The first columns are related with the location, so I want to keep them in the reshaped table.

The final query I'm using is:

select id, geom, "province/state" , "country/region", to_date( reportdate, 'MM/DD/YY'), nofcases
from covid, jsonb_each_text(to_jsonb(covid)) as x(reportdate,nofcases)
where x.reportdate ~ '[0-9]+'
order by 5;


Hinting to Redshift how to distribute a join?

You cannot use hints in Redshift like Oracle. You can do things like set the distribution keys. If X is always used in that fashion, it may make sense to incorporate that into your DDL.

See here:
http://docs.aws.amazon.com/redshift/latest/dg/t_Distributing_data.html


I would create "Status" as a lookup table

SentStatus
StatusId smallint identity (1,1)
StatusLabel char (10)

As it is easier to maintain and also add values to.

Of course, you should link "mail" to "Employer" and "Applicant" table using a many-to-many relationship.

Be mindful of over-using "var" if you don't need it, and definitely of the difference between nchar and char: if you know in advance that "status" is going to be a label in English which you assign, you don't need to accommodate for "nchar" which is double in length.


Is there a way to enable MariaDB MEMORY storage engine for every table?

There are two situations you must address

SITUATION #1 : TABLES TO CREATE

You must add this to my.cnf

[mysqld]
default-storage-engine=MEMORY

Then, restart mysql (MariaDB)

CAVEAT: This will not convert already existing tables to MEMORY tables.

What can done for those existing tables ?

SITUATION #2 : TABLES ALREADY EXISTING

Here is the script to convert all tables to MEMORY tables:

SQLSCRIPT=/root/Convert_To_MEMORY.sql
echo "SET SQL_LOG_BIN = 0;" >
MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MEMORY;')"
SQL="${SQL} ConversionSQL FROM information_schema.tables WHERE engine='InnoDB'"
SQL="${SQL} AND table_schema NOT IN ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -AN -e"${SQL}" > ${SQLSCRIPT}
less ${SQLSCRIPT}

If you are satistied with the contents of the SQL Script, login to MariaDB and run

mysql> source /root/Convert_To_MEMORY.sql

EPILOGUE

While I gave you the two major things to do to have everything as MEMORY tables, you should read my old posts on the good, the bad, and the ugly of using MEMORY tables:

May 22, 2011 : I am using the MEMORY storage engine but MySQL still writes to my disk...Why?
Sep 26, 2011 : Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?
Jan 17, 2012 : Mysql Memory table getting many locks
Jan 21, 2012 : Is InnoDB Engine up to speed against Memory Engine?
Jan 30, 2012 : How much memory will a MEMORY table take up?


Is it correct to dynamically add rows to a table in sqlite, or should I use csv? (I'm working with objective c.)

They just properly normalize their database so that it is possible to store an arbitrary number of numbers; something like this:

CREATE TABLE Contacts (
    ContactID INTEGER PRIMARY KEY,
    Name TEXT
);
CREATE TABLE PhoneNumbers (
    ContactID INTEGER REFERENCES Contacts(ContactID),
    Number TEXT
);


Need correct mysqlfrm command path to restore a database from .ibd and .frm files

You cannot just move the .exe around like that and expect it to work. All libraries and dll's (in your case I suspect _bz2.pyd) have to be in the same directory structure.

You can execute commands from other locations by specifying the entire path or from any directory which is in your PATH environment variable.

You could either execute

C:\wamp\bin\mysql\mysql5.6.12\data\wordpress>c:\path\to\mysqlfrm --server=root:rootpassword@localhost --port=3307

or change your PATH


About schema design of NoSQL DB.(How to query multiple data?)

I can save all user_id under a key 'New York', but that's ugly isn't it.

No, it isn't ugly and it's very much what any RDBMS does when it maintains an index. The difference with Redis is that it doesn't do that for you so you'll have to do these keys' management in the code.

  But, what should I do if want to get all user younger than 20, or all user live in New York?

Consider using Redis' Sets and Sorted Sets for storing these attributes. Redis provides several set operations (union, intersect...) that make building these conditions easier.

  Is there have some amazing way to do that with Redis?

That's pretty much it. It's always about a trade off - choose a full-fledged RDBMS for the comfort and you'll pay with performance, with Redis it's the other way around :) That said, it's not that bad once you "repress" some of your currently-dominant relational database instincts.

Another viable approach that may fit your requirements is to use an object mapper to abstract some of the underlying Redis details. Depending on your choice of programming language, there are quite a few out that do a lot of the work for you (at the expense of a little performance of course ;)).


Multiple relationships between the same entities

Nothing I know of. Calling it "multiple binary relationships" is acceptable, I believe.


Select countries and counts of people per country

You have to aggregate using GROUP BY SELECT statement, which is part of the SQL standard (and quite popular to be honest).

Although You haven't stated which database engine You are using, the query below should work in most, if not all products, because it uses very basic syntax:

SELECT b.country, count(a.id) AS user_count
 FROM countries b
 LEFT JOIN users a ON b.id = a.country_id
 GROUP BY b.country;

The query could use an inner join instead of the left outer join. In that case it would exclude countries for which there are no matches in the users table.

If You are thinking about writing more queries, it might be better to try some online SQL course. Code Academy one is rather good. Other noteworthy example is from SQLCourse, but other ones found on the Internet might be OK too. Checking the SELECT statement's online documentation on Your database's webpage might be a good idea as well (as implementations do vary in some details).


What are the Oracle Dataguard Implications of Migrating to a PDB?

The notes about this in Oracle 12.1.0.2 state that it is not required to move/copy the files from the primary during the plugin. The note also states that in this release the copy of the standby files of the source can be postponed using the standbys=none clause. This makes it possible to plugin a large primary database in a primary container database and perform redo apply/recovery later, when all files are copied into to correct file structure.

seeHandling of Standby Databases during plugin note 1576755.1

My conclusion is that in 12.1.0.2 the standby files need to be copied but this is not perse during the plugin of the primary database so it does not cause any extra downtime.

Still I would like to see this tested before I would do this on production.


How to model a relationship with different roles of users

Option 3 is considered the best practice in this case because it lets the RDBMS do what it is designed to do. You have the best options for controlling relationships with declarative referential integrity and your queries will be simple and direct.  If you specifically want to make this option work like Option 1, insofar as the relationships are constrained to many to one (many orders to one customer each for example) then add a unique constraint on the order_id foreign key column in each intersection table. However, in practical terms, you might actually want many-to-many because you might assign different managers or couriers over time and that history may be important. It all depends on your business rules.

Option 2 is considered a "SQL Code Smell" (polymorphic association)

Option 1 might be reasonable as well, depending on your business rules, but some people don't like it because it might result in "sparsely populated columns" (again, depending on your business rules).


How to deal with a table that exists in the ALL_OBJECTS table but cannot be selected

Database objects with lowercase names need to have their name enclosed in double quotes when referenced

Also, you left out the schema name.

Try

select *
from SCHEMA_NAME."rca_related_materials";


Why create a Materialized View Log with Primary Key or RowID, etc?

A MView Log table is with the name MLOG$_BASE_TABLE_NAME. Check this log table which show the changes being tracked. For eg, Primary key, ROWID, columns, OLD/NEW, DML Type, etc.

I had a similar challenge when designing a MView. I could try out following 3 to create the MView

WITH ROWID
WITH PRIMARY KEY
WITH (columns...)

When using ROWID and PRIMARY KEY, base table's record identification is present on the LOG table. In this case a query on the MView will have to fetch data from its MView storage and MView Log and the base table.
In the last approach, the columns specified in the list is stored on LOG table itself. In this case does oracle refer to the base table again or will it use the values available from the MView Log?

Further, the view log you have created, must specify INCLUDING NEW VALUES like below
CREATE MATERIALIZED VIEW LOG ON LLATTRDATA WITH PRIMARY KEY INCLUDING NEW VALUES
This will ensure you have fast refresh working.

These are the oracle pages i have gone through
https://docs.oracle.com/database/121/SQLRF/statements_6002.htm#SQLRF01302
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6003.htm


Subquery in INSERT works in SQL but not PDO

I could fix your problem, with the insert
INSERT INTO tab (id, seUuid4, rxUuid4, text)
SELECT
    1,
   IFNULL ((SELECT  uuid4 FROM otherTab WHERE lgUuid4 = UNHEX('00001234aaaa4444aaaa432187654321') AND data = 'JPK' LIMIT 0,1) IS NULL,0),
    (SELECT uuid4 FROM otherTab WHERE lgUuid4 = UNHEX('12340000bbbb6666bbbb432187654321') AND data = 'TXT' LIMIT 0,1),
    'some text';

but this gives following error

Schema Error: Error: ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key constraint fails (test.tab, CONSTRAINT tab_ibfk_2 FOREIGN KEY (seUuid4) REFERENCES otherTab (uuid4))

Which means that you must enter there a uuid, which is exists


You'll need to first create a list of every product_number and date combination. You can do this using a CROSS JOIN of your table:

select distinct p.product_number, d.date
from yourtable p
cross join yourtable d;

See SQL Fiddle with Demo. This will create a list of data similar to:

| PRODUCT_NUMBER |                            DATE |
|----------------|---------------------------------|
|            100 |  January, 01 2010 00:00:00+0000 |
|            200 |  January, 01 2010 00:00:00+0000 |
|            100 | February, 01 2010 00:00:00+0000 |

You will then use the above query and LEFT JOIN to your table to return the final result:

select
  pd.product_number,
  pd.date,
  t.value
from
(
  -- list of every product/date
  select distinct p.product_number, d.date
  from yourtable p
  cross join yourtable d
) pd
left join yourtable t
  on pd.date = t.date
  and pd.product_number = t.product_number
order by pd.product_number, pd.date;

See SQL Fiddle with Demo.  Giving a final result of:

| PRODUCT_NUMBER |                            DATE |  VALUE |
|----------------|---------------------------------|--------|
|            100 |  January, 01 2010 00:00:00+0000 |      1 |
|            100 | February, 01 2010 00:00:00+0000 |      1 |
|            100 |    March, 01 2010 00:00:00+0000 |      1 |
|            200 |  January, 01 2010 00:00:00+0000 |      1 |
|            200 | February, 01 2010 00:00:00+0000 | (null) |
|            200 |    March, 01 2010 00:00:00+0000 |      1 |

The LEFT JOIN returns all rows from your list of products and dates regardless of whether a matching row exists in the other table.

This could also be written as:

select
  p.product_number,
  d.date,
  t.value
from
(
  -- list of every product
  select distinct product_number
  from yourtable
) p
cross join
(
  -- list of every date
  select distinct date
  from yourtable
) d
  -- then join to the table
left join yourtable t
  on  d.date = t.date
  and p.product_number = t.product_number
order by p.product_number, d.date ;

See SQL Fiddle with Demo. This may have better performance depending on your table size.

Now if you wanted to return a list of all dates, regardless of whether or not they appear in the table, then I would suggest creating a table of dates.  This table would be used in a similar manner to create a list of all dates/products which you would then join.

The table would be similar to:

CREATE TABLE dates
    (`date` datetime)
;

INSERT INTO dates
    (`date`)
VALUES
    ('2010-01-01 00:00:00'),
    ('2010-02-01 00:00:00'),
    ('2010-03-01 00:00:00'),
    ('2010-04-01 00:00:00'),
    ('2010-05-01 00:00:00')
;

You'd then use the following query to get the list of dates/products:

select distinct p.product_number, d.date
from yourtable p
cross join dates d

And finally, you would join that back to your table:

select
  pd.product_number,
  pd.date,
  t.value
from
(
  -- list of every product/date
  select distinct p.product_number, d.date
  from yourtable p
  cross join dates d
) pd
left join yourtable t
  on pd.date = t.date
  and pd.product_number = t.product_number
order by pd.product_number, pd.date;

See SQL Fiddle with Demo. Or an alternative:

select
  p.product_number,
  d.date,
  t.value
from
(
  -- list of every product
  select distinct product_number
  from yourtable
) p
cross join
 dates
  d
  -- then join to the table
left join yourtable t
  on  d.date = t.date
  and p.product_number = t.product_number
order by p.product_number, d.date ;

See SQL Fiddle with Demo. Again this may have better performance based on the table size.  Using this type of solution, you'd return all dates even those not in your table:

| PRODUCT_NUMBER |                            DATE |  VALUE |
|----------------|---------------------------------|--------|
|            100 |  January, 01 2010 00:00:00+0000 |      1 |
|            100 | February, 01 2010 00:00:00+0000 |      1 |
|            100 |    March, 01 2010 00:00:00+0000 |      1 |
|            100 |    April, 01 2010 00:00:00+0000 | (null) |
|            100 |      May, 01 2010 00:00:00+0000 | (null) |
|            200 |  January, 01 2010 00:00:00+0000 |      1 |
|            200 | February, 01 2010 00:00:00+0000 | (null) |
|            200 |    March, 01 2010 00:00:00+0000 |      1 |
|            200 |    April, 01 2010 00:00:00+0000 | (null) |
|            200 |      May, 01 2010 00:00:00+0000 | (null) |


This query should do it for you

select
    tm,(tm + interval 15 minute) tm2,user,users
from
(
    select tm,user,COUNT(1) users from
    (
        select user,(access_time - interval
        MOD(unix_timestamp(access_time),900) second) tm
        from user_access where access_time between
        '2013-05-28 02:00:00' and '2013-05-28 10:00:00'
    ) AA
    group by tm,user
) A;

I'll leave the formatting to you

Give it a Try !!!


Take your most complex (most GROUP BY columns) then use the OVER clause

SELECT
   school, board, col3, col4, ...,
   cnt_whoPassedA, ...,
   SUM(cnt_whoPassedA) OVER (PARTITION BY board) AS cnt_whoPassedA_ByBoard,
   SUM(cnt_whoPassedA) OVER (PARTITION BY school) AS cnt_whoPassedA_BySchool,
   SUM(cnt_whoPassedA) OVER (PARTITION BY school, board) AS cnt_whoPassedA_BySchoolBoard,
   ...
FROM
(
select
 school,
 board,
 col3, col4, ...
 cnt_whoPassedA = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
 cnt_whoPassedB = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
from MyBigTable as A
group by A.school, A.board, A.col3, A.col4...
) T

However, I would consider doing this in my report layer (say in SSRS): do you need all levels of grouping at the same time?


Just so there is some sort of answer to this, here is some information I have found.

On MOS there is a document called "High Numbers of 'Asynch Descriptor Resize' Wait Events Seen" (Doc ID 1273748.1)  This seems to indicate that the problem is really another wait event slowing the system down and in turn causing these, so they aren't really the source wait event.

Oracle-L has a thread on the wait event, but no mention of it on Windows.

OakTable has some interesting info on the problem, and says,

  ...the resize apparently sucks on Linux. Perhaps that’s why other ports also suffer and have seen the same wait event.


Is it Possible to have a database table table of morethan 3000 fields

3000 optional fields today; 3001 tomorrow.  You are looking at all-to-frequent ALTER TABLE .. ADD COLUMN..

Keep the "common" columns as MySQL columns.  Put the rest into a JSON string.  Be aware that it is messy and inefficient to filter or sort on data buried inside a json string.

The EAV schema pattern has, in my opinion, worse drawbacks.


FK to table with non-unique values via a on commit refreshing materialized view?

You can still use GROUP BY.

create table t1 (c1 varchar2(3), c2 number);
insert into t1 values ('EUR', 100);
insert into t1 values ('EUR', 200);
insert into t1 values ('USD', 150);
commit;

select * from t1;
C1          C2
--- ----------
EUR        100
EUR        200
USD        150

create materialized view log on t1
  with rowid (c1) including new values;

create materialized view v1
refresh fast on commit as
select c1 from t1 group by c1;

select *from v1;
C1
---
EUR
USD

insert into t1 values ('CHF', 180);
commit;

SQL> select * from v1;
C1
---
EUR
USD
CHF


Backup up database to compressed local folder - what will be the performance implication?

Don't backup to a compressed folder.  There are supportability problems for SQL Server with compressed folders.  See here.  Take a compressed backup instead.


How to flush SQL Server database from RAM to hard disk?

The mdf and ldf files display the last date and time the SQL Server opened the file in question.  This is most likely to be the time SQL Server was last restarted.

There is no reason to force SQL Server to flush to disk, it does that automatically during its checkpoint mechanism.

Even if the entire machine crashed during the middle of a write-to-disk operation, when SQL Server is restarted it will run through the log file, rolling forward and backward any operations that were not fully committed to the .mdf file.  This is one of the primary tenets of an atomic, consistent, isolated and durable database server.

If you want to manually force a checkpoint operation, you can execute the following command in SQL Server Management Studio, or SQLCMD, etc:

CHECKPOINT

For further information on the CHECKPOINT command, see http://technet.microsoft.com/en-us/library/ms188748.aspx

Regarding your statement at the beginning of your question that you backup the database and not the log file, if your data is business-critical you should enable full recovery on the database, and ensure your log file is backed up several times a day (at least).  Backing up the log file assures that you can restore the database to a given point-in-time (most likely the point at which you last performed a log backup).  Depending on your business requirements for recovery point and recovery time, you may want to backup the log file as often as every 5 minutes!

For further information on how to correctly implement business-critical backup for SQL Server see http://technet.microsoft.com/en-us/library/hh393536.aspx


Data Comes from External Source

The IN clause is limited to 1000 values.  Don't use it for such searches.

Workaround:

Use a GTT (Global Temporary Table)
insert "values of interest" into that GTT
Modify the SELECT statement to JOIN against the GTT.

Example

Create the GTT

create global temporary table students_gtt (
  student_id int primary key
);

Insert values into the GTT

insert into students_gtt
select level
from dual
connect by level <= 50000;

and JOIN it in the SELECT statement

select
    *
from
    students       s
    left join grades         g on s.id = g.student_id
    join students_gtt   g on s.student_id = g.student_id

Notes

First, I suspect a good portion of your time is spent Hard Parsing your query.

I'm taking a 22,974,957 row table and left outer joining it to a 93,501,293 row table in addition to joining the 50,000 row GTT.  The values return in seconds.

Eventually, you are going to a run into a "node" limit for the parser.
The GTT method does not require you to "hack your SQL statement".

Data from another SQL Statement?

Just include that SQL within the SELECT statement.


Postgres: Merge database backup with development database

What I do is keep two copies of production on my test machine.  At 50GB, that should be pretty doable.  When I want a fresh copy of production, all I have to do is:

pg_ctl stop -D $WHERE/dev -mi
rm $WHERE/dev/* -r
mv $WHERE/pre_stage/* $WHERE/dev
cp $WHERE/recovery.sample $WHERE/dev/recovery.conf
pg_ctl start -D $WHERE/dev
tar xf /mnt/something/latest/base.tgz -C $WHERE/pre_stage &

It takes anywhere from a few seconds to a few minutes, depending on how much WAL log has been generated since the last base backup was taken.  The last step of replenishing the pre_stage takes longer, but it doesn't matter since I don't have to sit around and wait for it to finish.

Of course this requires you to be using log archiving on the production machine.  I would be doing that anyway, though.


tl;dr You can't force MySQL to use a lot of cores.

40 million (4 crore) rows is a large, but not huge, database for MySQL. It is well within the capability of that software. You don't have to resort to desperate measures to get MySQL to work with that amount of data. You do have to index it correctly though.

MySQL can use a lot of CPU cores as of recent releases. But it doesn't use them to accelerate single queries; that sort of workload is not embarrassingly parallel.   You can't get it to use a large number of cores for a single query. It simply doesn't work that way.

When it uses many cores, it uses them to handle simultaneous workload from many different connections. But of all those connections hit the same tables, it won't be able to use a lot of cores even if they are available.

At any rate, you should upgrade to version 5.7. It uses multithreading for more things than 5.6.

You will be able to improve performance with appropriate indexing for your query workloads. We can't advise you about that because you haven't offered any specifics about existing tables, indexes, queries, or simultaneous access by multiple clients.

Your server, with 20 cores and 64G of RAM, is overprovisioned in cores and RAM for MySQL, and for the sort of workload you mention.  You didn't mention your server's I/O capabilities. I guess that replacing your mechanical disk drives with SSD drives using PCIe interfaces will make the most difference. But that's a guess based on little information.

My blunt advice: sell that massive server, or put it into service as a hypervisor host to use for lots of virtual machines for other things.  It's overkill for MySQL.

Get a server with 16Gb and four fast cores. Use the extra money to provision it with as many PCIe SSD drives as you need for your application. Don't consider purchasing SSD drives smaller than about 0.2 terabyte; they are slower than the larger ones.  Index your tables properly for your queries. For tables of the size you mention, don't worry about core utiization; instead worry about I/O.

You can probably try all this out for a few tens of dollars on a cloud virtual machine (Amazon, Rack Space, Digital Ocean) so you don't waste money on another overprovisioned server.


Avoiding Key Lookup when using MIN and MAX

I would rewrite the query like this:

SELECT
    sus.MaxOnlineUsers, -- Only select the most users
    sus.MaxOnlineDevices, -- Only select the most devices
    sus.SDHour, -- Used to allow creating the graph split into 5 minute increments
    sus.SDMinute, -- Used to allow creating the graph split into 5 minute increments
    s.Id AS SoftwareId,
    s.FriendlyName AS FriendlyName, -- Each software peice has a name
    s.MaxUsers AS MaximumSupportedUsers, -- Maximum supported users
    s.MaxDevices AS MaximumSupportedDevices -- Maximum supported devices
    -- DATEDIFF(SECOND, @dtStartDate, min(sus.SampleDate)) as OrderTime -- Used to sort
FROM dbo.Software AS s -- Get sw details
--Not used: LEFT JOIN dbo.SoftwareTypes AS st ON s.SoftwareTypesId = st.Id

--- Run this once for each software:
CROSS APPLY (
    SELECT x.SDHour, x.SDMinute, x.UserDivsor,
           MIN(x.SampleDate) AS OrderColummn,   --- Just order by this column
           MAX(x.OnlineUsers) AS MaxOnlineUsers,
           MAX(x.OnlineDevices) AS MaxOnlineDevices
    FROM dbo.SoftwareUserSamples AS x
    WHERE x.SoftwareId=s.Id
      AND x.SampleDate > @dtStartDate -- Don't get all rows
      AND x.SampleDate < @dtEndDate -- Don't get all rows
    GROUP BY x.SDHour, x.SDMinute, x.UserDivsor) AS sus

WHERE s.ClientId = @gClientID -- Multi tannancy
  AND (@bFilterHiddenRows = 0 OR s.HideByDefault = 0) -- hide some rows

ORDER BY
    sus.OrderColumn,
    s.Id;

The CROSS APPLY construct lets SQL Server "loop" over each row in dbo.Software and for each query in that table, it runs an aggregate query on the much larger dbo.SoftwareUserSamples table, for which we'll optimize with the following index:

INDEX ON dbo.SoftwareUserSamples (SoftwareId, SampleDate, SDMinute, SDHour, UserDivsor)
    INCLUDE (OnlineUsers, OnlineDevices)

This should give you a nice, efficient seek (range scan, really) on (SoftwareId, SampleDate).

I removed the DATEDIFF expression and just sorted by the MIN(SampleDate) expression instead. It'll make your code a little more readable and perhaps even save you a few microseconds of CPU time. :)

Just crossing Ts and dotting Is, here's an index recommendation on dbo.Software as well, although I suspect this table doesn't really contain that many rows:

INDEX ON dbo.Software (ClientId, SoftwareTypesId)
    INCLUDE (Id, FriendlyName, HideByDefault, MaxUsers, MaxDevices)

A few best practices that I could gleam from your post:

Every table (with very few exceptions) should have a clustered index. It doesn't absolutely have to be unique if that's a problem.
Always prefix the table with the schema name.
Qualify all the column names with table aliases, so you can easily see which column belongs to which table.
If you're on SQL Server 2014/2016 Enterprise Edition or on 2016 SP1+, you should really try putting a columnstore index on the SoftwareUserSamples table for performance.


I can't see some databases even though my user has the "root" role

This is because you have not restarted your MongoDB instance in auth mode.
Restart your MongoDB in auth mode in linux like this

mongod --auth --fork --logpath /var/log/mongod.log

Reference: Enable Auth

Remember if you have enabled auth mode for your MongoDB instance, you must have put it in your code and start the mongod process in auth mode.

To simplify this (authenticating the db) use Studio 3T (MongoDB Client) but you still need to start the instance with the --auth command line option.


There's some very good tutorials on the web about this that go in depth and have lots of screenshots.

I'm assuming you also have to cross untrusted domains, correct?  Usually if the domains are trusted it means some sort of network link with AD/LDAP has been established.  If that's the case, you're looking at:
Merge replication across untrusted domains using web synch

Pull Merge Replication Security with Sql Server 2005 with no Domain Set Up (Largely applies to 2008)
http://www.jstawski.com/archive/2007/03/06/pull-merge-replication-security-with-sql-server-2005.aspx

How to: Configure a Subscription to Use Web Synchronization (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library/ms345214.aspx

You will also run into questions such as 'do I use a sql user, do I use security certs?  SSL or SQL certs, etc.?'  There's going to be a lot of decisions you'll have to make so be prepared to re-do it several times.


regexp_replace(TRIM(mbr.mbr_first_name) || ' ' || TRIM(mbr.mbr_last_name),'[^a-zA-Z0-9 ]')


Choosing index fillfactor for large, highly inserted table

The fill factor for a B-btree index is only used during initial creation of the index, and when you insert a new highest key.  So any effect of setting this won't last for long on a highly active index which is randomly inserted, as it will quickly settle in to a steady state which is independent of the starting fill factor.  If you change the fill factor and then rebuild the index, you might get a boost in performance.  But unless you can rebuild the index frequently, it wouldn't be a sustainable boost.

The fill factor for indexes, unlike for tables, is not a very generally useful knob.  If it didn't already exist and someone proposed adding it now, they might have a hard time getting it accepted.


The parameters you're looking at are only used if indexes are reorganized.

Depending on the level of fragmentation involved, which you provide when you run the task, the IndexOptimize stored procedure will either rebuild indexes (which do not require statistics updates), or reorganize (which do require statistics updates).

There is a third scenario, where you can only update statistics, using the following code (from Ola's site):

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y';

Note that the Fragmentation options are explicitly set to NULL in this scenario.


How to deal with finding nearby locations when given a zip code or city?

What you want is ST_DWithin. That will use an index.

SELECT ascii_name
FROM geoname
WHERE
ST_DWithin(
  ST_SetSRID(ST_MakePoint(-122.67621 45.52345), 4326),
  the_geom,
  16093
);

You probably want to use geography rather than geometry.


Design problem with auto-increment fields

So you just need to fetch the last auto-increment value that was inserted? There are a couple of ways to do this, but they're all pretty simple.

Query: SELECT LAST_INSERT_ID()

php mysql: $id = mysql_insert_id($mysql_conn);
http://php.net/manual/en/function.mysql-insert-id.php

php mysqli: $id = $mysqli->insert_id;
http://php.net/manual/en/mysqli.insert-id.php

Both of those links have some example code that looks pretty easy to follow.


Dependency Preservation and Normal Forms

Given R(A B C D E F G H I J ) and the FD's specified above
We would get three relations after normalizing it till 2NF:
Since the FD: A->DE is not satisfying 2NF
Finding A closure we get (ADEIJ).
Since the FD B-> F also does not satisfy 2NF
Finding B Closure we get (BFGH).
Hence we get the relations:
R1(ADEIJ) and R2(BFGH).
A is the Candidate Key for R1; B is the Candidate Key for R2
What remains from R is C.
So we get the third relation R3 (ABC).

This is a dependency preserving and loss less decomposition.


EF Code First uses nvarchar(max) for all strings. Will this hurt query performance?

Larger nvarchar (max) data items (over 8000 bytes or so) will spill over into text storage and require additional I/O.  Smaller items will be stored in-row.  There are options that control this behaviour - see this MSDN article for more details.

If stored in-row there is no significant I/O performance overhead; there may be additional CPU overhead on processing the data type but this is likely to be minor.

However, leaving nvarchar (max) columns lying around the database where they are not needed is rather poor form.  It does have some performance overhead and often data sizes are quite helpful in understanding a data table - for example, a varchar column 50 or 100 chars wide is likely to be a description or a free-text field where one that's (say) 10-20 chars ling is likely to be a code.  You would be surprised how much meaning that one often has to infer from a database through assumptions like this.

Working in data warehousing, as often as not on poorly supported or documented legacy systems, having a database schema that's easy to understand is quite valuable.  If you think of the database as the application's legacy, try to be nice to the people who are going to inherit it from you.


I ran the 'repair oracle installation' option from the setup.exe file which I used to install oracle in the first place.

So far, I've not had a recurrence and this seems to have fixed the problem.


Improve Insert performance for 200 Million rows

So many things...

If you have the Actual Execution Plan, please post it. I'm curious to see what indexes you have, and what the shape of the data is.

In the meantime, be aware that:

ON eord.lifnr = 'IVG'+ zsm2.werks

...isn't great for finding rows in zsm2. You may want to add:

AND eord.lifnr LIKE 'IVG%'
AND zsm2.werks = STUFF(eord.lifnr,1,3,'')

And you may as well put:

AND mbew.bwkey = zsm2.werks

Your types look like they need some attention, and of course your indexing strategy is bound to need some attention.

EDITED FOLLOWING PLAN UPLOAD

So, now that I've seen the plan. Yeah, it's ugly.

You should consider what is going to be a useful method for joining your main tables, and how you'd do this if you were solving it on paper.

Try indexes on both Order_Detail and Order_Header, on (SourceID, OrderID), including all the other columns that are needed. This will allow a Merge Join between those tables. Then making sure that you can quickly access the rows in each subsequent table, and you have a relatively good candidate for a nicer plan.

eg: an index on eord (flitnr, matnr, lifnr) --with the LIKE 'IVG%' thing I mentioned above.

There's going to be a bunch of pain around those join clauses with CASE. If you need it to be joining to exactly one, you may prefer to use OUTER APPLY (SELECT TOP (1) ... WHERE StartDate < CASE... oh.OrderDate...END ORDER BY StartDate DESC), with an index on (SourceID, CurrencyCode, ToCurrencyCode, StartDate DESC)... you don't really need EndDate.

I've seen worse, and at the end of the day, this should be quite tunable.


I've made some tests and manage to find some interesting results.

You can use the casting operation to solve the problem:

SET totalTaxRate = CAST(totalTax * 100 / amount AS DECIMAL(10,3));
You can use the truncate operation to solve the problem:

SET totalTaxRate = TRUNCATE(totalTax * 100 / amount, 3);
Somehow, using a select after changing the value of the variable seems to also solve the problem...

SET totalTaxRate = totalTax * 100 / amount;
SELECT totalTaxRate;

I'd rather not use this solution as it returns a recordset.
Using a select into also solve the problem

SELECT totalTax * 100 / amount INTO totalTaxRate;

Note: You can use the truncate or casting operation either inside or outside the procedure (even both), but I'd suggest to do it inside so you don't have use the truncate/casting operation on the output every time you call the procedure.

I still can't assert for sure the root of the problem but, at least, there are some ways to work around it.


MDX Query for RANK regardless of hierarchy

Please explain in more detail what you meany by "groupers"?

For instance, if you want to rank a member within a set, you need to tell SSAS what the set is that you are ranking within.

RANK( Tuple, Set, Measure to rank by)

So

RANK( Machines.Machines.CurrentMember, Machines.Machines.AllMembers, Measures.Efficiency)

Now, I think that what you are trying to do is have that all members be dynamic?

The question is "How would you both specify the current member and the set?"

And here's a kicker: the frontend tool you're using will make a difference here as well


How to list all functions with arguments that a role is allowed to execute in Postgres?

You can use the has_function_privilege function for that:

SELECT p.pronamespace::regnamespace as schema,
       p.proname||'('||pg_get_function_arguments(p.oid)||')' as function
FROM pg_proc p
WHERE has_function_privilege(p.oid, 'execute');
  and p.pronamespace <> 'pg_catalog'::regnamespace;


MYSQL any way to improve this query's performance?

On files, add these compound indexes:

INDEX(filesystem_fk, file_status, file_tsuid, created_time),
INDEX(filesystem_fk, file_status, created_time)

(I am not sure which will help more.)


Copying mysql data from one db to another with a where clause for a subset of tables

By "move" do you mean "copy"?  That is, are you leaving the rows behind?

Two different machines

mysqldump has options to specify particular a database and list of tables.  It also has an optional WHERE clause.  So...

mysqldump -h src -w "account_id=1234" -d db tbl1 tbl2 > some_tables.sql
mysqldump -h src -d db tbl3 tbl4 > other_tables.sql

(Please verify the syntax.)

Then load those two files into some other place (after doing CREATE DATABASE newdb):

mysql -h dest newdb <some_tables.sql
mysql -h dest newdb <other_tables.sql

Same machine

CREATE DATABASE newdb;
CREATE TABLE newdb.tbl1 LIKE db.tbl1;
INSERT INTO  newdb.tbl1 SELECT * FROM db.tbl1 WHERE account_id=1234;
 ditto for tbl2, etc
CREATE TABLE newdb.tbl3 LIKE db.tbl3;
INSERT INTO  newdb.tbl3 SELECT * FROM db.tbl3;
 ditto for tbl4, etc


MySQL - Number format - Separated with Comma


MySQL Range Optimization and Multipart index

There are many flaws in the output from EXPLAIN.  This is a new one to me.
Things like "covering" indexes, ranges, ORDER BY, etc are poorly indicated by key_length.  Also, the length of NULLable columns is a constant, not 1 or 2, depending on the schema.
"Using temporary, Using filesort" is usually on the first line, even if that is not when the sort occurs.
GROUP BY x ORDER BY y may require two sorts; there is no clue of such.
Use EXPLAIN FORMAT=JSON SELECT ... to get more information, sometimes close to all the info available.
Back to the specific example.
Well, a better index is this order: (value, customer_id).  Columns involved in col=constant should come first for both columns of the index to be efficiently used.
A guess is that there is a relatively new Optimization.  What version(s) of MySQL/MariaDB show this inconsistent key_len?
As Jørgen says in that blog:

The difference between <,> and BETWEEN sounds strange as these conditions should be treated the same way in the optimizer code. One theory is that the optimizer considers two ways of executing this query to be almost equally good and therefore sometimes picks a good plan and other times a suboptimal plan due to small differences in index statistics.
If you are able to reproduce this issue consistently, however, you might want to file a bug at bugs.mysql.com.

That is, please provide a full test case -- CREATE, INSERT, EXPLAIN, etc., that consistently demonstrates the issue in at least one version of MySQL (the newer, the better).
Found the answer in his latest blog:

But that leaves a question: Why is key_length=10 for the BETWEEN version of the query? The reason is that although the value keypart cannot be used to set up ranges, it is used to determine the starting point of the first range (there is only have one range in this query - try optimizer trace and see for yourself), and there is no use in reading index rows with value < 500 and customer_id=7.

Thanks for the Question; I learned something.
As for your Question 1:

By contrast, the condition key_part3 = 'abc' does not define a single interval and cannot be used by the range access method.

That, together with WHERE key_part3 = 'abc' (without any other clauses) together with INDEX(kp1, kp2, key_part3)...  It is saying that there may be several clumps (not a "single range") of places in the ordered list based on (kp1, kp2, key_part3) where abc is found.
It is better to go to one spot in the index, then scan until there are no more relevant rows.  But in that case, you need to look in several spots in the index.  The discussion is leading up to the 'new' ability to do so (somewhat).
Bottom line...  As discussed in my Index Cookbook, the best indexes start with column(s) that are tested against constant(s).  Sometimes the Optimizer can make some use of indexes where the columns are not optimally ordered.


How to subtract an object from another in PHP/MySQL?

$query ="select sum(copies) as totalbooks from booksdetail";
$result=mysqli_query($con, $query);
while($row=mysqli_fetch_array($result)){
$totalbooks = $row['totalbooks'];

$query ="select count(id) as issuedbooks from issue";
$result=mysqli_query($con, $query);
while($row=mysqli_fetch_array($result)){
$issuedbooks = $row['issuedbooks'];

$remainingbooks = $totalbooks - $issuedbooks;

PHP is off-topic for this site though, really.


How many threads being used while query execution?

I am confused and can't determine how many threads were running during query execution.

To understand that, you really need to look at the execution plan.  Here's what I get for this query on SQL Server 2017 at DOP 4 (in the SQL Server 2014 compatibility mode):

Each parallel branch gets DOP threads.  So at first glance, it looks like 12 workers could be active for this query at once, maximum.
However, a Hash Match join has to consume the entire build (upper) input branch before it activates the probe (lower) input branch.  So here's how execution goes, generally:

4 worker threads start running the red branch (remember, execution starts at the upper left of a query plan)
The Hash Match asks for rows from the black branch
4 worker threads start running the black branch (a maximum of 8 worker threads are active now)
Once the black branch is done, the probe side of the execution plan is activated
The 4 workers threads used by the black branch are reused by the blue branch (for a maximum of 8 active worker threads still)

Thus this query will use at most 8 parallel worker threads at a time.  SQL Server is smart enough to realize that only two branches can be active at once (because of the blocking nature of the Hash Match), and only reserves 8 threads (instead of 12):

Notice that it says "2" for branches - that's the number of branches that can execute concurrently.
To address your screenshot of active workers, some threads might finish all of their work before others (because they have less rows to process, for example).  So at a given point in the query's execution, you might only have 6 threads running (out of the 8 possible threads).
Related reading:
Parallel Execution Plans – Branches and Threads
In SQL Server, is parallelism per operator, or something else?


Database Design Regarding a Relationship for Patient Visit

In the long run, thinking in terms of tiers and of ID numbers is more likely to hurt you rather than help you.  At the start, use ID numbers only for those things that don't carry their identity with themselves. Studies should carry their identity with themselves; that is, their names should almost certainly be unique. Patients (people) don't carry their identity with themselves; there are many people named "John Smith".

My guess at primary keys is in parens. Foreign keys should be obvious.

Studies (study_name)
Patients (patient_id)
StudyPatients (study_name, patient_id)
Visits (study_name, patient_id, visit_timestamp)
PhysicalExam (study_name, patient_id, visit_timestamp)
ClinicalExam (study_name, patient_id, visit_timestamp)

It's not clear whether you need any additional columns to identify rows in PhysicalExam and in ClinicalExam. It doesn't look like it on first glance, though. Later, if there's sufficient interest and if I have enough time, I'll write this in SQL.


How can I properly use a trigger inside of stored procedure to validate an email?

Triggers are created on actual tables, and are automatically executed when an action is performed on the table in question, they're not actually created within procedures.

So, given the table:

create table Faculty
(
LastName varchar(100),
FirstName varchar(100),
Email varchar(100),
HireDate date,
Salary integer,
DepartmentID integer
);

... your trigger code, once removed from the procedure, does work:

mysql> DELIMITER $$
mysql>
mysql> CREATE TRIGGER Review_Email_Before
    ->         BEFORE INSERT ON Faculty
    ->         FOR EACH ROW
    ->       BEGIN
    ->         IF new.Email != '^\w+(\.\w+)*+@\w+(\.\w+)+$'
    ->         THEN
    ->           SIGNAL SQLSTATE  'HY000'
    ->              SET MESSAGE_TEXT= 'This email doesn\'t match FirstLast@college.edu';
    ->         END IF ;
    -> END;
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> INSERT INTO Faculty(LastName, FirstName, Email, HireDate, Salary, DepartmentID)
    ->               VALUES('Stanley', 'Mike', 'bad@bad.bad', current_date(), 20000, 1);
ERROR 1644 (HY000): This email doesn't match FirstLast@college.edu
mysql>


In Oracle, what grants do I need as a non-schema user to view rows in all_objects?

Oracle data dictionary views that have the ALL_ prefix provide information about objects that your user has access to.  Views that have the DBA_ prefix provide information about all objects in the database.  And views that have the USER_ prefix provide information about objects that you own.

In this case, MYDBUSER does not and can not have privileges on the indexes owned by MYDBOWNER because there are no privileges one can grant on individual indexes so it makes sense that MYDBUSER would not see the indexes in ALL_OBJECTS.  If MYDBUSER were granted access to DBA_OBJECTS or DBA_INDEXES (either individually or via the SELECT ANY DICTIONARY privilege), you could query DBA_OBJECTS (or DBA_INDEXES) to get a list of all the indexes owned by MYDBOWNER.


Postgresql group by and count() problem, missing row with 0 result

Basically, you want a subquery like this:

SELECT t1.id1, t1.let, COALESCE(tab.cnt, 0) AS my_cnt
FROM t1
LEFT JOIN
(
  SELECT
    id2,
    COUNT(id2) AS cnt
  FROM t2
  GROUP BY id2
  -- ORDER BY id2
) tab
ON t1.id1 = tab.id2;

See the fiddle here.

Basically, what I did was:

Create tables t1 and t2:

CREATE TABLE t1
(
  id1 int,
  let text
);

and

CREATE TABLE t2
(
  id2 int
);

Populate them:

INSERT INTO t1
VALUES
(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D');

and

INSERT INTO t2
VALUES
(1), (1), (2), (1), (4);

Ran my query:

SELECT t1.id1, t1.let, COALESCE(tab.cnt, 0) AS my_cnt
FROM t1
LEFT JOIN
(
  SELECT
    id2,
    COUNT(id2) AS cnt
  FROM t2
  GROUP BY id2
  -- ORDER BY id2
) tab
ON t1.id1 = tab.id2;

Result:

id1 let my_cnt
1   A   3
2   B   1
3   C   0
4   D   1

The inner query gets the counts and then the LEFT JOIN causes the id1 of 3 to be present - otherwise it would be missing from the result set and the COALESCE function (see here also) causes the value 0 to be present instead of NULL which would otherwise be there. I initially misread the question - p.s. welcome to the forum!


Turns out I had to run as VS as an administrator.  I guess because of the remote nature of the server.


Are there any fully declarative implementations of SQL

Any database with a cost-based optimizer (which includes basically any enterprise-class database) would generally treat those two queries as identical.  But no cost-based optimizer is or can be perfect-- they will all occasionally pick a poor plan.

If you're using a cost-based optimizer and the optimizer has reasonably accurate information about the tables and indexes (this information is generally gathered asynchronously and generally gathered in the evening/ morning when other activity is relatively light), it should be relatively clear (for some values of smalltable and bigtable and assuming proper indexes) that the most efficient plan is to do a full table scan of smalltable and then do index lookups on bigtable for those rows using the join condition.  If the database thinks that smalltable has 10% of the rows in bigtable rather than 0.1% of the rows in bigtable, on the other hand, there are a lot of different plans that have to be considered.  As the number of tables, the number of predicates, and the number of access paths the database supports grows, the potential number of query plans grows exponentially.  But the time the optimizer has to find the best plan does not grow-- you're generally going to be upset if the database takes a couple minutes to figure out the best plan before it even starts to execute the query.  So cost-based optimizers will never be perfect-- they don't have the time to fully explore every path and they rely on statistics that may be stale in order to figure out which plans are optimal.  Most of the time, however, they're quite good at figuring out the best way to join two tables when a query has no predicates and a simple join condition.


Best way to work around NOT IN NULL issue?

Don't know about Sybase but in SQL Server you can do like this.

select *
from yourtable
where value not in (select N
                    from (values (@var1),
                                 (@var2),
                                 (@var3),
                                 (@var4),
                                 (@var5)) T(N)
                    where N is not null)

If you can't use values to create a derived table you can use union all instead.

where value not in (select N
                    from (select @var1 union all
                          select @var2 union all
                          select @var3 union all
                          select @var4 union all
                          select @var5) T(N)
                    where N is not null)


SQL Server core licenses are expensive: using a SQL Server machine's CPU to send thousands of emails has no justification, especially when there are countless better options in terms of features, manageability, setup, troubleshooting.

Another point is that tight coupling between the email and database features makes the application difficult to scale, isolate and manage. One server - one service.

Database mail, in my opinion, should be used for administrative purposes only (alerting) and not for sendind business email messages.


User whose name is joe can connect from any host.

Please note what MySQL 5.0 Certification Study Guide

say in its bulletpoints on Page 498 Paragraph 6:

  On Unix, MySQL comes with a mysql_secure_installation script that can
  perform several helpful security-related operations on your
  installation. The script has the following capabilities:

  Set a password for the root accounts
  Remove any remotely accessible root accounts.
  Remove the anonymous user accounts. This improves security because
  it prevents the possibility of anyone connecting to the MySQL server
  as root from a remote host. The results is that anyone who wants to
  connect as root must first be able to log in on the server host, which
  provides an additional barrier against attack.
  Remove the test database (If you remove the anonymous accounts, you
  might also want to remove the test database to which they have
  access).

That user needs to be removed immediately.

Simply run

DELETE FROM mysql.user WHERE host='';
FLUSH PRIVILEGES;

GIVE IT A TRY !!!

UPDATE 2017-02-01 17:07 EST

According to the MySQL Documentation on Access Control, Stage 2: Request Verification

  A '%' or blank Host value means “any host.”

This is applicable at all levels of grants.


SQL Server: Using 4 part identifiers when database may be on the same server

Use a synonym, and just don't sync the synonyms. So when it is on a local server:

CREATE SYNONYM dbo.FooBar FOR DBName.dbo.TableName;

When it is remote:

CREATE SYNONYM dbo.FooBar FOR ServerName.DBName.dbo.TableName;

Now all your code just has to reference dbo.FooBar no matter where it is.


select
  speaker,
  listagg(product, ';')
    within group (order by product)
    as products
from
  (
    select distinct speaker, product
    from existing_data
  ) t
group by speaker
order by speaker ;

Test at: dbfiddle.uk


In PHP MySQL how to sum or not sum multiple columns?

Could add them up in SQL by reverting to 0 for NULL columns like:

SELECT students_info.s_name, subjects.subject, marks.*,
       COALESCE(marks.n1, 0) + COALESCE(marks.n2, 0) +COALESCE(marks.n3, 0) as  sum_marks
FROM ....


Reuse results from correlated subqueries in same query

Use LATERAL joins instead of correlated subqueries. This way you can reuse results to join to more tables:

SELECT dc.id AS catalog_id
     , dc.catalog_name
     , db.brands
     , ds.system_type_ids
     , bs.section_ids
FROM   distr_catalogs dc
CROSS  JOIN LATERAL (
   SELECT ARRAY(SELECT brand_id
                FROM   distr_catalog_brands
                WHERE  distr_catalog_id = dc.id) AS brands
   ) db
CROSS  JOIN LATERAL (
   SELECT ARRAY (
      SELECT id
      FROM   distr_catalog_system_types
      WHERE  display_status = true
      AND    distr_catalog_id = dc.id
      ) AS system_type_ids
   ) ds
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT id
      FROM   brand_catalog_sections
      WHERE  brand_id       = ANY(db.brands)
      AND    system_type_id = ANY(ds.system_type_ids)
      ) AS section_ids
   ) bs;

It's safe to use CROSS JOIN LATERAL (or just , LATERAL) here, because the array constructor always returns a row. Else, if a lateral subquery could come up empty (no row), you would use LEFT JOIN LATERAL (...) ON true to avoid losing rows.

Related:

What is the difference between LATERAL and a subquery in PostgreSQL?
Postgres datetime sort slow and not using index
Build JSON object from one-to-many relationship data in a single query?


How to save working hours of multiple stores and then query it without duplication?

If you only want the next day, and sundays are closed you can use this query,, when you want the next two days You have to UNION another Query and exclude all previous shops like i did in the WHERE clause

You can replace the @searchday session  variable with DAYOFWEEK(NOW()) to get the actual
day

CREATE TABLE `shop_openings` (
  `shop_id` int,
  `day` int,
  `open` varchar(5),
  `close` varchar(5)
  );

  INSERT INTO `shop_openings` VALUES
  (10, 6, '9:00', '16:00'),
  (10, 5, '9:00', '16:00'),
  (10, 4, '9:00', '16:00'),
  (10, 3, '9:00', '16:00'),
  (10, 2, '9:00', '16:00'),
  (10, 1, '9:00', '16:00'),
  (11, 6, '10:00', '18:00'),
  (11, 4, '10:00', '18:00'),
  (11, 2, '9:00', '18:00'),
  (12, 5, '9:00', '17:00'),
  (12, 3, '9:00', '17:00'),
  (12, 1, '9:00', '17:00'),
  (13, 4, '9:00', '16:00'),
  (13, 3, '9:00', '16:00');

✓

✓

SET @searchday = 6;
SELECT so.day, so.shop_id, so.open, so.close
FROM shop_openings so
WHERE `day` = @searchday
UNIOn
SELECT so.day, so.shop_id, so.open, so.close
FROM shop_openings so
WHERE `day` = IF(@searchday = 7 Or @searchday = 6,1,@searchday + 1)
AND shop_id NOT IN(SELECT so.shop_id
FROM shop_openings so
WHERE `day` = @searchday)

✓

day | shop_id | open  | close
--: | ------: | :---- | :----
  6 |      10 | 9:00  | 16:00
  6 |      11 | 10:00 | 18:00
  1 |      12 | 9:00  | 17:00

db<>fiddle here


how to update new password in maintenance plan

Navigate to SSMS --> Management-->Maintenance plans

Click on you Maint Tasks -->Right Click and select Modify

On this page you find Manage Connections

Check the Manage connections there you find options to change the credentials.


I don't know that the query could be refactored much and still be logically correct. And since I don't have your dataset it is difficult to test possible solutions for performance.

This query should still be logically the same, but may be executed in a more efficient way.

Select sid.internal_order_id,
    Min(sidr.due) As minDue
  From (
    Select delivery_id,
        Max(timestamp) As MaxTimestamp
      From sales_internal_deliveryrev
      Group By delivery_id) As lr
  Join sales_internal_deliveryrev As sidr
    On lr.delivery_id = sidr.delivery_id
    And lr.MaxTimestamp = sidr.timestamp
  Join sales_internal_delivery As sid
    On sidr.delivery_id = sid.id
  Where sidr.deleted = 0
    And sidr.due Is Not Null
  Group By sid.internal_order_id
  Having minDue > Now()
  Order By minDue Asc;

Edit

As per discussion about the proper way to handle revisions with deleted = 0 this is an alternate version which does not consider a revision with deleted = 1 when looking for the latest revision.

The WHERE deleted = 0 has been moved into the inner subquery.

Select sid.internal_order_id,
    Min(sidr.due) As minDue
  From (
    Select delivery_id,
        Max(timestamp) As MaxTimestamp
      From sales_internal_deliveryrev
      Where deleted = 0
      Group By delivery_id) As lr
  Join sales_internal_deliveryrev As sidr
    On lr.delivery_id = sidr.delivery_id
    And lr.MaxTimestamp = sidr.timestamp
  Join sales_internal_delivery As sid
    On sidr.delivery_id = sid.id
  Where sidr.due Is Not Null
  Group By sid.internal_order_id
  Having minDue > '2016-12-07'
  Order By minDue Asc;


Named instance configured on dynamic default port

When would it ever be useful to operate named instances on default ports?

I cannot think of one and Microsoft does not recommend doing so.  When you are connecting to a named instance through a firewall, it is recommended that Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall. That should be something other than 1433.  With dynamic port does not guarantee that SQL Server will always listen to the port you specify.  If some other process already using that port dynamic port will be set to zero and another port will be assigned.

  We do not recommend that you have a named instance listen on port
  1433.

What happens when a named instance listening on port 1433?

  If a Named Instance of SQL Server is listening on port 1433, the
  Microsoft OLE DB Provider for SQL Server (Provider=SQLOLEDB) and the
  SQL Server ODBC Driver (Driver={SQL Server}) may be unable to connect
  to the server. This failure to connect occurs when both the instance
  name and the port number from the connection string are omitted. If no
  port number is specified, the drivers validate the server instance
  name. If the server instance name is not the default instance,
  MSSQLSERVER, the connection is unsuccessful. To enable a successful
  connection, you must specify the instance name or the port number in
  the connection string or in a SQL Alias.

If somebody want to use a specific port number for SQL Server named instance they should consult this and pick a number not listed here. 1433 is listed for SQL Server default instance.

why would it ever make sense for them to be dynamic?

I am just making a wild guess. If they install a default instance 1433 will be used by that so dynamic port setting will pick some other port.

Ref:

https://support.microsoft.com/en-us/help/823938/how-to-configure-sql-server-to-listen-on-a-specific-port
https://www.iana.org/assignments/service-names-port-numbers/service-names-port-numbers.xhtml


Given that at 2/16/2014 12:23:46 AM the error message states that the instance has been using a process ID since 9/19/2014, I would suspect the system clock was rolled back after the instance startup. If this would be true we would also expect SSPI handshakes to fail due to clock difference between parties, and sure enough there are SSPI failures logged while the clock was adrift.


What is Oracle CPU Time and CPU Usage and why is it important?

This is a complex question but I will try to summarize and generalize.

The green is CPU used and CPU wait.  If CPU used is 100% (in terms of how many cores you have; this shows up in the OEM Top Activity graph which is similar to the ASH report) then your CPU starts waiting and this causes performance problems.  You would want to use your CPU as much as possible, but not at 100% because this causes the server to "flatline" and creates a queue of users waiting for CPU response.  There are other classes of wait events that would cause CPU to wait, such as concurrency locks, as this is bad for performance.  So if something shows more green, then yes it uses more CPU and this is not necessarily bad unless there are other sessions waiting for the resources it is using.

The blue is I/O usage and wait, and for the same reasons you want your sessions to consume I/O resources up to a point, and not have other sessions wait for I/O reads and writes.

Your orange is commit wait. You don't want your SQL to over-commit because this writes an excessive amount into the redo logs and in turn archive logs.

Now the memory usage is where it gets complex.  There are different layers of memory and cache.  Generally you want to read data from memory buffers rather than disk, and there are usually layers of cache on your operating system and I/O subsystems that help Oracle access commonly used data, and get cache hits.  The cache buffers in oracle are in the SGA (or server memory) and you want to get cache hits as far as possible here.  Reads from these buffers are regarded as 'consistent' and will show up as green.  Reads from disk are 'physical' and show up as blue.

Then you have PGA and UGA which are user areas of memory on the server and the client.  If PGA usage becomes too large and exceeds the amount of memory on the server, your operating system goes into 'swap' and reads from disk, and this can cause severe performance backlogs.  Your reads from SGA cannot go into swap because SGA is pre-allocated memory on startup of the database.

So in general your total resource pool consists of CPU, I/O and memory and there is a complex relationship between them. You want to use each resource up to a point, but keep enough free for contingencies.  If optimizations are done and resource usage is consistently near 100% then it's time for more optimization or splitting/upgrading servers.  Oracle RAC (clustering) is sometimes used to address these complex problems.


Which database transaction log will increase?

Creating a minimal, complete, and verifiable example will help you better formulate questions in future, and allow other folks to help you more easily.

I've created one:

CREATE DATABASE db1;
CREATE DATABASE db2;
GO
USE db1;
CREATE TABLE dbo.table1 (id1 int, id2 int);
USE db2
CREATE TABLE dbo.table1 (id1 int, id2 int);
CREATE TABLE dbo.table2 (id1 int, id2 int);

INSERT INTO db1.dbo.table1(id1, id2) VALUES (1, 2);
INSERT INTO db1.dbo.table1(id1, id2) VALUES (3, 2);
INSERT INTO db2.dbo.table1(id1, id2) VALUES (1, 2);
INSERT INTO db2.dbo.table2(id1, id2) VALUES (1, 2);

DELETE FROM db1.dbo.table1
FROM dbo.table1 t1
    INNER JOIN dbo.table2 ON t1.id1 = table2.id1

Looking at the tables is instructive:

SELECT *
FROM db1.dbo.table1;

╔═════╦═════╗
║ id1 ║ id2 ║
╚═════╩═════╝

SELECT *
FROM dbo.table1;

╔═════╦═════╗
║ id1 ║ id2 ║
╠═════╬═════╣
║   1 ║   2 ║
╚═════╩═════╝

SELECT *
FROM dbo.table2;

╔═════╦═════╗
║ id1 ║ id2 ║
╠═════╬═════╣
║   1 ║   2 ║
╚═════╩═════╝

Cleanup:

USE master;
DROP DATABASE db2;
DROP DATABASE DB1;

The takeaway appears to be that all rows in db1.dbo.table1 are deleted, which makes sense since the FROM dbo.table1 JOIN dbo.table2 ... clause does not reference database1.dbo.table1 at all.


How to get the AD account associated to a Proxy?

I think this is what you need.

select
    s.job_id
    ,s.name as [job_name]
    ,js.step_id
    ,js.step_name
    ,js.subsystem
    ,js.proxy_id
    ,js.command
    , c.credential_identity
from sysjobs S
INNER JOIN sysjobsteps JS ON S.job_id = js.job_id
INNER JOIN sysproxies p ON js.proxy_id = p.proxy_id
INNER JOIN sys.credentials as c on c.credential_id = p.credential_id

The proxy_id in sysjobsteps comes from sysproxies. You then get the account from credential_id in sys.credentials which is also available in sysproxies.


mysql multi-source replication to single slave from Debian repo's?

You shouldn't have to upgrade the master servers until you want to.

Officially, replication requires that the slave server be the same version as the master or newer.  That might seem counterintuitive, but derives from the fact that the replication protocol is always forward-extensible, but not always backward-compatible.  New capabilities can be added to the replication format, and when this happens, it only adds to what the master might write, but it adds to what the slave must be able to interpret.

As a rule, newer slaves know all the tricks an older master can do.  In contrast, newer masters can blow the mind of an older slave, by doing things the older slave won't recognize.

Because of the way replication is designed, the slave can actually learn the version of a master, but the master has essentially no way to learn the same information about a slave... nor is this needed, because the master only has one binlog, and all connected slaves receive exactly the same event stream.

Officially, replication is supported with a gap of one major release... the slave running 5.5 with a master running 5.1 (GA skipped from 5.1 to 5.5, so that's "one.") or the slave running 5.6 and master running 5.5, etc.

Unofficially, from 5.1 onward, you can generally make it work with any gap in the releases, as long as the slave is always newer, and you don't enable slave features the master doesn't implement, where the feature requires both servers to have the same configuration, such as binlog checksums or GTIDs. (...and you understand that Oracle won't be interested in any bug reports about a larger gap not working...)

Connecting a 5.7 slave to any number of pre-5.7 should be workable... but an important point here, relevant to your situation is that in the master/slave relationship, the slave is really in control of the entire interaction.  It initiates the connection to the master, sets any options on the connection, and asks the master to start sending the replication stream.  The master then dutifully sends replication events until the slave disappears, at which time the master closes the abandoned connection and forgets all about the slave.

Try it. The worst thing that should be possible is that you may have to change some defaults on the slave to get it to negotiate successfully with the older masters, but the older masters should not need any changes or be negatively impacted in any way by a connection from newer slave.

It's a matter of opinion, but I never, ever let the package manager mess with my MySQL servers. The package manager can be a bit of a black box, and if anybody is going to screw up my servers, it's going to be me.  If an upgrade ever fails, it's going to be me who broke it. If you use the "Linux Generic" tarballs from Oracle, it's pretty straightforward.

If you symlink /usr/local/mysql to point to the tarball's extraction directory (which is named for the version and architecture) and then symlink data inside the extraction directory to point to your "datadir," then upgrading MySQL to a new version is as simple as extracting the new tarball, stopping the server daemon, changing 2 symlinks, restarting the daemon, and running mysql_upgrade. If the new version won't start for any reason, well, hey, the old version is still right there.  Move the symlinks back, and punt.

The only dependency you have to take care of manually is installing libaio1, which is in the standard distro repositories, just not installed by default.


Does SQL Server 2012/2014 Standard support Hot Add memory on a Hyper-V guest?

The phrasing might be confusing, however it means exactly what it says.

The Hot memory adding is a feature. Not a subject of limitations of the environment.

So what the article actually says is:

You can only use the Hot add Memory feature in SQL Server Enterprise and Datacenter
Standard version of SQL Server 2012 could detect memory being added to the system, however it does not allow for hot add memory to the SQL Server so the feature is turned off

In other words, no it doesn't work.
I guess somewhere in the Microsoft office there is a dev who really really wanted people to know it wasn't his fault that the feature is limited to Enterprise and Datacenter editions. But rather a part of the licensing.


Where is the list of attached databases stored?

I don't see a table in master.mdf ... where the names of the other attached databases are stored.

That would be sys.databases. Running sp_helptext 'sys.databases' will quickly reveal what is the catalog table backing it:

CREATE VIEW sys.databases AS
    SELECT d.name, d.id AS database_id,
...
FROM sys.sysdbreg d
...

To query sys.sysdbreg see Using a Dedicated Administrator Connection.


Syntax for correctly capturing current user in a trigger

I don't understand how you have found a problem.

select CURRENT_USER();

| CURRENT_USER()                |
| :---------------------------- |
| fiddle_XQKHLETMKAXKBKBXJWXJ@% |

CREATE TABLE test (id INT, user VARCHAR(64));

CREATE TRIGGER tr_bi_test
BEFORE INSERT
ON test
FOR EACH ROW
SET NEW.user = CURRENT_USER();

INSERT INTO test (id) SELECT 1;

SELECT * FROM test;

id | user
-: | :----------------------------
 1 | fiddle_XQKHLETMKAXKBKBXJWXJ@%

db<>fiddle here

Search the uncorrectness elsewhere.


Limit access to specific database only, and restrict access to system tables

You can achieve this goal by revoking certain privileges.  You might have the reasons to do so - the only scenario where I'd start thinking about it is multi-tenancy.  Still, in this case, it is better not to allow the tenants to directly touch the database.

So, after this detour, what do you have to revoke?  For functions it's the EXECUTE privilege, for tables and views SELECT - ordinary users cannot change data in pg_catalog anyway.  For cleanliness, it is possibly better to revoke ALL, which then includes the former two as applicable.

Let's see now what happens:

# CREATE USER lame_bob [WITH PASSWORD 'l'];  -- note that this way the password
                                             -- might appear in the database logs,
                                             -- so use psql's \password command instead

# REVOKE EXECUTE ON FUNCTION pg_current_xlog_location() FROM lame_bob;

# \c test lame_bob

> SELECT pg_current_xlog_location();

 pg_current_xlog_location
──────────────────────────
 0/26517910

Oops.

The trick here is the public pseudorole, which has privileges on certain objects (like EXECUTE on the function in this example).  To disable Lame Bob, this has to be also revoked:

> \c test dezso

# REVOKE EXECUTE ON FUNCTION pg_current_xlog_location() FROM public;

# \c test lame_bob

> SELECT pg_current_xlog_location();

ERROR:  permission denied for function pg_current_xlog_location

In turn, this might disable other users, too, who should keep the ability of using this function.  If necessary, you can grant them the EXECUTE privilege one by one - but there is a better way:

# CREATE ROLE can_use_system_functions; -- no login rights

# GRANT EXECUTE ON FUNCTION pg_current_xlog_location() TO can_use_system_functions;

# GRANT can_use_system_functions TO able_alice;

And then able_alice (and all other users that eventually gets the membership in can_use_system_functions) will be able to do what they need to do.  Allowing new users to these objects is just a matter of granting them this single umbrella role.


ORA-01031: insufficient privileges, cannot login as any user

Based on your comments, I have to assume that you are new to Oracle Database.

alter system set processes = 1 ...

It takes lots of processes to run an Oracle Database.
I very rarely hear of anyone reducing this parameter - usually we're having to ramp it up to cope with increasing workload.

orapwd file=filename password=password entries=100

The Oracle Password File controls who can log into the database using Operating System authentication rather than a local, database password.
It should have as few entries in it as possible (I don't think I've ever exceeded five) and then only those that *administer" the database and are trusted to know what they're doing.

Also tried editting /u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora file

No, no, no.  Just ...   No.
You should consider most, if not all, of the files used / managed by Oracle Database to be in a proprietary format that you absolutely must not try to "edit".
This is Oracle database, not Microsoft's "ODBC Text" Driver.
The PFile is one of the few a text files that you can edit but their use is exceptional these days - most database instances run from an SPFile - intended for situations like this where you have to "fix" things on the fly.


Not enough space for MySQL optimize; do new rows overwrite existing data?

There is a user comment on the OPTIMIZE TABLE documentation page that had the same situation as you. His solution was to copy the table somewhere else, optimize, then copy back. This required offline time.
Unfortunately, my solution would also require offline time as well, for 70GB worth of data. Something like this:

mysqldump table from another machine with enough space
DROP TABLE foo
Load table from the mysqldump file: mysql -hXXX.XX.XX.XXX -u... -p... < foo.sql

The downtime will be from the DROP TABLE step until the load table from a remote host of 70GB of data can be completed.
One potential bright spot is that new INSERTS will re-use the space created from DELETES:

Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions.

But this is what causes fragmentation to begin with. So you will need to make sure your tablespace is optimized frequently to prevent this in the future.
I just want to throw out there that to run an optimize query, you're always going to need at least the database size + the size of the table you're optimizing available on disk.
As an example, this means for a database size of 30GB and the table your optimizing is 28GB, you will need at minimum a harddrive that can fit 58GB of data space. This is because MySQL rewrites a new temporary file and then replaces the old one.
I say at least, because there's always other things going on. You'll need space for other programs, MySQL might be creating on-disk temporary tables during the operation, etc.


usually the answers are that it should use AND, OR instead or Dynamic SQL.

There is no dynamic SQL in your code.

  any difference in performance?

Of course the construction which you show causes fullscan and cannot be optimized. But the condition built using logical operators cannot be optimized normally too. To achieve highest performance use UNION ALL with 3 separate subqueries, one condition per subquery. Because constant-evaluated condition for two of them will give false only one of 3 subqueries will be executed in practice.

In SP this can be 3 separate queries wrapped with external CASE:

DELIMITER $$;
CREATE PROCEDURE `case_in_where`(IN `column_selector` INT, IN `value` VARCHAR(255))
    BEGIN
    CASE WHEN `column_selector` IS NULL THEN
             SELECT * FROM `foo`;
         WHEN `column_selector` = 1 THEN
             SELECT * FROM `foo` WHERE `foo`.`column_1` = `value`;
         WHEN `column_selector` = 2 THEN
             SELECT * FROM `foo` WHERE `foo`.`column_2` = `value`;
    END CASE;
END $$;
DELIMITER ;

Flow Control Statements - CASE Syntax


Database design with different user roles connected by different relationships to one organizaiton table

What are the pros and cons of above schema?

The major con that I see is that freelancer and employee have the same fields, but are different tables. The difference is what primary_key is composed of, but I wonder if you could normalize this as

emplpoyee
{
    user_id,
    employee_type, (could be "FREELANCER" or "FULLTIMEEMPLOYEE")
    organization_id,
    Primary_key
}

For primary_key, you might need some application logic to create a string based on user_id and employee_type, and optionally include tha value of organization_id if the employee is a freelancer.

You could even go a step further and have a table employee_type:

employee_type
{
    id,
    description
}

and then reference that ID in employee.employee_type. This would deal with your problem of adding new tables every time there is a new type of user.

Also, Have you considered merging employee with user? This would simplify your schema even more, but you would lose the ability to have a user_id associated with multiple employees, which I think is important for this case.


Grant rights to create a table with an identity column on another schema

In order to create a table using the GENERATED BY... mechanism you need also the SELECT ANY SEQUENCE privilege, in addition to CREATE ANY SEQUENCE.

I don't know why that is the case, since you are able to create a standalone sequence. I suspect it has to do with setting up the link between the table and the sequence.

Here is an example:

SQL> connect pg_test/pg_test@graphdb
Connected.

SQL> select privilege from user_sys_privs;

PRIVILEGE
-------------------------------------------------------------------------------
CREATE ANY TABLE
CREATE SESSION
CREATE ANY SEQUENCE
SELECT ANY SEQUENCE
ALTER SESSION

5 rows selected.

SQL> create table scott.t1 (i number GENERATED BY DEFAULT ON NULL AS IDENTITY);
Table created.

And in the other schema:

SQL> connect scott/tiger@graphdb
Connected.

SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ ---------------------------------
...
T1                             TABLE
ISEQ$$_81682                   SEQUENCE

50 rows selected.

If I revoke the SELECT ANY SEQUENCE privilege from user pg_test then the table creation will fail.


Is it possible to tell if a database has AUTOMATIC_TUNING switched on?

Automatic query tuning requires that Query Store be enabled first, then you can enable Automatic Tuning.

To check Query Store, select * from sys.database_query_store_options. You're looking for actual_state_desc to be READ_WRITE.

Then check the tuning options with select * from sys.database_automatic_tuning_options where name = 'FORCE_LAST_GOOD_PLAN'. You're looking for actual_state to be set to ON.


Move SQL Server XML column to a new table - slow performance


Is there an easy way to debug and find out why a query runs slow?

Thanks to mrdenny! Your commment pointed me in the right direction!

I've pasted the query into SQL Management Studio

Then used the option Display Estimated Execution Plan

This gave me an output that an index in one of the tables belonging to the view needed to be recreated, so I deleted it and create it from scratch using the suggested output.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[MemberOrders] ([Client_Id],[Project_Id],[Person_Id])

Now the query takes only 2 seconds and not the 1:30min that it used to take!

Thanks a lot guys


I have dealt with some long running jobs in the past and the best way I have found to identify them is to run a separate job that checks on that sort of thing.   Thomas LaRock put together a nice script that does that, and you can modify it to either email you or if you wanted you could have it just stop the job as well.   In my case, the job literally just hangs, but it is not being blocked and in fact in a lot of cases it is doing nothing at all.  But since for whatever reason it never stops the job it will never run that next schedule.

HOW TO: FIND CURRENTLY RUNNING LONG SQL AGENT JOBS

I have put his code to use and added an email notification to it for myself, and it works great.   As long as you know your threshold's for what is a long running job or even if you just want to look at an individual job, this should get it done for you.   It would be easy enough to have your code find that it is running too long and then to within the same script call

exec msdb.dbo.sp_stop_job @job_name 'YourJobHere'

which would then allow the next schedule to pick up where this left off.  I have found that I like to see if there is anything else going on as opposed to just stopping the job but it will work either way.

Best of luck.


module_end extended events duration in microseconds?

It is in microseconds.  See the picture attached here.


The problem statement says: You decide to model a CONTACT table with primary key of Teacher-ID and Contact-Info, which is the contact information itself.
Your solution doesn't do this, which is why your professor didn't like your answer, I suspect.
Your solution has a few other issues that may have affected the professor's assessment:

You have abstracted out the email domain to a separate table. I'm guessing you did this because you thought that you were normalizing out the domain name. This is not really how normalization works. It's not about removing any data that appears to be repeated. It's about removing out data which is semantically identical so that when it changes you only have to change the data in one place. The only rational reason to "normalize" out the email domain is if you expect email domains to change and you want to get every email address with that domain updated in one operation. The chance of this happening realistically is vanishingly small, so it's not worthwhile to do.
The problem statement states outright that the contact table key is a composite key which includes an element which is natural
Your solution tries to address the requirement for a constraint that enforces the "at least one phone number" business rule by including a foreign key to phone in the Teacher table, but this relationship is not reflected in the diagram with a relationship line.
Your solution may or may not address the part of the problem that states your entities should represent the unique attributes of telephone numbers and email addresses so that no entity contained null values
Your Phone table does not have a primary key indicated

I believe what you were expected to do was to propose an Entity Sub-type solution for this problem.  Consider the following:

You want a single CONTACT table with a primary key consisting of a Teacher-ID and Contact-Info, which is the contact information, itself, as per the problem statement
You want to represent the distinct attributes of email addresses and phone numbers
You want to avoid nullable attributes
You want to have a constraint which enforces the requirement for one (primary) phone number included in the model itself (as opposed to code)

The parts of a phone number (in USA and Canada) would include:

Country Code (1)
Area Code (000) (aka NPA)
Exchange (000) (aka NXX)
Subscriber (0000)

The parts of an email address are:

user
domain

Note that email domain is a tricky one because the arbitrary constraint in the problem statement precludes dividing the email domain into subdomain (optional), domain name and TLD.
So taking all of this into account, you might have an ERD that looks something like this:

NOTE: This is not a good solution to a real-world problem, but it addresses the arbitrary requirements of the problem statement.
By using entity-subtyping, you can respect the problem constraint that says you have a CONTACT table with a compound key made up of the TeacherID and the contact information itself.
Furthermore, the two entity subtypes (PHONE_CONTACT) and (EMAIL_CONTACT) can each represent the parts of those types of contact methods without nullable fields. Note that in order to do that and also respect the foreign key needs of the subtypes you would want the whole contact information to be a derived column (made up of the individual parts) if your RDBMS has a feature which supports this, otherwise you would have to resort to redundant data, surrogate keys, violating the business rule that CONTACT includes the contact information itself in it's primary key, or a complex check constraint on each subtype.
The other issue with this solution for practical purposes is that it will require a complicated transaction to establish a new TEACHER record because the teacher and their primary phone number will all need to be established within a single unit of work and constraints may need to be relaxed inside that unit of work an re-imposed and checked at the end.  Such is the way of canned problems with arbitrary rules. They are good for learning concepts but they don't always make for sensible, supportable business applications in the real world.


SSIS Transaction Level

The default transaction level for all tasks and containers is Supported which means they will enlist in an transaction if available but they will not create one..

A Required settings will start a transaction if one does not exist and enlist in an existing transaction.

NotSupported indicates that the Executable/Container will ignore any existing transactions which may result in deadlocks depending on your design.

Your case

You have 3 Tasks and the desire is to have the third item behave in an atomic way. The answer is, partially, that you'll want to set the Transaction option to Required. Partially because you can commit throughout your data load. In an OLE DB Destination, if you have a non-zero commit size then the committed data is fully committed. A rollback isn't going to pull that data back out.


Restore of db's on local locks me out of accessing other sites

If you are using innodb tables, try to raise innodb_buffer_pool_size as much as you can. You must balance the ram from mysql and other tools you use for development. The idea is to don't have a swapping machine and to use memory resource as best as possible.


Which is faster? Multiple update queries in a cursor loop, or a single query?

Neither -- You need to present a 3rd option.

Option 1 is slow because it is going back and forth between the Stored Routine and the server.  (This is not as bad as between the Client and Server, but it is still extra effort.)

Option 2 is slow because of the tmp table and subquery.

Option 3 is something like

UPDATE   table2
    JOIN table1  ON table1.id = table2.id
    SET   table2.column = 0
    WHERE table1.column = 0;

(I assume id is the PRIMARY KEY in each table?)

Important:  table1 needs INDEX(column).

See "multi-table update" in the online reference manual.


Is it possible in Firebird to create a single index with ascending and descending columns?

The syntax for index creation does not allow for this:

CREATE [UNIQUE] [ASC[ENDING] | [DESC[ENDING]] INDEX indexname
   ON tablename
   { (<col> [, <col> ...]) | COMPUTED BY (expression) }

<col>  ::=  a column not of type ARRAY, BLOB or COMPUTED BY

As you must set a single order for the entire index, not for each individual column.

However,

  It is quite valid to define both an ascending and a descending index
  on the same column or key set.