Geospatial join using ST_Contains or ST_Within seems slow, with costly query and bad estimate Problem
I have two tables, one which which includes a point column and the other which includes a boundary (table definitions below). I want to select rows from the first column where the point is within the boundary in the latter column. Writing a SELECT query using what seems like a pretty straight forward join like so ends up being quite slow.
SELECT temp_locations.id
FROM temp_locations
INNER JOIN temp_boundaries
ON st_within(temp_locations.point, temp_boundaries.mpoly)
WHERE temp_boundaries.id = 11;

From a table with just over 120k rows, this takes almost 12s to return fewer than 70k rows (the exact timing isn't important here, but this is running on a new, amply powered developer machine).
The point gemoetry field is indexed, as is the mpoly geometry.
The boundaries are not trivial, they are US state boundaries, but even still this seems... very surprising to me.
I've tried both ST_Within and ST_Contains, expecting no difference but curious none the less (no difference!). The existing documentation and posts I've found about this all seem to point to doing exactly what this query does.
Am I missing something obvious here? Is this a function of using multipolygon geometry, and for filtering this should be simplified? Or is the expected and reasonable performance for this kind of query?
Tables and indexes
CREATE TABLE temp_boundaries (
    id integer,
    uuid uuid,
    mpoly geometry(MultiPolygon,4326),
    name character varying(255)
);
CREATE TABLE temp_locations (
    id integer,
    point geometry(Point,4326),
    street_address character varying(255),
    street_address_2 character varying(255),
    city character varying(255),
    state character varying(2),
    zip_code character varying(10)
);

CREATE UNIQUE INDEX temp_location_pkey ON temp_locations(id int4_ops);
CREATE UNIQUE INDEX temp_boundary_pkey ON temp_boundaries(id int4_ops);

CREATE INDEX temp_location_point_id ON temp_locations USING GIST (point gist_geometry_ops_2d);
CREATE INDEX temp_boundary_mpoly_id ON temp_boundaries USING GIST (mpoly gist_geometry_ops_2d);

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
QUERY PLAN
Nested Loop  (cost=5.49..3530.86 rows=3080 width=4) (actual time=18.877..11298.389 rows=67498 loops=1)
  Output: temp_locations.id
  Buffers: shared hit=6157153
  ->  Index Scan using temp_boundary_pkey on public.temp_boundaries  (cost=0.29..8.30 rows=1 width=51649) (actual time=0.019..0.021 rows=1 loops=1)
        Output: temp_boundaries.mpoly
        Index Cond: (temp_boundaries.id = 11)
        Buffers: shared hit=3
  ->  Bitmap Heap Scan on public.temp_locations  (cost=5.21..3522.44 rows=12 width=36) (actual time=18.853..11277.169 rows=67498 loops=1)
        Output: temp_locations.id, temp_locations.point
        Filter: st_within(temp_locations.point, temp_boundaries.mpoly)
        Rows Removed by Filter: 4884
        Heap Blocks: exact=4072
        Buffers: shared hit=6157150
        ->  Bitmap Index Scan on temp_location_point_id  (cost=0.00..5.20 rows=123 width=0) (actual time=16.476..16.476 rows=72382 loops=1)
              Index Cond: (temp_locations.point @ temp_boundaries.mpoly)
              Buffers: shared hit=523
Planning Time: 2.262 ms
Execution Time: 11309.728 ms

PostgreSQL chooses the optimal plan and uses your index.
The time is probably spent in the 72382 executions of st_within that are required to verify that the rows found by the index actually satisfy the condition (the index operates with bounding boxes and consequently can return false positives, in your case 4884 of them).
The bitmap heap scan has to read 6 million 8kB blocks, which can also be an explanation for the long execution time. I don't know why, because points shouldn't be large. One possible explanation might be lots of dead tuples - does VACUUM temp_locations; help?


Oracle Database Client Installation 12c minimal install - Centos / Red Hat 7 I'm looking at reducing the Linux package prerequisites when installing the Oracle Database Client 12c on the below headless application server / stack:

Install file: V839967-01.zip
Application server Linux OS: CentOS Linux release 7.6.1810 (Core)
Application server platform: x86_64
Database Server hosting DB: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Database Server Linux OS: Red Hat Enterprise Linux Server release 7.3 (Maipo)
Database Server platform: x86_64

The below Oracle guide, on page 3-9 includes a number of packages that seem unnecessary for example smartmontools.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lacli/database-client-installation-guide-linux.pdf

The installer is being run silently using a response file:

./runInstaller -silent -force -debug waitForCompletion=true -responseFile /u01/app/12c_installer/client/client.rsp

There is also no need for the 32bit client on the application server.

Would trimming the package selection down to the following packages introduce any problems? :

bc.x86_64
binutils.x86_64
compat-libcap1.x86_64
compat-libstdc++-33.x86_64
glibc.x86_64
glibc-devel.x86_64
ksh.x86_64
libaio.x86_64
libaio-devel.x86_64
libgcc.x86_64
libstdc++.x86_64
libstdc++-devel.x86_64
make.x86_64
net-tools.x86_64
sysstat.x86_64

So you want to perform a minimal install and instead of using the Instant Client which is below 100MB with minimal requirements, you chose the full client that may occupy over 1 GB space and requires additional packages to be installed, but you want to go against the documentation and have an irregular configuration, that is still way more than what you would have to do by using the Instant Client.

Just use the Instant Client and follow the installation instructions of that.


SQL query for number of users I'm very new to SQL, so really need some tips.

I need some help building a query that'll get me all users that have logged into to our platform over the past 12 months.

In short, you can't do this unless you configure some logon auditing in advance. Your options to do that are:

Configure Login Auditing. This will log all successful logins to the SQL Server event log. I usually advise against this due to the volume of logins written
SQL Server Audit: create an audit to write all logins to a file or the event log.
Extended Events: create an extended events session to write all logins to a file. There is a pre-defined template to achieve this, create a session via the wizard and select the "connection tracking" template
Logon Triggers: create a logon trigger and write logins to a table adding any further bespoke logic you choose. For example you can just update a logon count for each login so you don't insert massive volumes of data.


The fulltext filter daemon host (FDHost) process stops abnormally on SQL Alwayson Cluster We added a new SQL Always-On Cluster (FCI) 2017 and some of the databases needed  full text filter service. So we also added that feature to the install. Ever since we did that we have been getting the error that it stopped abnormally every minute filling up our error log. Here is the full error message.

  The fulltext filter daemon host (FDHost) process has stopped abnormally. This can occur if an incorrectly configured or malfunctioning linguistic component, such as a wordbreaker, stemmer or filter has caused an irrecoverable error during full-text indexing or query processing. The process will be restarted automatically.

Is there any recommendations to avoid this or any best practices with regard to Full text service running on alwayson clusters?

First, verify the service account, whether it has appropriate permissions on the server.

Then  consider Re-configuring Word Breakers as previous version of Word Breakers might not compatible with SQL 2017. For more details


Trigram index on all values of a JSONB column I have a JSONB column that contains a bunch of key/value pairs, no complicated nested structures or arrays in there. The keys are dynamic, and I don't know them in advance. I'm using PostgreSQL 9.6.

What I'd like to do is to query the values in that JSONB column with a query like

ILIKE '%mysearchterm%'

which is very well supported by a trigram index. I've used them to index regular text columns, and it works very nicely for this kind of query, but I'm not sure how exactly to approach the JSONB column here.

Is there a way to dump all values (not the keys) from a JSONB column into a trigram index?

You can create a function which returns the data as a string:

CREATE OR REPLACE FUNCTION public.jsonb_val_to_string(jsonb)
 RETURNS text
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
declare x text;
BEGIN
  select string_agg(value,' ') into x from jsonb_each_text($1);
  return x;
END
$function$

And then create the index on that:

create index on jason using gin (jsonb_val_to_string(x) gin_trgm_ops);

And then query also using the function:

explain select * from jason where jsonb_val_to_string(x) ilike '%foo%';

Of course you might want to change the delimiter in string_agg to something you know will never be included in the ilike pattern.


Unable to query for thread information in mysql using root I'm connected to a server's mySql database hosted on google cloud using the user 'root' with a password.
I was under the impression that the root user had all privileges but when I try to get the thread information using SHOW ENGINE INNODB STATUS, I get an error:

[Error Code: 1227, SQL State: 42000] Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

Does it have something to do with me trying to access the database remotely?
What's the difference between root@localhost and root@%?

This is something you quickly learn with GCP (CloudSQL), AWS (RDS), and all Cloud Providers of MySQL: Even root does not have all privileges.

You could run SHOW GRANTS when logged in as root. Regardless of the privileges it says it has, the Cloud Provider will restrict it.

You asked for a difference between root@localhost and root@%

root@localhost will allow you to connect to mysqld from within the OS of the MySQL Instance. This requires access mysqld via the socket file. Using any
root@% will allow you to connect to mysqld from any server via TCP
Please read my post about how mysqld authenticates users (MySQL error: Access denied for user 'a'@'localhost' (using password: YES))

Sadly, regardless of which root user you use and what SHOW GRANTS says you have, the Cloud Provider will preempt any operation that uses SUPER and PROCESS. I have written 7 posts in the past 4 years describing how Amazon does this with RDS. So, it is not surprising Google does the same thing with CloudSQL.

After all, they don't want to expose the MySQL Container with fixed log file sizes, log file locations, datadir location, and the OS itself.


What is the performance impact of queries against DM objects using DMVs in SQL Server 2008? Recently, I've been running queries against our production system on the DMVs for querying the plan cache.  In particular, here's the from clause of the query:

FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('.//IndexScan/Object[@Index=sql:variable("@IndexName")]') AS idx(obj)

There's roughly 28k rows in dm_exec_cached_plans.

When I do this, it's setting off alerts for PLE dropping very low and very high disk IO and latency (read/write stalls).

This comes as a surprise because I've never read about or experienced anything relating to performance issues running adhoc queries against the DMVs.  This server has 24 cores, 64gb RAM and SSDs.  I know there is always more to it than that, but I have a hard time believing these queries cause such a problem.

Can anyone explain exactly why this happens?  I'm happy to provide more info about our configuration, but I was hoping there would be a simple explanation as well.

There are other DMVs that can help you quantify this.  sys.dm_exec_sessions has columns cpu_time, memory_usage and total_scheduled_time amongst others.  While you're running your plan-getting query in one session you can interrogate this DMV in a second session to find how expensive the first is.

Pleasingly, you can also quantify how expensive your second session is at the same time!


How to deal with seldom used big database and postgresql? I loaded in PostgreSQL (9.3) OpenStreetMap data for whole europe (400gb). Then I installed a geocoding api (nominatim) that queries the database with gis queries.

My problem is :

  This database is queried a few times a day, but because postgres loads
  the data in its buffer on demand, my first query on a particular gps
  point is always slow, and I do only one query per GPS point, so it's
  always slow (like 60sec slow against 100ms when the data is buffered)

.

What could I do here ?

I'm using a A6 Azure instance (4 Cores, 28 GB memory, HDD).
Server is Ubuntu 14.04.4 LTS
Swapping is enabled
There is index on the geometry columns queried.
The shared buffer is 10GB
work mem is 256MB
maintenance work mem is 4GB

depending on your usage, you could try to partition the data to reduce the scanned data (at 400gb even your indexes are large and heavy to read...)
I think it's your best option as working with 400gb tables is heavy even on a very optimized queries...
You could also pre-warm the cache with the index data , by issuing some of your queries on frequent gps points or by using something like pg_prewarm


How can I view when mysql mixed mode replication is logging a statement using row or statement format? I have a couple of servers running in MIXED mode replication and I do maintenance regularly on them deleting a decent sized set of data. There is one table that had no primary key and is just used for logging. When we delete old data out of this table it runs fast on either the slave or the master but when it's part of a script that runs over night it blocks and runs very slowly on the slave.  This was working fine when we were using statement based replication everywhere on an older version of MySQL.

The table has a structure something like this:

CREATE TABLE `user_events` (
  `event_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `event_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `type` tinyint(3) unsigned DEFAULT '0',
  `ipnum` int(10) unsigned NOT NULL,
  INDEX(`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The delete statement is a simple.

 DELETE FROM `user_events` WHERE `event_time` < CURDATE()

Doing research I discovered that it is possible that this is because the system is switching over to row-based replication for this particular statement. In particular it looks a lot similar to this bug: http://bugs.mysql.com/bug.php?id=53375

Only this delete does not use RAND() or LIMIT so it should not be nondeterministic. So I'm not sure why it is using row based. It might be because the script it is being run in is using temporary tables. I could confirm this by running the delete inside and outside a script that uses temporary tables easily enough if I could see whether the statement was logged using row or statement based replication.

If I can confirm when a particular statement is run whether it was written row or statement based I can quickly solve the problem. I would also like to know this in general so I can determine which if any statements we are using are being pushed via row based replication.

I know the ultimate solution is to change to partitioning or adding a primary key and breaking the deletes into chunks and I'm also aware I can force the replication mode to statement for a session or even tell the sql statement not to write to the bin log and run an equivalent delete on the slave, but I'm right now just looking for a better way to understand and diagnose the problem. Thanks.

The table has no index on event_time. Your DELETE must therefore perform a full table scan every time. This has to be a big contributor to the slowness.

SUGGESTION #1

Add an index

ALTER TABLE user_events ADD INDEX (event_time);

SUGGESTION #2

Instead of doing

DELETE FROM `user_events` WHERE `event_time` < CURDATE();

Try loading a temp table and switching it into place

ALTER TABLE user_events RENAME user_events_old;
CREATE TABLE user_events_new LIKE user_events_old;
INSERT INTO user_events_new
    SELECT * FROM user_events_old
    WHERE event_time >= CURDATE() + INTERVAL 0 SECOND
;
ANALYZE TABLE user_events_new;
ALTER TABLE user_events_new RENAME user_events;
DROP TABLE user_events_old;

EPILOGOUE

Suggestion #1 may be all you need. Suggestion #2 is an alternative means of deleting old rows.

YOUR QUESTION

If you have binary logging on the Slave and the Slave is not a Master, disable it.

If you want to see which queries are expanding to ROW rather than STATEMENT, use mysqlbinlog against the binary logs on the Master to see a text representation of all queries. The row-based ones will be a little obfuscated. The statement-based queries will appear as it was executed.


Does the order I declare foreign key constraints in matter? Does it matter what order I add foreign key constraints to a table in, or what order I write them in a CREATE TABLE statement? MySQL clearly at least remembers the order; if I run the DDL statements below...

CREATE TABLE foo(id INT PRIMARY KEY);
CREATE TABLE bar(id INT PRIMARY KEY);
CREATE TABLE baz (
  foo_id INT,
  bar_id INT,
  FOREIGN KEY (bar_id) REFERENCES bar (id),
  foreign key (foo_id) REFERENCES foo (id)
);
CREATE TABLE qux (
  foo_id INT,
  bar_id INT,
  FOREIGN KEY (foo_id) REFERENCES foo (id),
  FOREIGN KEY (bar_id) REFERENCES bar (id)
);

... and then run SHOW CREATE TABLE on each of baz and qux...

mysql> SHOW CREATE TABLE baz\G
*************************** 1. row ***************************
       Table: baz
Create Table: CREATE TABLE `baz` (
  `foo_id` int(11) DEFAULT NULL,
  `bar_id` int(11) DEFAULT NULL,
  KEY `bar_id` (`bar_id`),
  KEY `foo_id` (`foo_id`),
  CONSTRAINT `baz_ibfk_1` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`id`),
  CONSTRAINT `baz_ibfk_2` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE qux\G
*************************** 1. row ***************************
       Table: qux
Create Table: CREATE TABLE `qux` (
  `foo_id` int(11) DEFAULT NULL,
  `bar_id` int(11) DEFAULT NULL,
  KEY `foo_id` (`foo_id`),
  KEY `bar_id` (`bar_id`),
  CONSTRAINT `qux_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`),
  CONSTRAINT `qux_ibfk_2` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

... then we can see that MySQL remembered that the foreign key pointing to foo comes first in baz, but second in qux.

It's possible to imagine this order making a difference; for instance, if MySQL, upon insert into a table with foreign keys, takes out locks on referenced tables in the order that the foreign keys are declared, then simultaneous inserts into baz and qux in my example above would have the potential to deadlock, and so it would be a best practice to ensure that foreign keys were always declared in the same order on all tables (e.g. by alphabetical order of the referenced table name). Do any such considerations exist in reality, or does the order of foreign key constraints have no effect on anything?

The order of FK Definition does not matter. When a transaction on table with FKs completes, it will release the lock on referenced tables simultaneously.


SQL query to check a table against a "rules table" I have a table PIT that looks like this:

PIT_TYPE    PIT_STYPE   PIT_APID
1           1           1313
1           7           1313
3           25          1316
247         1           1318
247         2           1318
247         4           1318
247         5           1318
5           2           1319
5           3           1319

I have a rules table that defines the rules for PIT. The rules table looks like this:

Type    SType   Category
1               1
3         26    8
3         25    5
3         25    4
5               7
6               9
9               0
13             10
17         2    7
21             12
22             11

PIT_APID mod 16 = Category E.g. 1313 mod 16 = 1

The rules specify following:

If PIT_TYPE / PIT_SType match no rule in the rules table --> No error
If PIT_TYPE / PIT_SType match a rule in the rules table and the category matches --> No error
If PIT_TYPE / PIT_SType match a rule in the rules table but the category is different --> Error

(If SType is empty in the rules table we don't care about the PIT_STYPE in PIT. Type is never empty)

How can I write a SQL query that returns all the rows in PIT that have an error according to the defined rules?

What I have tried is:

SELECT PIT.PIT_TYPE, tblCategoryRules.Service, PIT.PIT_STYPE, tblCategoryRules.Sub_Service, PIT.PIT_APIT , PIT.PIT_APIT MOD(16) as PCAT, tblCategoryRules.Category
FROM PIT
INNER JOIN tblCategoryRules
ON PIT.PIT_TYPE =  tblCategoryRules.Service
WHERE  (tblCategoryRules.Sub_Service IS NULL or  tblCategoryRules.Sub_Service = PIT.PIT_STYPE) AND tblCategoryRules.Category <> PIT.PIT_APIT MOD(16)

This works for everything except the rule that

Type    SType   Category
3         25    5
3         25    4

The rule that it can either be 5 or 4 is what causes the problem

I'm not real familiar with Access syntax, but I think the general idea you're after would be something like this... Instead of filtering for only records that don't meet your requirements, add a calculation to your select list that indicates if the record/ruleset combination resulted in a pass or fail.  Then group by your other selected columns, and and look for groups that have no passing records and at least one failing record.


Declaring cursor to loop over some values in SQL Server 2008 R2 I have some business unit such as India, International, US, UK.

I have to:

Create a table variable (virtual in-memory table) with columns that match stored procedure SP_Report resultset
Declare a cursor that contains all business units and then the cursor should loop over the BU's
For each BU, execute a stored procedure called SP_Report and insert the data into the table variable
Finally I have to select the columns from the table variable + timestamp + BU, insert them into another table and clear the table variable.

Code:

declare @K table (BU nvarchar(max), K nvarchar(max),Y money, A money, D money, YP money)
declare @FY int
declare @BU nvarchar(max)

INSERT INTO @K (BU, K, Y, A, D, YP)
    EXEC dbo.SP_Report '2012', 'India'

SELECT * FROM @K

This code returns the result of the table variable. Now I have to use it cursor, which I don't know how to. How I can solve the same.

The MSDN/BOL topic for DECLARE CURSOR contains example syntax for cursor usage. That said, as I had 5 minutes to spare on the train this morning...

-- Create temporary table for Business Units (replace with the "real" table)
DECLARE @BusinessUnit TABLE (BU NVARCHAR(MAX))

INSERT @BusinessUnit
SELECT 'India' UNION SELECT 'International' UNION SELECT 'US' UNION SELECT 'UK'

DECLARE @K TABLE (BU NVARCHAR(MAX),K NVARCHAR(MAX),Y MONEY,A MONEY,D MONEY,YP MONEY)
DECLARE @FY INT
DECLARE @BU NVARCHAR(MAX)

SET @FY = 2012

DECLARE BU_cursor CURSOR FAST_FORWARD FOR
SELECT BU FROM @BusinessUnit

OPEN BU_cursor
FETCH NEXT FROM BU_cursor INTO @BU

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT @K (BU, K, Y, A, D, YP)
    EXEC dbo.SP_Report @FY, @BU

    FETCH NEXT FROM BU_cursor INTO @BU
END

CLOSE BU_cursor
DEALLOCATE BU_cursor

SELECT * FROM @K


Truncate Table With Partitions - aligned/ non-aligned indexes SQL Server 2016 introduced a modification to the TRUNCATE TABLE statement which allows you to truncate a specific partition or set of partitions from a table:

  TRUNCATE TABLE < tablename > WITH (PARTITIONS (< partition_id >));

An article about the TRUNCATE TABLE WITH PARTITIONS contains the next note:

  To start using the new TRUNCATE TABLE statement option, make sure that
  all your table indexes are aligned with the source table. If there are
  non-aligned indexes in your table, drop or disable these indexes,
  truncate the partition(s) you want then rebuild the indexes again.

Does SQL Server 2016 provide an easy way (e.g. a query) to determine if the indexes of the partitioned tables are all aligned? (I want to avoid having to drop or disable indexes.)

Update 1

Example output of link:

*One index consists of 2 columns, but only one column is used in the partition function.

Is the index PK_TEST1 aligned or non-aligned?*

index_name: PK_TEST1
partition_function: psTEST1
partition_ordinal: 1
key_ordinal: 1
is_included_column: 0
columnName: COLUMN1

#

index_name: PK_TEST1
partition_function: psTEST1
partition_ordinal: 0
key_ordinal: 2
is_included_column: 0
columnName: COLUMN2

I put this query together for you that should show you what you want.  You can add additional where clauses to trim it further if you need to.

;WITH CTE_PartCount AS
    (
    SELECT P.object_id
        , P.index_id
        , COUNT(P.partition_number) AS PartitionCount
    FROM sys.partitions AS P
    GROUP BY P.object_id
        , P.index_id
    )
, CTE_Objects AS
    (
    SELECT O.object_id
        , O.name AS ObjectName
        , S.name AS SchemaName
        , I.index_id
        , I.name AS IndexName
        , I.type_desc AS IndexType
    FROM sys.objects AS O
        INNER JOIN sys.schemas AS S ON S.schema_id = O.schema_id
        INNER JOIN sys.indexes AS I ON I.object_id = O.object_id
    WHERE O.is_ms_shipped = 0
        AND O.type_desc = 'USER_TABLE'
    )
, CTE_Summary AS
    (
    SELECT O.object_id
        , O.ObjectName
        , O.SchemaName
        , O.IndexName
        , O.IndexType
        , PC.PartitionCount
        , TablePartCount = FIRST_VALUE(PC.PartitionCount) OVER (PARTITION BY O.object_id ORDER BY O.object_id, O.index_id)
    FROM CTE_Objects AS O
        LEFT OUTER JOIN CTE_PartCount AS PC ON PC.object_id = O.object_id AND PC.index_id = O.index_id
    )
SELECT S.object_id
    , S.SchemaName
    , S.ObjectName
    , S.IndexName
    , S.IndexType
    , S.PartitionCount
    , IsPartitioned = CASE WHEN S.TablePartCount <> 1 THEN 'YES' ELSE 'NO' END
    , IsAligned = CASE WHEN S.TablePartCount = S.PartitionCount THEN 'ALIGNED' ELSE '' END
FROM CTE_Summary AS S


sql server spatial index performance I have a table with around 2 million records in it. I created a spatial index, using the defaults other than the bounding box. I've been noticing that some queries are extremely fast, and some are extremely slow. The determining factor appears to the size of the polygon used in the query.

On larger search areas, using WITH(INDEX(SIX_FT5)) slows down the query considerably (from 0 seconds, to 15+ seconds). On smaller search areas, the exact opposite it true.

Here's some of the queries I'm testing with:

Fast:

SELECT TOP(1000)
  *
FROM [FT5]
WHERE
  shape.STIntersects(geometry::STGeomFromText('POLYGON ((-133462.805381701 -668610.241000959, 2934415.68824241 -668610.241000959, 2934415.68824241 2200521.65831815, -133462.805381701 2200521.65831815, -133462.805381701 -668610.241000959))', 2264)) = 1

Slow:

SELECT TOP(1000)
  *
FROM [FT5] WITH(INDEX(SIX_FT5)) -- Index hint is the only difference
WHERE
  shape.STIntersects(geometry::STGeomFromText('POLYGON ((-133462.805381701 -668610.241000959, 2934415.68824241 -668610.241000959, 2934415.68824241 2200521.65831815, -133462.805381701 2200521.65831815, -133462.805381701 -668610.241000959))', 2264)) = 1

Why is that WITH clause slowing things down sometimes?

That your indexing is slowing some queries but not others has a lot to do with the way spatial indexes work:

The spatial index is broken into 4 levels which are broken into grids.  With each level you will be able to specify a density which of low, medium or high corresponding to 16, 64 or 256 cells respectively. Then within each cell these are then subdivided into the next lower level with its setting.

All of this means that depending on your settings and your data distribution you may need to adjust the density at different levels to get proper index performance.  This may also mean that you need to balance your performance of your main queries of this data against your edge cases.

Take a look at this Microsoft TechNet library article for a more detailed explanation:

Spatial Indexing Overview


PostgreSQL slow query log entries contain no query details, only duration I have a PostgreSQL RDS instance hosted in AWS. I have set the log_min_duration_statement setting to 1 second. When checking my log for slow queries I found the following six entries which don't contain any query/statement:

Does anyone have any idea why this happened? How can I find out which slow queries caused these log entries?

UPDATE Thanks to jjanes for pointing me in the right direction. I found that the "log_statement" option causes duration and statement to be logged separately. Here it is in Postgre docs: https://www.postgresql.org/docs/9.1/runtime-config-logging.html#GUC-LOG-MIN-DURATION-STATEMENT

You may have log_duration set to on.
If so, changingit to off should allow you to get statements with your log_min_duration_statement set to 1.


How to select Overdue Rows with Date Frequencies? +------------------------+--------+
|  Invoice_id | due_date | amount |
+-------------+----------+--------+
|  20         |2020-01-18|  1250  |
+-------------+----------+--------+
| 21          |2020-01-15|  1335  |
+-------------+----------+--------+

Get all Records with date passed n days and its multiple serires
like below...

for example n=5

SELECT * FROM `invoices`
WHERE `due_date = DATE_ADD(CURDATE() + INTERVAL 5 days)
OR due_date = DATE_ADD(CURDATE() + INTERVAL 10 days)
OR due_date = DATE_ADD(CURDATE() + INTERVAL 15 days)`

but i want to make it universal for any n value

  Note: mySQL version in my machine is 5.*

WITH RECURSIVE
cte AS
( SELECT @starting_deviation num
  UNION ALL
  SELECT num + @delta_deviation
  FROM cte
  WHERE num < @final_deviation )
SELECT *
FROM invoices
JOIN cte ON invoices.due_date = CURRENT_DATE + INTERVAL cte.num DAY

For the query from the question @starting_deviation := 5, @delta_deviation := 5 and @final_deviation := 15. These values may be specified literally (inserted immediately into the query instead of variables), transferred via variables values assignement or taken from some service table.

In MySQL 5+ you must generate the numbers table in a subquery or use external static numbers table.

SELECT *
FROM invoices
JOIN ( SELECT 5 num UNION ALL
       SELECT 10 UNION ALL
       SELECT 15 ) cte ON invoices.due_date = CURRENT_DATE + INTERVAL cte.num DAY

  i want to add is my frequency is not upto 3 series. it can be more than 3 maybe any number.... so for example if n is 5 and i want 10 frquesnces means 5,10, 15, 20......, 50

Use numbers generator. For example it can be (shown generator may give up to 64 values):

SELECT *
FROM invoices
JOIN ( SELECT 5 * (1 + t1.num + t2.num * 4 + t3.num + 16) num
       FROM (SELECT 0 num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t1,
            (SELECT 0 num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t2,
            (SELECT 0 num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t3
     ) cte ON  invoices.due_date = CURRENT_DATE + INTERVAL cte.num DAY
WHERE cte.num BETWEEN 1 AND 10;


Mysql Server Crashing Regularly Error Log: MYSQLD log

140925 20:47:19 mysqld_safe Number of processes running now: 0
140925 20:47:19 mysqld_safe mysqld restarted
140925 20:47:26  InnoDB: Initializing buffer pool, size = 56.0M
140925 20:47:26  InnoDB: Completed initialization of buffer pool
InnoDB: **The log sequence number in ibdata files does not match**
InnoDB: **the log sequence number in the ib_logfiles!**
140925 20:47:26  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
140925 20:47:32  InnoDB: Started; log sequence number 4 3219462995
140925 20:47:32 [Note] Event Scheduler: Loaded 0 events
140925 20:47:32 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.71'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
Messages Log :

Sep 25 08:15:02 s1 kernel: Out of memory: Kill process 28796 (php-cgi) score 18 or sacrifice child
Sep 25 08:15:02 s1 kernel: Killed process 28796, UID 528, (php-cgi) total-vm:340760kB, anon-         rss:40048kB, file-rss:21088kB
Sep 25 08:15:03 s1 kernel: php-cgi invoked oom-killer: gfp_mask=0x280da, order=0, oom_adj=0,     oom_score_adj=0
Sep 25 08:15:03 s1 kernel: php-cgi cpuset=/ mems_allowed=0

Server Details:
Operating system: Centos 6.2 linux server,
Memory: 4GB
Swap Size: 2GB

Steps taken to resolve the Issue:

Enabled innodb_force_recovery=4 to commit the .ibd files and to mysql server.
Tried installing MYSQLTUNER to monitor query activities.However cudnt install it.

Behavior

Have to manually restart it after it shuts down. Not able to restart it automatically using mysqlsafe
Have been seeing this issue since past few days and also a suspect spike in number of hits which reflects in my AW stats.
Will appreciate if someone help me know where to look for solutions and help me configure the mysql config file for a proper performance and memory usage.

My.conf settings:

[mysqld] datadir=/var/lib/
mysql socket=/var/lib/mysql/mysql.sock
 user=mysql
 symbolic-links=0
 innodb_file_per_table = 1
 innodb_buffer_pool_size = 56M
 thread_concurrency = 8
 query_cache_size = 32M
 thread_cache_size = 8
 myisam_sort_buffer_size = 64M
 read_rnd_buffer_size = 8M
 read_buffer_size = 2M
 sort_buffer_size = 2M
 table_cache = 512
 max_allowed_packet = 1M
 key_buffer = 384M
 [mysqld_safe] log-error=/var/log/mysqld.log
 pid-file=/var/run/mysqld/mysqld.pid

Something is eating your Ram, possibly the mysql recover. Add more swap (dd if=/dev/zero count=1000 bs=1M of=swapfile; mkswap swapfile; swapon swapfile)  go to singleuser mode (init 1) so that nothing else is running and start your mysql recover.
Watch with top or htop for memory usage.


Can a session variable be set to the contents of a function parameter using SET SESSION? I'd like to set a session variable inside of a function, using the values passed into the function.

Example:

CREATE FUNCTION my_function(my_param INT)
RETURNS VOID AS $$
BEGIN
    SET SESSION "myapp.myvar" = my_param;
END
$$
LANGUAGE plpgsql;

SELECT * FROM my_function(100);
SELECT current_setting('myapp.myvar');

What I get:

my_param

What I want:

100

Use set_config()

CREATE FUNCTION my_function(my_param INT)
RETURNS VOID AS $$
BEGIN
    perform set_config('myapp.myvar', my_param::text, true);
END
$$
LANGUAGE plpgsql;


Is DROP/ADD UNIQUE constraint in ALTER TABLE atomic? I have a UNIQUE constraint of a number of fields on a table. Image that I want to change the fields in this constraint (remove one field). Normally I would do something like:

DROP INDEX unique_name ON table_name;
CREATE UNIQUE INDEX unique_name ON table_name (field1, field2, ...);

But these are two statements, and after executing the first one there will be no UNIQUE constraint anymore, so any INSERT may potentially break the constraint, until new UNIQUE is created.

There is another way to do that:

ALTER TABLE table_name
DROP INDEX unique_name,
ADD CONSTRAINT unique_name UNIQUE (field1, field2, ...);

This is one statement. The questions is: will this statement perform an atomic operation? By atomic I mean that there is no time window when there is no UNIQUE constraint at all.

According to the official ALTER TABLE documentation, there should be no issue with such an operation and there is no window when there is no UNIQUE constraint at all. See (emphasis mine):

  Storage, Performance, and Concurrency Considerations

  In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that are modifying the table, then proceeds. It incorporates the alteration into the copy, deletes the original table, and renames the new one. While ALTER TABLE is executing, the original table is readable by other sessions (with the exception noted shortly). Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates. The temporary copy of the original table is created ...

Of course nothing forbids you from doing the operation in two steps (but this will probably be less efficient).

first add the new UNIQUE constraint (with a different name):

ALTER TABLE table_name
    ADD CONSTRAINT unique_name_2
    UNIQUE (field1, field2, ...) ;

the delete the old one:

ALTER TABLE table_name
    DROP INDEX unique_name ;

This allows a small window where you'll have both constraints but again no time where none of them is there.


How should I structure my time based index? So I have this table in SQL Server 2008.  Let's just say it's called TimeSheets.

Now, that table has two columns I am concerned with.

TimeSheetDate and UserCustomerKey.

TimeSheetDate is a DateTime column and UserCustomerKey is a GUID.

UserCustomerKey is the primary ID of a join table for Users and Customers.

Typically, the primary application is going to query TimeSheets by the TimeSheetDate and UserCustomerKey.  And since we're dealing with time sheets, we usually need the most recent time sheets for reports.

So I created an index like:

TimeSheetDate (DESC)
UserCustomerKey (ASC)

Since we normally care about descending time sheet searches (most recent time sheets from today), this index makes sense.

However, there may be applications that JUST search by TimeSheetDate and not care about the UserCustomerKey.

1)  So, do I need to also create a single index for TimeSheetDate or will the above index also include it?

2)  Do I need to create another index that has UserCustomerKey BEFORE TimeSheetDate or does it matter?

I don't want to waste resources if I don't have to.

Thanks.

If a single column is used by a query for filtering, it can use an index if the that column is the left-most column in the index.

So assuming the TimeSheetDate is the left-most column in your index, you don't need to create another index for it.

Whether or not you should create another index in a reversed order is dependent on the selectivity of the UserCustomerKey column and whether queries filter using it.


MongoDB shell version error (Server has startup warnings) I installed MongoDB under Ubuntu server and when inter to MongoDB shell i got this error and warnings.

MongoDB shell version: 2.4.7
connecting to: test
Server has startup warnings:
Sun Oct 27 09:58:39.360 [initandlisten]
Sun Oct 27 09:58:39.360 [initandlisten] ** NOTE: This is a 32 bit MongoDB binary.
Sun Oct 27 09:58:39.360 [initandlisten] **       32 bit builds are limited to less than 2GB of data (or less with --journal).
Sun Oct 27 09:58:39.360 [initandlisten] **       Note that journaling defaults to off for 32 bit and is currently off.
Sun Oct 27 09:58:39.360 [initandlisten] **       See http://dochub.mongodb.org/core/32bit
Sun Oct 27 09:58:39.360 [initandlisten]

how I hide this warnings
Thanks

I don't believe there is a way to suppress only that warning, but you can always start your mongod instance with the --quiet flag.

  --quiet Runs the mongod instance in a quiet mode that attempts to limit the amount of output. This option suppresses:

  output from database commands, including drop, dropIndexes,
  diagLogging, validate, and clean. replication activity. connection
  accepted events. connection closed events.

  Reference - http://docs.mongodb.org/manual/reference/program/mongod/#cmdoption--quiet

So when you use this flag, you are actually disabling quite a lot of useful information. As you can see from the content of the warning, using the 32bit version will seriously limit the amount of data you will be able to store. I think you should consider upgrading to the 64bit version.


How to include concatenated values in a dynamic pivot column value? I am extending a dynamic pivot query. The original query works fine. In the second query, instead of displaying the date as a value for the pivot column, I would like to display a concatenated column. This concatenated column will contain datetime ~ user name ~ record **id.  I can't seem to make the extended query work with a concatenated column in the pivot column value, any ideas?

Original Query**

DECLARE @PivotColumnHeaders NVARCHAR(MAX)

SELECT @PivotColumnHeaders =
   COALESCE(
     @PivotColumnHeaders + ',[' +  cast(SystemFullName as Nvarchar) + ']',
     '[' + cast(SystemFullName as varchar)+ ']'
   )
FROM System
WHERE (@SelectedSystemIDs IS NULL OR  System.ID IN(select  * from dbo.SplitInts_RBAR_1(@SelectedSystemIDs, ',')))
AND ((@PlatformID =0) OR  (System.PlatformID = @PlatformID) OR (@PlatformID = 12 AND System.PlatformID <= 2))

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
   SELECT *
   FROM (
     SELECT
       TSBNumber [TSBNumber],
       SystemFullName,
       ClosedDate
     FROM ServiceEntry
     inner JOIN System
       ON ServiceEntry.SystemID = System.ID
     where
      (ServiceEntry.TSBNumber IS NOT NULL)
       AND
       (ServiceEntry.ClosedDate IS NOT NULL)
       AND
       (
       (''' + @SelectedTsbIDs + ''' = '''+ '0' + ''') OR
         (ServiceEntry.TSBNumber in (select * from dbo.SplitStrings_Moden(''' + @SelectedTsbIDs + ''', ''' + ',' + ''')))
        )
        AND (
         (''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = '''+ '0' + ''')
        OR(System.PlatformID = ''' + cast(@PlatformID as varchar(10)) + ''')
        OR(''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = ''' + '12' + ''' AND System.PlatformID <=  ''' + '2' + ''')
        )
        AND
        (ServiceEntry.ClosedDate between ''' + convert(varchar(10), @StartDate, 120)  + '''  and   ''' +  convert(varchar(10), @EndDate, 120) + ''')
  )

   ) AS PivotData
   PIVOT (
     MAX(ClosedDate)
     FOR SystemFullName IN (
       ' + @PivotColumnHeaders + '
     )
   ) AS PivotTable
'

EXECUTE (@PivotTableSQL)

Extended Query with Concatenated Column

SELECT @PivotColumnHeaders =
   COALESCE(
     @PivotColumnHeaders + ',[' +  cast(SystemFullName as Nvarchar) + ']',
     '[' + cast(SystemFullName as varchar)+ ']'
   )
FROM System
WHERE (@SelectedSystemIDs IS NULL OR  System.ID IN(select  * from dbo.SplitInts_RBAR_1(@SelectedSystemIDs, ',')))
AND ((@PlatformID =0) OR  (System.PlatformID = @PlatformID) OR (@PlatformID = 12 AND System.PlatformID <= 2))

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
   SELECT *
   FROM (
     SELECT
       TSBNumber [TSBNumber],
       SystemFullName,
       CONVERT(VARCHAR(10),ServiceEntry.ClosedDate,105) + '~' + us.FirstName + us.LastName + '~' +  CAST(ServiceEntry.ID AS VARCHAR(10)) AS CONCATCOLUMN

     FROM ServiceEntry
     inner JOIN System
       ON ServiceEntry.SystemID = System.ID
     LEFT JOIN [dbo].[User] as US ON ServiceEntry.CreatedBy =[dbo].[User].ID as US
     where
      (ServiceEntry.TSBNumber IS NOT NULL)
       AND
       (ServiceEntry.ClosedDate IS NOT NULL)
       AND
       (
       (''' + @SelectedTsbIDs + ''' = '''+ '0' + ''') OR
         (ServiceEntry.TSBNumber in (select * from dbo.SplitStrings_Moden(''' + @SelectedTsbIDs + ''', ''' + ',' + ''')))
        )
        AND (
         (''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = '''+ '0' + ''')
        OR(System.PlatformID = ''' + cast(@PlatformID as varchar(10)) + ''')
        OR(''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = ''' + '12' + ''' AND System.PlatformID <=  ''' + '2' + ''')
        )
        AND
        (ServiceEntry.ClosedDate between ''' + convert(varchar(10), @StartDate, 120)  + '''  and   ''' +  convert(varchar(10), @EndDate, 120) + ''')
   ) AS PivotData
   PIVOT (
    MAX(CONCATCOLUMN)
     FOR SystemFullName IN (
       ' + @PivotColumnHeaders + '
     )
   ) AS PivotTable
'

You can use REPLACE on the date column in the @PivotColumnHeaders string to build the concatenated column.

SELECT @PivotColumnHeaders =
   COALESCE(
     @PivotColumnHeaders + ',[' +  cast(SystemFullName as Nvarchar) + ']',
     '[' + cast(SystemFullName as varchar)+ ']'
   )
FROM System
WHERE (@SelectedSystemIDs IS NULL OR  System.ID IN(select  * from dbo.SplitInts_RBAR_1(@SelectedSystemIDs, ',')))
AND ((@PlatformID =0) OR  (System.PlatformID = @PlatformID) OR (@PlatformID = 12 AND System.PlatformID <= 2))

REPLACE(@PivotColumnHeaders, *datecolumn*, *datecolumn* + '' '' + username + '' '' + CONVERT(nvarchar(10), recorded))


Sync SQL Server Transaction Logs to Redshift I am researching the plausibility of syncing SQL Server logs to an AWS Redshift data warehouse.

Redshift has the COPY command to do parallel loads from S3 to Redshift already. Additionally, there are many 3rd party tools that promise near synchronous replication of the transaction logs.

Are these 3rd party tools the only solution?
Are these tools faster than an S3 COPY?
Is there an SQL Server based solution that still includes Redshift?

Nothing exists natively from SQL Server so 3rd party is your best bet. Homegrown is not recommended because SQL Server's transaction log format is not published so it'll be a pretty steep curve.

sp_BlitzErik's suggestion to talk to the vendors is the best place to start. If you don't have the right contacts or not enough vendors to evaluate, check out the RS data integration partners on https://aws.amazon.com/redshift/partners/1

You can also contact AWS directly or post a feature request on the AWS RS forum https://forums.aws.amazon.com/forum.jspa?forumID=155&start=0


Optimizing a slow MySQL query I'm trying to optimize a ~100GB database. All data values are in one single innodb table, is this bad? Here's what a common query looks like:

SELECT `CensorCode`, `LocalDateTime`, `UTCOffset`, `DateTimeUTC`, `MethodID`, `SourceID`, `QualityControlLevelID`, `DataValue`, `OffsetTypeID`, `OffsetValue`, `d`.`SampleID`, `s`.`LabSampleCode`, `d`.`QualifierID`, `q`.`QualifierCode`
FROM (`datavalues` d)
LEFT JOIN `samples` s ON `d`.`SampleID` = `s`.`SampleID`
LEFT JOIN `qualifiers` q ON `d`.`QualifierID` = `q`.`QualifierID`
WHERE `d`.`SiteID` =  '15'
AND `d`.`VariableID` =  '8'
AND `d`.`LocalDateTime` >= '2009-04-18T00:02'
AND `d`.`LocalDateTime` <= '2009-04-20T00:02'

Here's what the explain statement on the above query looks like:

| id | select_type | table | type        | possible_keys                               | key                                         | key_len | ref               | rows    | Extra                                                                     |
+----+-------------+-------+-------------+---------------------------------------------+---------------------------------------------+---------+-------------------+---------+---------------------------------------------------------------------------+
|  1 | SIMPLE      | d     | index_merge | FK_DataValues_Variables,FK_DataValues_Sites | FK_DataValues_Sites,FK_DataValues_Variables | 4,4     | NULL              | 1390435 | Using intersect(FK_DataValues_Sites,FK_DataValues_Variables); Using where |
|  1 | SIMPLE      | s     | eq_ref      | PRIMARY                                     | PRIMARY                                     | 4       | odm.d.SampleID    |       1 |                                                                           |
|  1 | SIMPLE      | q     | eq_ref      | PRIMARY                                     | PRIMARY                                     | 4       | odm.d.QualifierID |       1 |

Does this mean it's using indexes? If not, could someone explain how to create an index, I don't understand what it means. Any other suggestions would be greatly appreciated. Thanks in advance.

Also, here is the output from SHOW CREATE TABLE

| datavalues | CREATE TABLE `datavalues` (
  `ValueID` int(11) NOT NULL AUTO_INCREMENT,
  `DataValue` double NOT NULL,
  `ValueAccuracy` double DEFAULT NULL,
  `LocalDateTime` datetime NOT NULL,
  `UTCOffset` double NOT NULL,
  `DateTimeUTC` datetime NOT NULL,
  `SiteID` int(11) NOT NULL,
  `VariableID` int(11) NOT NULL,
  `OffsetValue` double DEFAULT NULL,
  `OffsetTypeID` int(11) DEFAULT NULL,
  `CensorCode` varchar(50) NOT NULL DEFAULT 'nc',
  `QualifierID` int(11) DEFAULT NULL,
  `MethodID` int(11) NOT NULL DEFAULT '0',
  `SourceID` int(11) NOT NULL,
  `SampleID` int(11) DEFAULT NULL,
  `DerivedFromID` int(11) DEFAULT NULL,
  `QualityControlLevelID` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ValueID`),
  UNIQUE KEY `DataValues_UNIQUE_DataValues` (`DataValue`,`ValueAccuracy`,`LocalDateTime`,`UTCOffset`,`DateTimeUTC`,`SiteID`,`VariableID`,`OffsetValue`,`OffsetTypeID`,`CensorCode`,`QualifierID`,`MethodID`,`SourceID`,`SampleID`,`DerivedFromID`,`QualityControlLevelID`),
  KEY `FK_DataValues_Sources` (`SourceID`),
  KEY `FK_DataValues_QualityControlLevels` (`QualityControlLevelID`),
  KEY `FK_DataValues_OffsetTypes` (`OffsetTypeID`),
  KEY `FK_DataValues_CensorCodeCV` (`CensorCode`),
  KEY `FK_DataValues_Variables` (`VariableID`),
  KEY `FK_DataValues_Methods` (`MethodID`),
  KEY `FK_DataValues_Qualifiers` (`QualifierID`),
  KEY `FK_DataValues_Samples` (`SampleID`),
  KEY `FK_DataValues_Sites` (`SiteID`)
) ENGINE=InnoDB AUTO_INCREMENT=282507112 DEFAULT CHARSET=utf8 |

Question: Does this mean the query is using indexes?

The Using intersect(FK_DataValues_Sites,FK_DataValues_Variables) means that the query is using two indexes on table datavalues with the Index Merge Intersection Access Algorithm and then (Using where) the rows are additionally checked against some other conditions.

Question: Any other suggestions? (I guess about efficiency)

This is most probably not the most efficient way, especially if the query is retrieving a small perecentage of the table's rows.

For example, lets say that the SiteID = 15 condition narrows the search to 10% of the rows and the VariableID = 8 condition narrows the search to 5% of the rows. The two existing indexes can be used invidually or combined (with tthe help of the index merge intersection algorithm) to narrow the search to (0.10 * 0.05 =) 0.5% of the tables' rows (and that is what the MySQL engine is doing in this case.)

With a 280M rows table, this means that the first index can efficiently give us the 28M keys that match the 1st condition and the second index can give us the 14M rows that match the 2nd condition. Then, the index merge algorithm can further narrow it to the 1.4M keys (at the extra cost of this algorithm). Then, the 1.4M rows have to be retrieved and the 3rd condition (LocalDateTime >= '2009-04-18T00:02' AND LocalDateTime <= '2009-04-20T00:02) - that is not covered by any index - has to be checked for every one of these 1.4M rows.

If this 3rd condition narrows the results to, say 5% or 70K rows, the overall efficiency of the query will not be the best possible.

The best would be if there was an index that covered all 3 conditions. Fortunately, there is a way. A multi-column index on either (VariableID, SiteID, LocalDateTime) or on (SiteID, VariableID, LocalDateTime) would be enough to narrow the search to only the needed keys (with the previous example to only 70K) and then only those rows would be retrieved from the table.

Extra question: Do I have to remove the current index and then replace it with a multi-column index?

No, I wouldn't remove any index, just add the new one, unless I was sure I don't need an existing index.

But analyzing queries (and indexing) should be done after examining all the queries against the database and the requirements (which queries need to be extra fast and optimized, which ones we can afford to wait longer for them, how other operations will be affected if we add a lot of indexes, etc.)


Oracle 10g Listener problem In Oracle 10g I have a listener problem,

Here is tnsnames.ora :

MYTNS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =
       (PROTOCOL = TCP)
       (HOST = testiold)
       (PORT = 1521)
      )
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mysid)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =
        (PROTOCOL = IPC)
        (KEY = EXTPROC)
      )
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

