You can use 12c ASM for 11g databases (even 10g), neither the MOS note nor the forum thread says you can not use it.

As far as the table in the MOS is concerned, for 12c, just ignore the first column. Starting with 11.2, the clusterware and ASM come together integrated, called Grid Infrastructure, and not separately as in earlier releases, so they will always be the at the same version. The table clearly shows that 10.2 - 12.1 databases are all certified with 12.1 ASM.


@ Mark Barinstein many thanks to you because you made it easier for me

This worked with me with below two steps :-

1- [db2inst@localhost ~]$ db2 connect to TESTDB

2- [db2inst@localhost ~]$ db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', '', current_user)"


Could not resize shared memory segment error

Lowering number on max_parallel_workers_per_gather to 8 seems to have fixed the problem in my case.


Well, having 10% free of big RAM is still a lot of space, but MySQL memory usage should be fairly predictable. You can check your config with http://www.mysqlcalculator.com/ or similar tool.

You don't want it to get to swap or OOM if swap is disabled, so tune it to use less than total RAM, maybe keep it under 90% for mysql and normal system usage. Then if it gets over 90, you actually know something is off (maybe some OS service memleak or similar stuff), but you still have few GB to delay real trouble.


Share data from one database to two different db

SELECT a.this, b.that
     FROM database_root AS a
     JOIN db_1 AS b  ON a.x = b.x;


It is the table_alias as shown in the documentation for the FROM clause.

[ FROM {  } [ ,...n ] ]
 ::=
{
    table_or_view_name [ [ AS ] table_alias ]
        [  ]
        [ WITH (  [ [ , ]...n ] ) ]
    | rowset_function [ [ AS ] table_alias ]
        [ ( bulk_column_alias [ ,...n ] ) ]
    | user_defined_function [ [ AS ] table_alias ]
    | OPENXML
    | derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]
    |
    |
    |
    | @variable [ [ AS ] table_alias ]
    | @variable.function_call ( expression [ ,...n ] )
        [ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]
    | FOR SYSTEM_TIME
}

It would be clearer if the code author had chosen to use the optional AS keyword, and to use the alias elsewhere in the query:

SELECT
    DOWS.wait_type,
    SUM(DOWS.wait_time_ms / 1000) AS [wait_time_s]
FROM sys.dm_os_wait_stats AS DOWS
WHERE
    DOWS.wait_type NOT IN
    (
        'SLEEP_TASK', 'BROKER_TASK_STOP',
        'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT',
        'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP'
    )
GROUP BY
    DOWS.wait_type
ORDER BY
    SUM(DOWS.wait_time_ms) DESC;


pt-online-schema-change tool will work for existing triggers for a table?

According to the documentation, the tool accomplishes its magic of keeping the old and new tables in sync while the copy process is happening via the creation of its own triggers and is therefore not compatible with tables already using triggers no matter what kind of change you intend to make.

Any modifications to data in the original tables during the copy will be reflected in the new table, because the tool creates triggers on the original table to update the corresponding rows in the new table. The use of triggers means that the tool will not work if any triggers are already defined on the table.
— http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html


Filtering on a virtual column from a UNION ALL with LEFT OUTER join causes a table scan?

Just off the top of my head, it makes sense that it needs to tablescan those two tables. They are both present in a UNION ALL. This essentially means all records are in consideration. You then also use a LEFT OUTER JOIN meaning everything from the left side (your two union'd) tables even if it doesn't match the right side (your Children table). This is telling DB2 that no matter what every possible record in the two tables of your UNION ALL is under consideration.

Now...to fix that problem. I would ask, do you have any indexes over the clazz column in both tables? Since clazz is a predicate of your where clause you may wish to have a index over that field in both tables (MyStuff and MyWarehouseStuff). You don't need an index over the id fields (as long as they are primary keys), because you gain that by default in DB2.

To better verify what DB2 is doing, you should use the visual explain (of course you may have done that). It should show you why it is picking what it is. A few other things to keep in mind when examining the explain, do you run stats? If you don't, you should. Running stats will keep DB2 better informed as to statistics about its tables, data, indexes, etc, and may cause it to choose a better access path. Also, this may seem a silly question, what version and fix pack of DB2 are you on? Occasionally certain versions/fix packs end up buggy and there are performance issues due to poor access path selection by the optimizer. An example of this, if you are on DB2 9.7 then you want to be on fix pack 4 or 5. There is at least one fix pack below those that had serious performance issues due to a bug in the optimizer.

Anyway, check out your indexes first. Adding one to both the tables I mentioned may solve your problem. Worse case, I suppose you could re-write your query to put the clazz predicate up into the sub select "table". That may cause the optimizer to eliminate rows before it unions and joins. Just another thought.

  EDIT: Here is a thought on your query

SELECT allStuff.id
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (/*fast sub select */)
       AND allStuff.clazz_= ?

  You are using a constant basically that from what I understand doesn't
  exist in the table. You also are not selecting this, but you have
  applied it to every row, and you use it in the WHERE clause. So I
  ask why do this? It really is just saying select every row. You may as
  well be doing select *. This will contribute to the table scan. Plus
  you are not displaying this value back. So why use it? Why not rather
  do the following:

SELECT allStuff.id
  FROM    (SELECT id FROM MyStuff
           UNION ALL
           SELECT id, FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (/*fast sub select */)

  Since you are not selecting the data, unless you oops'd on your query
  you are posting, isn't this what you would rather have? It union
  (all)'s the ids, joins them to the child table and then returns what
  you want?

  Although I would also question the point of your LEFT OUTER JOIN.
  Because if your left table (in this case allStuff) has a NULL for
  the id it shouldn't match your "fast sub select", unless that allows
  NULL's as well. (Does it?), so you would be excluding these. I don't
  know the context of  what you are trying to accomplish so perhaps I am
  not speaking to it appropriately, but it makes me question having a
  LEFT OUTER JOIN as well.


How to SELECT TOP(1) with COALESCE to pick the top NOT NULL row for each column?

WITH cte AS ( SELECT hierarchy, setting, "option", preference, color
              FROM source s1
              WHERE NOT EXISTS (SELECT 1
                                FROM SOURCE s2
                                WHERE s2.hierarchy LIKE s1.hierarchy+'_')
              UNION ALL
              SELECT s.hierarchy,
                     COALESCE(cte.setting, s.setting),
                     COALESCE(cte."option", s."option"),
                     COALESCE(cte.preference, s.preference),
                     COALESCE(cte.color, s.color)
              FROM source s
              JOIN cte ON cte.hierarchy LIKE s.hierarchy+'_'
              )
SELECT setting, "option", preference, color
FROM cte
WHERE hierarchy LIKE '_'

fiddle


Can I use Oracle Standard or Enterprise edition for my own training purposes?

Yes, that's fine:

  All software downloads are free, and most come with a Developer License that allows you to use full versions of the products at no charge while developing and prototyping your applications, or for strictly self-educational purposes.

The full Developer License can also be found on OTN, including:

  Oracle grants You a nonexclusive, nontransferable, limited license to internally use the Programs, subject to the restrictions stated in this Agreement, only for the purpose of developing, testing, prototyping, and demonstrating Your application and only as long as Your application has not been used for any data processing, business, commercial, or production purposes, and not for any other purpose.

As with any licensing question, I'd suggest you read Oracle's info carefully including the full license and satisfy yourself that what you want to do is OK rather than just taking my word for it.


Elastic queries available in SQL Server 2017

As the doc says its still in preview with limitations. So no, sql server 2017 on premise does not have that feature as of now.

Once it is in GA, it might be available in sql server (possibly with a Service pack or in newer version of sql server).

Other features like Query Store and adaptive join, etc were first avilable in Azure and then they got into onpremise SQL Serve, so I believe if a feature is featured in Azure, it will eventually get into onpremise sql server may be through a service pack or new version of the software.


metadata could not be determined because stored procedure contains dynamic SQL

The problem is not in your proc but in OPENROWSET/OPENQUERY.
Prior to SQL Server 2012 to figure out the resultset metadata set fmtonly was used. Starting with 2012 it was replaced by sp_describe_first_result_set that causes this error.

here how you can describe your resultset:

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC dbo.nachoTest ''e_MFA_All_Tokens'' WITH RESULT SETS
(
  (
    cnt int, name sysname
  )
)')

More on EXECUTE WITH RESULT SETS here: SQL Server 2012 T-SQL at a Glance – EXECUTE WITH RESULT SETS


Each data node's redo log has a number of 'parts' - currently 4 by default.  Each redo log part is like a mini redo log, with 1 / Num_parts of the capacity defined for 'Redo log'.  This design increases file system and lower layers parallelism when writing the redo log, but requires balance across the parts to be reasonable.

When a table is created, table fragments are created on each data node.  Each fragment is assigned a redo log part which will be used to log all of the activity on the fragment - e.g. inserts, updates, deletes.

A table with only a single fragment on a particular data node will use only one redo log part, and you will see an imbalance in the use of the redo log parts as you have shown.

Where a table has multiple fragments stored on a data node, they will use different redo log parts, and the use of the different parts is balanced.

Where you have more than one table, the fragments of other tables will use different redo log parts, and the use of different parts will be statistically balanced.

So try creating the single table with more fragments (increase MAX_ROWS, or specify PARTITIONS=X in the CREATE TABLE statement), and see if the redo log part usage is more balanced.


How can I expose an Amazon RDS database as read only to other partners who access through home broadband?

Presumably your connection is secured so only your office IP can access the database.

With that in mind a secure VPN tunnel that only directs out to the database area would provide security from them accessing any other systems

And from the database perspective his access rights should be secured down to read-only which will prevent him from modifying anything.

See http://dev.mysql.com/doc/mysql-security-excerpt/5.6/en/adding-users.html for more details on creating users, note the GRANT command, GRANT SELECT will allow them select access, so they cannot update, delete, insert or perform any other such actions.

See http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAM.html for more details on RDS authentication if that is required

As for VPN that depends on the infrastructure you have setup, speak to your IT Admins about this


Mariadb - Error declaring variable in trigger

The special delimiter you have defined should be directly after the END. That is, replace your END; with END$$.


Can't find any tables after psql dump import from pgexercises.com

The solution is: Schemas, SQL for namespace.

The third line in the exercise dump is: CREATE SCHEMA cd;

As a result, the tables are accessible by prepending cd. in front of table names.

Running \dt * with wildcard also shows all tables present, no matter the namespace. This is a good thing to run when \dt gives no result even though one expects that it should have.


Top Activity in OEM reports that dbms_scheduler jobs run as the job_creator user?

You have to distinct between the user of the current session you used for logon and the name of the database user whose privileges are currently active (see also About Definer's Rights and Invoker's Rights). The Scheduler jobs are running under Definer's Rights.

You can get the name of the database user whose privileges are currently active by SYS_CONTEXT('USERENV', 'CURRENT_USER'), see SYS_CONTEXT

In order to understand, try this test-case:

sqlplus kjohnston

CREATE TABLE UTILITIES.A_SCHEDULER_TEST
   (username VARCHAR2(30), CURRENT_USER VARCHAR2(30), ts TIMESTAMP);

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'UTILITIES.SCHEDULE_TEST',
        job_type => 'PLSQL_BLOCK',
      job_action => 'begin insert into UTILITIES.A_scheduler_test values (USER, SYS_CONTEXT(''USERENV'', ''CURRENT_USER''), LOCALTIMESTAMP ); commit; end;',
      repeat_interval => 'FREQ=MINUTELY;INTERVAL=1;BYSECOND=00',
        auto_drop => FALSE,
        enabled => TRUE);

    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'kjohnston.SCHEDULE_TEST',
        job_type => 'PLSQL_BLOCK',
      job_action => 'begin insert into UTILITIES.A_scheduler_test values (USER, SYS_CONTEXT(''USERENV'', ''CURRENT_USER''), LOCALTIMESTAMP ); commit; end;',
      repeat_interval => 'FREQ=MINUTELY;INTERVAL=1;BYSECOND=10',
        auto_drop => FALSE,
        enabled => TRUE);
