I would say always aim for join.

If this is not possible consider subquery or CTE.

Here is why

Query order processing is: FROM, WHERE, GROUP BY, SELECT... Sooner you narrow result is better since next processed clause will have less to do.

Using join like this: SELECT ... FROM users u JOIN purchase p ON u.id=p.id ... will narrow result in the first processed clause - FROM clause. It will return only records satisfying join condition. In this example only users with at least one purchase.

In WHERE clause you narrow further. E.g. WHERE users.name like 'Dr%' Or WHERE region.country='c'

Next there are GROUP BY, ORDER BY and FETCH. E.g. ... ORDER BY purchase.price FETCH NEXT 100 ROWS ONLY

Optimizer is sophisticated piece of software. Sometimes it can rewrite a query from CTE or subquery into join. I do not know if MySQL's is smart enough though.

After trying several things, finally this worked for me: I renamed the existing C:\Temp folder to C:\Temp_old and created a new Temp folder in the C drive.

A bit of guessing:

SELECT c.first_name,
   c.last_name,
   c.email
   NULLIF(L.FIRST_NAME, C.FIRST_NAME) 
   ... 
   ... 
from users u
INNER JOIN contacts c ON u.id = c.user_id
LEFT JOIN leads l ON c.id = l.contact_id
where u.user_id=850

Host name re-naming will not automatically take effect sys.servers system table where the instance name is recorded. The https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server?view=sql-server-ver15 needs to be performed to make the hostname changes to reflect in sys.servers metadata table.

Update system metadata to reflect computer name changes for remote connections and applications that use @@SERVERNAME, or that query the server name from sys.servers.

To make the application changes easy, we create CName(DNS record) which is mapped to the hostname/AGName. And applications should use the CName in their connection strings instead of direct host name or AG name. So whenever there is a change to host or AG name, all you need to do is update the DNS mapping, so the applications will automatically redirected to use the new changes.

Ex: your cahanges will look like this before and after host name is updated.

Before:

applicationName                 Record Type     Destination
application01.domain.com        CNAME           hostname01

After: (hostname01 is updated to hostname02)

applicationName                 Record Type     Destination
application01.domain.com        CNAME           hostname02

Your applications must use application01.domain.com in their connection string.

Read more about CName usage https://support.dnsimple.com/articles/cname-record/ .

I want to generate and write https://github.com/boundary/flake to a column from clients connected to my PostgreSQL database. These IDs are essentially a large globally-unique numbers that grow with time, similar to a monotonically increasing ID but without any coordination.

The two most common query patterns would look this:

• Sort by the Flake ID SELECT * ORDER BY flake_id
• Group by another column, and then select the maximum Flake ID in each group. SELECT max(flake_id) GROUP BY some_other_column

There seem to be a few possible ways to do this:

• bigint with two columns. Simple to order by, but unclear how to select maximum across two words after a group-by.
• bytea with 16 bytes.
• bit(n) where n=128
• uuid happen to be 128-bit, and testing yields ORDER BY results, but there is no max function that can be applied to it
• encoded as a string, and stored in text haven't tested it but a max function seems nonsensical unless it uses a lexicographic order. It also seems a bit dirty to use a string to encode what is order-able on its own numerically.

There's a bit of choice paralysis in the options and how they interact with indexes to perform the above query patterns most efficiently.

I am looking for insight into the ideal data-type considering the above query patterns, and how it would interact with the relevant indexes.

This is a classic problem in any environment (cloud, VMWare, bare-metal)

What you need to do you are already doing: Delete the rows in the table in chunks but you need to delete all the rows. Why ???

The data and index pages (each 16K) in the InnoDB Buffer Pool carry the tablespace ID reference to the table they came from. When you drop am InnoDB table, are the data and index pages are checked to see if they are in the InnoDB Buffer Pool and are up-to-date. Those pages must be invalidated in the InnoDB Buffer Pool if they are there. That can take time as you already surmised.

Once all 8 billion rows are deleted, drop the table should be faster.

You have already done your due diligence in this are.

What to do next ???

SUGGESTION #1 : Truncate the Table

TRUNCATE TABLE mygianttable;

SUGGESTION #2 : Make the table BLACKHOLE

ALTER TABLE mygianttable ENGINE=BLACKHOLE;
DROP TABLE mygianttable;

SUGGESTION #3 : Restart MySQL with empty InnoDB Buffer Pool, and Drop Table

• Go to DB Parameter
• Set innodb_buffer_pool_dump_at_shutdown OFF
• Set innodb_buffer_pool_load_at_startup to OFF
• Restart RDS Instance (Starts with Empty Buffer Pool)
• Drop the Table
• Set innodb_buffer_pool_dump_at_shutdown to OriginalValue
• Set innodb_buffer_pool_load_at_startup to OriginalValue

Give it a Try !!!

Big question: How to rotate passwords in an Oracle database in a zero downtime (ZDT) way?