and the listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mysid)
      (ORACLE_HOME = /oracle/10.2.4/db/OraHome_1)
      (SID_NAME = mysid)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
     (ADDRESS_LIST =
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST = testiold)
        (PORT = 1521)
      )
     )
    )
  )

The proble is I cannot start the listener with this configs, when I try lsnrctl start I got this:

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production on 14-AUG-2012 22:11:44

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /oracle/10.2.4/db/OraHome_1/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production
System parameter file is /oracle/10.2.4/db/OraHome_1/network/admin/listener.ora
Log messages written to /oracle/10.2.4/db/OraHome_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testiold)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testiold)(PORT=1521)))
TNS-12535: TNS:operation timed out
 TNS-12560: TNS:protocol adapter error
  TNS-00505: Operation timed out
   IBM/AIX RISC System/6000 Error: 78: Connection timed out

and whenever I try to connect to DB it says:

ORA-28547: connection to server failed, probable Oracle Net admin error

Can you please help me?

As Phil Says I replaced hostname with Actual IP but the connection prblem was for

(PROGRAM = extproc)

In listener.ora file, Removing it solved the issue.

To let external procedures work these changes should be done:

Adding new SID entry in SID_LIST_LISTENER SID_LIST with SID_NAME = SID in tnsnames.ora at EXTPROC_CONNECTION_DATA and PROGRAM = extproc and a proper ORACLE_HOME(Environment variables can be added)