END;

The content of A_scheduler_test will be like this

USERNAME      CURRENT_USER      TS
------------  ---------------   --------------------
KJOHNSTON     UTILITIES         2015-12-15 08:50:00
KJOHNSTON     KJOHNSTON         2015-12-15 08:50:10

JOB_CREATOR is relevant only for result of USER, resp SYS_CONTEXT('USERENV', 'SESSION_USER'), but the PL/SQL block runs in context of Job owner.

If you run the scheduler job manually you can define how it is executed: RUN_JOB Procedure

use_current_session: This specifies whether or not the job run should occur in the same session that the procedure was invoked from.

use_current_session => TRUE (default): The job runs as the user who called RUN_JOB.
use_current_session => FALSE: The job runs as the user who is the job
owner.

However, when I test this in my database (11.1.0.7.0), this seems to be a (documentation) bug in Oracle!

For use_current_session => FALSE, function USER always returns the job creator.
For use_current_session => TRUE, function USER always returns the job owner.


Deleting would-be orphans when creating FK in Postgres

As has been suggested in comments, the standard tool pg_dump might serve you better. It takes a consistent snapshot and preserves referential integrity during backup automatically - given the source DB is consistent (FK constraints in place). Use it if you can!

The related tools pg_dumpall and pg_basebackup have also been mentioned, but those work on whole DB clusters while you obviously only want to copy (parts of) a single database.

However, there may still be valid reasons for the question. It seems unfair to turn it down on speculation. The question itself is interesting enough:

How to import inconsistent data into a FK relationship and fix it?

NOT VALID option

There is no DELETE ANY WOULD-BE ORPHANS option in the creation of FK constraints (like you'd wish), but the related NOT VALID option for FOREIGN KEY constraints in Postgres 9.1 or later should be instrumental. The manual on ALTER TABLE:

ADD table_constraint [ NOT VALID ]

  This form adds a new constraint to a table using the same syntax as
  CREATE TABLE, plus the option NOT VALID, which is currently only
  allowed for foreign key and CHECK constraints. If the constraint is
  marked NOT VALID, the potentially-lengthy initial check to verify that
  all rows in the table satisfy the constraint is skipped. The
  constraint will still be enforced against subsequent inserts or
  updates (that is, they'll fail unless there is a matching row in the
  referenced table, in the case of foreign keys; and they'll fail unless
  the new row matches the specified check constraints). But the database
  will not assume that the constraint holds for all rows in the table,
  until it is validated by using the VALIDATE CONSTRAINT option.

So you can create involved tables and import data and create PK constraints, and FK constraints with NOT VALID.

ALTER TABLE child ADD CONSTRAINT child_parent_id_fkey
FOREIGN KEY (parent_id) REFERENCES parent(parent_id) NOT VALID;

That prevents any new writes from violating referential integrity, while tolerating existing violations. (And the system is aware of it.)

Delete orphans later, at the earliest opportunity:

DELETE FROM child c
WHERE  NOT EXISTS (
   SELECT 1 FROM parent p
   WHERE  p.parent_id = c.parent_id
   );

This does nothing if the data is consistent, and the work needs to be done either way to verify integrity. So it won't get any more efficient for unreliable data that needs verification.

Then validate the constraint:

ALTER TABLE child VALIDATE CONSTRAINT child_parent_id_fkey;

Doesn't have to be in the same transaction. Read the manual for details.

Related:

Partial db dump/restore
Disable all constraints and table checks while restoring a dump


How to select the most recent entry based on a field in MySQL

WITH CTE AS (
SELECT customer_email,
    supplier,
    purchase_date,
    ROW_NUMBER() OVER (PARTITION BY customer_email ORDER BY purchase_date DESC) AS rn
 from customer_table
 )
SELECT customer_email,
    supplier,
    purchase_date
FROM CTE
WHERE supplier = 'A' AND rn = 1

For MySQL versions older than 8 you can use this query:

SELECT customer_email,
    supplier,
    (SELECT MAX(purchase_date) FROM customer_table WHERE customer_email = t.customer_email) AS purchase_date
FROM (
    SELECT c.customer_email,
        (SELECT supplier FROM customer_table WHERE customer_email = c.customer_email ORDER BY purchase_date DESC LIMIT 1) AS supplier
    FROM (SELECT DISTINCT customer_email
            FROM customer_table) AS c
    ) AS t
WHERE supplier = 'A'

this query probably may be revised for better performance but for this I need to see your database structure.


Using schema + Filegroups or entirely separate database(same instance) to partition some big tables?

Your problem is apparently all about storage space.

Your SSD array is not large enough to hold all your history on SSD.

Creating another filegroup is a good way to separate the use of storage space. That way you control what goes onto the historic data disk array. (When creating objects on a filegroup other than default, you must be sure to specify the filegroup you are using of course. )

Depending on how massive the historic data is, you may not need to split the data by quarters, etc.

For example, you were running well on the SSDs until space became a problem.  So, remembering that space is the problem, you could ask yourself questions like:

Are my indexes well-designed for this workload?
Do my queries require scanning all of the historic data?  (Apparently not, according to your comments above.)
Is the overhead of maintaining quarterly databases going to buy you significant query improvements?  (Especially if you will need data from more than one database.)

Making things simpler and well-designed should be a first goal.  But, if that is not sufficient then you should add layers as they are needed. Keeping performance statistics will help you measure the benefits.

There are plenty of web sites with help.  Here is one set of queries by Glenn Berry: http://www.sqlskills.com/blogs/glenn/category/dmv-queries/


Combining Postgres jsonb query statements

What I ended up doing using inspiration from MatheusOl answer.

INSERT INTO test (post_id, username, votes)
VALUES (12345, 'testuser', '{"commentid" : {"vote": true }}'::jsonb)
  ON CONFLICT ON CONSTRAINT test_pkey DO UPDATE

  SET votes = test.votes || coalesce(test.votes->'commentid', '{"commentid" : {"vote: true"}}'))
   || jsonb_set(test.votes, '{commentid,vote}', 'true'::jsonb)

WHERE NOT test.votes @> EXCLUDED.votes
RETURNING *;

When using his answer, "commentid" would still be overwritten, but I don't really understand why.

This doesn't do things very efficiently, as it recreates the "commentid" key every time as a new object, but in my case it will have significantly more reads than writes, so it should be fine


Two records in the same table must be related somehow

I believe the requirement to record "these 2+ items are used to make this 1 bigger item" is called a BOM. (Bill Of Materials).
The only set of BOM tables I've seen in action used 1 table for each depth.  Your BOM would only need the two tables.
TL;DR - use the "association table".


You can migrate the password file with the input_file option of orapwd.

Example:

[oracle@o71 ~]$ orapwd file=test format=12

Enter password for SYS:
[oracle@o71 ~]$ orapwd describe file=test
Password file Description : format=12
[oracle@o71 ~]$ orapwd file=test2 input_file=test format=12.2
[oracle@o71 ~]$ orapwd describe file=test2
Password file Description : format=12.2


how can I count by week, month, and YTD with postgres?

Too long for a comment now: I don't speak PGSQL so try adapting the following for your date extraction functions:

SELECT
     MAX(CurrentWeekCount) As CurrentWeekCount
    ,MAX(CurrentMonthCount) As CurrentMonthCount
    ,MAX(AllCount) As AllCount
FROM
(

  count all the records by the current week

    SELECT
     COUNT(CustomerID) As CurrentWeekCount
    ,NULL As CurrentMonthCount
    ,NULL As AllCount
    FROM YourTable
    WHERE DATEPART(week, YourDateColumn) = DATEPART(week, GETDATE())

    UNION ALL

  month

SELECT
     NULL As CurrentWeekCount
    ,COUNT(CustomerID) As CurrentMonthCount
    ,NULL As AllCount
    FROM YourTable
    WHERE DATEPART(month, YourDateColumn) = DATEPART(month, GETDATE())

    UNION ALL

  all of time

    SELECT
     NULL As CurrentWeekCount
    ,NULL As CurrentMonthCount
    ,COUNT(CustomerID) As AllCount
    FROM YourTable
) x


Select from two tables with not direct relationship

The COUNT is too high, correct?  That is because the JOINs get more records before the GROUP BY comes along.  I suggest you remove the ORDER BY and SELECT more fields so you can see which rows are coming redundantly.  That may lead to splitting it into

SELECT ...
FROM ( SELECT person, COUNT(...) FROM ... JOIN ... ) X
JOIN some other tables

AND W.home_work_id IN (SELECT home_work_id FROM fixes GROUP BY home_work_id)

optimizes very poorly; change it to yet another JOIN.  Why do you have the GROUP BY there?  Perhaps you meant DISTINCT?  Perhaps in this case, do

JOIN ( SELECT DISTINCT home_work_id FROM fixes ) Q ON Q.home_work_id = W.home_work_id


MYSQL+MyISAM+Need information regarding check table statement

Big table?  It should be read-only.  If it finds something, CHECK TABLE suggests that you run REPAIR TABLE -- which will modify the table.
Whenever the server crashes (power failure or whatever), CHECK TABLE is needed for MyISAM tables, but not for InnoDB tables.  I recommend you change Engines.
Since almost every action locks the whole table in MyISAM, I would guess that CHECK does, too.


Read the definition of sys.dm_hadr_database_replica_states at https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql?view=sql-server-ver15 - you’ll notice that it says “On the primary replica, this view returns a row for each primary database and an additional row for the corresponding secondary database.”
I recommend you debug this by adding AG_DB.* to your select clause, to see what is in the rows you don’t want.