My current thought is to rotate the users. Originally, I had MY_USER that had all the tables and such. Now, I have:

CREATE USER MY_USER NO AUTHENTICATION;
GRANT CREATE SESSION TO MY_USER;
CREATE USER MY_USER_PROXY_1 IDENTIFIED BY "abc123";

GRANT CREATE SESSION TO MY_USER_PROXY_1;

ALTER USER MY_USER GRANT CONNECT THROUGH MY_USER_PROXY_1;

When I want to rotate the password, I simply create MY_USER_PROXY_2 and give the 'connect through' grant to MY_USER. This way, the app can continue to create new connections until I deploy it using the new user. Because of the 'connect through', the new user is essentially the same as the old user so everything should continue to work without much fanfare. Afterwards, I can decommission MY_USER_PROXY_1 or let the password normally expire

This seems like a reasonable approach if there is 1 'physical' user (MY_USER)

However, I am already using proxy users for multi-tenancy. Same as before, but instead of 1 user, there could be hundreds of users going through the same proxy user:

CREATE USER TENANT_PROXY_1 IDENTIFIED BY "abc123";
GRANT CREATE SESSION TO TENANT_PROXY_1;
-- During tenant onboard

CREATE USER TENANT_1234 NO AUTHENTICATION;

GRANT CREATE SESSION TO TENANT_1234;

ALTER USER TENANT_1234 GRANT CONNECT THROUGH TENANT_PROXY_1;

The issue with creating TENANT_PROXY_2 is recreating all the connect through grants. I could iterate through all the users like 'TENANT_%' and apply the grant that way but will always be a window of opportunity after TENANT_PROXY_2 is created and updated with the grants BUT BEFORE the app is restarted to use the new proxy user. So it would add the grant to TENANT_PROXY_1 and thus fail when the proxy user is rotated to TENANT_PROXY_2

I tried using roles (both as the connector and the connectee) but looks like only users are supported ( https://dba.stackexchange.com/questions/16887/is-it-possible-to-configure-oracles-connect-through-based-on-roles confirms this):

ALTER USER TENANT_1234 GRANT CONNECT THROUGH TENANT_PROXY_ROLE;
GRANT ROLE TENANT_PROXY_ROLE TO TENANT_PROXY_1;
-- or
ALTER ROLE TENANT_PROXY_ROLE GRANT CONNECT THROUGH TENANT_PROXY_1;
GRANT ROLE TENANT_PROXY_ROLE TO TENANT_1234;

My only thought is to pre-create TENANT_PROXY_1 through TENANT_PROXY_N and apply the grant to all N proxy users during onboarding and manually round-robin through the N users. Not quite as graceful, but still seems reasonable

I also tried 2 level proxying (e.g., TENANT_PROXY_1[TENANT_PROXY[TENANT_1234]] or TENANT_PROXY_1[TENANT_PROXY][TENANT_1234]) but that was a no-go as well

Any thoughts on either solution? Databases have been around for decades, is there any official solution or pattern to ZDT password rotation problem?

You won’t get close to SSD speeds no matter how you set up HDDs (well, unless you give them a cache). That said, the impact on your application performance is going to be against your IO time only. If you don’t do a lot of IO (because you have so much memory that nothing really hits the disk) then this could be fine. I find that if you build it (a database with fast storage) then they (IO intensive SQL) will come, so you’ve probably already made this a requirement going forwards.

In reality, you may be trying to solve a problem that doesn’t exist - are you really going to wear out your SSDs faster than you can afford? The network attached storage idea with RAID is fine as a general storage solution, but you’re going to want SSDs if your application is already relying on your database being supported by SSD.

This should work, duration filter is in Microseconds.

CREATE EVENT SESSION [captureProcDuration] ON SERVER 
ADD EVENT sqlserver.module_end(
    ACTION(
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.server_principal_name,
        sqlserver.sql_text,
        sqlserver.tsql_stack)
    WHERE (
        [object_name] = N'testProcDuration'
        AND [package0].[greater_than_uint64]([duration], (5000)))
    )
ADD TARGET package0.event_file(
    SET filename = N'captureProcDuration', max_file_size = (256)
)
WITH (
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
)
GO

Proc that my test worked:

CREATE OR ALTER PROC testProcDuration
AS
BEGIN
    WAITFOR DELAY '00:00:07'
END

The situation is as follows: I started at a new company several months ago in a support capacity. Part of my role will involve running queries in production to help diagnose application issues. I was given production db access (read only) on my first day. I was a bit worried about this but got over it. I then set about investigating an issue. One of the biggest tables is the ubiquitous orders table. I have to filter on 'CustomerField' (string data) and join on foreign keys. Now I looked at the schema and 'CustomerField' isnt even indexed. In fact there are barely any indexes throughout the whole database. What are the performance implications of running this query and others like it? It feels wrong, how worried should I be?