In ADDRESS_LIST in LISTENER part of listener.ora new ADDRESS Entry should be added with PROTOCOL = ipc and KEY = EXTPROC


Error when assigning a role to user I have created a role called "role1" with these steps :

postgres=# CREATE ROLE role1;
CREATE ROLE
postgres=# GRANT CONNECT ON DATABASE paybills TO role1;
GRANT
postgres=# \c paybills
You are now connected to database "paybills".
paybills=# GRANT SELECT, UPDATE, INSERT, DELETE ON myeg_users TO role1;
GRANT

The steps above are all successful.

However, when I run the command below :

paybills=# GRANT role1 TO surentran;
ERROR:  role "surentran" does not exist

As you can see, an error is thrown. I am in the db called "paybills", and the user details are stored in a table called "myeg_users" within this database.

How do I add all the users in the "myeg_users" table into the role "role1"?

You can only GRANT to a PostgreSQL role.  If you have created your own role system at the application level, storing the data about them in the database, PostgreSQL has no idea that you did that and certainly isn't going to grant PostgreSQL privileges to these roles.

You either need to convert the users listed in "myeg_users" into PostgreSQL users/roles, or you need your app server to connect as PostgreSQL user "role1" and then to voluntarily do only those things the app-side role it is implementing is allowed to do.