How to derive a column name in the return type from input parameters to the function?

The short answer: Not possible.
SQL is very rigid about column data types and names. Those have to be declared before or at call time at the latest. No exceptions. No truly dynamic column names.

For a couple of possible workarounds see the answer to the duplicate question on SO:

How to derive a column name in the return type from input parameters to the function?


Alternating usernames while connecting to mysql database

I would opt to increase the number of connections instead of owning two different users/configurations in the same host with same privileges.

Unless, there is a persistent connection, php itself will close the connection explicitly even though you do not close them yourself.


PHP-MYSQL messaging system performance problems

You can use a database such as mySQL just fine. Especially if the table structure is simple and indexed well.  I would presume a record for each message would create a need for inserts, but not updates, which would keep index rebuilding needs low too.

If you wanted to scale, just create a read-replica for the querying of the messages and send the write queries to the master server.

Want to scale even more, I suppose you could implement some sort of memcached intermediate level, but this would help more on expanding performance on the read side, not the write side particularly (since you'd have to write them eventually, and you could lose them in memcached in the interim potentially).

In short - mySQL is fine.  Other options include cloud options from amazon like DynamoDB and/or nosql options, but not essential.


Postgresql: How to select records by column and jsonb array column

You can use the @> operator to check if the array contains a specific element. There is no need to use jsonb_array_elements
SELECT dv.id, dv.device_name, dv.device_subname
FROM devices AS dv
WHERE dv.user_id = 1235
  and (dv.device_name = 'rear light'
       or dv.device_subname @> '[{"name": "rear light"}]')

Your cross join is essentially an "inner join" so for rows that have no array elements, the row from the base table is not included.
You would have to use an outer join against the array elements if you really wanted to filter in the WHERE clause
SELECT dv.id, dv.device_name, dv.device_subname
FROM devices AS dv
  LEFT JOIN jsonb_array_elements(dv.device_subname) as sub(item) on true
WHERE dv.user_id = 1235
  and (dv.device_name = 'rear light' or sub.item ->> 'name' = 'rear light')

The @> is more efficient though.


Best way to update list of rows in oracle database

Here's one idea that may or may not fit your needs. Create, say, 3 statements:

s1: UPDATE mytable set mycolumn = ? where id in (?, ?, ...,?) -- 100 ?
s2: UPDATE mytable set mycolumn = ? where id in (?, ?, ...,?) -- 10 ?
s3: UPDATE mytable set mycolumn = ? where id = ? -- 1 ?

As long as you have more than 100 parameters unprocessed, use s1. When there are 10-99, use s2, and for the remaining ones use s3. This can be thought of as a compromise between the number of statements that have to be cached in SQL area, and the number of trips to the database. If we have 963 parameters that mean that 3 statements will be cached, and there will be 9+6+3=18 updates sent over to the database.

Many host languages also have some kind of batchexecute so that you pass
 an array of statements, if that exists in your host language the code is likely to be cleaner than what I suggested above.


The last line in alert.log says that your instance should have been killed (Shutdown abort). This is why you can't connect: listener is up and running and responds to tnsping, BUT the instance is not available (listener does not currently know of service requested in connect descriptor").

You can verify with:

ps -ef | grep pmon

and check if the process ora_pmon_INSTANCE is present or not.

First you have to free space in archive destination, moving elsewhere archived logs, change parameter db_recovery_file_dest_size to a bigger size and/or add disk space to filesystem where path /u01/app/oracle/fast_recovery_area/XE/archivelog/2018_12_01 resides.

Then restart the instance. Sometimes I saw redolog corruption after this scenario, but this is a different issue, you can face of after resolving the first one (startup the instance).


PostgreSQL not using partial index when using boolean in WHERE clause

Partial indexes make sense when they match the query pattern. In your case if you would have queries with inactive IS NOT TRUE condition then the index would be used otherwise it's simply not useful.

Cannot use the partial index:

SELECT location FROM pitest WHERE name = 'Anne Lopez';

Can benefit from the index:

SELECT location FROM pitest WHERE name = 'Anne Lopez' AND inactive IS NOT TRUE;


How to get one MAX() between SUMs of several columns in SQL?

It's never going to be pretty, but heres one attempt:

select gt, case gt when math then 'Math'
                   when physics then 'Physics'
                   when english then 'English'
           end
from (
    select greatest(math, physics, english) as gt
         , math, english, physics
    from (
        select sum(math) as math
             , sum(english) as english
             , sum(physics) as physics
        from scores
    )
)


Search within same ID across several rows

This requirement is known as relational division. One popular approach is

SELECT sentence_id
FROM   YourTable
WHERE  meaning_id IN ( 12455, 15333 )
GROUP  BY sentence_id
HAVING COUNT(DISTINCT meaning_id) = 2

SQL Fiddle

If your table has a unique constraint on sentence_id,meaning_id you can drop the DISTINCT in the query above.


You can use solution 2, but you should have a stronger INSERT logic validation, to check whether data has already a policy or not.

So it would be something like:

check for law rules first (applies to all your users) check for user
consent rule (user specific, so you have to relate it to userID)

So you can use tables: User, DataCollectionPurpose(a flag for law or user consent) and Consent(so you will join to it where your flag in the other table is for user consent)

Hope this helps!


It sounds like you want the recovery_min_apply_delay option of streaming replication.

This will give you a rolling 10 minute window, not a leaping window.


Inline table-valued function vs inline sql

In SQL Server 2000 and earlier we all were accustomed to using alternating forms of logically equivalent queries as a means of query tuning.

Since then the optimizer has improved so much that we are surprised if two different but logically equivalent queries result in different execution plans.

However, the search space of all possible execution plans for a given query is of significant size even for only moderately complex queries so that it is impossible for the optimizer to look at all of them. The optimizer uses a mix of heuristics and rules and information about the data to determine where to look next after having found a first suitable plan. If the query is complex enough even a trivial change like this can set the optimizer of on totally different paths that in the end lead to such big differences in run time. (Check out my post on join hints for a more in detail discussion of this: http://sqlity.net/en/1443/a-join-a-day-join-hints/

Often we can remedy a situation like this by making sure that the information the optimizer is basing its decisions on is as accurate as possible. That means, you should make sure that your tables are appropriately indexed and that all statistics are up to date. You might also look into adding additional - maybe filtered - statistics to the tables involved.


Rebuild index online without risk to other requests

There are several options available to help reduce the problems you're seeing:

/* set the LOCK_TIMEOUT value to the maximum number of milliseconds
   you want to wait for the ALTER INDEX REBUILD to start
*/
SET LOCK_TIMEOUT 100;

/* this tells SQL Server our ALTER INDEX REBUILD statement should
   be the deadlock victim
*/
SET DEADLOCK_PRIORITY LOW;

/* Rebuild the index */
ALTER INDEX MyIndex
ON MySchema.MyTable
REBUILD WITH (
    ONLINE = ON
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , MAXDOP = 1
    );

I know you have SQL Server 2012, however with SQL Server 2014+, you can use the WAIT_AT_LOW_PRIORITY option to tell SQL Server to allow other sessions to proceed while the online index build operation is waiting to obtain its schema modification locks.

ALTER INDEX MyIndex
ON MySchema.MyTable
REBUILD WITH (
    ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF))
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , MAXDOP = 1
    );

From the MSDN SQL Server Books Online:

  In order to execute the DDL statement for an online index rebuild, all active blocking transactions running on a particular table must be completed. When the online index rebuild executes, it blocks all new transactions that are ready to start execution on this table. Although the duration of the lock for online index rebuild is very short, waiting for all open transactions on a given table to complete and blocking the new transactions to start, might significantly affect the throughput, causing a workload slow down or timeout, and significantly limit access to the underlying table. The WAIT_AT_LOW_PRIORITY option allows DBA's to manage the S-lock and Sch-M locks required for online index rebuilds and allows them to select one of 3 options. In all 3 cases, if during the wait time ( (MAX_DURATION = n [minutes]) ) there are no blocking activities, the online index rebuild is executed immediately without waiting and the DDL statement is completed.


Today, without time, is:

DECLARE @d SMALLDATETIME;
SET @d = DATEADD(DAY, GETDATE(), DATEDIFF(DAY, '19000101', GETDATE());

-- when you move past SQL Server 2005, which you should, you can use the much tidier:
-- DECLARE @d DATE = SYSDATETIME();

Then to move to the first day of this month:

SET @d = DATEADD(DAY, 1-DAY(@d), @d);

Now to get all employees who started within the month 6 months earlier, you can say:

DECLARE @d SMALLDATETIME;

SET @d = DATEADD(DAY, 1-DAY(GETDATE()), DATEDIFF(DAY, '19000101', GETDATE()));

SELECT ...
  WHERE Dt_Of_Join >= DATEADD(MONTH,-6,@d)
    AND Dt_Of_Join <  DATEADD(MONTH,-5,@d);
-- I assume you'll have other filters to make
-- sure employee is still employed, etc.

The smart way to do this would be through a parameter, which falls back to today if no date is supplied.

CREATE PROCEDURE dbo.GetNamesByDate -- not the name you should use
  @dt SMALLDATETIME = NULL
AS
BEGIN
  SET NOCOUNT ON;
  SET @dt = COALESCE(@dt, GETDATE());
  SET @dt = DATEADD(DAY, 1-DAY(@dt), DATEDIFF(DAY, '19000101', @dt));

  SELECT ...
  WHERE Dt_Of_Join >= DATEADD(MONTH,-6,@dt)
    AND Dt_Of_Join <  DATEADD(MONTH,-5,@dt);

  -- for debugging only:
  PRINT '>= ' + CONVERT(CHAR(10), DATEADD(MONTH,-6,@dt), 120);
  PRINT '<  ' + CONVERT(CHAR(10), DATEADD(MONTH,-5,@dt), 120);
END
GO

(Now it's also very easy to test that this works for any date, including February 29th in leap years...)

Here is the illogical way to do it in one line (I have absolutely no clue why you think this is a valid or important requirement):

ALTER PROCEDURE dbo.GetNamesByDate -- not the name you should use
  @dt SMALLDATETIME = NULL
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ... WHERE Dt_Of_Join >= DATEADD(MONTH,-6,DATEADD(DAY, 1-DAY(COALESCE(@dt, GETDATE())), DATEDIFF(DAY, '19000101', COALESCE(@dt, GETDATE())))) AND Dt_Of_Join <  DATEADD(MONTH,-5,DATEADD(DAY, 1-DAY(COALESCE(@dt, GETDATE())), DATEDIFF(DAY, '19000101', COALESCE(@dt, GETDATE()))));
END
GO

Oh yeah, that's much easier to read and troubleshoot. Also, it didn't save you any characters, it's actually more... </facepalm>


Testing a Hive array for IS NULL says not null

I just investigated the same issue: In my case it was due to Hive CBO (Cost based optimizer) erroneously replacing the IS NULL evaluator by a constant operator during query planning.
You can try set hive.cbo.enable=false; to see if it solves the issue.
The bug about structs in https://issues.apache.org/jira/browse/HIVE-21778 looks very similar. Haven't tested it yet, however the patch (preventing Calcite type nullability optimization to apply) look like it should solve this bug too.


How to connect to a local server in Oracle SQL Developer like SQL*Plus with no TNSlistener?

In SQL Developer, use the Local/Bequeath connection type:


There's no ELSE in your first CASE, which means if none of your WHEN expressions are matched then it returns NULL.

My guess is there's an issue in your logic below that causes date1 and date2 to always be equal, which would miss both your WHENs in the first CASE and return NULL.


Type conversion in expression try_cast may affect cardinality estimate in query plan choice

Basically, this could mean that the type conversion cannot effectively use an index. Cardinality is a important property of the indexes that you might use.  For performance, the ideal is to have the data types match, instead of trying to convert them on the fly.

You will find Thomas LaRock's post on this useful: http://thomaslarock.com/2012/08/why-datatypes-matter-3-ways-they-can-hurt-performance/


How can I rewrite the following removing the date_format function?

The comment from  @ChristianAmmer is the key to answering your question. Why ???
First look the the WHERE clause
DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month )

You are forcing mysqld to evaluate DATE_FORMAT(t1.when, '%Y-%m-%d') for every row and comparing each result to LAST_DAY(now() - interval 1 month ). That quickly adds wear-and-tear on the query. I have discussed this before (See my answer to  What happens when we take a timediff between timestamp and datetime).
When comparing to LAST_DAY(now() - interval 1 month ), there is no need to do the DATE_FORMAT function against t1.when. Look at what actually happens when you do:

You evaluate DATE_FORMAT(t1.when, '%Y-%m-%d') and the result is a string
The string has to be converted back to a DATE
That converted DATE is then compared to LAST_DAY(now() - interval 1 month )

These steps must be done  for every row.
To remove those steps, just switch the WHERE clause to have
AND t1.when <= LAST_DAY(now() - interval 1 month)

as @ChristianAmmer specified.
That way it compares DATE to DATE without any string manipulate or function call.
To answer your last comment

@ChristianAmmer:I was not sure if it is the same

The comparison of the result is the same, but the way the comparison is executed is not.
Doing it with AND t1.when <= LAST_DAY(now() - interval 1 month) will always be faster. Leaving the t1.when on the left side of the comparison (left side of the equal sign) obligates the MySQL Query Optimizer to try using the index. Using a function call on the left side of the comparison eliminates index usage.
CONCLUSION : Go ahead an d use AND t1.when <= LAST_DAY(now() - interval 1 month)
UPDATE 2014-04-15 10:46 EDT
To further prove function calls on the left side confuses the MySQL Query Optimizer, I have  other posts going back to 2011 that discuss how searching a FULLTEXT index cause the MySQL Query Optimizer to stop using other indexes (since you have to use the MATCH function on the left side).

May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
Jan 26, 2012 : Mysql fulltext search my.cnf optimization
Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
May 23, 2011 : Optimizing mysql fulltext search (StackOverflow)


Switching replication master in cascading replication scenario

Yes, it's safe. B is an identical copy of A that must always be at or behind it in time, so it is safe to switch replication from B to A.

Personally, though, I'd use WAL shipping from A as my fallback. I'd leave streaming replication configured to use B. If it's unreachable, C will try the WAL archives, and it'll find the WAL archives from A and replay from them, periodically retrying B until it can connect again.


Multiplying approximate values will multiply errors: sometimes they will cancel but usually they will exaggerate

float is also accurate to 15 significant figures at most: as you multiply and SUM you can quickly hit this and lose accuracy.

I guess that the changes in results will depend on how what order the data is accessed (changes with eg new rows, row overflow, page splits etc) so the series of calculations involved will change order each with their own rounding.

Edit: had a thought.

Each core/socket will do float calculations differently.

Try these to see if we can make this deterministic (note this isn't the same as "correct")

Add MAXDOP 1 to the query and see how many different results you get. I'm expecting no more then the number of cores or sockets. This reduces the number of permutations with parallelism

Then try processor affinity to always use the same core for all queries to force the query to run on one core/cpu

To fix it, use decimal or bigint.


Updating records from the past with MySQL query

Define an unique key in your table such that each day can only be represented by one row (UNIQUE KEY(date) would be enough for the example you show). Then use

INSERT INTO $tablename(...) VALUES(...)
    ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2);

Alternatively you might use REPLACE INTO, but that does the delete and insert, "burning" through autoincrement IDs which you seem to have.

You might even use the date column as a PRIMARY KEY with this setup.


SQL Server 2005 upgrade to Service Pack 4 (SP4) via command line

Make sure you include the /IAcceptSQLServerLicenseTerms switch, otherwise your installation will fail.  I have commonly done silent installs of service packs to using the following syntax:

.\<SQLServicePack>.exe /q /action=patch /instancename="FOOBAR" /IAcceptSQLServerLicenseTerms

Full syntax documented here.


Unpivot can be done using an array and unnest:

select s.state, s.city, 'item'||t.i as items, t.item
from state1 s
  cross join lateral unnest(array[item1, item2, item3]) with ordinality as t(item,i);

Online example: http://rextester.com/VPDDN96018


Log Shipping: Unable to change from Standby mode to No Recovery mode

If I understand correctly, that is not possible as you described it.

Kendra Little described the actions that are available to you.  Please read her article at:

https://www.brentozar.com/archive/2015/01/reporting-log-shipping-secondary-standby-mode/

A couple of important points:

Logs Restore More Slowly if You Use STANDBY (Readable) Mode
Everyone’s Kicked Out When You Restore Transaction Log

So, you get to choose between (1) slower restores for STANDBY MODE and (2) having faster restores, with the penalty of losing the connection.


Select data based on records created_at column

Note the slightly modified schema of my test in the fiddle.
Using actual primary keys and proper column names instead of id.

Also, you seem to be operating with dates exclusively. So I suggest to convert your timestamp columns to date.

Items 1 and 2

SELECT k.keyword_id
     , k.name
     , pr.project_id
     , COALESCE(min(pr.position), 0) AS pos
     , COALESCE(pr.created_at, now()::date) AS created_at
FROM   keyword             k
LEFT   JOIN project_report pr USING (keyword_id)
GROUP  BY k.keyword_id, pr.project_id, pr.created_at
ORDER  BY keyword_id, created_at
;

In Postgres 9.1 or later the pk column covers the whole table in GROUP BY.
Use COALESCE to replace possible NULL values.

A guess at item 3

WITH cte AS (
   SELECT k.keyword_id
        , k.name
        , pr.project_id
        , COALESCE(min(pr.position), 0) AS pos
        , COALESCE(pr.created_at, now()::date) AS created_at
   FROM   keyword   k
   LEFT   JOIN project_report pr USING (keyword_id)
   GROUP  BY k.keyword_id, pr.project_id, pr.created_at
   )
, x AS (
   SELECT DISTINCT ON (keyword_id, project_id) *
   FROM   cte
   ORDER  BY keyword_id, project_id, created_at DESC
   )
SELECT x.*
     , COALESCE(y.pos, 0) AS yesterday_pos
     , COALESCE(w.pos, 0) AS week_pos
     , COALESCE(m.pos, 0) AS month_pos
FROM   x
LEFT   JOIN  cte y ON y.keyword_id = x.keyword_id
                  AND y.project_id = x.project_id
                  AND y.created_at = x.created_at - interval '1 day'
LEFT   JOIN  cte w ON w.keyword_id = x.keyword_id
                  AND w.project_id = x.project_id
                  AND w.created_at = x.created_at - interval '1 week'
LEFT   JOIN  cte m ON m.keyword_id = x.keyword_id
                  AND m.project_id = x.project_id
                  AND m.created_at = x.created_at - interval '1 month'
;

Explain

In CTE cte produce daily aggregates per (k.keyword_id, pr.project_id).
In CTE x pick the latest day per (k.keyword_id, pr.project_id).
In the outer query LEFT JOIN the latest day x to cte multiple times to retrieve past values from the same (k.keyword_id, pr.project_id) for 1 day / week / month earlier.

SQL Fiddle.


Oracle: Output not shown even if SERVEROUTPUT variable set to ON

As you can see from the output below, a test anonymous PL/SQL block works in SQL Plus as expected:

[oracle@node1 ~]$ sqlplus phil/phil

SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 1 21:55:35 2012

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

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

SQL> set serveroutput on
SQL> DECLARE
  2      v_rec_count number;
  3  BEGIN
  4      select count(*) into v_rec_count from dual;
  5      dbms_output.put_line ('count: ' || v_rec_count);
  6  END;
  7  /
count: 1

PL/SQL procedure successfully completed.

SQL>

I'd guess that you're not using SQL Plus to test & are testing in another tool (such as SQL Developer) and it's not set DBMS_OUTPUT.enable() or you haven't configured the tool to correctly display the output from the RDBMS.

Or, it could be the fact that SET SERVEROUPUT ON is a typo :)


Once you switch to Simple the Trans Log will no longer maintain a log of operations since your last (database files) backup - at the point of the switch all transactions will have been committed to the main database files so will not be discarded - they are already effective in the database files.

Your point 1 is not (in my opinion) worth doing - once switched to SIMPLE, you should backup the Database files - which will contain all data.

Remember that the FULL recovery model only exists so that you don't have to backup the database files every time (so in FULL recovery model your recovery process is: Restore Main Database files, then Restore Transaction Log Backups). With the SIMPLE model the recovery process is only Restore Main Database files.

What I'm trying to say is: As quickly as possible (depending on performance and the type of transaction) the transactions that can be found in the Transaction Log are Committed to the Main Data files (mdf, ndf) which are stored in the .bak file when Backed up.
So SIMPLE model allows you to restore to the point in time when you took a .bak
FULL model allows you to restore to the point in time when you last took a .trn (transaction log backup) - in the case of multiple trn backups that would be a restore of Bak + trn + trn + trn (if you had taken 3 trn backups since your last bak).

The only purpose of the differing recovery models is backup strategy and time saved by not taking a bak every time.

Now I've typed it out, I see why you've asked the question!

Although not perfect English, I think this gives a relatively good breakdown of the different models and why you'd use them: http://www.todo-backup.com/backup-resource/sql-backup-software/three-sql-server-recovery-models.htm

This is how I would do your list:

Switch to simple.
Perform full backup of data files (.bak)
Possibly run DBCC SHRINKFILE with TRUNCATE ONLY to free up some disk space.
Possibly alter the database to decrease the trans log file size.
Perform a full backup.
Switch to full (I assume you're switching back!??!)

Notes: If you're worried, then do a Backup before 1 as well. I'll leave 3 and 4 as they are, because they're a whole different question! (http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/)


Why is a round robin distribution better for a skewed dataset?

Round robin always entails data movement because of its nature, but it needn't be catastrophic for your performance.  The reason you use it for skewed data is because, when you distribute by hash, a unique hash is generated for each value and the rows distributed across the 60 distributions accordingly.  In  your example, most of your data would end up on one (or only a few) distribution(s) and you are therefore not taking advantage of the compute available to you.  Let's say you only have the opportunity of taking advantage of 20% of the compute resources available to you and the rest is idle.

The requirements for good hash columns are: they should not be updateable, not NULLable, should have large number of distinct values and even distribution.

Do you have an option to create a concatenated key from the others?  That could help create a more even distribution and would be useful as long as you used it in joins between the two tables.

Just some other advice, design for your key queries, use some of the other features available in SQL DW like the right DWU, resource classes, non-clustered indexes, auto-statistics.  Please also note that generation 2 of SQL DW is now available.

HTH


After some more research I realised that what I wanted was data regions. I'm now using different lists and tablixes to bind to the different datasets.


Backup standby database using LVM snapshot

To do that on the standby server you need to configure it like a master server to enable the cascading replication[1].

  To enable it, you need to change the follow parameters:

  wal_level to archive or hot_standby
  If plan to use streamming replication:

  max_wal_senders to 2

  If you plan to use wal archiving:

  archive_mode to on
  archive_command to some place

  Then restart your standby database.

  When you configure that, you can run the pg_start_backup function without a problem.

To do what you need (backup with LVM) you must:

  PLEASE test this steps on a test machine to avoid any issues on your production database server.

run pg_start_backup:

psql -U postgres -c "SELECT pg_start_backup('my lvm backup');"

make the lvm snapshot[2] (and export it to a file[3]), eg:

## Repeat that action for each tablespace
## TODO: Review max grow size for your snapshot
lvcreate --size 10G --snapshot --name pgdata_backup_snapshot /path/to/pgdata/dir
## export the LVM to a image (with the `dd` command):
### TODO: Replace the `vg_name` by correct one
dd if=/dev/vg_name/pgdata_backup_snapshot of=/path/to/pgdata_backup.dd
## now remove the snapshot
lvremove /dev/vg_name/pgdata_backup_snapshot

run pg_stop_backup function:

psql -U postgres -c "SELECT pg_stop_backup();"

Hope it helps.

References:

https://www.postgresql.org/docs/current/static/warm-standby.html#CASCADING-REPLICATION
http://www.tecmint.com/take-snapshot-of-logical-volume-and-restore-in-lvm/
https://www.howtoforge.com/linux_lvm_snapshots_p2
http://www.tldp.org/HOWTO/LVM-HOWTO/snapshots_backup.html


Indexing to support advanced search queries

You're trying to write one magical query that will handle all possible combinations of search criteria efficiently. I call this the kitchen sink but it's simply not achievable to do this for 10 parameters with one query and one index. It's just not possible.
The bad news: You're not going to be able to create an index (or even 100 indexes - see here to get an idea of how many combinations, not permutations, you would have to cover) that can satisfy all possible combinations of parameters. And if you write a query with a bunch of OR conditionals, the plan that gets compiled will be based on the first combination of parameters that are passed in, which may not be good for any subsequent query, never mind all subsequent queries.
The good news: You can handle this scenario in a way that lets you prioritize.
Here's how I do it - I create a dynamic SQL version of the query for each possible combination as I see them. I append only the clauses necessary to search based on the set of parameters provided. This way I don't use queries optimized for @p1 when @p1 isn't even passed in. I also set up some qualitative criteria to determine whether I need to recompile for parameter skew. When the parameter represents a unique index key, there is no reason to recompile, but if different values could produce vastly different plans (based on my knowledge of the data, selectivity, usage patterns, etc.), then it might be useful to do so.
CREATE PROCEDURE dbo.SinkMe
  @p1 int,
  @p2 varchar(32),
  @p3 datetime
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(max) = N'SELECT <cols> FROM dbo.table WHERE 1 = 1';

  SELECT @sql += CASE WHEN @p1 IS NOT NULL THEN N' AND c1 = @p1'    ELSE N'' END
              +  CASE WHEN @p2 IS NOT NULL THEN N' AND c2 LIKE @p2' ELSE N'' END
              +  CASE WHEN @p3 IS NOT NULL THEN N' AND c3 >= @p3'   ELSE N'' END;

  IF @p1 IS NULL
  BEGIN
    SET @sql += N' OPTION (RECOMPILE)';
  END

  SET @sql += N';';

  EXEC sys.sp_executesql @sql,
                         N'@p1 int, @p2 varchar(32), @p3 datetime',
                         @p1, @p2, @p3;
END

You can periodically review the plan cache to see which combinations of parameters get used most often and/or lead to the worst-performing queries, and create additional supporting indexes based on those queries (rather than trying to create all potentially helpful indexes up front and maintaining them all even though some may rarely/never get used).


I try this queries is ok.

DECLARE @xml XML='<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="MyOldValue"><SomeTag></SomeTag></Root>';

SELECT @xml = CAST( REPLACE(CAST(@xml AS NVARCHAR(MAX)),'MyOldValue','MyNewValue') AS XML);

Select @xml;


Is a query with UNIX_TIMESTAMP using indexes in mysql?

No. Up to now, the optimizer in all MySQL versions cannot use an index for such a condition. It is not sargable

If you want it to be able to use indexes, you'll have to use a function to rewrite/reverse your condition so no function is applied on the column:

WHERE some_timestamp > ReverseFunctionOf_UNIX_TIMESTAMP( NOW() )

What to use, depends on the column some_timestamp datatype.

If it is a TIMESTAMP, you can simply use

WHERE some_timestamp> NOW()

If some_timestamp is an INT that stores Unix timestamps (seconds since '1970-01-01 00:00:00'), I think you had the correct function but in the wrong place. You should use:

WHERE some_timestamp > UNIX_TIMESTAMP( NOW() )

or the equivalent:

WHERE some_timestamp > UNIX_TIMESTAMP()

Check MySQL documentation: DATE and TIME functions


What SQL execution time should I expect when querying 4k records in a 150M records table?

It looks like you were measuring the query right after importing a lot of data into the table, so your query has to set hint bits, or like shared_buffers is so small that the query finds no free buffers and has to write out some (written=1118).
Try an index that includes id so you can get an index only scan and VACUUM the table:
CREATE INDEX ON answer_submission (student_id) INCLUDE (id);
VACUUM answer_submission;

That should make the query fast.


Migration to SQL Database using Transaction Replication - down time?

If you configure replication, replicate to the external site, keep the replication going until a quiet time then stop it and reconfigure the destination as not replicated. There are some limits replicating to Azure, see https://docs.microsoft.com/en-us/azure/sql-database/replication-to-sql-database to see if they apply to you. There are also some differences between on-prem SQL Server and Azure SQL, they are minimal-to-nothing for most simple databases these days but do check the documentation (and test the application thoroughly before switching over) to make sure they don't affect you.

There will be a small amount of downtime as you stop replication and reconfigure your application to point to the new database, but that should be minimal.

Obviously you'll want to test this out: point a second instance of your application at the target DB and test thoroughly. Once you are convinced all is well wipe it down (as it will now be out of date) and build a new replica (or have the new replica building while you perform the testing). You may need a couple of iterations of this do->test->fix->do loop if there are problems with the replication or your app reconfiguration, or you need to make changes to account for differences in Azure SQL.


Is it possible to select distinct values in a json document using PostgreSQL?

Assuming a JSON array in a Postgres 9.4 jsonb column, this would do the job:

SELECT DISTINCT ON (doc->'s') doc
FROM  (
   SELECT '[
    {
      "id":1,
      "s":"foo"
    },
    {
      "id":2,
      "s":"bar"
    },
    {
      "id":3,
      "s":"foo"
    }]'::jsonb AS j
   ) t
   , jsonb_array_elements(t.j) WITH ORDINALITY t1(doc, rn)
ORDER  BY doc->'s', rn;

Or, unless s is a nested object, it's probably cheaper to fold on the text value instead of the jsonb (sub-)record. Just use the operator  ->> instead of -> in this case. The result is the same:

 doc
----------------------
'{"s": "bar", "id": 2}'
'{"s": "foo", "id": 1}'

Replace the subquery t with your actual table.

Key elements are jsonb_array_elements() (or json_array_elements()) in a LATERAL join with WITH ORDINALITY and then the Postgres-specific DISTINCT ON.

Related, with more explanation:

How to remove object from json array?
How to turn JSON array into Postgres array?
How to preserve the original order of elements in an unnested array?
Select first row in each GROUP BY group?


How to get last messge between two friends in the messge table having id in descending order?

select a.id, a.msg, a.sender, a.receiver, a.friend_id
from msg a
left join msg b on (a.friend_id = b.friend_id and a.id < b.id)
where b.id is null
  and (a.sender = 1 or a.receiver = 1)
order by a.id desc;

Can be tested at http://sqlfiddle.com/#!9/35823/10

It uses "groupwise-max" solution with a left join as can be found for instance in mysql manual (last example).

Your schema seems a bit weird keeping sender, receiver and friend_id, but I suppose friend_id is a FK to some friends table where pairs of (1,2) and (1,6) are stored, so it is more of a "friendship_id" ?


