Why would my Oracle read from disk? [Copied from ServerFault to what seems like a better-suited forum]

Our Oracle server (version 12c) is running on a RedHat-6.7 server with 128GB of RAM.

The current size of the only database on it is only about 60GB.

Yet, we see plenty of disk reads -- on the device where the database is stored -- in the iostat-output (shown in blue). There are some writes (shown in yellow) too, of course:

While the writes to permanent storage make sense because we do make modifications to the data some times, the reads do not -- the entire DB can fit in memory twice over... Indeed, we should be able to store it on a USB-stick and still have good read-times -- comparable to those of Redis, for example.

What parts of the Oracle-configuration should we examine and tune to make the server use all available RAM?

We will look at the INMEMORY settings. But it would seem, the inmemory_* parameters come into play only, when the server has to (because of RAM shortage) decide, what to drop from memory to be re-read back later. In our case nothing should ever be dropped from memory, because there is room for everything. And yet some things are, evidently, being (re)read again and again...

Update: the output of free on the server currently reads:

             total       used       free     shared    buffers     cached
Mem:     132044132   99974992   32069140   89131408     292508   93060984
-/+ buffers/cache:    6621500  125422632
Swap:     33554428          0   33554428

The processes owned by the oracle-user all seem to share the above-mentioned 85GB memory piece. Maybe, we really are just constraining the server ourselves -- the 30GB unused memory seems to suggest that. How do we tell the server to use more?

Just a few possibilities with not 100% accurate explanation.

Let's say you have 100+ GB SGA (with 100 GB buffer cache) and 20 GB PGA.

Operations performing a sort (queries with ORDER BY) or a hash (HASH JOIN) use PGA memory to do that.
A single session can typically use up to 5% percent of target PGA for these operations, if it requires more memory, then it switches to the temp tablespace, where it continues its work, but instead of memory, it performs its on disk.
So if your query needs to sort some bigger but not-even-close-to-the-available-memory amount of data (1 GB+ data), it will sort it on disk.
Big tables (=segments) are not cached when read with a full scan. By default, a big table (=segment) is typically a table (=segment) bigger than 2% of the buffer cache.
This behaviour can be controlled by setting the "_small_table_threshold" parameter, that specifies the threshold in database blocks for deciding if a segment should be cached.
Similar example: if you have a table that is over 2 GB, and your query reads it with a full scan, the database will not even try to cache it.
If you execute this query multiple times, the database will read the table from disk over and over again.
LOB columns are stored in seperate segments. LOB segments are not cached by default (if not specified explicitly at the time of creation or later), so they will be read from disk over and over again.
The caching can be enabled as:

alter table table1 modify lob (lob_column1) (cache); or (cache reads) to cache reads only.

+1. You have a bigger SGA configured than your database. (Hopefully you configured it at instance level as well).
You can force full database caching as:

ALTER DATABASE FORCE FULL DATABASE CACHING;

+2. In-Memory is an extra cost option for the database that needs to be licensed. Use it only if you have the appropriate license for it.


show table name + number of records for each table in a mysql innodb database How to list all tables in the current database, together the number of rows of the table.

In other words, can you think of a query to come up with something like this in mysql?

+------------------------++------------------------+
| Tables_in_database     |  Number of rows         |
+------------------------++------------------------+
| database 1             |   1000                  |
| database 2             |   1500                  |
+------------------------++------------------------+

Different approaches are welcome.

I have a very aggressive approach using brute force Dynamic SQL