How to identify row change timestamp columns in Db2 Table I need to copy some Db2 tables programmatically (java 11 using the standard Db2 jdbc driver). The tables in question lie on a Db2 for z/OS database (v12) and on a Db2 LUW database (v11.1). The target tables exist and have all the required columns.
Some tables contain rows defined like this:
<SOME_COL_NAME> TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

In order to do the copy, I wish to use the Db2 load utility and row change timestamps need to be preserved. The syntax for this is identical for both Db2 types. When the table contains a row change timestamp, then the LOAD statement requires a ROWCHANGETIMESTAMPOVERRIDE modifier. All this has been gleaned from the IBM documentation.
The difficulty is that I'm  unsure how to tell whether the table in question has a row change timestamp. For Db2 LUW it is possible to query SYSCAT.COLUMNS, since this has a a field called ROWCHANGETIMESTAMP which would be either 'Y' or 'N'. That would mean checking each column in each table, but it's doable. However, it would be nice if there was an easier way to determine whether the table has a row change timestamp.
For z/OS it isn't clear what should be checked. Is there a catalog query that would help?
I can provide more details, if necessary.

In Db2 for z/OS this information is in the DEFAULT column of the SYSIBM.SYSCOLUMNS catalog table. In your case the value you'll be looking for is 'E'.