Rather than change MAXDOP or Cost Threshold based on CXPACKET waits (especially logged by that script -- it's a bit incomplete), I'd rather look at the plan cache.

In there you can see execution plans for queries (assuming your cache isn't too volatile), whether they went parallel, and what their cost is.

That way, you're changing things based on your actual queries, and not just on aggregated numbers.

If you're not sure how to do that, start here with sp_BlitzCache (full disclosure, I'm one of the authors). We'll tell you all sorts of stuff about your plan cache from the XML in there.

EXEC dbo.sp_BlitzCache @SortOrder = 'cpu'

A generic run will get you information about your top 10 plans by CPU (though you can choose other sort orders like reads, duration, and more).

And generate overall warnings, plus some info about the age of your plan cache.

As long as your plan cache is reasonably stable, you should be able to see fairly typical plan costs.

Since you have CTFP set to 50, you may see some serial plans with a cost near but under that, and start A/B testing them for performance with parallelism. One way to do that is Trace Flag 8649.

If you see queries with a lower cost benefit from going parallel, you may want to consider dropping CTFP down to a lower number so they stand a better chance of going parallel.

Quick edit: if your plan cache isn't helpful for some reason, you should probably invest in a monitoring tool if you're serious about performance tuning this server.

Hope this helps!


How to dedicate server to MySQL for max performance?

Sorry this startetd as comment, but it git to big

This only means that the database server should run on a hardware where it doesn't share Memory, CPU and Hard drive with other services like webserver for example, or run in a virtual hardware with other virtual environments, which share memory and CPUs.

The biggest Problems you will face are huge amount of data or a lot of parallel connections, which will need be addressed by better hardware or one of the cloud services.

Till then check the error logs of mysql and server, if something is a miss like to small temporary tables, not enough connection and lots more, you will find log entry about it and find a solution by searching for that error or warning.

On a final point the settings depend a lot on what you are doing have  you for example huge inner joins and 6 or 7 of them your settings will be nit the same as other scenarios.

Check your query with EXPLAIN and see that teh indexes are correct set, that will help much more than tweaking one or other parameter.


Is it possible that information_schema.columns doesn't hold an user table column information?

You will only see entries for a table in the information_schema views if you have permissions on the table in question.

You can get column metadata for tables where you have no access from pg_attribute, but you have to deal with PostgreSQL's internal data structures that may change between versions.


SQL Server transactional replication - behaviour of a MERGE command on the subscriber(s) vs. publisher

If you truncated the table on the publisher the record in the transaction log would be an insert, and therefor an insert would be executed against the subscriber.

Now what you can do is modify the stored procedures that replication uses at the subscriber so that when the insert procedure fires it first looks to see if the record exists and if it does it does an update, and if it doesn't it does an insert.


Function to compute difference between 2 times

Assuming exp_hr is always ahead in time, but never more than 24 hours.
I suggest to return an interval for simplicity. Then it works for hours, minutes, seconds, microseconds etc. alike.
Besides correct, this should be faster by orders of magnitude:
CREATE OR REPLACE FUNCTION f_time_diff(hr INT, min INT, sec INT, exp_hr INT)
  RETURNS interval
  LANGUAGE sql PARALLEL SAFE IMMUTABLE AS
$func$
SELECT CASE WHEN hr >= exp_hr       -- wrap around midnight
            THEN 24 + exp_hr - hr
            ELSE exp_hr - hr
            END * interval '1 hour'
                - interval '1 min' * min
                - interval '1 sec' * sec;
$func$;

Note that this returns 24h for input = output exactly. You might decide otherwise for the corner case.
Demo:
test=*> SELECT f_time_diff( 1, 15, 0, 2) AS example1
test-*>      , f_time_diff(23, 15, 0, 1) AS example2
test-*>      , f_time_diff( 1, 15, 0, 1) AS example3
test-*>      , f_time_diff( 1,  0, 0, 1) AS example4;
 example1 | example2 | example3 | example4
----------+----------+----------+----------
 00:45:00 | 01:45:00 | 23:45:00 | 24:00:00
(1 row)

If you actually need the number of minutes as integer, (truncating seconds if any), extract the epoch and use integer division:
SELECT EXTRACT(epoch FROM f_time_diff(23,15,0,1))::int / 60 AS example2;  -- 105

(Or build that into the function.)
db<>fiddle here
The most expensive part is the type conversion now. If you input time / interval, it gets much faster, yet. Or better yet: timestamp or timestamptz, then it gets trivial and you wouldn't need a function to begin with ...
Concatenating strings before casting to time (like you had it) is much more expensive, yet.


No, it is not. Data that is gone is gone. This type of auditing is normally implemented by custom code (triggers writing to an audit table or something like this).


Does SQL Server Query Store capture parameter values?

Depending on the plan, you should be able to see the ParameterCompiledValue for each parameter by inspecting the XML plans saved in sys.query_store_plan.

The list is typically contained towards the end of the xml plan, and can be found in the ParameterList node.

This doesn't provide the value for every execution, but does give you an idea of what the plan was compiled with.

If you truly need to get the value for each execution, you could create an Extended Events session to gather the details.


How to increase size of temp tablespace in Oracle 12C RAC with ASM?

I've created a new temp file using command:
ALTER TABLESPACE temp
   ADD TEMPFILE '+DATA/ORCL/DATAFILE/temp02.dbf' SIZE 2048M REUSE;

After that SELECT * FROM dba_temp_free_space; command showed me much more space available. However I don't know where old temp file is located and what is its name.
I've found old tempfile name and resized it:
SQL> SELECT * FROM dba_temp_files;

SQL> ALTER DATABASE TEMPFILE '+DATA/ORCL/TEMPFILE/temp.XYZ.XXXXXXXXX' RESIZE 500M;


Import all csv files into a table oracle

Use a shell loop, instead of trying to pass a wildcard:

#!/bin/bash
. ~/.bash_profile
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/client_1

for FILE in /data/SDP/Import/*.csv; do
    sqlldr USERID=user/pass@mydb "DATA=$FILE" \
        CONTROL=/home/path/test.ctl LOG=/home/path/test.log
    done


Plan A (not good):

Off hand, I would say it is optimal is to list = column(s) first, then a range:

INDEX(status, can_be_closed,   -- in either order
      updatedAt)               -- last

But...  It is more complicated than that.

'2018...' + INTERVAL interval_before_close ...

is essentially a function call to compute a date.  That makes it useless to put either updatedAt or interval_before_close in the index.

Plan B (wimpy):

So...  The following may be useful, but only if not many of the rows match those two flags:

INDEX(status, can_be_closed)   -- in either order

Cookbook

Plan C (complex):

If, on the other hand, you had a column (real or computed) that contained

updatedAt - INTERVAL interval_before_close MINUTE

Say that it column is called early_close; then change to

AND early_close < '2018-06-22 10:14:36'

and add

INDEX(status, can_be_closed, early_close)


A solution could be to build a simple SSIS package that runs the stored procedure you need; then call it from tsql in this way:
declare @execution_id bigint
 exec ssisdb.catalog.create_execution
  @folder_name = 'folder'
 ,@project_name = 'project_name'
 ,@package_name = 'run_sp.dtsx'
 ,@execution_id = @execution_id output

 exec ssisdb.catalog.start_execution @execution_id

This call is asyncronous by default


What principles should I follow when entering data into a complex relational database?

A foreign key relates to the primary key of another table. When the record with the foreign is entered, the corresponding record with that primary key must be present. Therefore you must always start by inserting in the tables on the 1-side of the relation (the master or parent or primary key table). Then only you can enter a record on the n-side of the relation (into the detail or child or foreign key table).

Order: 1. Entry, 2. Keyword, 3. Keyword_Priority.

If a table has several foreign keys, then the related records in all the other tables with the corresponding primary keys must be entered first. For instance, before entering a record into SenseRestricted..., the corresponding records in Sense and in Reading must have been inserted.


Detecting Logical Replication Error on Postgres v10 on AWS RDS CloudWatch metrics

Through trial and error I found out that you can use OldestReplicationSlotLag.

If there are replication errors,  it accumlates the slotlag bytes


If database is compromised and downloaded, does it matter if user passwords are hashed?

There are two concepts at play here:

Data at rest
Data in motion

Data at rest

As you correctly noted, if someone gets access to your database and the data itself is not encrypted, then hashing and salting passwords makes no difference.

  Encrypt the actual database file itself on the server, so if it were downloaded, they wouldn't be able to have access without knowing the key.

You should enable on-disk encryption in some way or form. SQL Server offers Transparent Data Encryption (TDE), where as soon as data leaves memory and is persisted to storage, it is encrypted. This is called "encryption at rest".

This feature may not be available in your RDBMS, so you might rely on the storage vendor's on-disk encryption. Otherwise, you can investigate file-system level encryption.

The important thing is to keep the database administrators away from the encryption keys, and to ensure that database backups are handled with the same security considerations as the database server.

Data in motion

As for your question around applications having access to encrypted data, this is handled differently. I'll return again to SQL Server because that's what I know.

  Encrypt as much of the information in the database as possible - but can't encrypt it all otherwise we wouldn't be able to perform quick insight queries on our user base for example.

The application uses a certificate to connect to the server, so that only it can see the data it has access to. It's called Always Encrypted, and unless someone has that certificate, the data remains inaccessible.

The database server you end up using should take these two concepts into account. Here's the documentation for SQL Server, but the other more established players should have similar features.


Generate database diagram from SQL Server with no proper integrity

Try Dataedo - it's a database documentation tool that I think can help in your case. You will be able to import multiple database schemas into one repository, then further split them into smaller pieces called modules. You can describe with rich text and create ER diagram for each. You can define missing FKs and use them for the diagrams. You are able to describe every data and program element (table, column, function, parameter etc.) and export such a documentation to PDF document or HTML. Maybe this will straighten out your spaghetti ;)

I'm the product manager of Dataedo.


Is it a bad practice to associate database records to relevant object storage by ID?

In this case, for any Song you would have only one possible audio.mp3 recoding.

With various versions of the same song you will have to provide an additional field within the metadata.

In certain circumstances it may be valuable to prepare in advance some structure for various versions of the same record, building a path with defined version number, default to, say, 0.


The query will be slow because cardinality of category index is low. There are 12 categories, so in average the query will read 1/12 part of the index. You can't improve this query.

Your original approach can improve overall performance. Just instead of manually updating book_count create a trigger on INSERT and DELETE event.

UPDATE: To prove the query will partially read index category

mysql> select count(*) from books;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> select category, count(*) from books group by 1;
+----------+----------+
| category | count(*) |
+----------+----------+
|        0 |       50 |
|        1 |       77 |
|        2 |       88 |
|        3 |       84 |
|        4 |      102 |
|        5 |       79 |
|        6 |       79 |
|        7 |       73 |
|        8 |       84 |
|        9 |       76 |
|       10 |       87 |
|       11 |       83 |
|       12 |       38 |
+----------+----------+
13 rows in set (0.01 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from books where category = 6;
+----------+
| count(*) |
+----------+
|       79 |
+----------+
1 row in set (0.00 sec)

mysql> show status like 'Hand%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 79    |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.01 sec)


Manage Postgres user permissions with many databases and users

You use roles that have privileges on the objects.

The end users have no privileges assigned, but are members of those roles whose privileges they should inherit.

Then you can simply drop end users, and adding them just means adding them to a couple of roles.


Optimize InnoDB settings for current VPS setup

Batch inserts into a single INSERT.  Optimal is to gather 100-1000 at a time and build a single, large, INSERT, then perform it.  It will run about 10 times as fast.
Build a CSV file and use LOAD DATA.  Also much faster than single-row inserts.

For tossing the table and starting over, have innodb_file_per_table=ON.  Caution:  You have probably already put the table in ibdata1, it is too late to take advantage of that flag.  Do not change it.

DROP TABLE or TRUNCATE TABLE will free up space for reuse tomorrow.
 (With it ON, it would also give the space back to the OS.)  But, if you will simply be reloading it, then there is no real need to give the space back.  Simply TRUNCATE TABLE.

Yeah, 10GB vs 8GB is feasible.  But you won't lose another 2GB every day.

10GB for 30M rows?  That's 300 bytes per row?  That sounds high.  Let's see the schema; there are probably some improvements to be made.  Most likely is TINYINT / SMALLINT / MEDIUMINT / INT / BIGINT -- sizes 1/2/3/4/8 bytes, but with different max values.

innodb_buffer_pool_size should probably be 5G if you have 8GB of RAM (and you are running only MySQL on it).  More CPU cores won't help anything.

If your 30M will grow, you probably cannot survive in a tiny 20GB of disk.  However... If you could summarize the data either as you go, or hourly, then you won't need to store all 30M.  Give us more details; we can help further.


One of the slickest ways I've seen to control backups is Ola Hallengren's free maintenance script solution:

http://ola.hallengren.com/

It's installed as a series of stored procs in Master that do all kinds of nifty utility tasks.  It'll automatically handle newly added databases, leverages third party backup compression tools, and does index rebuilds/defrags too.


mysql join many to one table and display each 'many' record as an additional column

As Kondybas suggests in his comment, you can use GROUP_CONCAT to concatenate the columns

SELECT user_id, GROUP_CONCAT(item,',') as items
FROM table_name
GROUP BY user_id

Note that all original columns is fit into one column in the answer.


I have accidentally updated over 3,000 records in one of my tables hosted on Azure.

You are out of luck (since you do not have any backups).

Also, it is not possible to use the undocumented command fn_dblog in Azure. It is only supported in SQL Server.

Also Transaction logs are no longer managed by the DBA in SQL Azure; this is automatically managed by SQL Azure's infrastructure.

check out : Supported Transact-SQL Statements (Azure SQL Database)


In Oracle you can have your own aggregation function. To write one, we need to implement an interface which looks like this:

create /*or replace*/ type mult_agg_type as object (
    totalAggValue    number,
    static function ODCIAggregateInitialize(sctx IN OUT mult_agg_type) return number,
    member function ODCIAggregateIterate(self IN OUT mult_agg_type, value IN number) return number,
    member function ODCIAggregateTerminate(self IN mult_agg_type, returnValue OUT number, flags IN number) return number,
    member function ODCIAggregateMerge(self IN OUT mult_agg_type, ctx2 IN mult_agg_type) return number
);

The above is just the type definition introducing the type with the required type interface amended by a field definition which stores the value aggregated so far.

This interface can be implemented as follows:

create /*or replace*/ type body mult_agg_type is

    static function ODCIAggregateInitialize(sctx IN OUT mult_agg_type) return number is
    begin
        sctx := mult_agg_type (null);
        return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(self IN OUT mult_agg_type, value IN number) return number is
    begin
        if self.totalAggValue is null then
            self.totalAggValue := value;
        else
            self.totalAggValue := self.totalAggValue*value;
        end if;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self IN mult_agg_type, returnValue OUT number, flags IN number) return number is
    begin
        returnValue := self.totalAggValue;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self IN OUT mult_agg_type, ctx2 IN mult_agg_type) return number is
    begin
        self.totalAggValue := self.totalAggValue*ctx2.totalAggValue;
        return ODCIConst.Success;
    end;