SET group_concat_max_len = 1024 * 1024 * 100;
SELECT CONCAT('SELECT * FROM (',GROUP_CONCAT(CONCAT('SELECT ',QUOTE(tb),' Tables_in_database,
COUNT(1) "Number of Rows" FROM ',db,'.',tb) SEPARATOR ' UNION '),') A;')
INTO @sql FROM (SELECT table_schema db,table_name tb
FROM information_schema.tables WHERE table_schema = DATABASE()) A;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

Example: In my test database, I get this

mysql> use test
Database changed
mysql> SET group_concat_max_len = 1024 * 1024 * 100;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('SELECT * FROM (',GROUP_CONCAT(CONCAT('SELECT ',QUOTE(tb),' Tables_in_database,
    '> COUNT(1) "Number of Rows" FROM ',db,'.',tb) SEPARATOR ' UNION '),') A;')
    -> INTO @sql FROM (SELECT table_schema db,table_name tb
    -> FROM information_schema.tables WHERE table_schema = DATABASE()) A;
Query OK, 1 row affected (0.00 sec)

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+--------------------+----------------+
| Tables_in_database | Number of Rows |
+--------------------+----------------+
| biblio             |              3 |
| biblio_old         |              7 |
| dep                |              5 |
| e                  |             14 |
| emp                |              4 |
| fruit              |             12 |
| fruit_outoforder   |             12 |
| nums_composite     |              0 |
| nuoji              |              4 |
| prod               |              3 |
| prodcat            |              6 |
| test2              |              9 |
| worktable          |              5 |
| yoshi_scores       |             24 |
+--------------------+----------------+
14 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

GIVE IT A TRY !!!

CAVEAT : If all the tables are MyISAM, this will happen very fast. If all tables are InnoDB, each table will be counted. This may be brutal and unrelenting for very large InnoDB tables.


How do I setup a foreign key relationship based on two columns? I have the following two tables:

CREATE TABLE [dbo].[company]
    (
      [CompanyID] [int] NOT NULL ,
      [CompanyName] [varchar](150) NULL ,
      [DepartmentID] [int] NOT NULL ,
      [DepartmentName] [varchar](150) NULL ,
      [ResidentialAddress] [varchar](500) NULL ,
      [ResidentialSuburb] [varchar](150) NULL ,
      [ResidentialPostCode] [varchar](20) NULL ,
      [ResidentialState] [varchar](150) NULL ,
      [ResidentialCountry] [varchar](100) NULL ,
      [PostalAddress] [varchar](500) NULL ,
      [PostalSuburb] [varchar](150) NULL ,
      [PostalPostCode] [varchar](20) NULL ,
      [PostalState] [varchar](150) NULL ,
      [PostalCountry] [varchar](100) NULL ,
      [Phone] [varchar](50) NULL ,
      [IsActive] [bit] NOT NULL ,
      [Created] [datetime] NULL ,
      [CreatedBy] [varchar](50) NULL ,
      [LastModified] [datetime] NULL ,
      [LastModifiedBy] [varchar](50) NULL ,
      CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED ( [DepartmentID] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY]

AND

CREATE TABLE [dbo].[user]
    (
      [UserId] [int] NOT NULL ,
      [CompanyID] [int] NOT NULL ,
      [DepartmentID] [int] NOT NULL ,
      [EmailAddress] [varchar](150) NOT NULL ,
      [Title] [varchar](50) NULL ,
      [FirstName] [varchar](100) NOT NULL ,
      [MiddleName] [varchar](100) NULL ,
      [LastName] [varchar](100) NULL ,
      [DateOfBirth] [date] NULL ,
      [ResidentialAddress] [varchar](500) NULL ,
      [ResidentialSuburb] [varchar](150) NULL ,
      [ResidentialPostCode] [varchar](20) NULL ,
      [ResidentialState] [varchar](150) NULL ,
      [ResidentialCountry] [varchar](100) NULL ,
      [PostalAddress] [varchar](500) NULL ,
      [PostalSuburb] [varchar](150) NULL ,
      [PostalPostCode] [varchar](20) NULL ,
      [PostalState] [varchar](150) NULL ,
      [PostalCountry] [varchar](100) NULL ,
      [Phone] [varchar](50) NULL ,
      [Mobile] [varchar](50) NULL ,
      [BrowserDetails] [varchar](500) NULL ,
      [IsActive] [bit] NOT NULL ,
      [LastLogin] [datetime] NULL ,
      [Created] [datetime] NULL ,
      [CreatedBy] [varchar](50) NULL ,
      [LastModified] [datetime] NULL ,
      [LastModifiedBy] [varchar](50) NULL ,
      CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED ( [UserId] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY]

I would like a foreign key on dbo.user where a company id and department id identifies the correct row on dbo.company. Is something like this possible?

Yes, by adding a UNIQUE constraint (or a unique index) on the company table, using these 2 columns:

ALTER TABLE company
  ADD CONSTRAINT CompanyID_DepartmentID_UQ
    UNIQUE (CompanyID, DepartmentID) ;

Then you can add the foreign key:

ALTER TABLE [user]
  ADD CONSTRAINT Company_User_FK
    FOREIGN KEY (CompanyID, DepartmentID)
    REFERENCES company (CompanyID, DepartmentID)  ;

Test at SQL-Fiddle

Minor comment: Since the table company has DepartmentID as the primary key, shouldn't it be called department table? (How do you call the table where the CompanyID is the primary key?)


Require assistance in writing a mysql query for the problem below I need help writing a query to display hotel id, hotel name and hotel type of
hotels which have not taken any orders in the month of 'MAY 19'. Sort the result based on hotel id in ascending order.

Assume we have two table hotel_details and orders.

I have tried the following code

select hotel_id,hotel_name,hotel_type from hotel_details
natural join orders
where order_id is null and monthname(order_date)="May"
group by(hotel_id);

You where on the right track;

natural joins are horrible because a) if there is no table structure, you can't read what they say (hence I'm guessing as the join critiera) b) people change the table structure in the future, and then the query is different.

A left join is required to match hotels where there is no orders. Your where order_id is null could never be true on the same row as monthname(order_date)="May" (if order_id is null, then order_date must be also).

As a left join criteria however, if there is no match, the order side of the result set is empty. Because of this properties we apply the month criteria here.

Grouping by hotel_id was correct.

Finally using having is a criteria based after the aggregation. As NULL isn't counted, the total count for the month must be 0.

select hotel_id,hotel_name,hotel_type
from hotel_details
left join orders
   on hotel_details.hotel_id = orders.hotel_id
   and monthname(order_date)="May"
group by hotel_id
having count(orders.order_id) = 0
order by hotel_id


Renaming a column in a materialized view with an unknown name I have a materialized view with an unknown column name, and I need to give that column a name. I can't just recreate the view because it took a long time to create.

dba=# \d temp_name_relationships;
Materialized view "temp_name_relationships"
  Column  |       Type       | Modifiers
----------+------------------+-----------
 id       | bigint           |
 source   | integer          |
 name1    | text             |
 name2    | text             |
 ?column? | double precision |
 sum      | bigint           |

That ?column? in there is the offender. How do I rename it? I've tried:

ALTER MATERIALIZED VIEW temp_name_relationships RENAME COLUMN ?column? TO score;
ERROR:  syntax error at or near "?"

I just remembered you can quote table names, and I guess this works to escape the question marks.

ALTER MATERIALIZED VIEW temp_name_relationships RENAME COLUMN "?column?" TO score;


SQL Server Failover Cluster Scalable by adding nodes? In the book Pro SQL Server Always On Availability Groups They write about scalability. Here is what they say

  Windows Server 2012 (and above), failover clustering supports up to 64
  cluster nodes. This helps to easily scale out the solution by adding
  nodes to it. For example, if you have a two-node failover cluster
  hosting applications that is getting close to hitting a node-specific
  capacity limit such as a CPU or memory, it is very easy to add a third
  node and redistribute the load.

How is that possible? If you have one instance on two nodes and you need to add memory. How will another node help? You can't load balance to 3 nodes. I completely understand running two instances ( one on each node) but then adding a 3rd will provide what?

It doesn't mean that it provides every type of scalability, such as for adding memory and CPUs to a given instance.  It means that you can scale up the overall environment by installing more instances that are spread across various nodes and have different possible owning nodes.


Current Date in where clause How do I put current date into the where clause without having to type it in all the time?

Select SALEITEMS.SKU,
       STOCK.SHORTDESC,
       SALEITEMS.SALEPRICEEX,
       SALEITEMS.COSTPRICEEX,
       SUM(((SALEITEMS.SALEPRICEEX - SALEITEMS.COSTPRICEEX) /
             SALEITEMS.SALEPRICEEX) * 100) AS GP
  From SALEITEMS
 Inner Join STOCK On SALEITEMS.SKU = STOCK.SKU
 where SALEITEMS.FP_DATE = '13 Nov 2018'
 group by 1,2,3,4

Use current_date:

where SALEITEMS.FP_DATE = current_date

This assumes that FP_DATE has the data type DATE .

If FP_DATE is a timestamp column, then the above will only return rows where the time part of the column is 00:00:00. To ignore the time part you could cast the column to a date value:

where cast(SALEITEMS.FP_DATE as date) = current_date

That however will not make use of an index on the column so it might not be fast enough. In that case the usual approach is to use a range query:

where SALEITEMS.FP_DATE >= current_date
  and SALEITEMS.FP_DATE < current_date + 1

current_date + 1 is "tomorrow" (at midnight)


ORA-01427:single-row subquery returns more than one row I wrote in PL/SQL Developer:

select * from lc_history2

and I got that error:

ORA-01427: single-row subquery returns more than one row

Can someone help?  It's necessary.

Without further information, I can only guess that lc_history2 is a view that somehow looks like

 select
   c,o,l,u,m,n,s
 from
   table where
     some_column = (select another_column
              from table
             where something = 'SOMEVALUE')

and the inner select returns more than one record for something = 'SOMEVALUE'


Fetching User Recent Status I am running mysql with MyISAM storage engine to store user status(user updates data).
To load user recent updates data i am using this query:

$limit = 20;
$uid = 3;
SELECT * FROM userstatus WHERE uid = {$uid} ORDER BY id DESC LIMIT {$limit}

but is the query is performance efficient, because i am ordering it descending by the status ID (which is in ascending order)?
SQL command to create the Table:

CREATE TABLE userstatus (
  id INT NOT NULL AUTO_INCREMENT,
  uid INT NOT NULL,
  status TEXT NOT NULL,
  datentime TIMESTAMP NOT NULL,
  visible INT (3) NOT NULL,
  statustype CHAR (16) NOT NULL,
  PRIMARY KEY (id)
);

So how the query will work? Is it go through and fetch all the posts of an user then order it DESC using the status ID and finally limit it to 20? is it ok, or any better way to query it?
Thanks

You have not defined any indexes, so this query would be slow even without sorting, because it needs a full table scan.

You should define a combined index on (uid, id). The first part helps in finding all rows WHERE uid = X. If these have been found, the second part can be used to sort the result set without the need to use filesort.

Use this create table statement

CREATE TABLE userstatus (
  id INT NOT NULL AUTO_INCREMENT,
  uid INT NOT NULL,
  status TEXT NOT NULL,
  datentime TIMESTAMP NOT NULL,
  visible INT (3) NOT NULL DEFAULT 0,
  statustype CHAR (16) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  INDEX (`uid`, `id`)
);

and see this and your query in action in this sqlfiddle.

If you are interested in being capable to do similar improvements on your own, you should start by learning to read the output of EXPLAIN.


How to design tables for dynamic payroll items I have to design a database for the payroll system. The requirements are as follows:

Admin can create payment items, such as Basic Salary, Overtime, Housing, Allowances at run time.
Each payment item will have its own calculation formula.
Some item's calculation formulas may depend on each other. For example, Housing is 20% of Basic.
Each employee will have different sets of payroll items.  For example an employee may have certain allowances that others don't have.
For each employee, some items may be processed monthly (e.g. salary), yearly (housing), anytime during the year(bonus).

The problem is that I cant see consistency between the items to create a suitable design. For example, Overtime takes monthly inputs (number of hours worked each month) to perform the calculations, while basic salary input is fixed.

Does anyone have any suggestions about how I could design this?

You could try to build a fully table-driven rules engine, sometimes also called an inference engine, but you would be much better off buying one than trying to build your own, because they are very complex.

If the cost and/or complexity of a fully table-driven rules engine is too high for your organization, then you can build a compromise solution.  The compromise involves mixing tables and procedural (code based) rules.  The objective is to optimize the balance between flexibility and maintainability.

The way to achieve this is to carefully examine all of your current rules, taking into consideration as much as possible any potential rules that aren't used now, but which might come into effect in the future.  This analysis should give you a set of rules for doing your payroll calculations.

You then need to break these rules down into two categories: (i) rules to be based in procedural code and (ii) rules to be table-driven.  The best way to do this is to keep scalars in tables and more complex rules in code.  For example the fact that one amount can be based on another amount is a good candidate for a code-based rule.  The fact that the derived amount is 20% of the base amount is a good candidate for a table-driven rule.

For practical purposes, your rule tables will have a structure that includes the following:

Rule Type: This is a value that tells your procedural code which function/code-based rule uses the scalar data in a particular record.
Scalar Value: This is a numeric value that has a meaning which is understood by the procedural code indicated by the Rule Type.
Business Identifier: This is an indicator of which business element the rule applies to.  Like the Scalar Value the Rule Type indicates which procedural code uses the rule record and that procedural code knows what to make of the Business Identifier.  It might be a department number, an employee ID, a job code or whatever else your rules require to link payroll amounts to employees.  Your rules table may need more than one Business Identifier, depending on how your rules work, or your requirements might dictate that business identifiers are normalized out into an intersection table.
Effective Date: One thing you can bet on is that your payroll scalars will change over time.  Your system is going to need a record of what the rules used to be so you can make corrections to your payroll.  Also, for the sake of the sanity of your HR and IT staff, you need to be able to record new rules before they go into effect.
Expiry Date: This completes the effective date range of the rule.  I'd recommend using a max collating value so that every record has a fully qualified effective range.  That will make your queries much simpler.

One of the realities of a solution like this is that you will have to come back to build new code-based rules from time to time as your organization's payroll policies change.  Don't be discouraged by this.  Even if you had a fully table-driven rules engine, changing payroll policies would entail making changes to the rules data.  Business changes entail system changes - that's a fact of life.


SQL Server 2008 log files have minimum sizes, what gives and how do I make them smaller? Ok, to start with, I screwed up when I created the databases, using a create script roughly like so: (artificial linebreaks and names/paths for wrapping purposes)

CREATE DATABASE [EXAMPLE] ON  PRIMARY
( NAME = N'EXAMPLE_Data', FILENAME = N'J:\SQLServer2008\MSSQL.INSTANCE\EXAMPLE.mdf',
    SIZE = 446046KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON
( NAME = N'EXAMPLE_Log', FILENAME = N'J:\SQLServer2008\MSSQL.INSTANCE\EXAMPLE.ldf',
    SIZE = 664505KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

because I scripted it out from an existing development database, and I just wanted to get something going.  I screwed up when I didn't change my sizes to something reasonable (like 4096KB) and so now I can't shrink the logfile below roughly 600MB.

I know where I went wrong, but how do I fix it easily?

Detach the database.
sp_detach_db @dbname = 'EXAMPLE'

Physically delete the log file from disk (this is crucial, but can be risky - see mrdenny's comment below).

Attach the database using the sp_attach_single_file_db stored procedure.
sp_attach_single_file_db @dbname = 'EXAMPLE', @physname = N'J:\SQLServer2008\MSSQL.INSTANCE\EXAMPLE.mdf'


Why are some aggregates treated differently in the RHS of a rule in the model clause? With my limited understanding of the model clause, I'd expected the following three queries to return the same results because there are no nulls in the data, and the functions are equivalent:

select *
from (select level k, 100 v from dual connect by level<=2)
model return updated rows
dimension by (k)
measures (v, 0 shr)
rules ( shr[any] = v[cv()]/sum(v)[any] );  --plain sum
/*
         K          V        SHR
---------- ---------- ----------
         1        100        0.5
         2        100        0.5
*/
select *
from (select level k, 100 v from dual connect by level<=2)
model return updated rows
dimension by (k)
measures (v, 0 shr)
rules ( shr[any] = v[cv()]/nullif(sum(v)[any],0) ); --with nullif
/*
         K          V        SHR
---------- ---------- ----------
         1        100       0.25   <------\___ why?
         2        100       0.25   <------/
*/
select *
from (select level k, 100 v from dual connect by level<=2)
model return updated rows
dimension by (k)
measures (v, 0 shr)
rules ( shr[any] = v[cv()]/decode(sum(v)[any],0,null,sum(v)[any]) );  -- with decode
/*
         K          V        SHR
---------- ---------- ----------
         1        100        0.5
         2        100        0.5
*/

What am I missing about the processing of rules that explains this behaviour?

SQLFiddle here

In the Oracle Database SQL Language Reference one can read

  NULLIF(expr1, expr2)
  ...
  The NULLIF function is logically equivalent to the following CASE expression:
      CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

but Oracle 11.2.0.3 on Linux gives the following

SQL&gtselect *
  2  from (select level k, 100 v from dual connect by level&lt=2)
  3  model return updated rows
  4  dimension by (k)
  5  measures (v, 0 shr)
  6  rules ( shr[any] = CASE WHEN sum(v)[any] = 0 THEN NULL ELSE sum(v)[any] END);

         K          V        SHR
---------- ---------- ----------
         1        100        200
         2        100        200

SQL&gt select *
  2  from (select level k, 100 v from dual connect by level&lt=2)
  3  model return updated rows
  4  dimension by (k)
  5  measures (v, 0 shr)
  6  rules ( shr[any] = nullif(sum(v)[any], 0));

         K          V        SHR
---------- ---------- ----------
         1        100        400
         2        100        400

SQL&gt

Therefor this is (at least a documentation :-) bug in 11r2

One gets the same results in Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production


SQL Server 2014 Authentication I recently installed SQL Server 2014 in my machine. I configured my user and provided high level access for the same. While connecting through SSMS 2014; if I choose 'Windows Authentication', I can able to connect to database engine successfully. However if I choose to connect through 'SQL Server Authentication' and provide my username/password; I'm getting Error: 18456.

The purpose of me trying this option is - I wanted to try out subscriptions for my SSRS report. Since I opted for 'Windows Authentication' in my report settings I couldn't able to do subscriptions for my reports.

Any suggestions will be very useful. Thanks.

A way of knowing why is this error happening would be to check the SQL Server log and look for details into why is this happening:

Try to login again with SQL Server Authentication, after getting your Error 18456 change to Windows Authentication and in your Object Explorer go to: Management -> SQL Server Logs -> Current - XX/XX/XXXX XX:XX:XX
Now search for your newly logged error and identify the error State

With this State number assest the nature of the error using the following table:

As you can see in this example I failed to login because my UserID is incorrect:

Also make sure you have hibrid authentication. Check the server properties from the Object Explorer and go to Security section and make sure Server Authentication is set to SQL Server and Windows Authentication mode

More detail


MySQL Drive Storage Allocation In our production servers for SQL Server, we usually split our drives by Software, Logs, Data, and Backups. I've been assigned to help with a MySQL installation (On Windows Server) and I'm not as experienced in setting up the environment with it. We are working with 500 GB.

Are there best practices with the drive assignments? Or does MySQL best manage everything on a single drive? Most of what I find on the web has to do with configuring InnoDB and Partitioning.

YOSHINORI MATSUNOBU'S BLOG would be the place to start. He recommends splitting up files that do sequential writes from files that do random reads and writes.

Random i/o oriented:

Table files (*.ibd)
UNDO segments (ibdata)

Sequential write oriented:

REDO log files (ib_logfile*)
Binary log files (binlog.XXXXXX)
Doublewrite buffer (ibdata)
Insert buffer (ibdata)
Slow query logs, error logs, general query logs, etc

I have referred to this blog in some of my older posts

Jul 29, 2015 : Should the Redo log on a production MySQL server be configured to another location?
Jan 27, 2015 : what would be the primary disadvantages of splitting my tables into table spaces and/or separate schemata
Jun 04, 2014 : Can I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server?
Feb 06, 2014 : MySQL on SSD - what are the disadvantages?
Aug 14, 2013 : How do I determine how much data is being written per day through insert, update and delete operations?

Since you are doing this in Windows, you really need to split up the InnoDB files. Just remember when specifying a Drive to use drive letter, colon, and forward slash or double backslash. See my answer to Change existing datadir path for an example.


What effect does serializable isolation level have on DDL-statements? I'm using Red Gate SQL Compare to create a release script based on differences between SVN and a database. This results in a script containing a bunch of table- and procedure-changes and it works fine. However, one thing puzzles me, it's using transaction isolation level serializable.

I know what it does to dml-statements, but I'm not sure what it means for ddl. Can someone enlighten me, perhaps with an example?

I know what it does to dml-statements, but I'm not sure what it means for ddl.

Generally speaking, not much.

DDL encompasses a huge range of operations (many of which have their own special behaviours), but most DDL will take a schema modification (Sch-M) lock on the underlying object, either for the duration of the command, or at a critical stage (e.g. at the end of most online index builds). This is the most restrictive type of lock, preventing any sort of concurrent access, at any isolation level.

Even where Sch-M is not taken, the engine is careful to take the right locks at the appropriate times to ensure the end result is correct. For example, it is not possible to build an index that does not exactly reflect the underlying data.

All of the above applies regardless of any session isolation level specified by the user.

As far as changes to metadata (i.e. the system structures underlying views like sys.tables) are concerned, while SQL Server ensures correct changes automatically, user access is only fully supported and guaranteed to be honoured at the default locking READ COMMITTED isolation level.

Quoting from Metadata Access, Isolation Levels, and Lock Hints (emphasis added):

  SQL Server does not guarantee that lock hints will be honored in queries that access metadata through catalog views, compatibility views, information schema views, metadata-emitting built-in functions.

  Internally, the SQL Server Database Engine only honors the READ COMMITTED isolation level for metadata access. If a transaction has an isolation level that is, for example, SERIALIZABLE and within the transaction, an attempt is made to access metadata by using catalog views or metadata-emitting built-in functions, those queries will run until they are completed as READ COMMITTED. However, under snapshot isolation, access to metadata might fail because of concurrent DDL operations. This is because metadata is not versioned. Therefore, accessing the following under snapshot isolation might fail:

  Catalog views
  Compatibility views
  Information Schema Views
  Metadata-emitting built-in functions
  sp_help group of stored procedures
  SQL Server Native Client catalog procedures
  Dynamic management views and functions

From the same page:

  The following table provides a summary of metadata access under various isolation levels.

Despite not being guaranteed or supported, it can be important to access metadata (e.g. catalog views, DMVs) under READ UNCOMMITTED isolation to avoid blocking issues. Most tools and diagnostic scripts make heavy use of READ UNCOMMITTED isolation (often using the synonymous but misleadingly-named NOLOCK hint). For an example, see Glenn Berry's Diagnostic Queries.

As an example of READ UNCOMMITTED access not being honoured, see Metadata functions should follow same isolation semantics as metadata queries on Connect and Bad habits : Using (certain) metadata "helper" functions by Aaron Bertrand.

Accessing metadata at an isolation higher than read committed is not even honoured - the information will be read with READ COMMITTED isolation semantics.

If I had to guess, I would say the comparison tool in the question sets isolation to SERIALIZABLE to cover any DML in the generated script.


How can "HierarchyID" type work when "CLR" is disabled? The following statement sp_configure 'clr enabled' returns:

name        minimum maximum config_value    run_value
clr enabled 0       1       0               0

I assume this means there are no CLR objects; however if I execute the following statements I get these:

SELECT * FROM sys.assembly_files

SELECT * FROM sys.assembly_types

I have read that The HierarchyID type is available to CLR clients as the SqlHierarchyId data type..

How can it be used without having CLR enabled and why do I need to enable it to create my own CLR objects?

The clr enabled server configuration option only controls whether user assemblies can be run by the SQL Server instance.

The hierarchyid, geometry and geography types are system CLR types. These are contained in system assemblies, so are available regardless of the clr enabled setting.

Similarly, other system features that rely on CLR integration, like FORMAT (SQL Server 2012 onward) are available even if clr enabled is off.

If you need to manipulate hierarchyid data in a custom CLR routine, you will need to enable user clr assemblies via this setting.

See also: Hierarchical Data (SQL Server)


Copy indexes from one table to another I have a series of ETL jobs in which I create a swap table using CREATE TABLE table1_swap LIKE table1. In order to make the populating of table1_swap faster I do not include the indexes. When I'm finished loading however I need to re-apply those indexes to the newly populated table. These indexes are created outside of the scope of these ETL jobs so I would rather not have to hardcode the CREATE INDEX calls if I don't have to.

Is it possible to "transfer" or "copy" a set of indexes from one table to another?

You can retrieve the complete DDL statement for each index from the Postgres system catalogs.

The following will retrieve all CREATE index statements for a single table:

select pg_get_indexdef(idx.oid)||';'
from pg_index ind
  join pg_class idx on idx.oid = ind.indexrelid
  join pg_class tbl on tbl.oid = ind.indrelid
  left join pg_namespace ns on ns.oid = tbl.relnamespace
where tbl.relname = 'your_table_name'
   and ns.nspname = 'your_table_schema';

You can spool the output into a script and run it after you have copied the data and swapped the table names.


Mongodb crash after WiredTiger Cannot allocate memory error I am using MongoDB (3.2.6) as storage for my application and since a couple of weeks MongoDB started to crash usually every 1 or 2 weeks. The crash seems to come from an issue during WiredTiger memory allocation here is the full stacktrace of the last crash:

2016-10-09T18:25:36.389+0200 E STORAGE  [thread1] WiredTiger (12) [1476030336:325695][993:0x7f559c19a700], file:index-1108-8065571460375661294.wt, WT_SESSION.checkpoint: memory allocation of 18624 bytes failed: Cannot allocate memory
2016-10-09T18:25:36.547+0200 E STORAGE  [thread1] WiredTiger (12) [1476030336:422567][993:0x7f559c19a700], file:index-1108-8065571460375661294.wt, WT_SESSION.checkpoint: checkpoints cannot be dropped when in-use: Cannot allocate memory
2016-10-09T18:25:37.347+0200 I COMMAND  [conn10793] command admin.$cmd command: ping { ping: 1 } keyUpdates:0 writeConflicts:0 numYields:0 reslen:37 locks:{} protocol:op_query 208ms
2016-10-09T18:25:37.386+0200 E STORAGE  [thread1] WiredTiger (12) [1476030337:254969][993:0x7f559c19a700], checkpoint-server: checkpoint server error: Cannot allocate memory
2016-10-09T18:25:38.448+0200 E STORAGE  [thread1] WiredTiger (-31804) [1476030337:484838][993:0x7f559c19a700], checkpoint-server: the process must exit and restart: WT_PANIC: WiredTiger library panic
2016-10-09T18:25:38.677+0200 I -        [conn10894] Fatal Assertion 28559
2016-10-09T18:25:38.994+0200 I -        [WTJournalFlusher] Fatal Assertion 28559
2016-10-09T18:25:38.994+0200 I -        [TTLMonitor] Fatal Assertion 28559
2016-10-09T18:25:40.978+0200 I -        [thread1] Fatal Assertion 28558
2016-10-09T18:25:41.207+0200 I -        [thread1]

The server it's running on has 7Gb RAM and is exclusively used for Mongo.

free -m
             total       used       free     shared    buffers     cached
Mem:          6811       6140        670          0         28        634
-/+ buffers/cache:       5478       1332
Swap:         2047       1009       1038

Several dbs are running on it with a total size around 925MB while WiredTiger cache status is :

db.serverStatus().wiredTiger.cache
"bytes currently in the cache" : 969927022,
"bytes read into cache" : 800513540,
"bytes written from cache" : 1349774879,
"maximum bytes configured" : 3221225472,
[...]

According to the documentation, WiredTiger should use by default

  60% of RAM minus 1 GB, or [...] https://docs.mongodb.com/manual/faq/storage/#to-what-size-should-i-set-the-wiredtiger-cache

Based on 7GB RAM we get ~ 3.2Gb so it seems good.

Now how can 925Mb databases can bust a 3.2Gb cache ? I know that we have to take the indexes into account but even by indexing every fields (which is not the case) it should not fill the 2+ Gb remaining ? Last crashed occurred yesterday so the provided stats are just after a fresh restart. Could it be that the way I'm using Mongo a memory leak occurs somewhere slowly increasing the memory consumption ? I may completely miss the point as I am new to Mongo so any leading advice or article would be a relief.

I found this issue : https://jira.mongodb.org/browse/SERVER-24408 but it does not provide any further explanations.

EDIT 12/10:

3 days later the cache status is the following :

"bytes currently in the cache" : 1319874144,
"bytes read into cache" : 1054001990,
"bytes written from cache" : 8936911271,

Potentially the unix box is forking the process from your application and causing a duplicate process to try to allocate the same amount of memory but crashing when it does so.


Transactional replication failure handling Web Application is High school record management system (HSRMS). Users of the application are

High school management (Normal Users)
Data verifying users (Admin)

User group 2 will allow the User group 1 to submit data periodically and then user group 2 will validate it and ask for corrections. Hope the system flow is quite clear.

Replication Plan (Proposed)

I have setup a proposed transactional replication as

Publisher (Server A)
Distributor (Server B)
Subscriber (Server(s) i.e C,D,E etc).

Note: All the servers will be in the same area/location.

What I understood is that the publisher should be the database server which should be connected to the HSRMS application, the distributor contains the transactions(updates) and is responsible to carry forward to subscribers, but I need to record a server status on any agent which should notify the sysadmin to update their DB server address in case of server failure (due to any reason) and the subscriber must be responding meanwhile.

Is my approach and understanding correct or I need to dive deep into it?
Is there any mechanism to keep the application server aware of the failure and act accordingly?
Dynamic Server connection upon failure of any db server? I.e Instead of sys admin the server connection should be automatically mapped to the active running db server?
What could be the role of subscribers if the application is just connected to the Server A i.e Publication for updates? Are subscribers there only for the DB backup and will not interact with the web application?

Yes, your understanding is correct. Usually, the Publisher and Distributor are the same servers.
Yes, there are jobs that are automatically created that notify you when the replication is down. After creating the replication, check your SQL Server Agent and see the new jobs. You can alter the schedule and the notification properties. But you can create your own job that checks some latest record in two instances if they have linked server to one another.
That one I think you will have to set it up by the application.
Subscribers can get queries and read operations, that will let you the publisher with less I/O and tend to be faster. Updates will work though, so you will have to treat it so that the user knows that it may not persist.


What are the "GDR" vs "QFE" hotfixes for SQL Server 2008 R2 SP3? I'd always assumed that higher patch numbers were necessarily later releases, but now I'm not so sure.

Looking at the SQL Server Build List for SQL Server 2008 R2 SP3, there are two possible ways to apply security update MS15-058, one labeled "GDR", and one labeled "QFE".

What's the difference between these?

According to this blog entry, the GDR (general distribution release) are the ones we should all be installing, and the QFEs are the "only if you have this specific issue" releases, or something like that?

If I don't need TLS1.2 support, should I apply the 10.50.6220 patch? Or the 10.50.6529 patch?

To understand the jargons, Aaron Bertrand has a #BackToBasics blog post

Also, refer to this decision matrix for TLS 1.2 support or MS KB - TLS 1.2 support for Microsoft SQL Server

10.50.6220 => SQL Server 2008R2 SP3 GDR

10.50.6529 =>  SQL Server 2008R2 SP3 QFE

From the decision matirx, if you dont want TLS 1.2 support, dont install 10.50.6542. TLS 1.2 support got introduced in 10.50.6537.

So to answer your question which one to install ... it will be 10.50.6220.

As always, test out your application behavior when installing a new fix.


How to split a string on every 2nd delimiter? I have curves consisting of X/Y points (an array?) stored in a string with the same delimiter. There is 1 curve per row. Below is a simple example:

0,0,1,1,2,1.9,2.9,2.8,3.6,3.5

I want to extract each curve into a #table so I can edit them and ultimately put them back.

X,Y
0,0
1,1
2,1.9
2.9,2.8
3.6,3.5

A curve could have anywhere from a dozen to hundreds of values, and the points may not be ordered.

What is the cleanest/simplest way of doing this?

I created a table value function (taken from here) to create an ordered split of your string.

CREATE FUNCTION dbo.SplitStrings
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS @t TABLE([Index] INT IDENTITY(1,1), Item NVARCHAR(255))
AS
BEGIN
    INSERT @t(Item) SELECT SUBSTRING(@List, Number,
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
        FROM sys.all_objects) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ORDER BY Number OPTION (MAXDOP 1);

    RETURN;
END
GO

I then used that TVF in the following code

Declare @X table (string varchar(50))
insert into @x (string) values('0,0,1,1,2,1.9,2.9,2.8,3.6,3.5')
SELECT s1.Item AS colx
    ,s2.item AS coly
FROM @x AS x
CROSS APPLY dbo.SplitStrings(x.string, ',') AS s1
CROSS APPLY dbo.SplitStrings(x.string, ',') AS s2
WHERE s2.[Index] = s1.[Index] + 1
    AND s1.[index] % 2 = 1
    AND s2.[Index] % 2 = 0

The code passes in your string into SplitString TVF using CROSS APPLY.  We use TWO CROSS APPLY's.  One for column X (alias s1) and one for column Y (alias s2).

Each table (s1 and s2) have the exact same rows ordered.  I'm basically using the logic that you commented on in your question.

  Hmmm could i split on each value and use use odd/even row numbers to split into x,y?

We know the X sequence values are odd and the Y sequence values are even.  The logic is making sure the s2 index (which represents the Y-column) is +1 from the s1 row (which represents the X-column).

WHERE s2.[Index] = s1.[Index] + 1

We want to make sure the s1 rows are only the odd ones by using the modulus     operator (%).  If the remainder of the s1.index / 2 = 1, we have an odd row

AND s1.[index] % 2 = 1

We want to make sure the s2 rows are only the even ones by using the modulus operator (%).  If the remainder of the s1.index / 2 = 2, we have an even row

AND s1.[index] % 2 = 0

It seems to work on your sample data.

| colx | coly |
|------|------|
| 0    | 0    |
| 1    | 1    |
| 2    | 1.9  |
| 2.9  | 2.8  |
| 3.6  | 3.5  |


Query column A, order by B - is index needed if A and B together are the PK? I have a table (in Postgresql) which has a primary key on columns a and b. I want to do a query:

select * from table where a='XXX' order by b;

Does the existing primary key serve to help with the performance of that query?

Can I improve performance by making one or more other indexes eg. an index on a and/or an index on b?

No, if you already have an index on (a, b) and the PK constraint means you do, then you don't need anything else for this query.

Separate indexes on (a) and (b) would not be useful.

The only thing that might be better than the index on (a, b) would be a covering index. Say you have a query with only SELECT b, c, d columns in the select list while the table has some 10+ columns. Then, an index on (a, b) INCLUDE (c, d) or in lack of that on (a, b, c, d) would be much narrower than the table and would suit the query perfectly. It would result in a better query plan (only an inde seek, no access to the table needed), on the expense of course that you have an additional index targeted for the specific query.


Message file not found when starting SQLPlus I've installed Oracle XE 11.2 on Fedora 18 but have trouble starting SQLPlus. It quits with the following error:

Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

All of the environment variables are properly set (copied from installation guide)

# ~/.zshrc
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe # It is in fact installed there
ORACLE_SID=XE
NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
PATH=$ORACLE_HOME/bin:$PATH

NLS_LANG is AMERICAN_AMERICA.AL32UTF8. As far as I understand Oracle ignores system language related variables but here they are anyway:

LC_CTYPE=en_US.UTF-8
LANG=en_US.UTF-8

Any suggestions?

This error usually occurs if you are executing sqlplus as a different user (other than the one which installed Oracle software), due to permission issues

Check Oracle Note: 356850.1


How to set Cassandra volumes? I have started a Cassandra server using Bitnami AMI, I have attached volumes to the EC2 instance, but how would I set Cassandra to use the attached volume?

commitlog_directory and data_file_directories (use this link if you are on 3.0+) will tell Cassandra where to look for commitlog and data files (there are a few other directories you might be interested in, search through the documentation page for 'director'). However, your volumes have to be mounted first to be able to put any directories onto them.

How to mount them is not really a DB question, though.  It is described in any case in the EBS documentation.


Find queries executed by an user in sql server I want to find all the queries executed by an particular user in a database over a period of time.

I learnt about trace and XEvents, but these are not setup yet, Will i be still able to get the queries executed by an particular user?

You would need to start logging account activity of accounts that you don't trust to a table for a complete list.  All of the other options (like querying dmvs) will give you temporal data and there's no guarantee that it is complete.

Here is an example of what you are looking for but consider creating a job or something that logs these results to a table that only you have access to view or even see.  You could put a filter that only stores accounts that you are wanting to monitor(check out the second where clause that's commented out) if storage is an issue.

This queries session info, although you might want to only select the columns that you are interested in like Database Name, login_name, Loginin_Time, and Query.

/******************************************************
Script : Findout Who did what ?
Author : Kin Shah .. written for dba.stackexchange.com
*******************************************************/

USE master
go
SELECT sdest.DatabaseName
    ,sdes.session_id
    ,sdes.[host_name]
    ,sdes.[program_name]
    ,sdes.client_interface_name
    ,sdes.login_name
    ,sdes.login_time
    ,sdes.nt_domain
    ,sdes.nt_user_name
    ,sdec.client_net_address
    ,sdec.local_net_address
    ,sdest.ObjName
    ,sdest.Query
FROM sys.dm_exec_sessions AS sdes
INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id
CROSS APPLY (
    SELECT db_name(dbid) AS DatabaseName
        ,object_id(objectid) AS ObjName
        ,ISNULL((
                SELECT TEXT AS [processing-instruction(definition)]
                FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
                FOR XML PATH('')
                    ,TYPE
                ), '') AS Query

    FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
    ) sdest
where sdes.session_id <> @@SPID
--and sdes.nt_user_name = '' -- Put the username here !
ORDER BY sdec.session_id


How can I use expressions as arguments to EXEC? I am building some scripts and found an annoyance to be that I can't provide expressions as arguments to EXEC.

Here is an example I have run into. I want to setup various items using a consistent format that includes the database name. I can't simply concatenate the strings when passing an argument

EXEC msdb.dbo.sp_help_schedule
    @schedule_name = 'FullBackup_'+@DatabaseName,
    ...

instead I have to declare a variable just for the final string and pass that...

DECLARE @ScheduleName varchar(100)
SET @ScheduleName = 'FullBackup_'+@DatabaseName
EXEC msdb.dbo.sp_help_schedule
    @schedule_name = @ScheduleName,
    ...

I have built up a command string dynamically when necessary which could achieve this but would rather not to do that every time. Is this a limitation in t-sql or is there a way around it?

Yes, this is a limitation in T-SQL. There are several scenarios where you can't build up a string dynamically, but rather have to do so beforehand.

For example:

RAISERROR('String' + @variable,1,0);
EXEC dbo.procedure @param = 'String' + @variable;

There are other cases where it is valid, but usually just for assignment, not for passing or doing anything constructive with the result, e.g.

DECLARE @var VARCHAR(32) = 'String' + @variable;

T-SQL is just a quirky language with a lot of parsing peculiarities and odd rules. I think it's just the nature of a long-evolving language with different folks in charge of implementation at different times (made more obvious by their changing stance on adhering to things like the ANSI standard).


Choosing good indexes for my queries I have a table which looks like this:

Messages table:

Message_ID (BIGINT) PK auto_increment
FromUser (INT 16)
ToUser (INT 16)
DateCreated (Timestamp)
MessageText (Varchar (500) )
HasRead (TINYINT (0=false, 1=true) )

The queries which will be run will include:

SELECT *
FROM messages
WHERE FromUser = '10000000'
ORDER BY DateCreated DESC
LIMIT 10;

SELECT *
FROM messages
WHERE ToUser = '10000000'
ORDER BY DateCreated DESC
LIMIT 10 ;

UPDATE messages
SET HasRead = 1
WHERE Message_ID = '123456789'; // When a message has been read update it to show it has been read.

As a result I have created the following indexes:

Message_ID (primary index)
(FromUser,DateCreated) BTREE DESC
(ToUser,DateCreated) BTREE DESC

I have the following conclusions:

The MessageID primary index is used for the update operation.
The composite FromUser andDataCreated index are is used to get all the messages from a user. My question here is if this is a proper index? Can I just include FromUser or do I need both if I am also sorting (User can have a lot of messages)? If I drop DateCreated using explain I can still see it uses this index, but then it seems to use filesort to sort; is this efficient or is it better to have a larger index and use the index to get the sorted values?
The composite ToUser and DataCreated index are is used to get all the messages  a user sent. My question here again is if this is a proper index? Can I just includeToUser or do I need both if I am also sorting (User can have a lot of messages)? If I drop DateCreated using explain I can still see it uses this index, but then it seems to use filesort to sort; is this efficient or is it better to have a larger index and use the index to get the sorted values?

These are chat messages so they will accumulate quickly. Would it be wise to partition as well? If so would what would be a good partition strategy? If I partition a range on from users it will work great as a users messages will be stored on the same partition but to users won't be on the same partition. Vice versa also, so I can't find a good partition key

The questions:

Your indexes are appropriate for these queries and your conclusions are correct.
About partitions, they are rarely useful for performance. I don't think there is any point in using them in your case.

Minor remarks:

The DESC doesn't mean anything in that place. You can (try to) define a composite index with 4 ways:

(FromUser ASC, DateCreated ASC)
(FromUser ASC, DateCreated DESC)
(FromUser DESC, DateCreated ASC)
(FromUser DESC, DateCreated DESC)

but MySQL does not supoort DESC indexes and will silently ignore these ASC and DESC. The index created will be the (ASC, ASC) one.

Nothing to worry though as the indexes can and will be used for the queries you have. Indexes can be read fowards or backwards. The index would not be very efficient if you had (for example) a query with:

ORDER BY FromUser ASC, DateCreated DESC

where an (ASC, DESC) index would be better.


WITH statement does not persist outside of query I run a select to check what records I should register in another table.

Each record returned in the query I should give an insert in my table campanha_cliente_chamada.

WITH psql as ( /* stuff */ )
SELECT * FROM psql;

WITH disca ( /* reads from psql */ )
INSERT INTO callcenter.campanha_cliente_chamada ( columns )
SELECT col1, col2, col3
FROM   disca;

This is the error I'm getting.

Error : relation psql does not exist LINE 11: ( SELECT
id_camapanha_cliente FROM psql),(SELECT c...
                          ^

You can actually chain CTEs:

WITH psql as ( /* stuff */ ),
disca as ( SELECT * FROM psql )
INSERT INTO callcenter.campanha_cliente_chamada ( columns )
SELECT col1, col2, col3
FROM   disca;

... which may help you.


Is there a simple way in PL/pgSQL to check if a query returned no result? I'm currently experimenting a bit with PL/pgSQL and want to know if there is a more elegant way to do something like this:

select c.data into data from doc c where c.doc_id = id and c.group_cur > group_cur order by c.id desc limit 1;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        select c.data into data from doc c where c.doc_id = id and c.global_cur > global_cur order by c.id desc limit 1;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                RETURN NULL;

Exception blocks are meant for trapping errors, not checking conditions. In other words, if some condition can be handled at compile time, it should not be trapped as error but resolved by ordinary program logic.

In Trapping Errors section of PL/PgSQL documentation you can find such tip:

  Tip: A block containing an EXCEPTION clause is significantly more
  expensive to enter and exit than a block without one. Therefore, don't
  use EXCEPTION without need.

Instead using exceptions (bad), or IF/THEN/ELSIF (better), you can rewrite this to one query:

SELECT c.data into data
FROM  doc c
WHERE c.doc_id = id
  and (
    c.group_cur > group_cur
    or
    c.global_cur > global_cur
  )
ORDER BY
  -- this will make group always preferred over global
  case when c.group_cur > group_cur then 1 else 2 end ASC,
  -- and this is your normal ordering
  c.id DESC
limit 1;

If you really want two queries, you can use special FOUND variable to test if previous query gave any result:

select c.data into data
from doc c
where c.doc_id = id and c.group_cur > group_cur
order by c.id desc limit 1;
if not found then
    select c.data into data
    from doc c
    where c.doc_id = id and c.global_cur > global_cur
    order by c.id desc limit 1;
    if not found then return null; end if;
end if;

Obligatory RTFM links folllow :-)

See this for description of FOUND variable, and this for IF/THEN blocks.


How to get the ID of the conflicting row in upsert? I have a table tag with 2 columns: id (uuid) and name (text). I now want to insert a new tag into the table, but if the tag already exists, I want to simply get the id of the existing record.

I assumed I could just use ON CONFLICT DO NOTHING in combination with RETURNING "id":

INSERT INTO
    "tag" ("name")
VALUES( 'foo' )
ON CONFLICT DO NOTHING
RETURNING "id";

But this returns an empty result set, if the tag with the name "foo" already exists.

I then changed the query to use a noop DO UPDATE clause:

INSERT INTO
    "tag" ("name")
VALUES( 'foo' )
ON CONFLICT ("name") DO UPDATE SET "name" = 'foo'
RETURNING "id";

This works as intended, but it is somewhat confusing, because I'm just setting the name to the already existing value.

Is this the way to go about this problem or is there a simpler approach I'm missing?

This will work (as far as I tested) in all 3 cases, if the to-be-inserted values are all new or all already in the table or a mix:

WITH
  val (name) AS
    ( VALUES                          -- rows to be inserted
        ('foo'),
        ('bar'),
        ('zzz')
    ),
  ins AS
    ( INSERT INTO
        tag (name)
      SELECT name FROM val
      ON CONFLICT (name) DO NOTHING
      RETURNING id, name              -- only the inserted ones
    )
SELECT COALESCE(ins.id, tag.id) AS id,
       val.name
FROM val
  LEFT JOIN ins ON ins.name = val.name
  LEFT JOIN tag ON tag.name = val.name ;

There's probably some other ways to do this, perhaps without using the new ON CONFLICT syntax.


Find procedures that haven't been called in  days We are deleting old stored procedures and tables.

How can I know what procedures haven't been called recently?

dm_exec_procedure_stats and dm_exec_query_stats aren't reliable, since they only return procedures in the plan cache.

If sys.dm_exec_procedure_stats is not reliable for you (probably more because the information doesn't survive restarts than anything to do with the plan cache), SQL Server doesn't keep track of this in any other way.

The only way to do this would be to add logging to your stored procedures (or to the app that calls them, if that is feasible and inclusive enough), or to run a very targeted server-side trace perpetually and review the trace.

Also note that just because a procedure hasn't been called in a week doesn't mean it won't be called tomorrow. You could have reporting procedures that are only called monthly or annually or some obscure operation that doesn't happen very often. Deleting that stored procedure could be disastrous days or weeks from now, potentially beyond any backup you have at the time (and assuming you aren't following best practices and storing your stored procedures in source control).

The safest way, IMHO, is to rename stored procedures (maybe with a zzz_ prefix so they sort to the bottom of any lists) that you've already identified through other means as potential candidates of being "too old" - then at least when you do that to one inadvertently and something breaks, it is easy enough to rename it again, restoring functionality without having to scrounge for old code in backups. Only delete the procedures when a full business cycle has passed and nobody has complained.


How to tackle a large undocumented database I recently got hired as the only IT Guy in a certain Company X and I am tasked to fix their applications, and in my opinion, the best way to start is by understanding the database.

Their current database is a MySQL database with 186 tables(note that some tables are empty for god knows why). And the application is communicating with the database through an MS Access database interface. (I ask myself why the developers did that too)

Question is, How do I start tackling this large undocumented database? Yes, it is undocumented because the application's developers aren't willing to give me an ERD or data dictionary or any information at all about the database to make my life easy. How would you suggest to take on this perilous endeavor of understanding every nook and cranny of the rather large database?

Related Question: How to dive into an ugly database?

The linked answer tackles the problem bottom-up, database first. As your responsibilities encompass the applications and database, I'd be inclined to attack this top-down starting at the application(s).

Focus your attention on understanding the most frequently used features of the application through consultation with the user base. Trace the database interactions of those features through profiling/logging tools so you can identify the key tables and procedures.

This way your early efforts are constrained to the "stuff that matters", rather than wasting time documenting tables and queries that may be rarely or never used. The focus should also bring the Pareto Principle to bare on your bug fixing efforts (so says Microsoft anyway).


How do I remove partitioning? There is a table my_table which has not been created any partition in MySQL 5.6. And I'm trying to create partitions by procedure in my_table, when I run this code:

alter table my_table remove partitioning

I'm getting this error:

1505 - Partition management on a not partitioned table is not possible

But when I run above code in MySQL version 5.5, this code can be run.

If my_table has been created partitions, and when I run above code, not only MySQL 5.5 can run, but also MySQL 5.6 can run too.

I guess that if this code can be run, MySQL 5.6 need to be config some files.But I am not able to why.

Can you solve it?

I'm not sure I would use the mechanism that you're using to purge old data, but since that's what you want to do, there is a way to work around this.

What you are seeing appears to be an undocumented incompatible change in behavior between MySQL 5.5 and MySQL 5.6 -- although, arguably, the real problem is that the statement should not be valid in MySQL 5.5 either when the table is not partitioned.

I confirmed the behavior you are seeing with MySQL 5.5.30 (no error) and 5.6.10 (error).

  I'm trying to create partitions by procedure

Do you mean in a Stored Procedure?

If that's the case, then you could use a CONTINUE HANDLER specifically written to allow your stored procedure to ignore that one specific error code.

The following works in both MySQL 5.5 and 5.6.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`test_continue_handler` $$
CREATE PROCEDURE `test`.`test_continue_handler` ()
BEGIN

  -- you may have some code that executes before the ALTER TABLE, here

  BEGIN
    DECLARE CONTINUE HANDLER FOR 1505 BEGIN END;
    ALTER TABLE t1 REMOVE PARTITIONING;
  END;

  SELECT 'that worked okay' AS message; -- remove this, your usual code after the ALTER TABLE goes here

END $$

DELIMITER ;

mysql> call test_continue_handler;
+------------------+
| message          |
+------------------+
| that worked okay |
+------------------+
1 row in set (0.06 sec)

Query OK, 0 rows affected (0.07 sec)

The BEGIN END; after 1505 is telling the server "when this error occurs, run the code that appears in this empty block instead of throwing an error."  There's no code between BEGIN and END but MySQL doesn't care and this results in suppressing error 1505.

The BEGIN and END; before "DECLARE..." and after "ALTER..." limit the scope of that CONTINUE HANDLER to only the single ALTER TABLE so that any other statements throwing that same error code later in your stored procedure (or in any procedure called by your procedure) will not be ignored.  Note this does not mean statements after the ALTER TABLE but before the next END will not be executed if an error occurs -- they will.  It only means that after the END;, the CONTINUE HANDLER will be out of scope and will not trap subsequent errors.


Change IDENTITY_INSERT to ON in SQL server How can I insert an ID into an identity column in SQL Server? I'll make a special item into my table and I will quickly detect that item by the ID. So because all other ID's are bigger or equal to one, I will give that special item the ID zero.

The problem is now, when I've created that table I've place the identity of the column ID on true. If I insert now the special item with code below, I've got next error:

INSERT INTO MyTable(ID, Name, Description)
VALUES (0, 'Special title', 'special item');

Error:

  Cannot insert explicit value for identity column in table MyTable when IDENTITY_INSERT is set to OFF.

How can I change this table, column or property so I can create a record with ID zero?

Explicit identity insert require IDENTITY_INSERT property set to ON.

SET IDENTITY_INSERT MyTable ON  -- Statement Allows explicit values to be inserted into
                                -- the identity column of a table.
GO

INSERT INTO MyTable(ID, Name, Description)
VALUES (0, 'Special title', 'special item');
GO

SET IDENTITY_INSERT MyTable OFF  -- Statement revert granted permission
GO


Memory usage increases whenever I run stored procedures I want to know why SQL Server memory use increases whenever I run stored procedures.

After I stop and restart SQL Server, the memory used is just 1 GB.

Each time I run stored procedures, the memory use increases, and has now reached 8GB.

I checked temporary tables, there are just a few.

I don't know why and how to reduce SQL Server memory use.

I'm using SQL Server 2012 under Windows 8.

I just want to know why SQL Memory increase whenever runs stored procedures.

Because SQL Server is a messy. It tries to keep everything it ever reads. The idea behind this is that using memory is faster than using disc. Not sure you agree here - the rest of the world does.

  After I stopped and restarted SQL. The memory just has 1 GB.

Because right after a restart obviously SQL Server has nothing read and thus nothing cached.

  Each time I run stored, the memory will raise and now it reach 8GB.

That is still a VERY small server, you know. I have seen machines using hundreds of gb. So what? It is faster than reading the data from disc.

  I don't know why and how to reduce SQL memory.

Why would you ever do that? Set good limits for memory use and then let SQL Server do it's work. After all, accessing cached data is many times faster than even reading it from SSD, you know.

If you are SERIOUSLY memory starved then set a good limit and if that leads to performance problems (particularly when dealing with small amounts like 8gb of memory) just plug in more RAM. RAM is not that expensive. At least as long as you  talk about such small amounts.

So, in short: SQL Server loves caching data under the correct assumption it makes it faster. If you have not set a limit, it will gladly do so up to the memory installed in the machine. It DOES release that, but having multiple servers fighting for memory is not good. If you are not running a dedicated SQL Server and/or are memory starved, set the max memory parameter in the server options to a sensible value.


mysql check constraint not working I have used the below query with a constraint to check the values which are inserting to the stu_code column start with 's'.

mysql> CREATE TABLE student_info(
       no INT(3) PRIMARY KEY,
       stu_code VARCHAR(10),
       CHECK (stu_code like 's%'));

Query OK, 0 rows affected (2.68 sec)

but it does not work. When I inserting the values (like below) I can insert values which are not starting with 's'.

mysql> INSERT INTO student_info VALUES (1,'k001');
Query OK, 1 rows affected (0.47 sec)

I am beginer with SQL, Can anyone help me find out what is the issue?

MySQL only parses the text of a CHECK constraint, it does not actually enforce them.

From the documentation:

  The CHECK clause is parsed but ignored by all storage engines.

A workaround is to use a trigger:

DELIMITER //
create trigger student_info_bitrig before insert on student_info
for each row
begin
    declare errormessage varchar(100);
    if new.stu_code not like 's%' then
        set errormessage = concat('Error - Value not allowed: ', new.stu_code);
        signal sqlstate '45000' set message_text = errormessage;
    end if;
end
//

DELIMITER ;

Test:

mysql> INSERT INTO student_info VALUES (1,'k001');
ERROR 1644 (45000): Error - Value not allowed: k001
mysql>


How to do safety mirroring innodb table on mysql? I have one Mysql InnoDb table with rush trafik on row update. The table is also frequently used for heavy select query (especially group by for summary report).  As I know this heavy select query will interfere or reduce the update peformance. So i have an idea to mirror this table, so the heavy select query will do in this mirror table (delay 15 menit still accepted).
Fyi, this table have size approximately 10 GB, I have used mysql event (scheduled every 15 minute) to copy this table to another with "insert into select" query. Actually it need more than 60 second for copy process, but the update query still impacted when this event running.
So Is there any best/common practise for mirroring table mysql with minimum impact to currently query (especially update) process on the master table?
Note : I want to do on the same server

Plan A:  Speed up the Summary by building and maintaining a Summary Table:  http://mysql.rjweb.org/doc.php/summarytables .  In particular, this is likely to segregate the summary select, which will now touch only the summary table, from the updates.  And it will
Plan B:  Use replication on the same server.  Be sure to have a different "Port" (such as 3307) for that Replica.  Direct summary requests to that port.  Continue to direct writes to the Primary (your current instance).
Plan C:  If the "update" is just a "Likes" or "Visits" counter, then do the following to avoid some of the interference.  Move that column to a separate table.  The two tables would have the same PRIMARY KEY for ease of JOINing.  The update will avoid interfering with any selects that don't need also need to counter.
Plan D:  Plans A,B,C are independent; do a combination of them.
The repeated copying of the table will (I predict) be poor for both maintenance and performance.


Compare two databases and get the differences I want to between two databases and get the differences:

columns: in the first table the column name is varchar2(12), in the  second table the column name is varchar2(20). Or I have an
additional column in the first table.
data: I have to compare data between the same tables in different databases

I am aware of DBMS_COMPARISON, but need to do this on Oracle 10g.

Added after the answer by thatjeffsmith:

I know that SQL Developer can do that, but I have to develop a J2EE application which does that.

For item #1, you can use the Database Diff feature in SQL Developer - and yes, it should work on Oracle 10g.

This is a free feature in a no-cost tool provided by Oracle Corp.


Replicate View different servers Using MySQL, and related to this answer open in another thread (How to replicate MySQL table in different servers?), It arises me the idea of replicating only a View in different servers (through master-slave)

To create a replicate-view without replicating original tables in remote server, is it possible? Or Do I have to replicate the tables as obligatory condition?

A VIEW is a shorthand for accessing the underlying table(s).  If the table(s) are not there, the VIEW won't work.

You can reach into other servers via FEDERATED.  See also Spider (a 3rd party product).  Each has limitations.

Master-Slave is predicated that the Slave always contains an exact copy of the Master.  (There are exceptions, but your question does not hit any of them.)


Is there a cutoff when using datetime and date datatypes in the where clause? I have a column titled achieved_date. In my query most of the values for that column are 2016-09-30 23:59:59.997.

I then have a where clause that reads as follows:

WHERE achieved_date between '20160901' and '20160930'

The records with the 2016-09-30 23:59:59.997 value are not being included in my results when they should be. I mean the transaction was recorded on the 30th of September.

Any ideas if this is a odd datatype issue?

Stop using BETWEEN for date range queries; it's ambiguous and brittle. WHERE COL >= '20160901' and COL < '20161001' is so much easier to construct and isn't vulnerable to unexpected changes in type / precision / rounding.

See this post and this post.


SQL Server 2008 R2 SP1 - Tempdb "Available free space" showing negative value Please help as this issue is driving me nuts...

Tempdb has four data files and one log file. They are all located on the same dedicated drive.

When I right click on tempdb in SQL Server Management Studio (SSMS) to view Properties --> General...I'm seeing "Size" as ~65000 MB with "Space Available" as ~64000 MB. However, when I try to shrink the files via the GUI, I see that the "currently allocated space" hasn't changed and is still showing the initial size.

This is also where the "Available free space" is shown as a negative number. The only workaround I have found so far is to restart SQL Server to flush out tempdb, but I can't keep on doing this.

Has anyone ever come across this and knows the root cause/permanent solution for it please?

Open Query Analyzer
Run DBCC UPDATEUSAGE('tempdb')
After it is done, check temdb value and it should show the true values.

Also, this might help you.

Also, check if there are any open transactions using dbcc opentran or sp_whoisactive (Adam Mechanic's SP)

This was a bug in SQL Server 2008 SP1 described here.

Out of curiosity, is it causing any performance problems ?


How to reset slave without over write of master dump in mysql My master having position is
File                 Position

mysql-bin.000046   430674598
I lost my data dir of mysql on master after that I have backup of slave which restored in master.Now Both having different log position .But I am  afraid of the starting slave.the dump which i stored in master will rewrite on my salve.
slave position is
Relay_Master_Log_File:
mysql-bin.000040
Exec_Master_Log_Pos: 933087278
Relay_Log_Space: 767900934
Please suggest me way to configure it properly.
how to resync my slave ?**

Nice! Now the case is you have lost your datadir on master and some how you have the master log position!! Care to tell us how?

Anyways... In  such case we will have to reconstruct the master from slave. But because your slave is behind master (not caughtup with binary logs) we need to fix that first.

Backup your slave before working

The slave co-ordinates are mysql-bin.000040 and some-log-pos...

This means that when your datadir was getting "lost" slave was still lagging behind.

Now, if your slave has fetched all the required binlogs (relay logs) from master before master went down then just start sql thread and let it catchup.

On Slave:
Start slave sql_thread;

This will let the slave catch-up with latest available changes from master.

If your binary logs were not yet shipped (via replication) to slave, then you might want to  ship them manually and play it on slave. (Provided they're available - as you say you have co-ordinates)

mysqlbinlog mysql-bin.xxxx > mysql-bin.xxxx.sql

scp all sql files to slave (You may choose to compress them)

cat mysql-bin.xxxx | mysql -uroot -p

After all these slave will have the "latest" data.

Assuming that that's the only thing we have got:
- Restore your master from slave's backup.
- Reconfigure replication from master to slave afresh.

For next time onwards:

Configure backup on slave & binary log backups if PiT restores required.
Have log_slave_updates.
Don't delete datadir on master :P


ETA: translate Oracle SQL queries to MySQL task: replace Oracle 11g with MySQL

current setup:

Oracle 11g SE One
about 80 tables
stored procedures, PL/SQL are in place but out of scope for this question
client applications do not use a generic access technique like JDBC but OCI libs
applications have roughly 500 fixed SQL queries for Oracle

Management now wants to have outlined a migration draft. Don't bother about the schema or data migration.

I'm trying to figure out a rough estimate of time to spend for translating 500 queries. Naturally these queries range from very simple INSERTs/SELECTs to rather complex ones with subqueries, multiple JOINs and expressions like that:

TRUNC(EXTRACT(DAY FROM (afs.next - TO_TIMESTAMP_TZ('1970-01-01 00:00:00 UTC',
 'YYYY-MM-DD HH24:MI:SS TZR'))) * 86400 + EXTRACT(HOUR FROM (afs.next -
TO_TIMESTAMP_TZ('1970-01-01 00:00:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR')))
* 3600 + EXTRACT(MINUTE FROM (afs.next - TO_TIMESTAMP_TZ('1970-01-01 00:00:00 UTC',
 'YYYY-MM-DD HH24:MI:SS TZR'))) * 60 + EXTRACT(SECOND FROM (afs.next -
TO_TIMESTAMP_TZ('1970-01-01 00:00:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR')))) as \"next\"

Hint: sense and purpose of such expressions has to be read out of the existing queries because at creation time no one bothered about documenting details of the implementation.

I'm completely aware that there may be queries that cannot be translated as is because MySQL perhaps does not support some things.

Question: can you give a rough hint about the expected "throughput" you think an expert could be capable of? The expert will be a person skilled with MySQL, Oracle and the applications. What do you think how much time is required to "translate" a single average query and simply manually test it in the target database?

Please ignore the required testing of the whole application. Don't regard that we have to setup the target schema beforehand. Hints to and stories of failed migrations and disastrous problems are welcome.

Thanks!

Why are you migrating to MySQL?  Did you consider PostgreSQL?  I only ask because PostgreSQL is pretty much feature-compatible with Oracle, and would make a migration an order of magnitude simpler.

That said, translating 80 table definitions and 500 queries will probably take a month or so.  Simple CRUD statements will be limited to the expert's typing speed, but if you're doing any aggregation or other BI-type work it can take a day just to get one statement working.  You might be better off simply documenting every query then re-writing them from scratch using MySQL idioms rather than trying to translate them.


Update + where not exist once again, I come here for your rescue.

how can I improve a query like this for a table with 20 million rows?
I'm trying to update a table, by using the date and getting the first date of attendance for each student

update student_activity c join
(select * from  student_activity a
where not exists
        (select * from student_activity b where a.student_id = b.student_id
                                            and a.class_type = b.class_type
                                            and a.date > b.date)) r
on r.student_id = c.student_id and c.class_type = r.class_type
set c.first_attendance = c.date
;

Your query can be collapsed to the single JOIN without subselects:

UPDATE      student_activity AS c
  LEFT JOIN student_activity AS b ON c.student_id = b.student_id
                                 AND c.class_type = b.class_type
                                 AND c.date > b.date
 WHERE b.date IS NULL
   SET c.first_attendance = c.date
;

I hope your table student_activity have the index (student_id, class_type, date)


Backup/restore of a database that has a filestream I have a SQL DB to which I have recently added Filestream data.  Now we have a daily backup/restore to a QA server, and since I added the Filestream data, this has started failing.  So I modified the "restore" statement to include a "with move":

RESTORE DATABASE MyDb
FROM DISK = 'c:\somepath\mydb.bak'
WITH RECOVERY, REPLACE,
MOVE 'MyDb' TO 'f:\sql\MyDb.mdf',
MOVE 'MyDb_Log' TO 'f:\sql\MyBb_log.ldf',
MOVE 'MyDb_Filestream' TO 'f:\sql\MyDb_FS', NOUNLOAD, STATS = 10;

But this is failing with the following error:

  File 'MyDb_Filestream' cannot be restored to 'f:\sql\MyDb_FS'. Use WITH MOVE to identify a valid location for the file.

I've Googled all over to find a solution for this.  This answer says that you need 3 separate RESTORE DATABASE statements, but doesn't spell out what they are.  The accepted answer here indicates that you need to copy over the whole Filestream folder manually, which I find very difficult to believe.  And in any case it doesn't make clear what the process is.

Can anyone please spell out to me, in idiot-proof, step-by-step instructions, how exactly one is supposed to backup a DB containing a filestream, copy it to another server and restore it there?

Thanks!

I believe the mystery is cleared up.  The documentation only says that the SQL Server account needs full access to the filestream folder, but when I checked who had access to the main DATA folder, I saw that the SYSTEM account also had access.  I gave full rights to the FS folder to the SYSTEM user, and now I don't get this error anymore.


Adding filter for a decimal column slow down SELECT query significantly I have a long query that returns 2000 rows in less than 2 seconds:

SELECT  target.Id, target.Decimals
FROM    dbo.t1
        JOIN dbo.t2 on ...
        JOIN dbo.t3 on ...
        JOIN dbo.t4 on ...
        JOIN dbo.target on ...
WHERE   t1.Id = ...
        AND t2.CreatedAt >= ''
        AND t2.Type = ''
        AND target.Id NOT IN (
            SELECT  s1.Id
            FROM    dbo.s1
                    JOIN dbo.s2
            WHERE   s1.Type = '' AND s2.Type = ''
        )

This is all good until I add one more condition to filter out a decimal column (NOT the sub-query):

        AND target.count = 0

The query than run really slow (> 1 min). If I select all 2000 rows, copy paste them on excel and filter manually, then return to SQL, the query would still running.

My table target has over 140k rows. I tried with another DB (different data set) with ~40k rows and it just need 1 second to filter all 6000 rows returned by the initial query.

In addition, changing the filter value would also affect the performance. The bigger it is (less likely to exist), the faster the query run. Ex: AND target.count = 100000 would return immediately.

I am not able to share any execution plans.

You didn't actually provide a question to answer, so I will assume that you're looking for a way to improve the performance of your query. People have written books on that topic so it's difficult to give an answer that fits in the site's format when an execution plan cannot be provided. With that said, the strategy that I use most often is to figure out why a query is slow. Comparing the actual plans between a fast and slow version of similar queries can be very helpful. For a query like yours, the issue might boil down to a missing index or a cardinality estimate issue.

If you want an approach that's nearly guaranteed to work, trying using a temp table. You can insert the unfiltered 2k rows into a temp table in a few seconds and it'll take less than a second to apply the filtering to the temp table with a second SELECT query. Using a temp table here avoids diagnosing the root cause of the issue, but there's nothing wrong with using a temp table to aid the performance of a complex query, or really any query.

In the comments, you asked the following question:

  [C]an I instruct SQL to just apply the addition filter on the 2000
  rows selected by the initial query?

This cannot be done directly but you can achieve the desired behavior with careful placement of TOP. Adding a TOP N with an extremely large N won't change the results of the query but it can change optimizer behavior. The optimizer must guarantee correctness in whatever plan it creates. You and I may know that taking the first 987,654,321,987,654,321 rows will never change the results but the optimizer will implement and honor that check because it cannot guarantee that it wouldn't change the results. For a query like the following:

SELECT q.Id, q.Decimals
FROM
(
    SELECT TOP (987654321987654321) target.Id, target.Decimals, target.count
    FROM    dbo.t1
            JOIN dbo.t2 on ...
            JOIN dbo.t3 on ...
            JOIN dbo.t4 on ...
            JOIN dbo.target on ...
    WHERE   t1.Id = ...
            AND t2.CreatedAt >= ''
            AND t2.Type = ''
            AND target.Id NOT IN (
                SELECT  s1.Id
                FROM    dbo.s1
                        JOIN dbo.s2
                WHERE   s1.Type = '' AND s2.Type = ''
            )
) q
WHERE q.count = 0;

The optimizer will not give you a plan that pushes down the count filter into the derived table. That means that it's pretty likely that you'll get the same query plan that finishes in a few seconds for the derived table, and overall performance should be roughly on par with that query. I expect that the slow query plan that runs for longer than a minute would be avoided.


SQL Server Cardinality Estimation Warning How can I fix the following cardinality estimation warning?
while exists(select 1 from @images)
begin
   declare @imageid     int
   declare @filename    varchar(max)
   select TOP (1)
      @imageid = [imageid],
      @filename = concat([imageid], '.', [extension])
   from
      @images
   order by
      [imageid]
   ...
   delete @images where [imageid] = @imageid
end

SQL Server Compatibility Level: SQL Server 2019 (150)

Sometimes the warning is nothing more than that, just a warning, and not something actually affecting performance.
The two things that are most likely affecting your performance are the Table Variable, and the fact you're looping instead of using a more relational solution. So I'd first run a SELECT * INTO #images FROM @images to put it inside of a Temp Table before your WHILE loop, and then use that Temp Table inside the loop instead, to potentially improve performance.
To answer your question though, I believe the fact that your imageid is an INT but you're using it in a string function like CONCAT() is where the Implicit Conversion is coming from that is inducing that Cardinality Estimate warning. If you stored a copy of it in your @images table variable already casted as a string data type that was the same type as the extension field and used it in the CONCAT() function instead then the warning should go away.
Also, Table Variables typically result in poor Cardinality Estimates themselves because of their lack of statistics, which may be why the "Estimated Number of Rows Per Execution" is showing 1. (Note there's been improvements in SQL Server 2019.)


Differing results from gv$session The following query returns no results, yet it seems like it should.

SELECT * FROM gv$session where SID = SYS_CONTEXT('USERENV','SID');

In my 11.2.0.2 Standard Edition RAC environment the above query returns no results, yet the following variations all return results.

SELECT sid FROM gv$session where SID = SYS_CONTEXT('USERENV','SID');

SELECT * FROM gv$session where SID = 256; --Your SID will likely be different.

SELECT * FROM v$session where SID = SYS_CONTEXT('USERENV','SID');

Can anyone confirm this behavior and/or explain it?

I got following results

SELECT SYS_CONTEXT('USERENV','SID') FROM DUAL;
-- 5276

SELECT * FROM gv$session where SID = SYS_CONTEXT('USERENV','SID');
-- No result

SELECT * FROM gv$session where SID =5276;
-- 4 results for 4 node RAC
-- 1 result matches my USERNAME,OSUSER, MACHINE and PROGRAM
-- 3 results are different from my USERNAME,OSUSER, MACHINE and PROGRAM

SELECT * FROM v$session where SID = SYS_CONTEXT('USERENV','SID');
-- 1 result

I cannot explain but following query in 4 node RAC gives interesting output.

SELECT '1' AS QUERY_NUMBER,S.* FROM gv$session S where SID = SYS_CONTEXT('USERENV','SID')
UNION ALL
SELECT '2',S.* FROM gv$session S where SID = ( SELECT  SYS_CONTEXT('USERENV','SID') FROM DUAL)
UNION ALL
SELECT '3',-1,S.* FROM v$session S where SID = SYS_CONTEXT('USERENV','SID');

QUERY_NUMBER = 1, no rows

QUERY_NUMBER = 2, 4 rows (4 node RAC)

QUERY_NUMBER = 3, 1 row


What are all the ways that a SELECT statement could be made to not terminate or take a *very* long time? One way is using WITH RECURSIVE:
WITH RECURSIVE
  t AS (
    SELECT 1 n
    UNION ALL
    SELECT n + 1 FROM t
  )
  SELECT * FROM t;

Joins can be made to return a lot of rows from even small tables.
Tables themselves could be huge.
Views can be done with any such SELECT statement and so SELECT statements that use them may also not terminate.
Is that it? If SELECT statements did not have WITH RECURSIVE syntax, could not do arbitrary joins, were limited to small tables or hard a small LIMIT enforced, and could not use any such view, would they all be guaranteed to terminate in a reasonable time*?
I'm particularly interested in MariaDB/MySQL.
I'm thinking of making a new language based on SQL's SELECT statements that transpiles/compiles to SQL and I'm wondering what I can do to guarantee query termination in reasonable time*.
* What's reasonable time is of course subjective. I just mention it to exclude queries that are set to terminate in a time frame so long that we may as well treat it as non-terminating.

MariaDB-10.1.1 has max_statement_time to abort long-running statements.  I don't think MySQL has anything equivalent.
(Of course, you need at least 10.2 to get Recursive CTEs.)


Cassandra: maintenance I am inexperienced with Cassandra, but I have some experience with SQL-based relational databases.

I have been unable to find best practices information about how to maintain Cassandra once deployed.  Is it necessary to VACUUM the database?  I should think that read/write loads cause fragmentation in the storage.

Or more generally: what are the best practices for maintaining a Cassandra production deployment?  What has to be done at regular intervals to maintain the health of the system?  The Operations manual really doesn't discuss this aspect.

Thanks.

In general, a well designed cluster can live for YEARS without being touched. I've had clusters that ran for years hands-off. However, here are some guidelines:

Monitoring is hugely important:

1) Monitor latencies. Use opscenter or your favorite metrics tools to keep track of latencies. Latencies going up can be signs of problems coming, including GC pauses (more common in read workloads than write workloads), sstable problems, and the like.

2) Monitor sstable counts. SSTable counts will increase if you overrun compaction (each sstable is written exactly one time - deletes are handled by combining old sstables into new sstables through compaction).

3) Monitor node state changes (up/down,etc). If you see nodes flapping, investigate, as it's not normal.

4) Keep track of your disk usage - traditionally, you need to stay under 50% (especially if you use STCS compaction).

There are some basic things you should and shouldn't do regularly:

1) Don't explicitly run nodetool compact. You mention that you've done it, it's not fatal, but it does create very large sstables, which then are less likely to participate in compaction moving forward. You don't necessarily need to keep running it, but sometimes it may help to get rid of deleted/overwritten data.

2) nodetool repair is typically recommended every gc_grace_seconds (10 days by default). There are workloads where this is less important - the biggest reason you NEED repair is to make sure deletion markers (tombstones) are transmitted before they expire (they live for gc_grace_seconds, if a node is down when the delete happened, that data may come back to life without the repair!). If you don't issue deletes, and you query with sufficient consistency level (reads and writes at QUORUM, for example), you can actually live a life without repair.

3) If you are going to repair, consider using incremental repair, and repair small ranges at a time.

4) Compaction strategies matter - a lot. STCS is great for writes, LCS is great for reads. DTCS has some quirks.

5) Data models matter - just like RDBMS/SQL environments get into trouble as unindexed queries hit large tables, Cassandra can be problematic with very large rows/partitions.

6) Snapshots are cheap. Very cheap. Nearly instant, just hard links, they cost almost no disk space immediately. Use snapshot before you upgrade versions, especially major versions.