DDL Trigger in MySQL? Are there any options for running DDL triggers in a MySQL database. I would like to create a view every time someone create a table.

If the "Users" table is created, I want the DDL trigger to create a view called "v_Users".

MySQL only supports triggers for BEFORE / AFTER INSERT, UPDATE, DELETE, see documentation for the CREATE TRIGGER syntax.

An alternative solution might be to create a MySQL EVENT that runs at at a specified interval and checks whether there are any tables that don't have corresponding views, and if so, create them. (Make sure to enable the event scheduler. The default is OFF until MySQL 8.0. See documentation here.)

Maybe another option would be to create the view "if not exists" whenever a row of data is INSERTed into the table, in which case you could use a BEFORE INSERT trigger. (One obvious disadvantage of this approach is that the trigger will be fired every time you INSERT to the table, so there is a performance penalty on INSERT.)

Edit: Maybe yet another option could be that you only allow CREATE TABLE through a special stored procedure which will automatically also create the view.


Unexpected SLAVE STATUS after CHANGE MASTER I'm trying to setup MySQL replication but I am getting duplicate keys when I start the slave.

I've got master and relay.  I've also got backup, which is intended to be slaved to relay, but my issue is on relay.  I create a dump from master using the following:

mysqldump \
    --all-databases \
    --flush-logs \
    --single-transaction \
    --master-data \
    | gzip > dump.sql.gz

