So this can definitely depend on the database system and version you're using, so you should include those as tags on your post, but typically I would write the predicate to have a way to short-circuit based on a certain value of the parameter. Generally I use NULL as the short-circuit value.

An example of how to re-write your predicate to be able to be short-circuited is like so:

where ( IS NULL OR a = )
  and ( IS NULL OR b = )
  and ( IS NULL OR c = )
  and ( IS NULL OR d = )

Otherwise, no, I don't think it's possible with your original predicate to pass in some value that always results in true such that it doesn't affect the results (assuming a, b, c, and d are column names and not some construct holding scalar values).

I am trying to determine whether or not this is an execution plan reporting bug or an actual feature that is working as intended.

In my execution plan, when using MAXDOP 1, I am seeing the entire table being scanned ( https://www.brentozar.com/pastetheplan/?id=HkPFquLdc - Very bottom of plan Object 11 shows ~2.5 million rows read which is the entire table).

However, when I let the engine choose it's own plan with no hints, it goes parallel ( https://www.brentozar.com/pastetheplan/?id=r12p5OUdc ) and does a bitmap/hash match and that same Object11 only shows ~534k rows read despite doing an index scan and having a bunch of predicates on other columns not in the clustered index.

I would expect that SQL has to read every single row in the table to evaluate each predicate, but perhaps the PROBE IN (you can't see this in paste the plan) on Object 11 in the parallel plan is able to "Filter out pages" since the probe is on the PK/CX.

Here is a procedure from Microsoft on how to achieve what you are asking. Your issue is that the only session that can exist in the database when changing it to RCSI is the Alter DATABASE statement. However, the mirroring session exists, so there 2 sessions connected to the database. You only want a single session to exist.

You need to break mirroring , make the change to RCSI and then re-enable mirroring. Don't take my word for it. Review the Microsoft link below

https://techcommunity.microsoft.com/t5/datacat/how-to-enable-rcsi-for-a-database-with-database-mirroring/ba-p/305043

I would like to increase the size of a column. The column currently is varchar(1000) and will be changed to varchar(2000). Null value is already permitted.

The tables has about 2 millions of records, huge reads and many writes (less than reads).

MySQL/Aurora version = 5.7.mysql_aurora.2.09.3

1 - I wrote this command:

LOCK TABLES mytable WRITE;
ALTER TABLE mytable MODIFY COLUMN description VARCHAR(2000);
UNLOCK TABLES;

This command took about 4 minutes to complete.

2 - I wrote only this:

ALTER TABLE mytable MODIFY COLUMN description VARCHAR(2000);

This command took about 3 seconds to complete.

3 - I wrote this (just for testing purposes):

ALTER TABLE mytable MODIFY COLUMN description VARCHAR(50); -- Decreasing

This command took about 4 minutes to complete.

Questions:

I think that second option is faster because mysql will update only metadata, and "knows" that does not exists any record to check due that it is a increasing operation. Is it right?

I am searching for the best way to update this table without using strategies like copy to another table or another column.

Note: I tested in a separated instance (the same hardware) and simulate the same queries (read and write) for all wrote command.

Edited: Change MySQL Version

I have two tables I need to join together. However, I only want to join records when there are unqiue matches rather than picking one of several for the join.

Versioning:

Using MariaDB version 10.3.34

Example Data:

Core (LEFT) data towns

Joined (RIGHT) data peoples:

My SQL:

My initial query would be something like:

SELECT towns.id, towns.town, peoples.name FROM towns 
       LEFT JOIN people ON towns.postcode = peoples.postcode

But this will include Edinburgh but there are two people in Edinburgh, I only want to join when there's a single unqiue row to join on.

I use LEFT join because I need to return all of towns but only the unique rows of peoples.

Expected results:

What I've tried

I've tried using COUNT() in the JOIN but can't get this to work,

SELECT towns.id, towns.town, peoples.names FROM towns 
       LEFT JOIN people ON towns.postcode = peoples.postcode AND count(peoples.id) = 1 

Comes up with a syntax error.

I can't think about how I can qualify this join that it only joins when there's a single result found. Internet searching gives me lots of far more vague and off topic references.

I'm sure it's simple but I can't do it. Also, I'd like to avoid subquerying if possible?

https://www.db-fiddle.com/f/axkxaR2RVzzdMWNE3dWsfL/0

Some references say quorum disk and vote disk are the same, but "quorum disk" name was used in older versions. But Oracle 19c documentation still use both the names, so I think there is a difference. Also, when to use quorum failure group and what is it? documentation says:

A quorum failure group is a special type of failure group that does not contain user data. Quorum failure groups are used for storing Oracle ASM metadata. A quorum failure group may also contain voting files if those files are stored in a disk group that contains a quorum failure group. Additionally, Oracle ASM uses a quorum failure group to help determine if the disk group can be mounted in the event of the loss of one or more failure groups.

Ok, but, for example, we have a Oracle Clusterware running and there is no quorum failure group configured, so it seems it can be used or not. When to use it?

Using Maxscale 6.3.0 in Read-Write Split, with 1 master and 2 slaves( all MariaDB 10.4), MASTER not configured to accept reads, is it normal that Maxscale redirect these to master? If yes, why?

1721780 Prepare SELECT * FROM `jfs_edge` INNER JOIN `jfs_node` ON jfs_edge.inode=jfs_node.inode WHERE `jfs_edge`.`parent`=? AND `jfs_edge`.`name`=? LIMIT 1
1721780 Close stmt
1721780 Prepare SELECT * FROM `jfs_edge` INNER JOIN `jfs_node` ON jfs_edge.inode=jfs_node.inode WHERE `jfs_edge`.`parent`=? AND `jfs_edge`.`name`=? LIMIT 1
1721780 Close stmt
1721780 Prepare SELECT * FROM `jfs_edge` INNER JOIN `jfs_node` ON jfs_edge.inode=jfs_node.inode WHERE `jfs_edge`.`parent`=? AND `jfs_edge`.`name`=? LIMIT 1
1721780 Close stmt
1721780 Prepare SELECT * FROM `jfs_edge` INNER JOIN `jfs_node` ON jfs_edge.inode=jfs_node.inode WHERE `jfs_edge`.`parent`=? AND `jfs_edge`.`name`=? LIMIT 1
1721780 Close stmt
1721611 Prepare SELECT * FROM `jfs_edge` INNER JOIN `jfs_node` ON jfs_edge.inode=jfs_node.inode WHERE `jfs_edge`.`parent`=? AND `jfs_edge`.`name`=? LIMIT 1
1721780 Prepare SELECT * FROM `jfs_edge` INNER JOIN `jfs_node` ON jfs_edge.inode=jfs_node.inode WHERE `jfs_edge`.`parent`=? AND `jfs_edge`.`name`=? LIMIT 1
1721611 Close stmt

This has probably been asked before, but I couldn't find the answer.

I have a complex datamodel in sql-server-20XX (more likely to be something old) in which cascade delete has been turned off (by design).

A large chunk of our database has been sold (I can make the distinction on the top-level) and there are reasons why we want to delete that data. I want to do that in a single script, in which I first turn on cascade delete, than delete all the data, than turn off cascade delete again, but I can't find the correct syntax.

Relevant things to mention:

• I want to delete about 40% of the rows from 10-15 tables in total, that are all somehow 1:n-1:n...-coupled to the Organisations-table
• Performance is not relevant, since the application is not used outside of business hours

The tables must stay, I need to get rid of 10/33 rows in the Organisations-table and I need to get rid of ALL the rows in Orders, users... etc that are coupled to those organisations

I'm with Mustaccio, there's no reason moving the calculated field should cause the rows to change.

I did notice that the statement being run in the working screenshot appears to be just the statement you're expecting...

whereas in the broken, you've picked up some commented out stuff first...

I also notice you've not terminated any statements, with a ;. I'd suggest you do so. As the only explanation I can come up with is that you're picking up some code you don't intended to. On the Messages tab of Run SQL Scripts, take a look at the statement actually being run.

In addition, you should always use ISO date/time format for literals.

It appears that kitchensenttime and parkcartime are timestamps, not dates. Comparing them to a date forces the DB to implicitly convert either the column value or the literal. It's better practice to explicitly do the conversions yourself.

Furthermore, '0001-01-01' is suspect, if the column is nullable, it's more likely that the columns are null and don't actually contain '0001-01-01-00:00:00.000000'. If the columns are defined NOT NULL and the records are being written out using RPG op-codes, then RPG is defaulting in the '0001-01-01-00:00:00.000000'

--having this semicolon will ensure the parser doesn't see 
-- any prior statements as being a part of this one.
;
--try this
Select storenum,
       businessdate,
       kitchensenttime,
       parkcartime as delivertime,
       (parkcartime-kitchensenttime) as deliveryseconds,
       case
           when parkcartime-kitchensenttime>360 then 1
           else 0
       end as disastrouscount
from irislib.tblorder
where not(kitchensenttime = timestamp_iso(date('0001-01-01'))
          or parkcartime = timestamp_iso(date('0001-01-01'))
         )
    and parkcartime>kitchensenttime
    and businessdate= date('2022-05-18');

One of my tables only has about 5k rows, but is the table with the most logical reads, of all the tables in the database (based on dm_db_index_usage_stats). I'd like to work on the indexes to this table, but hundreds of objects use the table. Is there a shortcut to figure out WHICH of the many objects that use this table are the ones that are causing the most logical reads, specifically on this table? Perhaps some way of using QueryStore data for this?