7) Be careful with deletes. As hinted in #2, delete creates more data on disk, and doesn't free it for AT LEAST gc_grace_seconds.

When all else fails:

I've seen articles that suggest Cassandra in prod requires a dedicated head to manage any sized cluster - I don't know that it's necessarily true, but if you're concerned, you may want to hire a third party consultant (TheLastPickle, Pythian) or have a support contract (Datastax) to give you some peace of mind.


how to get the difference between 2 tables in 2 different schema's in Oracle? I am using Oracle 11G.
I have a table called Tickets in the schema called 'tickets1"
I have the same table structure in another schema called 'tickets2'
I want to find out

What data is the same in both tables?
What data is different?

CREATE TABLE tickets(
    ticket_number NUMBER NOT NULL,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    PRIMARY KEY(ticket_number)
);

To mustaccio's point, there's nothing particular about a schema that prevents you from querying objects from different schemas within the same query (putting privileges aside - since that's a function of security, not a unique feature of schemas).
So you can use a FULL JOIN between the two tables across schemas with an appropriate WHERE clause like so to answer your questions:
-- Gets all rows that have the same primary key (ticket_number) between the two tables
SELECT * -- Replace * with only the columns you need, as using * is generally bad practice
FROM tickets1.Tickets T1
FULL JOIN tickets2.Tickets T2
    ON T1.ticket_number = t2.ticket_number
WHERE T1.ticket_number IS NOT NULL
    AND T2.ticket_number IS NOT NULL

-- Gets all rows that have different primary keys (ticket_number) between the two tables
SELECT * -- Replace * with only the columns you need, as using * is generally bad practice
FROM tickets1.Tickets T1
FULL JOIN tickets2.Tickets T2
    ON T1.ticket_number = t2.ticket_number
WHERE (T1.ticket_number IS NOT NULL AND T2.ticket_number IS NULL)
    OR  (T1.ticket_number IS NULL AND T2.ticket_number IS NOT NULL)

Note the above only compares the two tables by the primary key (ticket_number). Depending on your definition of "same data" / "different data", you may want to compare by other fields in the tables as well.


What things we should consider when configuring Cassandra Cluster? I'm new to Cassandra, so wanted to understand what basic things we should consider when are we going to configure Cassandra cluster in on or multiple database centers?

Thanks!

what basic things we should consider when are we going to configure Cassandra cluster in on or multiple database centers?

This is a bit of an open-ended question, but I build multi-DC Cassandra deployments on a fairly regular basis, so I'll try to provide some insight here.

The first thing to consider, is what you hope to achieve by building a presence in both data centers.  The main reasons are data locality and disaster recovery.

Data locality is in reference to the application Cassandra is serving.  So if an application team is deploying to two DCs, they're going to want their data nearby.  Disaster recovery is good to have if you lose an entire DC.  This allows you to easily and quickly rebuild another logical or physical DC.

Another point to consider is whether or not the data centers are hosted by the same provider.  Assuming a cloud deployment, you'll want to understand the network between the cloud regions, what your bandwidth is, and how much it'll cost to use.  Obviously, things will be easier if your nodes can easily communicate to each other cross-DC when they need to.

If the data center providers are different, you're going to want be very sure about how consistent that network connection is.  In this scenario, you're going to want to set the phi_convict (default 8) to a higher value.  That will give your nodes a little more leniency in marking nodes as up or down, when the network becomes unstable.

You will also want to set the replication strategy on the system_auth table to use the NetworkTopologyStrategy, and make sure it has an appropriate number of replicas in each DC.  Otherwise, your authorization requests will be jumping DCs.


Second Normal Form (2NF) Normalisation Exercise I'm using a past exam paper for practice, can someone help me normalise this database to 2NF? I have uploaded a picture of the original below, and also my attempt at normalising it myself. I am certain I have gone wrong with the YEAR column, but can't be sure.

You have the right idea and have just gone a little wrong with the year column.
Background
The table given shows that Company Id + Year is the key to the table - meaning that each row must have a unique combination of the value of these two columns.  This is indicated by the solid line under these two columns.  A table is normalized when that table is a faithful representation of a relation.  To approximate a relation, the table must:

Have unique unordered rows
Have uniquely labeled unordered columns
Have a single value of whatever domain the column is defined upon in cell

In looking at the table in the exercise these conditions are met.  Once a table is normalized, it can be further normalized as a way to eliminate certain redundancies which occur due to functional dependencies between the columns.  By functional dependency we simply mean that the value of one column always determines the value of the second.  This is based on the mathematics of algebraic functions.  For example, the value 2, when plugged into the algebraic function 2x + 3 will always yield 7.  2NF is defined to mean that each non-key column in the table is fully dependent on the entire key - thus no partial key functional dependencies.
The Exercise
When inspecting the example table we can easily see Company Nameis dependent only on Company Id and not on Year.  Each time for example we see a value of 295 for Company Id we see a value of Miracle Holidays.  The repair is exactly as you have done - split Company Id and Company Name out into their own table where there can be a single unique row for each Company Id with the corresponding Company Name.  Now Company Name is fully dependent on the key -  Company Id.  Year simply stays in the original table and there is no need to redundantly split it out into a third table.
A Caution
The exercise is asking you to infer the functional dependency of Company Name on Company Id based solely on inspection of the data.  While in this simple example it is obvious, in the real world you cannot simply assume a functional dependency exists based on the existing data.  What appears to be a functional dependency might turn out to instead be coincidental.  Because of this, in the real world you would always ask the expert in the domain of knowledge the table represents what the functional dependency should be.
Getting More Information
Normalization is a very complex topic and I have glossed over many important concepts.  CJ Date has written an entire book with respect to it called Database Design and Relational Theory:  Normal Forms and all that Jazz.  While definitive, it is hard to grasp all the formalisms.  An excellent reference that presents the formalisms in language more easily understood by common practitioners is Fabian Pascal's Practical Database Foundation Series.  Studying both of these references - first Fabian's and then Date's - will give you all the information you need to master normalization as a repair procedure.