This dump includes something like the following:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=120;

I then import the dump into relay, and execute the following:

FLUSH PRIVILEGES;
CHANGE MASTER TO
    master_host='master',
    master_user='username',
    master_password='secret';
START SLAVE;
SHOW SLAVE STATUS\G

I'm getting what I find to be unexpected results from the slave status:

Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 800917736
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 635
Relay_Master_Log_File: mysql-bin.000001
Last_Error: Could not execute Write_rows event on table myschema.mytable; Duplicate entry '5191176' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 778

A few things I don't understand:

how does it have a duplicate key, when I just pulled in a fresh dump?
why is it loading from mysql-bin.000001 when the CHANGE MASTER and Master_Log_File are mysql-bin.000012?
how has it progressed to Read_Master_Log_Pos: 800917736 if it encountered an error on mysql-bin.000001?

So after fiddling more, I've discovered the order of CHANGE MASTER ... is important.  Once I figured this out, I found this in the docs explaining in further detail.

  If you specify the MASTER_HOST or MASTER_PORT option, the slave assumes that the master server is different from before (even if the option value is the same as its current value.) In this case, the old values for the master binary log file name and position are considered no longer applicable, so if you do not specify MASTER_LOG_FILE and MASTER_LOG_POS in the statement, MASTER_LOG_FILE='' and MASTER_LOG_POS=4 are silently appended to it.

MySQL docs - CHANGE MASTER


how to define trigger scope variable My trigger is meant to calcalute the grade of a student base on the column value for every insert.But if a column value for the column is modified it the corresponding Grade should change. Currently i having error:"Invalid object name StudentMarks for each code section.
I did some search to the cause of the error but still can find one. Please advise

        -- ================================================
    -- Template generated from Template Explorer using:
    -- Create Trigger (New Menu).SQL
    --
    -- Use the Specify Values for Template Parameters
    -- command (Ctrl-Shift-M) to fill in the parameter
    -- values below.
    --
    -- See additional Create Trigger templates for more
    -- examples of different Trigger statements.
    --
    -- This block of comments will not be included in
    -- the definition of the function.
    -- ================================================
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:      TSQL PROGRAMMER
    -- Create date:
    -- Description: Calcute studnet Grade
    -- =============================================
    CREATE TRIGGER dbo.ComputeGrade
       ON  dbo.StudentMarks
       AFTER INSERT,UPDATE
       declare @markobtained  int =(Select MarkObtained from inserted i )
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        if(@markobtained between 0 and 49)
        BEGIN
        UPDATE StudentMarks SET Grade='Fail'
        END
        ELSE IF (@markobtained between 50  and 64)
        BEGIN
        UPDATE StudentMarks SET Grade='Fair'
        END
       ELSE IF(@markobtained between 65 and 74)
       BEGIN
       UPDATE StudentMarks SET Grade ='Good'
       END
       ELSE IF(@markobtained between 75 and 84)
       BEGIN
       UPDATE StudentMarks SET Grade='Very Good'
       END
       ELSE IF(@markObtained between 85 and 100)
       BEGIN
       UPDATE StudentMarks SET Grade ='Excellent'
       END
       ELSE

       PRINT'Invalid mark range'

     END
    GO

While the syntax of your trigger is not quite correct, this is not the source of the error. The main problem...

  Msg 8197, Level 16, State 4, Procedure ComputeGrade
  The object 'dbo.StudentMarks' does not exist or is invalid for this operation.

...is exactly what the error message states. Either you have spelled the name of the table wrong, you are in the wrong database, or it is in a schema other than dbo. To locate it, make sure you are in the right database, then run:

SELECT s.name, t.name
  FROM sys.schemas AS s
  INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
  WHERE t.name LIKE N'StudentMark%';

Now, on to the trigger. The main problem is this: which row do you expect to process in the case of a multi-row insert or update? Assigning a value from inserted to a variable assigns exactly one value. Which one will it be? The first one? The last one? Random? You should re-write it using a set-based operation, for example:

CREATE TRIGGER dbo.ComputeGrade
   ON  dbo.StudentMarks
   AFTER INSERT,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE m
      SET Grade = CASE
        WHEN MarkObtained < 50 THEN 'Fail'
        WHEN MarkObtained < 65 THEN 'Fair'
        WHEN MarkObtained < 75 THEN 'Good'
        WHEN MarkObtained < 85 THEN 'Very Good'
        ELSE 'Excellent' END
   FROM dbo.StudentMarks AS m
   INNER JOIN inserted AS i
   ON m.[key column] = i.[key column];
END

But even better than a trigger would be a computed column.

ALTER TABLE dbo.StudentMarks DROP COLUMN Grade;

ALTER TABLE dbo.StudentMarks ADD Grade AS
  CONVERT(VARCHAR(10), CASE
        WHEN MarkObtained < 50 THEN 'Fail'
        WHEN MarkObtained < 65 THEN 'Fair'
        WHEN MarkObtained < 75 THEN 'Good'
        WHEN MarkObtained < 85 THEN 'Very Good'
        ELSE 'Excellent' END);

This way there is no trigger to maintain (or keep enabled) and all data is automatically synced at all times.


Indexing for a table with many large subsets In SqlServer 2017 we have a table with millions of rows. The table has a few dozen columns. One of the columns is a varchar(50) with a well defined range of allowed values, the value of this column is guaranteed to be one string from a list of about 4,000 strings. The only queries we care to optimize against this table all exclusively deal with the rows that have just one of those values, but they need to retrieve all of the columns of those rows. Does it make sense to put an index on that column and if so which type of index should be used?

Example Schema:

+---------------------------------------+
| Id   | Category | ... 20 other columns|
+------+----------+---------------------+
| 1    | Food     |      .....          |
+------+----------+---------------------+
| 2    | Lumber   |      .....          |
+------+----------+---------------------+

Example Query: select * from table where Category = 'food';

So, in this example, the Category column contains one string from a list of about 4000. I've considered a clustered index, but this column is not unique. I'd add a non-clustered index, but the query calls for all columns to be returned so it has to go from the index back to the main table to retrieve all of the data anyway, right? Then do we have to use a full table index or is there a better option?

Does it make sense to put an index on that column and if so which type of index should be used?

We can't answer that for you. It depends on if query performance is good enough without the index. If end user response time, transactions per second, overall server CPU, or whatever metric is the most important to you is fine without the index then you don't need it right now. You may need it in the future if the table grows bigger. With all of that said, the current query and table structure that you have won't give you the best performance. If an improvement is needed then you have three main options:

Create a nonclustered primary key on the id column and a clustered index on Category.
This will give you the best possible performance for the query that you listed in the question. The downside is that changing the clustered index may have negative impacts on other queries. Clustered indexes do not need to be unique. You also do not need a primary key on your table. I just proposed an example schema that had both (I assume that you have a primary key today).

Add a nonclustered index on Category that includes all other columns.
This will also give you the best possible performance for the query that you listed in the question. As a downside it will double the disk space required for the table and will slow down DML operations on the table.

Add a nonclustered index on Category that doesn't include all other columns.
This won't give you the best possible performance. It will improve performance as long as you're filtering on a value that's selective enough and SQL Server chooses to use the index. If you need to help the optimizer along, the techniques in this blog post by Distinguished Answerer Erik Darling may be helpful. There's still a DML impact to this option, but it should be significantly less than option 2.

Ultimately the only way that you can get a satisfactory answer is by looking into it yourself. We don't know what kind of importance that you put on DML performance, total disk usage, the performance of other queries, etc. All of the options that I mentioned above have their own drawbacks. Good luck!


How to decrease RAM usage of SQL Server 2012 without restarting Service Is there a way of decreasing RAM usage of SQL Server 2012 without restarting it?

Setting up MAX Memory usage is not an option as it's quickly building up to limit I have set and then app based on this SQL Server instance is acting up, being not responding or working very slow.

App based on this SQL Instance is acting up, being Not Responding or working very slow

You have a performance issue that you need to investigate. Read How to analyse SQL Server performance for a starter.

Do not change random settings, like memory, in hope you fix the problem. Analyze, find the root cause and fix it.

You're probably missing an index or more.

For the record: DBCC FREESYSTEMCACHE('ALL')


Postgresql table function: get where condition I have defined a function that returns a table. In this function, I use multiple temporary table to pre-elaborate data.

Here, there is a very simple example:

create or replace function public.fn_world_cities()
returns table (
    city_id int,
    city_name varchar,
    country_id int
    )
as $$
begin

    ---- Extract temp table with all cities
    create temporary table tmp_all_cities on commit drop
    as
    begin $$
    select city_id, city_name, country_id
    from public.table_world_cities
    where row_status = 'A'; ---- filter active record

    ---- Return data
    return query
    select city_id, city_name, country_id
    from tmp_all_cities;

end; $$ language plpgsql;

The temporary table creation is time expensive.
When I use the function, always use some WHERE condition. Like:

select * from public.fn_world_cities()
where country_id = 10;

In my function, there are many fields on which I can set a WHERE, so it's impossible to add function parameters for everyone.

Is there a way, at run time, to know there WHERE condition values? In that way I can pre-filter data during temporary table creation.

I wish I can have a function like this:

create or replace function public.fn_world_cities()
returns table (
    city_id int,
    city_name varchar,
    country_id int
    )
as $$
begin

    ---- Extract temp table with all cities
    create temporary table tmp_all_cities on commit drop
    as
    begin $$
    select city_id, city_name, country_id
    from public.table_world_cities
    where row_status = 'A' ---- filter active record
    and ***WHERE CONDITION APPLIED TO FUNCTION CALL***
    ;

    ---- Return data
    return query
    select city_id, city_name, country_id
    from tmp_all_cities;

end; $$ language plpgsql;

The temporary table will only make things slower, you don't need it. And if you change the function to be a SQL function, the Postgres optimizer can push the condition from the WHERE clause automatically into the query inside the function:

create or replace function public.fn_world_cities()
  returns table (city_id int, city_name varchar, country_id int)
as $$
  select city_id, city_name, country_id
  from public.table_world_cities
  where row_status = 'A';
$$
language sql
STABLE; --<< this is important!

If you now run:

select *
from public.fn_world_cities()
where country_id = 10;

And country_id has an index that is worth using, Postgres will use it Note that you need a non-trivial number of rows in that table, and the condition needs to be selective enough to warrant an index scan (as with any other index usage)


What is controlling SQL Server's page file usage? SQL Server or Windows? If Lock Pages In Memory is not enabled and SQL Server data is paged out to the page file does SQL Server decide what data gets paged out? Or is this handled by the OS?

If Windows is handling this does it tell SQL Server which data has been paged out?
It is my understanding that SQL Server knows how much data is in RAM and how much is in the page file.
But does SQL Server know what data pages are put where and does it have any influence on whether a data page is put in one or the other?

Here's some Background:

I am not really trying to solve a problem as such.

My question is more about understanding what's going on inside SQL Server...