end;

Now that the datatype is defined completely, we need to generate a stub function which can be used as an aggregating function in a select-statement:

create /*or replace*/ function agg_product(input number) RETURN number
    PARALLEL_ENABLE AGGREGATE using mult_agg_type;

I chose the name agg_product, but feel free to choose any name you prefer or like best. As an example of how this can be used in your case, try:

select agg_product(value) as prod from your_table_name;


Missing rows (gaps) on range read from the end of the table

You are inserting things into a single table.  That table is acting as a "queue".  Each 'insert' is effectively (even if it is not written this way):

BEGIN;
get an AUTO_INCREMENT id -- but not yet visible, nor replicated
do some stuff (including INSERTing the row)
COMMIT;
send to Slave(s) -- only after COMMIT

Since you have multiple threads doing this, the getting of the ids is not synchronized with the replication, so the ids arrive out of order at the Slave(s).

Instead of depending on AUTO_INCREMENT values to arrive in order, devise some other way to discover "new" items in the queue.  Note that no technique can precisely keep the items in order.

One technique it to use an UPDATE to flag, say, 100 rows as being handled by your Slave 'worker'.  But, beware, if the worker dies, you will need a 'reaper' to figure out what really got processed.

If you would like to discuss this further, please describe the data flow more precisely ("processing pipeline" is too vague).  I encountered your specific problem more than a decade ago, and have since dealt conjured up multiple solutions.

My main solution is "Don't queue it, just do it."  This came from analyzing a system that was running too slow; it turned out that the queuing (especially in a cross-country replication environment) was more overhead than the processing!  Ripping out the queue allowed nearly 10x capacity!


To the extent your bottleneck is in streaming realtime reads and writes, you may want to look into the open source PostgreSQL extension: pg_shard

It shards and replicates your PostgreSQL tables for horizontal scale and high availability. It also distributes your SQL statements, without requiring any changes to your application.

https://github.com/citusdata/pg_shard


Is there an easy way to have a non-IDENTITY, auto incrementing column based on another column?

If I am Understanding you correctly, you are looking to select a group of product that's already in a DB, and assign a new Product ID to each Product that is Unique to each Product Category, if this is correct then you can add this to your SELECT statement,

ROW_NUMBER() OVER (PARTITION BY ProductCategory ORDER BY ID) AS ProductCode

it should do what you are looking for.


Clear out remnant LDF log files of Sharepoint DB not visible on SBS 2008 Domain Server?

I have never used SharePoint or worked with its databases specifically, but both links in your question indicate that you should be able to see, and work with, SharePoint databases in SQL Server Management Studio.

The fact that you cannot see them in your particular case most likely means that they are hosted by a different server instance – not the ...SBS\SBSMONITORING one you are connected to, according to your screenshots. If you do not know the name of the instance hosting your SharePoint databases, try looking it up in SQL Server Configuration Manager → SQL Server Services.

Once you have found out the name and connected to the right SQL Server instance, you should be able to follow the instructions you have discovered on the Web. But please read the answers on this question first to make sure you understand what you are doing and what the consequences may be:

Why Does the Transaction Log Keep Growing or Run Out of Space?


Using a CTE will do what you want:

WITH CTE AS
(
    SELECT SUBSTR(PARAM_ID,6) AS PAR_ID, T.* FROM DB.TABLE T
)
SELECT *
FROM CTE
WHERE PARAM_ID LIKE 'CLC_Q%'
AND PAR_ID BETWEEN 35 AND 45
ORDER BY PAR_ID ASC;

or simply using table_alias.*, instead of *:

SELECT SUBSTR(PARAM_ID,6) AS PAR_ID, T.* FROM DB.TABLE T
    WHERE PARAM_ID LIKE 'CLC_Q%'
    AND SUBSTR(PARAM_ID,6) BETWEEN 35 AND 45
    ORDER BY PAR_ID ASC;


You have 2 good options that I see.  One is to use a numbers table that you put into the database.  This is simply a table that has a sequential list of numbers from 1 to 1,000,000 or any upper limit that you find useful.  This table will allow you to accomplish what you want here, but you will also find many other useful things to do with it. See here for other potential uses of a number or tally table.

So first create a numbers table:

SELECT TOP 1000000 N=IDENTITY(INT, 1, 1)
INTO dbo.Numbers
FROM master.dbo.syscolumns a CROSS JOIN master.dbo.syscolumns  b;

ALTER TABLE dbo.Numbers ADD CONSTRAINT NBR_pk PRIMARY KEY(N);

From here we can write the query.

IF OBJECT_ID('tempdb..#test_dates') IS NOT NULL
  DROP TABLE #test_dates

CREATE TABLE #test_dates (Response_Date DATE, Response_Count INT)

INSERT INTO #test_dates (Response_Date, Response_Count)
VALUES ('8/3/2016',123),('8/12/2016',43)

DECLARE
  @JurisdictionName AS CHAR(30) = 'Something'
,  @StartDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
,  @EndDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 1, -1);

WITH all_dates AS
       (SELECT DATEADD(day, n - 1, @StartDate) AS Dates
          FROM dbo.Numbers n
         WHERE n.n <= DATEDIFF(day, @StartDate, @EndDate) + 1)
    ,response_dates AS
       (  SELECT Response_Date
                , Response_Count
            FROM #test_dates
           WHERE Response_Date BETWEEN @StartDate AND @EndDate)
SELECT a.Dates, ISNULL(Response_Count, 0) AS Respose_Count
  FROM all_dates a LEFT JOIN response_dates r ON a.Dates = r.Response_Date

The other way to accomplish it is to use a date table.  This table is very similar to the numbers table but it has all the dates for a time frame with some additional information about the dates.  Again you will find many different uses for the table for the limited amount of space that it will take.  See here for more information on how to create a date table and what you can use it for:

You could also use a recursive CTE to accomplish something similar, but it would likely have performance issues as things scale up.


DB links are probably not going to work as a solution. Oracle7 is crazy old.

The question's facts are little limited. What types of objects (e.g., tables, stored procs, triggers, etc.) are involved? How much data? That said:

As it relates to table data, my solution would be to spool the data to text and access the text via external tables you setup in the 11g db.

In the 11g db, create the tables with the structure and referential integrity you require. It's been so long since I used Oracle7, so I don't think you can leverage DBMS_METADATA to obtain this DDL. You may have to get creative to reconstruct it.

Fill them with data as selects from the external tables.

Finally, index the new tables as desired.


The usual design is to relocate each column with repeating values into its own table.

You don't say, but I'll call your current table Incidents, and I'll assume the Id is the primary key.

Next create a table for each repeating-value column and link them back to Incidents. For example

IncidentCategories1
  IncidentId
  CategoryID

Values will be (45, 10), (45, 28), (46, 2), (46, 22) etc. Similarly with IncidentCategories2. Reps column becomes

IncidentReps
  IncidentId
  RepName

with values (45, person1), (45, person2) etc. And and so on for the other columns with repeating values.

As for your question about whether it is worth it, well, that's quite subjective. You are using a relational database. The assumption is data is normalised. Your current design isn't. It makes sense to give the tool what it expects to receive, unless there are overwhelming reasons to do otherwise. The only one I can think of is that you only ever treat those repeating groups as a whole and never parse them (in SQL). In that case they can be considered strings that just happen to have commas at certain positions. For this your current design is fine.


What is the syntax of mongodump and mongorestore?

The dump path has to be the final argument of mongorestore.

https://docs.mongodb.com/manual/reference/program/mongorestore/

mongorestore -v --db atelier2 dump/atelier

should do the trick. If not the verbose flag -v should give hints to the deeper cause.


Normal form for a relation where the number of tuples having the same value in a column is the same for all the possible values in that column

How would you populate such a relation? If you required 3 records per id the relation could not exist, as when it is first created this criteria will not be met (until the relation is populated with records).

I think this would have less to do with normal form and more to do with database constraints. Even then, I think it would only allow a MAX of n records with the same id, but not a minimum. Resulting in 0-n records per id.

What problem are you solving? Perhaps some fresh eyes could guide you toward a new approach.


What are the practical impacts of the sys.dm_exec_query_stats warning in the documentation?

I've updated the documentation to be more straight-forward on the wording. The intent was to tell the user that running a query against the DMV will produce output only for items that had completed and not for items in-flight. Thus, run the workload in its entirety to make sure all is captured.

The new wording will be the following and applied the various DMVs the previous wording was incorporated into.

  The results of sys.dm_exec_query_stats  may vary with each execution
  as the data only reflects finished queries, and not ones still
  in-flight.


Stored procedures and network congestion

In SQL Server, the query optimizer goes through these steps to execute any query (stored procedure call or ad-hoc SQL statement):

syntactically check the query
if it's okay - it checks the plan cache to see if it already has an execution plan for that query
if there is an execution plan - that plan is (re-)used and the query executed
if there is no plan yet, an execution plan is determined
that plan is stored into the plan cache for later reuse
the query is executed

The point is: ad-hoc SQL and stored procedures are treatly no differently.

If an ad-hoc SQL query is properly using parameters - as it should anyway, to prevent SQL injection attacks - its performance characteristics are no different and most definitely no worse than executing a stored procedure.

Stored procedure have other benefits (no need to grant users direct table access, for instance), but in terms of performance, using properly parametrized ad-hoc SQL queries is just as efficient as using stored procedures.


Writing a T-SQL stored procedure to receive 4 numbers and insert them into a table

You could also use a table valued parameter type in the stored procedure and pass numbers through this tvp.

Create the type

CREATE TYPE GetNumbers AS TABLE
( Numbers INT );
GO

Create the procedure

CREATE PROCEDURE dbo.InsertNumbers
@GetNumbers GetNumbers READONLY
AS
SET NOCOUNT ON;

CREATE TABLE #Temp(Num int);
INSERT INTO  #Temp(Num)
SELECT Numbers
FROM  @GetNumbers;
SELECT * FROM #Temp;
DROP TABLE #Temp;
GO

Inserting into temp table is not really needed here, only done to keep it the same as the question.

Fill up a variable with data and call the procedure

/* Declare a variable that references the type. */
DECLARE @GetNumbers AS GetNumbers;

/* Add data to the table variable. */
INSERT INTO @GetNumbers (Numbers)
VALUES(10),(20),(30),(40);

/* Pass the table variable data to a stored procedure. */
EXEC InsertNumbers @GetNumbers;

The example used and more on tvp's here


Sql Server 2008 x64 ODBC Linked Server to Oracle Not Working

If I try to right click on a view name and select Script -> SELECT To.... I get a message

The GUI is probably trying to access an Oracle Data Dictionary View (in order to read the column names & data types) that it does not have permission to select from, hence the error message.

Ask the Vendors Oracle DBA to trace the ODBC session so that you can track down the exact cause.


Features and Patterns for Managing Ordered Lists

The last is a technique used quite often, and that works. If you're afraid of running out of decimals you can refine it to detect when the average between the next two previous/following values cannot be distinguished from one of them (i.e.: you run out of decimals).

When this happens, you should resequence as many rows up or down as needed.

Let's assume you work with just 2 decimals, and you have two rows with Seq 1.00 and 1.01; the next value being 2.00. If you want to move a row using your technique between these two, you would have Seq = (1.00, 1.005, 1.01, 2.00). This would need more decimals than are available and, depending on the rounding rules, 1.005 would either be rounded to 1.00 or 1.01. In any case, you can detect that the inserted value is not distinguishable from either 1.00 or 1.01.

In that case, you need to "push down (or up) and make room". That is, you should change one more row, and have (1.00, 1.25, 1.50, 2.00). You've spread the change to two rows instead of just one.

This might need to be done recursively (or iteratively) if you ever run into a situation such as (1.01, 1.02, 1.03, 1.04, ...).

With this extensions to your original technique, you don't need to renormalize. This is taken care of only in an "as needed" basis, and scales quite well.

NOTEs:

Implementing the algorithm might be a bit tricky, because you need to take into account when you're at the first or last rows, and how to handle those, and when your Seq numbers reach the maximum or minimum available values.
The algorithm works with any number of decimals, including 0. That is, you can use it as well with integer values instead of decimal, which might prove more computing efficient, or, depending on how decimals are implemented, be also more space efficient.


Increased performance from single statement, multiple row inserts?

There is a big difference. Insert as many rows as possible at once. Best in a single INSERT statement, but at least in a single transaction. Inserting individual rows takes much longer. Quoting the chapter Populating a Database:

  If you allow each insertion to be committed separately, PostgreSQL is
  doing a lot of work for each row that is added. An additional benefit
  of doing all insertions in one transaction is that if the insertion of
  one row were to fail then the insertion of all rows inserted up to
  that point would be rolled back, so you won't be stuck with partially
  loaded data.

If you insert a large number of rows, consider COPY, which is much faster, yet. And read that chapter in the manual. It has all you need.


Cassandra - Why does Read/Write Latency fluctuate?

I did not fully understand the stress test was doing. It was stepping up the number of threads, hence the load was stepping up, causing the latency stepping up too.

Running with 4 threadCount
Running WRITE with 4 threads until stderr of mean < 0.02

Running with 8 threadCount
Running WRITE with 8 threads until stderr of mean < 0.02

Running with 16 threadCount
Running WRITE with 16 threads until stderr of mean < 0.02

Running with 24 threadCount
Running WRITE with 24 threads until stderr of mean < 0.02

Running with 36 threadCount
Running WRITE with 36 threads until stderr of mean < 0.02

Running with 54 threadCount (08:03)
Running WRITE with 54 threads until stderr of mean < 0.02

Running with 81 threadCount (08:04)
Running WRITE with 81 threads until stderr of mean < 0.02

Running with 121 threadCount (08:04)
Running WRITE with 121 threads until stderr of mean < 0.02

Running with 181 threadCount (08:05)
Running WRITE with 181 threads until stderr of mean < 0.02

Running with 271 threadCount (08:06)
Running WRITE with 271 threads until stderr of mean < 0.02

Running with 406 threadCount (08:07)
Running WRITE with 406 threads until stderr of mean < 0.02

Running with 609 threadCount (08:07)
Running WRITE with 609 threads until stderr of mean < 0.02

Running with 913 threadCount (08:07)
Running WRITE with 913 threads until stderr of mean < 0.02

Because I repeated the test 10 times back to back, there were 10 peaks.


New to MongoDB, but this is how I learned how to do the same thing following the MongoDB 202 class. There are other ways to balance traffic, such as moving, splitting and merging chunks. I haven't read anywhere that directly updating the tags collection in the config database was dirty or incorrect.

The code below is untested, and you will have to replace certain values like nameofShard and the minimum, maximum values. You will notice that I stop the balancer before modifying the tag ranges

// stop the balancer
sh.stopBalancer()

// remove the tags from the shards
sh.removeShardTag("nameofshardN", "rangeTime1")
sh.removeShardTag("nameofshardN+1", "rangeTime2")

// remove the existing tags
db.tags.remove({ "_id" : { "ns" : "testlab.range", "min" : { "_id" : 1 } }, "ns" : "testlab.range", "min" : { "_id" : 1 }, "max" : { "_id" : 100 }, "tag" : "rangeTime1" })
db.tags.remove({ "_id" : { "ns" : "testlab.range", "min" : { "_id" : 100 } }, "ns" : "testlab.range", "min" : { "_id" : 100 }, "max" : { "_id" : 200 }, "tag" : "rangeTime2" })

// Add new tags
sh.addTagRange("testlab.range", {_id : new_minimum_value}, {_id : new_maximum_value}, "rangeTime1");

sh.addTagRange("testlab.range", {_id : new_minimum_value}, {_id : new_maximum_value}, "rangeTime2");

// Add shardTag
sh.addShardTag("nameofshardN", "rangeTime1")
sh.addShardTag("nameofshardN+1", "RangeTime2")

// start balancer
sh.startBalancer()

sh.status() or mongostat --port 27017 --discover to verify balancing

test.lab is not a valid database name, so I changed my database name to testlab.

One additional remark about the _id field not being updated. When I query the tags collection like so

mongos> db.tags.find({}, {_id:1})
{ "_id" : { "ns" : "testlab.range", "min" : { "_id" : 1 } } }
{ "_id" : { "ns" : "testlab.range", "min" : { "_id" : 100 } } }

It shows that the _id field contains the min value or the tag range. If you are updating the tags collection and modifying the tags min field value, then wouldn't it also be reasonable to update the _id field's min value as well?


SHOW tables returns empty set, but MYI.Crypt files are there

There is more information need to troubleshooting.

What is the error logs for Mysql daemon failed to start for normal
start?

[ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
What is the engine for MYD/MYI?
Do you have previous backups or replication site?


Postgres Foreign Data Wrappers for Data Migration. Do transactions hold across databases?

I tried an experiment where I used the Foreign Data Wrapper and executed SQL like I had above. In between the insert and delete lines I put a sleep and then killed the process while it was sleeping. No data was committed as a consequence of the insert until it actually hit the connection.commit() line.
So, to answer my own question, it should be safe.


You need better indexing, and a bit of a rewrite.
Because you’re only using one table, you can use ROW_NUMBER() in a sub-query and then grab the top row from each partition.
SELECT grp_fk_obj_id, grp_name, grp_id
FROM (
    SELECT grp_fk_obj_id, grp_name, grp_id, ROW_NUMBER() OVER (PARTITION BY grp_fk_obj_id ORDER BY date_from DESC, ISNULL(date_to, '4000-01-01') DESC) AS rownum
    FROM tbl_groups
) g
WHERE g.rownum = 1;

But also, fix your ORDER BY clause, because it forces it to do the Sort. Because you’re ordering on an expression, you can’t index it effectively. If you can force date_to to have a value, order by date_from desc, date_to desc, then an index on (grp_fk_obj_id, date_from, date_to) include (grp_name) will really help. It would even make your original query run way quicker.


ORA-24033 : No recipient for message has been detected

I will write the solution here for those who might encounter such error in the future.

I solved the error by re-compiling invalid objects.