I had an issue with an application that had a long queue of jobs/queries that timed out waiting for a SQL Server instance to handle the requests.
I noticed that two SELECT queries on the database in question were briefly blocking an UPDATE and a DELETE query.
After a short while two new SELECTs were blocking two new UPDATE and DELETE queries. This continued.

SQL Server worked as expected - except it was too slow for the application that experienced timeouts.

The SQL Server instance is located on a active/passive cluster with 14 other SQL Server instances.

I worked on this with the application developers.
We discussed allocating more RAM to the instance and while not really thinking it would help I raised the max server memory (MB) setting from 40GB to 55GB.
Within a few seconds all the jobs waiting in line at the application server where executed - the developers were watching them being removed from the queue one by one very quickly.

The server hosting the SQL Server was not at the time set to allow Lock Pages In Memory for the service account, so I was thinking that if the data needed to handle the requests mentioned were placed in the page file and when the extra RAM was allocated the data was moved from the page file to the RAM maybe that could explain why all the jobs finished so fast immediately after the extra RAM was allocated.

Firstly, the most important thing you really need to know is that it is critical to configure memory usage by SQL Server and other applications on the system to ensure that it never starts paging to disk.  RAM access is measured in nanoseconds, and disk access is measured in milliseconds, so whatever gets paged out is going to take hundreds, if not thousands, of times longer to access.  It will also create a massive load of I/O on the system to do the swapping, contending for memory, CPU, and disk access.  So even if you have SSDs, it is still going to hurt if it starts swapping to disk.

When the max memory setting is changed, SQL Server discards everything in the buffer and starts over.  So if it was swapping the buffer to disk, yes, this may have caused the disk swapping to stop and performance somewhat restored.  However, if it was swapping to disk with a 40 GB setting, it will be more likely to swap in the future now that it's going to use even more memory.

However, the change you made could have just been a coincidence--do you know for sure that there were no long-running queries blocking those jobs?  There are dozens of possible causes, and unless you have data from monitoring mechanisms, there's no way to be sure at this point.

Memory management to avoid swapping to disk is one of the dangers of running multiple instances of SQL Server on one system.  If you have 128 GB of RAM, for example, the max memory setting of all instances should only total around 110 GB or so, perhaps a few GB more after monitoring available memory for a while

If SQL Server cannot lock pages in memory, then it has no control over what memory will be swapped to disk.  A process has an address space of virtual memory, and when the process reads the memory, the OS will retrieve it from the physical location (memory, or disk).  The OS keeps track of when memory was last accessed and it tries to page out the memory that is least active to reduce the impact.

The process does not know about the physical location of each offset of its virtual memory--this could create quite a lot of pointless overhead in tracking it.  SQL Server does, however, have a way of knowing how much of its memory is swapped to disk, as it will log a message as seen in How to reduce paging of buffer pool memory in SQL Server when it reaches a certain threshold.

Highly recommended reading: RAM, virtual memory, pagefile, and memory management in Windows, particularly to understand that monitoring Memory, Pages/Sec is not a valid measure of memory being swapped to disk.  Many other articles on the Internet incorrectly advise this counter to be used.

  Memory, Pages/Sec: This counter is one of the most misunderstood measures.

  A high value for this counter does not necessarily imply that your performance bottleneck stems from a shortage of RAM. The operating system uses the paging system for purposes other than swapping pages because of memory over-commitment.

You can easily verify this by simply having an application write a large amount of data to disk, and you'll see high numbers for Memory, Pages/Sec.  This is because the data that's being written to disk first has to be in memory, and then it is "paged" to disk.  The terminology around "paging" is misunderstood by many system administrators and it is often defined as "swapping process working set to disk," but "paging" is a much broader term than covers every read and write to disk.


How to remove Windows NTFS Compression from Oracle Database datafiles? We know Windows NTFS compression on Oracle Database datafiles is a bad idea.

See here and here.

It prevents Asynchronous Random Disk I/O which can increase the risk of corruption and generally slow down the database.

However, we have some Oracle databases (11g & 12c) on Windows Server 2012 R2 servers with Windows file compression on the datafiles. How do we safely deactivated this file compression? Are there any watch-outs?

Thanks.

As Paparazzi said for MSSQL, just shutdown the database and then decompress the files and startup again.  If your architecture allows, you could offline a tablespace at a time and then just decompress the datafiles for that tablespace.  Eventually though you will need do do the system tablespace and will need to shutdown the whole system.


Summing "Other" remaining after Top 5 I have the query below which returns the top 5 sum per brokerage

SELECT *
FROM  (SELECT b.brokerage_name AS Brokerage,
              SUM(c.cost)      AS Cost
       FROM   (tblquotesnew q
               left join tblbrokeragesnew b
                      ON q.brokerage_id = b.id)
              left join tblcommissionnew c
                     ON q.quoteid = c.quote_id
       WHERE  c.calc_date >= Trunc(SYSDATE, 'iw')
              AND c.calc_date <= Trunc(SYSDATE, 'iw') + 7 - 1 / 86400
       GROUP  BY b.brokerage_name
       HAVING SUM(c.cost) IS NOT NULL
       ORDER  BY SUM(c.cost) DESC)
WHERE  ROWNUM <= 5
ORDER  BY ROWNUM;

Is it possible, in the same query, to have a sixth row returned for Other, which would be a sum of the remaining c.cost?

You can use a CTE (WITH clause), that precomputes all the SUM(Costs) and then UNION ALL two queries: one with the "top 5" from the CTE; the other one with the SUM of the rest:

WITH q2 AS
(
SELECT
    q1.*, row_number() over (order by Cost DESC) AS r
FROM
    (
    SELECT
        q.brokerage_id, SUM(c.cost) AS Cost
    FROM
        tblquotesnew q
        join tblcommissionnew c  ON q.quoteid = c.quote_id
    WHERE
        c.calc_date >= Trunc(SYSDATE, 'iw')  AND c.calc_date <= Trunc(SYSDATE, 'iw') + 7 - 1 / 86400
    GROUP BY
        q.brokerage_id
    HAVING
        SUM(c.cost) IS NOT NULL
    ) q1
ORDER BY
    Cost DESC
)
-- The "top 5"
SELECT
    q2.r AS rank, b.brokerage_name AS Brokerage, q2.Cost
FROM
    q2 LEFT JOIN tblbrokeragesnew b ON b.id = q2.brokerage_id
WHERE
    r <= 5
UNION ALL
-- The sum() of the rest (notice there is no group by; we add them all)
SELECT
    6 AS rank, 'Others', SUM(Cost)
FROM
    q2
WHERE
    r > 5
ORDER BY
    rank ;

You'll get a result that looks like:

RANK | BROKERAGE   | COST
---: | :---------- | ---:
   1 | Brokerage 1 | 8351
   2 | Brokerage 5 | 2231
   3 | Brokerage 4 | 2227
   4 | Brokerage 3 | 2223
   5 | Brokerage 7 | 2210
   6 | Others      | 4420

NOTES:

Your LEFT JOIN on tblcommissionnew is actually an INNER JOIN, since you use tblcommissionnew in your where in such a way that it will eliminate all nulls.
You don't need to JOIN with the tblbrokeragesnew table until the end. Oracle probably is good enough to optimize it, but it's cleaner to do it explicitly. (I've assumed that tblbrokeragesnew.id is a PK).

You can check everything (including the table definitions I've assumed) at dbfiddle here


Using pg_dump and psql to transfer table with timestamp into another Db I am using the following pg_dump and psql command lines through the terminal in order to transfer my table from one database to another.

pg_dump -U postgres -t public.table1 database1 | psql -U postgres -d database2

But the problem is that the table1 from the database1 is getting pasted into database2 with the same name, i.e. table1. I want to add the timestamp also into the name, something like table1_18_11_2014_20_45_30, in order to keep a track on when the transfer operation was taken place (in this case, at 20:45:30 hours on 18th Nov 2014). Any idea how to do this? Thx.

Cheers!

When you transfer the table you can alter the table name with ALTER TABLE table RENAME TO table_datestamp. I am not aware of automatic renaming tables while doing backup.

Or you can modify sql dump file and then run the script after modifying it.


MySQL: change column type using expression In a MySQL database, I need to convert some UUID columns (BINARY(16)) into VARCHAR. I want to convert them in the canonical form, not as bytes. For clarity, in lowercase and with hyphens, and not in the 0xHEXADECIMAL format.

In Postgres, this is enough:

ALTER TABLE my_table
  ALTER COLUMN my_uuid_column
  TYPE VARCHAR(36);

The MySQL equivalent returns an error:

ALTER TABLE my_table
  MODIFY COLUMN my_uuid_column
  TYPE VARCHAR(36);

ERROR 1366 (HY000): Incorrect string value: '\x.....' for column 'my_uuid_column' at row 1

And even if it worked, the result wouldn't really be in canonical form.

I know that in Postgres I can resolve this with USING expression:

CREATE FUNCTION foobar(foo uuid) returns VARCHAR
    LANGUAGE SQL IMMUTABLE STRICT AS $$
  SELECT 'foobar' -- format the UUID using some helpers.
$$;

ALTER TABLE my_table
  ALTER COLUMN my_uuid_column
  TYPE VARCHAR(36)
  USING foobar(my_uuid_column);

And it does what you'd expect.

I can't find a way to do something similar in MySQL.

The correct syntax would be

ALTER TABLE my_table1 MODIFY COLUMN my_uuid_column VARCHAR(36) GENERATED ALWAYS AS (1 +1)  STORED;

But you can't add many functions to a generated column, for example uuid() doesn't work, so you must do this in your code. where you generate the insert

Stored functions and user-defined functions are not permitted.
Stored procedure and function parameters are not permitted.
Variables (system variables, user-defined variables, and stored program local variables) are not permitted.
Subqueries are not permitted.

See official Documentation


MySQL 1175 Error While Using Primary Keys What would cause the error 1175 that starts out with: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.... The problem I have is that I did include a WHERE clause with key columns: WHERE a.a_id = b.b_id

a_id and b_id are the only key columns in their respective tables.

START TRANSACTION;
    UPDATE table_a a, table_b b
    SET a.update_me = b.update_from_me
WHERE a.a_id = b.b_id;
SELECT * FROM a;

What am I missing?

Well it's quite simple.

You have enabled the safe update mode (as the error stated). Your statement has a where clause, right. But it's interpreted as a JOIN not as a WHERE as you provide a syntax like a INNER JOIN.

Your code:

UPDATE table_a a, table_b b
SET a.update_me = b.update_from_me
WHERE a.a_id = b.b_id;

Is technically the same (and interpreted as such) as this one:

UPDATE a
FROM table_a a
INNER JOIN table_b b
        ON a.a_id = b.b_id;b_id
SET a.update_me = b.update_from_me;

Which means, there is no really WHERE. You update the whole table table_a, which is matching with the other table table_b.

If you really want to achieve this, you can disable the Safe Update Mode for this query using this code:

SET SQL_SAFE_UPDATES=0;
UPDATE table_a a, table_b b
SET a.update_me = b.update_from_me
WHERE a.a_id = b.b_id;

Anyway, your mysql server is running with mysqld --safe-updates or mysqld --i-am-a-dummy which is pretty the same option.

You can check if this is really enabled using one of the following commands:

SELECT @@SQL_SAFE_UPDATES
-- or
SHOW VARIABLES LIKE 'sql_safe_updates'

By the way, you can also try a workaround to get the update done. Safe Update Mode requires a WHERE or a LIMIT. Which will limit the impact of the Update. Another idea can be to use a pretty high LIMIT for your UPDATE.

UPDATE table_a a, table_b b
SET a.update_me = b.update_from_me
WHERE a.a_id = b.b_id
LIMIT 100000;

If the Safe Update Mode is enabled, you need to be aware of DELETE's too. They need both. A WHERE and a LIMIT. See the docs.


Using postgres with logstash - log filename is causing issues I'm having some trouble forwarding postgres logs to logstash. Normally it's pretty easy to do, but the problem with postgres is that the current logfile has the current date in the filename, so the name changes every day, making it very difficult to put the filename into logstash's config. Is there anything I can do about this? Maybe there's some trick to logstash that I can use to resolve this?

I figured it out, you can just use wildcards in the logstash-forwarder config. :p So far that seems to be working, but I'll keep an eye on it to make sure it rotates properly.


SSAS error deploying project: Ensure that 'SQL Browser' service is running After successfully building my project, I got this error when deploying it:

The project could not be deployed to the '\SQLEXPRESS' server because of the following connectivity problems :
A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running.
To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.

Things I've checked/tried so far:

Changing the deployment server name (from localhost to my actual SQL Server name)
Making sure SQL Server Browser service is running (which is running in SSCM)
Registry SSrpListener value (should be 1 -- mine is 1)
Running both Visual Studio and SSCM as administrator

Versions:

Visual Studio Community 2019 (16.9.2)
SQL Server Data Tools (16.0.62103.10080)
SQL Server Analysis Services (15.0.19445.0)
SQL Server Configuration Manager (2019.0150.2000.05)
SQL Server Management Studio (15.0.18369.0 or v18.8)
SQL Server 2019 Express (15.0.2000.5)
Windows 10 (10.0.19042)

Based on the information I've provided above, I still could not deploy the project.

i'm not sure to have a good view of what you are doing...
But i can say that SASS service is not included with sql server express. You can only have the reletional database service .\SQLEXPRESS, not the analytics database that is SSAS.
https://docs.microsoft.com/en-us/analysis-services/analysis-services-features-supported-by-the-editions-of-sql-server-2016?view=asallproducts-allversions
Instead of Express edition, download devenloper edition. It's free for not production env and full of features like enterprise edition. So you can test everything. Don't use it in production.
During sql dev setup be sure to select SASS instance too:


Mysqldump backup size issue - 5.6 I'm using MySQL 5.6 with about 5 databases on linux. I have scheduled daily full backups using mysqldump. All databases use InnoDB engine.

What I observe is that the backup size seems to vary everyday. This would make sense if the backup size keeps increasing but what is happening is randomly the backup size goes down.

I have confirmed with application teams that do not purge or delete data and I understand mysqldump is a logical representation of the data but how can backup size decrease randomly when there is no delete/truncate of data?

How much does the size go down by when it does? If it is by a small amount it could be users deleting data (or replacing it by smaller data) during normal operation.

Are you compressing the dumps and looking at the size of the compressed files? The data may not always come out in exactly the same order in the dump and this could affect the compresibilty of it.


Migration of oracle database from rhel5 to rhel6 I currently have oracle 11G running on RHEL5 on one server. (This Server has reached END of Life and no hardware support for it)

This server is connected with a Storage and ORACLE_HOME is on storage.

So Now I've a brand new machine with RHEL6 & I want to migrate that full oracle database to this machine.

So what is best feasible way.

I am thinking of RMAN backup and restore it on this new machine.  <-- This works

But If I connect that storage to new RHEL server what is best way (may be using control files or changes in Pfile or SPFILE can help) (then its just mapping of LUN task and connect to default oracle DB instance)

I don't want full procedure, just few ideas from SO users who faced this scenario and can share their best solution.

The database[s] and software are on SAN/NAS storage. In that case you can remount them on the new server with the same path's. Because the OS version is different issue a relink for the Oracle binaries. Don't forget to also copy /etc/oratab, /etc/oracle and /usr/local/bin files etc.

Backup and restore are not bad at all but assuming there is some pre production environment with similar setup, you can use that to verify the procedure before the production migration. This is the quickest option (remounting).


How to set up a transactional replication to an Always On Availability Group (AG) with two replicas through connecting to the AG Listener I want to replicate some tables on a SQL Server instance (Publisher) to an Always On Availability Group (AG) with two replicas (nodes) using its Availability Group Listener. The Distributor is the same as the Publisher. Some tables of a database on the Publisher is supposed to be replicated (Transactional Replication) to both replicas of the remote Always On Availability Group (AG) through connecting to the AG Listener. I successfully built a publication. I could build a subscription and set the AG listener as the Subscription server and didn't see any error but when I checked the AG listener, there was nothing in the Local Subscriptions folder. There was nothing in Local Subscriptions folders of both replicas, either. I tried to use official documents of Microsoft but it didn't resolve the problem. I searched a lot but couldn't find a proper documentation for such a situation.

I want to describe the whole process of setting up the replication that worked for me. I hope it is useful for you too :)

If you create a Publication using GUI of SQL Server Management Studio, you don't need to run scripts mentioned in the steps 1-5 and can quickly jump to step 6. However, you can use the following scripts, too.

Introduce the Distributor. The following query is run on the     Distributor that can be the same as the Publisher. The Distributor
must be a SQL instance that doesn't currently belong to the
Availability Group and won't be part of the Availability Group in
the future.

USE master;
GO
EXEC sys.sp_adddistributor
@distributor = 'WIN-ABCDE123F', -- IP address doesn't work
@password = 'pa$$w0rd'; -- an arbitrary password

Create the distribution database at the Distributor. Run the
following commands on the Distributor. The @security_mode parameter
is used to determine how the publisher validation stored procedure
that is run from the replication agents, connects to the SQL
Listener. If set to 1 Windows authentication is used to connect to
the SQL Listener. If set to 0, SQL Server authentication is used. If so,
@login and @password parameters must be provided.

USE master;
GO
EXEC sys.sp_adddistributiondb
@database = 'distribution',
@security_mode = 1;

Add the Publisher to the Distributor. Most of the times, the Publisher is the same as the Distributor. Run the following commands on the Distributor.

USE master;
GO
EXEC sys.sp_adddistpublisher
@publisher = 'WIN-ABCDE123F',
@distribution_db = 'distribution',
@working_directory = '\\WIN-ABCDE123F\WorkingDir',
@security_mode = 1;

@working_directory is a UNC path. For more information about UNC click here. Its default value is ReplData folder for the instance of SQL Server, for example C:\Program Files\Microsoft SQL Server\MSSQL\MSSQ.1\ReplData.
Add the Distributor to the Publisher. If the Publisher was different from the Distributor, you must have run the following commands on the Publisher. If not, it is the same as step 1 command and there is no need to run it again.

USE master;
GO
exec sys.sp_adddistributor
@distributor = 'WIN-ABCDE123F',
@password = 'pa$$w0rd';

Create a publication on the Publisher. You can do it using GUI or some scripts.
Set snapshot security account. Create a Windows user account for snapshot security agent, say repl_snapshot. Grant required access to it using SQL Server Management Studio GUI. Then open Replication\Local Publications folder. Right click on the publication and select Properties. From “Select page” panel, select Agent Security, click on Security Settings, select “Run under the following Windows account” and in "Process Account" box type: 'WIN-ABCDE123F\repl_snapshot'. Enter its password and confirm it. You can also use the Administrator account but it is not recommended regarding security issues.
Run the following script on the Publisher to create a subscription and add
subscription agent to the publisher.

use [publication_db]
EXEC sp_addsubscription @publication = N'PUBLICATION_NAME',
   @subscriber = N'SQL_Listener_Name', -- IP address doesn't work
   @destination_db = N'db_on_subscriber',
   @subscription_type = N'Push',
   @sync_type = N'automatic', @article = N'all',
   @update_mode = N'read only', @subscriber_type = 0;
GO

EXEC sp_addpushsubscription_agent @publication = N'PUBLICATION_NAME',
   @subscriber = N'SQL_Listener_Name', -- IP address doesn't work
   @subscriber_db = N'db_on_subscriber',
   @subscriber_login='SQL_LOGIN_SQL_LISTENER',
   @subscriber_password='PASSWORD', @subscriber_security_mode = 0;
GO

You can see the subscription has been created just on the SQL Listener, not nodes (replicas).
Before running the replication, on the publisher, go to Replication/Local Publications. Select 'Validate Subscriptions...', select the subscription and confirm it. Again go to Replication/Local Publications. Click on View Snapshot Agent Status. On the dialog opened, click on Start and wait until the process finishes. If all things went well, you could start the replication. Go to Replication/Local Publications. Right click on the subscription and select View Synchronization Status. Click Start and wait until the process finishes.


How to uninstall SQL Server Management Studio 2012? How to uninstall  SQL Server Management Studio 2012?

ControlPanel's "Uninstall or change programs" (in Windows 7 Prof) gives a row of entries:

while internet search results always tell: "click Microsoft SQL Server 2005, and then click Change"

UPDATE (commenting Shawn Melton's answer):

Upon choosing " "Microsoft SQL Server 2012 (64-bit)"--> Uninstall/Change, I observe:

and after choosing "Remove", "Setup Support Rules" without any SSMS or continuation option:

You will select "Microsoft SQL Server 2012 (64-bit)"

Upon loading of the setup wizard, you will select the instance for SQL Server 2012. The next screen you check the shared component you want to remove.


Oracle database 18c – ORA-12541: TNS:no listener I have installed Oracle 18c in my local machine and i have created a user names as

USER_NAME := c##scott
Passkey : oracle

When i used to connect to that user using cmd. The following error is populating out.

Command: sqlplus c##scott/oracle@agndev

ERROR:
ORA-12541: TNS:no listener

After checking the listener error I have rechecked my listener file

Listener File:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\ora_base)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\ora_base\bin\oraclr18.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = AGNDEV))
    )
  )

Tnsnames.ora file:

# tnsnames.ora Network Configuration File: D:\ora_base\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

    ORACLR_CONNECTION_DATA =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
        (CONNECT_DATA =
          (SID = CLRExtProc)
          (PRESENTATION = RO)
        )
      )

    AGNDEV =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = AGNDEV)
        )

  )

I am new to 18c can anyone say the solution to this error.

Thank you.

The error message clearly says there is no listener listening on the requested address.

Check if it is running (lsnrctl status), and start it if necessary (lsnrctl start). On Windows you will need to do this in a command prompt started with administrator privileges, because it also start a Windows service. Or you can start the Windows service called something similar to OracleOraDB18Home1TNSListener.