SQL Job step run as different sql account I'm curious how to set up a SQL job step to be executed as a different SQL login account.  It appears I need to set up a New Proxy Account which requires an existing credential.  When I create a credential my only option is to use a Windows Login credential.

The job I'm attempting to run is below.  There are other additional statements but when I set the job step to run as the SQL login it fails.

insert into [dbo].[TableA]
SELECT
       ss.[Ref_ID]
      ,mm.[studentID]
      ,mm.[studentPersonID]
      ,mm.[studentFirstname]
  FROM [dbo].[TableB] mm
  left outer join [dbo].[TableC] ss on ss.parentPersonID=mm.parentPersonID and mm.studentPersonID = ss.studentPersonID
  where ss.Ref_ID is not null;

When this is run through a SQL Job Step it fails.

Executed as user: an_admin_account. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274).  The step failed.

I'm unclear as to why it's trying to access a remote server when all of these tables exists on the local db.

If you are configuring a T-SQL job step go to the Advanced Page and configure the "Run as user" to the login of your choice.

If you are working with other job step types like PowerShell it will require a proxy account to be configured.


What happens when sga and pga reach limits? I have the following doubt, I have a server with Oracle 9i and another with Oracle 12c, in both cases I have the doubt that happens when Oracle reaches the limit imposed by the defined memory parameters (SGA and PGA)? ... Example hypothetical:
I have a server with 32GB of RAM in which I only have running an instance of oracle 12c which has assigned the following parameters:

sga_max_size = 19327352832
sga_target = 19327352832
pga_aggregate_target = 10737418240
pga_aggregate_limit = 12884901888

If it were to reach the limit of those parameters that would happen ?, the operating system (RedHat) would begin to swap?

SGA: nothing serious, it is a fix sized memory area, once it gets full, the database reuses memory that is rarely accessed using LRU (least recently used) algorithm

Memory Architecture

PGA: Once PGA_AGGREGATE_LIMIT is reached, the database aborts calls of sessions having the most untunable (like PL/SQL collections) PGA memory, and those sessions receive ORA-04036 error. If that does not help and PGA usage is still above the limit, the database may even kill those sessions:

PGA_AGGREGATE_LIMIT

  Actions Taken When PGA_AGGREGATE_LIMIT is Exceeded

  Parallel queries will be treated as a unit. First, the sessions that
  are using the most untunable memory will have their calls aborted.
  Then, if the total PGA memory usage is still over the limit, the
  sessions that are using the most untunable memory will be terminated.

  SYS processes and background processes other than job queue processes
  will not be subjected to any of the actions described in this section.
  Instead, if they are using the most untunable memory, they will
  periodically write a brief summary of their PGA usage to a trace file.

(19327352832 + 12884901888)/1024/1024/1024 = 30

You have 32 GB memory in your server, you have allocated 30 GB memory to the database, and there is nothing else running on it, as you said. With proper configuration and sizing, you should not see swapping under normal circumstances:

hugepages for decreasing page tables
filesystemio_options=setall for direct I/O and avoiding filesystem caching
vm.swappiness set to 10 or even lower
sizing: reasonable amount of database sessions/processes

This does not mean the system will never swap. For example:

SYS sessions and background processes are not limited by PGA_AGGREGATE_LIMIT. You can still have rogue procesesses consuming PGA memory above the limit causing swapping.
If you use and unreasonably sized connection pool, like 100s or even 1000s of database processes, those processes have some private memory that is not part of PGA, so they add up on top of the SGA + PGA limit. This is especially significant on AIX, fortunately not so much on Linux.


How to detect whether MySQL server is running as Amazon RDS I am writing an application wherein I want to detect whether MySQL server is running as Amazon RDS instance?
Can somebody help me out how to detect it?

auto_increment_increment1
auto_increment_offset1
autocommitON
automatic_sp_privilegesON
back_log50
basedir/rdsdbbin/mysql-5.1.45.R1/
big_tablesOFF
binlog_cache_size32768
binlog_direct_non_transactional_updatesOFF
binlog_formatMIXED
bulk_insert_buffer_size8388608
character_set_clientutf8
character_set_connectionutf8
character_set_databaselatin1
character_set_filesystembinary
character_set_resultsutf8
character_set_serverlatin1
character_set_systemutf8
character_sets_dir/rdsdbbin/mysql-5.1.45.R1/share/mysql/charsets/
collation_connectionutf8_general_ci
collation_databaselatin1_swedish_ci
collation_serverlatin1_swedish_ci
completion_type0
concurrent_insert1
connect_timeout10
datadir/rdsdbdata/db/
date_format%Y-%m-%d
datetime_format%Y-%m-%d%H:%i:%s
default_week_format0
delay_key_writeON
delayed_insert_limit100
delayed_insert_timeout300
delayed_queue_size1000
div_precision_increment4
engine_condition_pushdownON
error_count0
event_schedulerOFF
expire_logs_days0
flushOFF
flush_time0
foreign_key_checksON
ft_boolean_syntax+-><()~*:""&|
ft_max_word_len84
ft_min_word_len4
ft_query_expansion_limit20
ft_stopword_file(built-in)
general_logOFF
general_log_file/rdsdbdata/log/mysql-3306.log
group_concat_max_len1024
have_community_featuresYES
have_compressYES
have_cryptYES
have_csvYES
have_dynamic_loadingYES
have_geometryYES
have_innodbYES
have_ndbclusterNO
have_opensslYES
have_partitioningYES
have_query_cacheYES
have_rtree_keysYES
have_sslYES
have_symlinkYES
hostnameip-10-28-182-30
identity0
ignore_builtin_innodbOFF
init_connect
init_file
init_slave
innodb_adaptive_hash_indexON
innodb_additional_mem_pool_size2097152
innodb_autoextend_increment8
innodb_autoinc_lock_mode1
innodb_buffer_pool_size1179648000
innodb_checksumsON
innodb_commit_concurrency0
innodb_concurrency_tickets500
innodb_data_file_pathibdata1:10M:autoextend

Try

SELECT @@version;

I believe that Amazon has their own build of  MySQL and  would be apparent there.

EDIT 2012-05-30

@@version does not return a unique string, but it appears that all RDS instances have the same basedir/datadir/tmpdir, etc, so you could look at @@basedir and see if it matches /rdsdbbin/mysql/:

mysql> select @@basedir;
+------------------+
| @@basedir        |
+------------------+
| /rdsdbbin/mysql/ |
+------------------+
1 row in set (0.00 sec)

This is not 100% definitive, but chances are pretty high that if the basedir is set to something like rdsdbbin, then it's an RDS instance.


How do you disable anonymous login? According to the MySQL documentation, you can harden a MySQL server by adding passwords, or removing the anonymous accounts.

  If you want to prevent clients from connecting as anonymous users
  without a password, you should either assign a password to each
  anonymous account or else remove the accounts.

Before hardening, my users table looked like this.

mysql> select user,host,password from mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *F3A2A51A9B0F2BE246XXXXXXXXXXXXXXXXXXXXXX |
| root             | gitlab    |                                           |
| root             | 127.0.0.1 |                                           |
| root             | ::1       |                                           |
|                  | localhost |                                           |
|                  | gitlab    |                                           |
| debian-sys-maint | localhost | *95C1BF709B26A5BAXXXXXXXXXXXXXXXXXXXXXXXX |
| myuser           | localhost | *6C8989366EAF75BB6XXXXXXXXXXXXXXXXXXXXXXX |
+------------------+-----------+-------------------------------------------+

I've remove all anonymous accounts, so that the user table now looks like this.
(I'm using puppet to manage the users, but puppet effectively performs a DROP USER command).

mysql> select user,host,password from mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *F3A2A51A9B0F2BE246XXXXXXXXXXXXXXXXXXXXXX |
| debian-sys-maint | localhost | *95C1BF709B26A5BAXXXXXXXXXXXXXXXXXXXXXXXX |
| myuser           | localhost | *6C8989366EAF75BB6XXXXXXXXXXXXXXXXXXXXXXX |
+------------------+-----------+-------------------------------------------+

Why is it that I am still able to login to my test system without a username or a password?
What do I need to do to prevent any unwanted users from logging in?

root@gitlab:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 98
Server version: 5.5.32-0ubuntu0.12.04.1 (Ubuntu)
....
mysql>

Update:
I've also just discovered that I can login as root, without entering a password.

Update2:
I found this question which has some good information, but does not solve the issue.

There are no anonymous users.

mysql> select user,host,password from mysql.user where user='';
Empty set (0.00 sec)

I login as root@localhost.

mysql> select USER(),CURRENT_USER();
+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+

I do not have a default password, or skip-grant-tables defined in my.cnf

root@gitlab:~# cat /etc/mysql/my.cnf |grep -i 'skip-grant-tables'|wc -l
0
root@gitlab:~# cat /etc/mysql/my.cnf |grep -i 'pass'|wc -l
0

Update3:

I have tried performing these steps with puppet, (which should perform a flush privileges automatically). I have also manually flushed privileges and also tried restarting mysql.

Update4:
I've also tried changing the mysql root password and flushed privileges. No luck, I can still log in as any user without a password.

I figured it out. While /etc/mysql/my.cnf didn't have a password keypair stored, there was a password stored in /root/.my.cnf.

As soon as I commented out the password in /root/.my.cnf, I was not allowed to log in without a password (which is what I expected).


MySQL: creating a user that can connect from multiple hosts I'm using MySQL and I need to create an account that can connect from either the localhost or from another server, i.e. 10.1.1.1. So I am doing:

CREATE USER 'bob'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'bob'@'10.1.1.1' IDENTIFIED BY 'password123';
GRANT SELECT, INSERT, UPDATE, DELETE on MyDatabse.* to 'bob'@'localhost', 'bob'@'10.1.1.1';

This works fine, but is there any more elegant way to create a user account that is linked to multiple IPs or does it need to be done this way?

My main worry is that in the future, permissions will be updated form 'bob' account and not the other.

Use this query to creating user account that can be connect from the localhost or from another server

CREATE USER 'bob'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;


Is it possible to rename the columns of a resultset? I have two tables joined on an ID.

they might look like this:

table_overview
| id | version | description |
------------------------------
| 1  | 1.2.3   | new stuff.. |

table_files
| id | type    | file_name   |
------------------------------
| 1  | TEXT    | as.txt      |
| 1  | IMG     | abc.png     |
| 1  | AUDIO   | def.wav     |

Where the primary key of table_overview is id and the primary key of table_files is id and type.

I want to return a result set where each row contains all of the information, but replace the heading file_name with the type instead, so a row in the result set would look something like this:

| id | version | description | text   | img     | audio   |
| 1  | 1.2.3   | new stuff.. | as.txt | abc.png | def.wav |

Is something like this possible? Or am I better off simply creating the table that way in the first place.
The initial idea was that it should be easy to add new types in the future.

If you have a fixed set of properties and there will be at most one value for each entity then you can do:

SELECT o.id, o.version, o.description
     , t.file_name AS text
     , i.file_name AS img
     , a.file_name AS audio
FROM   table_overview AS o
LEFT OUTER JOIN
       table_files AS t ON t.id=o.id AND t.type = 'TEXT'
LEFT OUTER JOIN
       table_files AS i ON i.id=o.id AND i.type = 'IMG'
LEFT OUTER JOIN
       table_files AS a ON a.id=o.id AND a.type = 'AUDIO'

or if there will always be a row for each property (i.e. the row will exist with a NULL value if there is no value, instead of not having a row for the property at all) for each entity using inner joins may be more efficient:

SELECT o.id, o.version, o.description
     , t.file_name AS text
     , i.file_name AS img
     , a.file_name AS audio
FROM   table_overview AS o
JOIN   table_files AS t ON t.id=o.id AND t.type = 'TEXT'
JOIN   table_files AS i ON i.id=o.id AND i.type = 'IMG'
JOIN   table_files AS a ON a.id=o.id AND a.type = 'AUDIO'

If there might be more than one of a property for any given entity (for example if there could be multiple image files for one overview row) then this will get more complicated.

Using GROUP BY and aggregates can change the performance for the better too, as mentioned by Raymond, depending on how many properties there are, what other filtering clauses are in your queries, and how your tables are indexed. In many cases it will be better, but in some it will be noticeably worse (particularly when filtering on one of the transposed properties or picking a few properties from a bag that can contain many per item) due to causing scans where a small number of seeks may otherwise be sufficient:

SELECT o.id, o.version, o.description
     , MAX(IF(type = 'TEXT' , file_name, NULL)) AS text
     , MAX(IF(type = 'IMG'  , file_name, NULL)) AS img
     , MAX(IF(type = 'AUDIO', file_name, NULL)) AS audio
FROM   table_overview AS o
LEFT OUTER JOIN
       table_files AS f ON f.id=o.id
GROUP BY o.id, o.version, o.description

You'll need to test/benchmark your application to see if this method is faster or not.

In other databases like SQL Server there are PIVOT features that make this sort of thing less hassle, but IIRC mySQL doesn't yet have such features.

If you don't have a fixed set of properties that you need to transpose then you are forced to use ad-hoc SQL either generated by your application layer or via stored procedure (see http://buysql.com/mysql/14-how-to-automate-pivot-tables.html for one example of the latter). This is usually the case even in DBs with PIVOT/UNPIVOT/equivelant too as such features are generally implemented with fixed properties/columns in mind.


Why is index not being used? Can I replicate plan? I have been tuning a query/ indexes in a dev environment, but cannot replicate the new query plan when applying the same changes to a UAT environment.
Specifically, in the UAT environment the optimizer chooses to ignore a particular index and instead performs a nonclustered index seek on an existing unique constraint, followed by a key lookup on the clustered index.
An abridged version of the query is:

select  d.dim_date_id
       ,   f.dim_form_id
       ,   d.date

INTO #TMP
from    DWH.dbo.tbl_fact_outcome f
join    DWH.dbo.tbl_dim_date d      ON d.date >= DATEADD(DAY,-1,f.known_from) and d.date < f.known_to
join    DWH.dbo.tbl_dim_form df     ON  f.dim_form_id = df.dim_form_id
join    DWH.dbo.tbl_dim_question Q  ON f.dim_question_id = Q.dim_question_id

where   (d.flag_latest_day = 'Y' or d.flag_end_of_month = 'Y'  or (d.flag_end_of_week = 'Y' AND d.flag_latest_week = 'Y'))
       and d.flag_future_day = 'N'
       and df.flag_latest_form = 'Y'
       and f.deleted = 0
       and q.question_key like 'R%'

and d.date >= '14/07/2020'

The index in question is:
CREATE NONCLUSTERED INDEX [ix_tbl_dim_date_flag_future_day_includes] ON [dbo].[tbl_dim_date]
([flag_future_day] ASC)
INCLUDE([dim_date_id],[date],[flag_end_of_month],[flag_latest_day],[flag_end_of_week],[flag_latest_week])

It's not a large table and should only return one or two dates which filter results from subsequent joins accordingly. Adding other fields to key columns in index made no difference in the dev environment - the only improvement came from the order/ shape of the query plan.

The dev query plan (good) is here : https://www.brentozar.com/pastetheplan/?id=SyZjIGA1v
The UAT query plan (bad) is here : https://www.brentozar.com/pastetheplan/?id=BkBBLzCyv

Both environments have the exact same table structure and indexes and are using the same query.
The dev environment does have around half the volume of data overall, but even if I amend the date range in either to return a similar number of records the query plans in both environments stay as they are.
There was a dramatic improvement on performance in the dev environment since this query plan has been used and I am fairly confident the same would be seen in my UAT environment.
I have tried using query hints to force the index and/ or the order but haven't been able to replicate this plan.
Essentially I have two questions:

Why would the optimizer choose a key lookup over this index?
Is there anything I can do to force it to follow the shape seen in the dev environment?

I am using SQL Server 2014 enterprise edition.

EDIT - 20/07/2020 - Actual execution plans added
Actual good execution plan from dev environment - running this took 37 seconds:
https://www.brentozar.com/pastetheplan/?id=BJkr-Q7gP
Actual bad execution plan from UAT - running this took 33m 42s: https://www.brentozar.com/pastetheplan/?id=H1nsxX7ew

This was totally a workaround, but it worked for me.  I had 4 servers running the same database with different data sets.  Three of them would chose an optimal plan, but the fourth would never choose that plan, and was consistently taking significantly longer.  In my case I could see the good plans seeking one of the tables for a handful of rows out of around 100,000 first, then doing the other joins.  The bad plan would do that seek last and would seek that table 500,000 plus times.  I also tried the query hints and re-writing the query in different ways with no luck.
So my work around was to manually query that table for those handful of rows first into a temp table, then join to that instead of the full table.
So in your case could you run something like this first into a temp table and then join against it instead of the whole DWH.dbo.tbl_dim_date table:
select  d.dim_date_id
   ,d.date
   ,d.flag_future_day
from    DWH.dbo.tbl_dim_date d
where   (d.flag_latest_day = 'Y' or d.flag_end_of_month = 'Y'
          or (d.flag_end_of_week = 'Y' AND d.flag_latest_week = 'Y'))
   and d.flag_future_day = 'N'
   and d.date >= '14/07/2020'

Not sure in your case if the above query would be selective enough to be really helpful and I realize that it is a workaround. But sometimes workarounds work!
Another thought, are you able to modify that NCI you included? It might help if you added the date column to the indexed columns instead of the INCLUDE columns. There are a lot of filters done in that query based on that date column.


SET sql_log_bin = 0 in RDS MYSQL 5.6 I have moved into RDS MYSQL 5.6 and now I have a problem. I have 5 read replicas and a/z master.

I am trying to alter a table and want to change a column value and also on some tables I want to add a new column. Although this operation is non blocking on MYSQL 5.6 but when it gets replicated to the slaves it causes slaves to lag for entire duration of it, I cannot afford slave lag as we are 24/7 operations.

I tried doing SET sql_log_bin = 0; to skip the query to the slave but RDS do not allow this, otherwise I could have done slave (one each) --> master approach and eventually changing on master.

Anyone has any advise how to go on about this in RDS MYSQL 5.6

What we have done in a similar scenario (using Aurora RDS for master and slaves) is to use an intermediate small EC2 instance with a standard MySQL acting as a filter. This instance is a relay-slave (secondary master) between our main master and final slaves with a configuration similar to this.

We came to this solution dued to the lack of SUPER privilege and replication configuration options in Aurora RDS: You can not set sql_log_bin or define replication filters in RDS.

The problem here is with DDL statements, they are replicated to slaves although you have replication filters by db or tables in the slave. So a possible workaround is:

Create an auxiliary schema in master.
Set a db filter in mysql.cnf of secondary master, so it only adds to its binlog changes in desired schema:
binlog-do-db=<myschema>
If you need to add or modify columns of one of your tables in main master, first set the auxiliary schema as default:
use auxiliary-schema;
Modify the table indirectly, like
alter table <myschema>.<table> ...

This way, DDL statements are applied in secondary master, but not stored in its binlog and therefore not send to the slaves.


Creating an index on a really busy table We have a table with a lot of insert\select every time ( like 100 machines inserting\updating every second).

What is the best way to create an index on a table that can't be locked not even for a second?

If I create the index, i'm sure it will lock the requests, and I can't do it.

It's a big table with 1 million rows+.

There is no such option as CREATE INDEX WITH (NOLOCK) (and even NOLOCK on a query takes locks, just fewer than without the hint).

The best you're going to do is WITH (ONLINE = ON), which still takes locks at the beginning and end of the operation (both relating to recompiling plans related to the table - see this blog post by Paul Randal for more details).

This will significantly reduce the impact of building the index on your applications, but there is no practical way to completely eliminate that impact. Also, this feature is not free: it requires Enterprise Edition.


How to remove some data not needed on new row from mysql query with rollup Hello I have a problem with the rollup mysql query. I want to get total of each column. I use mysql SUM() and ROLLUP to get the result. But on the new row that is added it copies the data of the last row. How can I remove those data. I provided an image on what I really needed to achieve.

Here is my query.

    SELECT  IFNULL(payments.id, "General Total"), payments.driver_id,
        payments.vehicle_specifications_id,payments.admins_id,
        payments.vehicle_id, payments.boundaries, payments.cashbond_payments,
        payments.loans, payments.penalties, SUM(payments.total_payments),
        a.first_name, a.mid_name, a.last_name, d2.date, d.first_name,
        d.mid_name, d.last_name, v.plate_number, v2.car_rate
    FROM  payments
    INNER JOIN  admins AS     a  ON payments.admins_id = a.id
    INNER JOIN  drivers AS    d  ON payments.driver_id = d.id
    INNER JOIN  dispatch AS   d2 ON payments.dispatch_id =d2.id
    INNER JOIN  vehicles AS   v  ON payments.vehicle_id = v.id
    INNER JOIN  vehicle_specifications AS v2
                ON payments.vehicle_specifications_id = v2.id
    GROUP BY  payments.id WITH ROLLUP

Plan A (rather tedious):

IFNULL(payments.id, "General Total"),
IF(payments.id IS NULL, '', payments.driver_id),  -- conditionally blank each
...

Plan B:

( SELECT ... )  -- without ROLLUP
UNION ALL
( SELECT 'Grand Total', '', ... SUM(...), '', ... )  -- Just total
ORDER BY ...   -- to get "Grand Total" to be last


How can I share values in maintenance plan tasks? A simple example would be a maintenance plan that clears a directory of old files and create some copy backups of all databases on that instance to that same directory. This is run daily

The first cleanup step contains the path where the files are deleted. The second step actually creates the copy backups in the same directory as the first step. So any changes made to the path have to be done in multiple places. For a simple 2 step maintenance plan this is not really a big issue but could be for more complicated plans.

So is there a way that plan steps can share information? I don't necessarily mean passing information between steps but perhaps a global space of the steps?

I could not see anything in the toolbox that would work for this. Best I got is that I could add two new steps.

Create a temp table with the path (and any other constants.)
Using that path delete directory contents
Run copy-backups
Delete temp table

I had thought this question: Can I pass data between stages in a maintenance task would have helped but it was focused on sending completion information in email.

For global parameters we would use package configuration files, perhaps they would work for you as well: Package Configurations


MongoDB Shell support for map.set() I am trying to use the MongoDB shell to run some scripts. Inside the JavaScript I am attempting to run var map = new Map() then map.set(1,1).

However, I am getting the following error:

E QUERY    [js] TypeError: map.set is not a function :

I am running this mongo shell version: MongoDB shell version v4.2.1

Does the mongo shell not support the map.set() function?

No, they hide the standard implementation in implscope.cpp

  // This builtin map is a javascript 6 thing.  We want our version.  so
  // take theirs out

and implement a new one in types.js.

Well, you can workaround doing:

Map.prototype.set = Map.prototype.put


Two instances sharing same system database? I have a situation that I encountered with one of my clients, that I have never seen before. They are running SQL Server Express 2008 R2. There are two instances of SQL Server installed - a default instance on DATA-SVR, and a named instance on DATA-SVR\Product.

When trying to investigate a problem on the system, I checked the data link files on all workstations, and saw that some users were connecting to DATA-SVR, and others were connecting to DATA-SVR\Product. They were able to do this, because both instances have exactly the same databases. Not good, to say the least.

However, when I checked the database paths between the two instances, they are both pointing at the same data files. I checked that this was the case by creating a table in DATA-SVR, and then accessing the same table from DATA-SVR\Product. They're definitely pointing at the same data, so catastrophic failure was averted. However, to clean things up, I tried detaching the databases from the DATA-SVR\Product instance so that there was only one instance to connect to, but this also detached the database from the default instance.

We have a service running on the server, which for some reason is only able to access the named instance, and not the default instance, so I've now changed all users to use the named instance. Everything is working now. However, I'm still uneasy about how this situation could have come about.

Can anyone shed some light on what is happening here, and how to fix it (preferably without reinstalling the database server)?

This happened to me once when I restore system database of SQL 2014. When I investigate further, the 2 different name was pointing to same physical file and from @@version I can see both having similar naming.
The database which I restored was pointing to another database and it got fixed when I revert back from single user mode to multi user.


Where are database specific settings stored? Postgres documentation says ALTER SYSTEM writes the given parameter values to the postgresql.auto.conf file, which is read in addition to postgresql.conf.

I am curious about where the alter database set configuration parameter details are stored.

Database-level settings are not stored in any configuration file, but the pg_db_role_setting catalog, the only question is how to get them out there.

As often in similar cases, psql can be of tremendous help here.  The key is \set ECHO_HIDDEN on, which then shows the queries behind the different meta-commands.

In one of my sandbox DBs I see the following:

test=# \drds
                       List of settings
    Role     │ Database │              Settings
─────────────┼──────────┼─────────────────────────────────────
 alice       │          │ log_statement=none                 ↵
             │          │ work_mem=12MB
 bob         │          │ log_statement=all                  ↵
             │          │ work_mem=64MB
 luser       │          │ log_statement=all
 tom         │          │ log_statement=all
             │ test     │ search_path=test, public
             │ wacky    │ default_transaction_read_only=true

We are interested in the rows where there is no user specified - these are exactly the settings you were asking about.  With ECHO_HIDDEN on, you also get the following query (the formatting is mine):

SELECT rolname AS "Role",
       datname AS "Database",
       pg_catalog.array_to_string(setconfig, E'\n') AS "Settings"
  FROM pg_catalog.pg_db_role_setting s
  LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase
  LEFT JOIN pg_catalog.pg_roles r ON r.oid = setrole
 ORDER BY 1, 2;

One can simplify this to get only the DB-specific settings:

SELECT datname AS database,
pg_catalog.array_to_string(setconfig, E'\n') AS settings
FROM pg_catalog.pg_db_role_setting s
JOIN pg_catalog.pg_database d ON d.oid = setdatabase ORDER BY 1, 2;

 database │              settings
──────────┼─────────────────────────────────────
 test     │ search_path=test, public
 wacky    │ default_transaction_read_only=true


Error Authenticating Proxy Account when Executing SSIS job I have a SQL Server instance that runs 5 scheduled tasks each night, each of which run SSIS packages.

These packages have been running for years and the associated steps run via a Proxy Account (PackageExecutor).  PackageExecuter is associated with a SQL Credential that was a former  domain admin account.

Soon, the domain associated with this admin account is going to be shutdown.   I have to use a new account, on a new domain, as the admin account associated with my roxy, PackageExecutor.  When I created a new Credential for the new Admin account and associated it with PackageExecutor, I started to get the following error when I tried to run one of my SQL jobs as a test:

Unable to start execution of step 1 (reason: Error authenticating
proxy *Domain\Admin_Account*@*fully.qualified.domain.com*, system
error: Logon failure: unknown user name or bad password.).  The step
failed.

If I'm understanding this reasonably explicit error, what it's telling me is that the Credential accounts, associated with my proxy is in correct.  How do I validate this?

I know that this account is legitimate-- I've already associated it with every associated server group, I've made it a sysadmin user on the server.

What could be causing this problem?

To be clear, I haven't mis-typed the account name or the password associated with the Proxy Credential.  However, when I entered the account name Domain\Admin_Account and clicked the Check Names button, SQL Server automatically transformed the User ID to the fully-qualified version.  I'm not sure if this has anything to do with this problem.

I'm at a bit of a loss.  I've given my credential account full access to everything that I can think of.  What might I need to do to get this to work?

UPDATE

Sorry, one more quick mention.  I've found this MSDN kb article.  Resolution method #1 is what I've been doing for years.  The others don't seem to apply, or I'm missing something.  Any tips or clarification would be beneficial.

Just in case others have gotten here for the same reason.  Make sure that the account your proxy/credential is using is a user in its default database in SQL Security.

We went through all the other suggestions (checking that the password hasn't changed, switching to a local service acct for SQL Agent User, rebuilding the credentials and proxy, etc.) and until we made sure the account was a user in its default DB, for some reason, that was causing it to pass a Null SID when it was trying to authenticate to AD.


Postgresql server is running, but service appears stopped I am running Postgresql 9.4 as a service on Windows Server 2008 R2. It usually works as a charm, but now there is a situation when the server is apparently running (I can connect to it, Task Manager shows the running processes), but the Windows Service appears stopped.

I tried using pg_ctl, but I get:

$>pg_ctl restart -D c:\psqldata94
pg_ctl: old server process (PID: 1436) seems to be gone
starting server anyway
server starting
2016-02-23 09:59:00 CET  LOG:  could not bind IPv6 socket: No error
2016-02-23 09:59:00 CET  HINT:  Is another postmaster already running on port  5432? If not, wait a few seconds and retry.
2016-02-23 09:59:00 CET  LOG:  could not bind IPv4 socket: No error
2016-02-23 09:59:00 CET  HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2016-02-23 09:59:00 CET  WARNING:  could not create listen socket for "*"
2016-02-23 09:59:00 CET  FATAL:  could not create any TCP/IP sockets

$>pg_ctl stop
pg_ctl: PID file "c:/psqldata94/postmaster.pid" does not exist
Is server running?

I tried looking for help online, but the only thing I could find, was this ominous post at psql-admin mailing list.

It's exactly as the author of the post describes: the server is running, but it does not respond to commands. I suppose I can restart the underlying Windows server, but obviously this could have grave consequences if I just kill the running processes.

Does anybody know how to gracefully stop the server under this circumstances?

For me, the answer lied in more careful reading of the excellent manual. Particularly the following three points:

See section 9.26.2, Server Signalling functions
See section Notes on how to signal to postgres
See description of the kill command

The general discussion about server shutdown is here.

So what I did was to terminate all the backends that were visible through pg_stat_activity view by pg_terminate_backend and then logging onto the server and running pg_ctl kill TERM <pid> to the remaining running postgres processes.


How to Query Properties of an Entity in Apache Cassandra I am new to Apache Cassandra, and I would like to know how to create a table in which I can filter rows by non-key columns.

In relational databases, I would simply create a table with an ID as a primary key, and then execute a SELECT, filtering by non-key column entries. In Cassandra however, I can not do such a thing.

I have already read about secondary indexes in Cassandra. I know that I could add indexes to the property columnss and then execute a simple CQL SELECT, similar to relational databases, and which yields me the desired rows, but that would be inefficient. Isn't there a better way?

In Cassandra, as far as I know, for SELECT filtered by non-key attributes, you only have three options:

(1) Application side filtering.  That is, if you get your results from a CQL SELECT, use your application to filter the results.  For all but the smallest data sets, this is ill-advised.

(2) Bite the bullet, and create those secondary indices.

(3) Probably the most common option, duplicate your data by having rows composed of keys.  That is, for whatever filter condition you want to apply, create a new entry in your database where you store the keys of all the relevant row entries which match the filter.

Note that while the third option is most common, you will almost eventually develop some data inconsistency due to the inherent de-normalization. Apache Cassandra is not a cure all, it simply handles some applications very well.

Good luck!

P.S.:  Here's a couple decent blog entries which can explain a bit of the theory at the logical data model level.  Part 1 & Part 2


ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX; JOB_SCHEDULER is biggest occupant DBMS_SCHEDULER is occupying all of my SYSAUX tablespace. I ran dbms_scheduler.purge_log which deleted 100 million rows from dba_scheduler_job_run_details, however v$sysaux_occupants and the data file size remain unchanged. Is there some additional action I need to take to clear the SYSAUX tablespace of DBMS_SCHEDULER generated data?

Attempting to insert a single row into a newly created table in my regular tablespace fails with:

ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX

I can see that JOB_SCHEDULER is taking up 92.4% of the SYSAUX tablespace by querying v$sysaux_occupants:

SELECT
    occupant_name,
    round (sum(space_usage_kbytes) * 100 / sum (sum(space_usage_kbytes)) over (), 2) Pct
FROM v$sysaux_occupants
GROUP BY occupant_name
ORDER BY 2 desc NULLS LAST
;

I originally had over 100 million rows in dba_scheduler_job_run_details.

Yesterday, I ran the purge command (which took 3.5 hours):

BEGIN
    dbms_scheduler.purge_log;
END;
/

Today, dba_scheduler_job_run_details has less than 1K rows.

However, the query on v$sysaux_occupants is unchanged; today it still says JOB_SCHEDULER is occupying 92.4%. Likewise querying my data file sizes show that SYSAUX is still maxed out:

select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_data_files d,
    v$datafile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;

It seems like I have not actually deleted the space consumed by DBMS_SCHEDULER.

Is there some step I am missing to clean up all the space consumed by DBMS_SCHEDULER?

To release space to SYSAUX tablespace you can try:

alter table sys.scheduler$_event_log enable row movement;
alter table sys.scheduler$_event_log shrink space;
alter table sys.scheduler$_event_log disable row movement;


Multiple person roles in database(how to define if same person) I have the following data structure:

For internal purposes I can register my outside partner. In this case I don't know his/her identify card number. So I add my partner to database but without id card number,and has no user access.

*I cannot add more pictures, sorry.

Once my partner comes and he/she wants to use the system. He decides to register as a member. He may would not know that he/she has already registered as an outside partner. Filling all Person datafields and the user fields, including the identify card number.

And here comes trouble. I don't want to split the same person into 2 in the case when the partner is already in the system.  I want to use the same person details insted of duplication. It is extremely important that we do not allow person duplication.

What would you suggest?

The Name of a Person and his/her Bith Date are almost enough to make an unique index with both columns. This way you would garantee 100% of unique individuals in your table, but you could have problems with those miracle situations where two people born the same day with same name.

In this case I would recommend you add a new columns called Birth place (City/State/Country) to your Person table and make the Unique Index with those 3 columns.

It will solve your problem.

create unique index single_person_id on Person(name, birth_date, birth_place);


In SQL Server what is the database cost for setting a default value on table that already has over 1mil records? NOTE: I am not a dba. :)

I have a database table that a client needs to set a column's default value. This table has 1.5mil+ records in it and this column is already populated on all existing records (they do this manually right now). They would like this column to be set automatically on all new records that their users create.

Would it cause down time or any major latency issues to set this column's default value during business hours?

In order to apply this change the process will need to acquire a SCH-M lock and once it's got that it should be pretty instant (a metadata only change.)

The only risk is that if you have a long running transaction that is using the table whilst the ALTER TABLE statement waits to acquire the lock any other subsequent requests coming in that require a lock on the table will be queued up behind it to prevent your alter request from being starved.

So you could end up blocking production queries for a while until the lock is granted and the change made.


Unable to upgrade to postgresql 13 I'm getting the following error:

postgres@:~/>cat loadable_libraries.txt could not load library
"$libdir/plpython2": ERROR:  could not access file
"$libdir/plpython2": No such file or directory In database: db_name

When trying to run pg_upgrade from PostgreSQL-11 to PostgreSQL-13.
/usr/pgsql-13/bin/pg_upgrade -b /usr/pgsql-11/bin -B /usr/pgsql-13/bin -c -d /localpart0/db/postgres/11/db_name/data -D/localpart0/db/postgres/13/db_name/data -j 20 -r -v --clone

I don't have any plpython* extensions and already tried changing the pg_pltemplate entries for plpythonu and plpython2u to plpython3 and run the pg_upgrade but it fails either way.
Installed Packages:

postgresql11-plpython.x86_64    11.7-1PGDG.rhel7  @postgres11.prd
postgresql13-plpython3.x86_64   13.1-1PGDG.rhel7  @postgres13.prd

Any guidance would be greatly appreciated.

If someone has a better answer or suggestion please let me know. Otherwise, I believe I found the answer in the pgsql-general mailing list;
https://www.postgresql.org/message-id/936117.1605644139%40sss.pgh.pa.us
Hi,
On Tue, 2020-11-17 at 21:00 +0100, Marcin Giedz wrote:

Hi all, trying to performe upgrade from 12 to 13 installed from
Centos8 repo gives such error:
cat loadable_libraries.txt
could not load library "$libdir/plpython2": ERROR: could not access
file "$libdir/plpython2": No such file or directory

I dropped PL/Python support along with PostgreSQL 13 RPMs.
Regards,
Devrim Gündüz
Open Source Solution Architect, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


Putting a constraints on a already made table in PostgreSQL I was wondering is it possible to add Not Null to a variable created already in the PostgreSQL database. I created a table already, but I forgot to add not null constraints to the variable ?

Tables don't have "variables" - they have columns.

But yes, you can change an existing column to NOT NULL:

alter table existing_table
   alter  the_column set not null;

Note that this will fail, if the column contains NULL values. In that case you need to first update all NULL values to something different, before you can run that statement.

See the manual for more examples.

Another option is to use a check constraint, which allows you to name the constraint:

alter table existing_table
  add constraint check_column_not_null check (the_column is not null);


How to fetch those records who appears in many records in many to many relationship in PostgreSQL? I have the following three tables:

create table users(id int primary key, name varchar);
create table groups(id int primary key, name varchar);
create table user_groups(uid int references users(id), gid int references groups(id));

Following are the records in the respective tables:

insert into users values (1, 'John'), (2, 'Bob'), (3, 'Maria'), (4, 'Jin');

insert into groups values (1, 'Persons - A'), (2, 'Persons - B'),
(3, 'Persons - C'), (4, 'Persons - D'), (5, 'Pay - A'), (6, 'Pay - B'), (7, 'Pay - C');

insert into user_groups values (1, 1), (1, 2), (2, 1), (2, 4), (2, 6), (2, 7),
(3, 1), (3, 7), (4, 1), (4, 3);

Note: There are two types of groups, Persons and Pay

Now I want to find those users who have access to both types of groups (Persons and Pay), below is the desired output:

id        name       groups_user_has_access_to
2         Bob        Persons - A, Persons - D, Pay - B, Pay - C
3         Maria      Persons - A, Pay - C

The result should have only those users with their groups that have access to both types of groups.

Sorry for the bad English and appreciate your help in advance.

Here's my sample query for your question, the query could be slow depending on your table size

SELECT t.uid, u.name, t.groups_user_has_access_to
FROM (
  -- Each of GID, finding its big group (PERSON, PAY) --> called: group_type.
  -- Using distinct to remove duplicated element in group_type
  SELECT ug.uid,
    array_agg (distinct
      case when g.name like 'Person%' then 'Person'
           when g.name like 'Pay%' then 'Pay' end) as group_type,
      array_agg(g.name) as groups_user_has_access_to
  FROM user_groups ug
  JOIN groups g on ug.gid = g.id
  GROUP BY ug.uid
)t
JOIN users u on t.uid = u.id
WHERE array_length(t.group_type, 1) > 1 -- Reject any UID which belong to 1 group.
ORDER BY t.uid;

I would suggest you to read the array functions in the query above.


Is there any performance advantage to marking a SQL function as LEAKPROOF? PostgreSQL has an attribute LEAKRPOOF which you can use to declare a function,

  LEAKPROOF indicates that the function has no side effects. It reveals no information about its arguments other than by its return value. For example, a function which throws an error message for some argument values but not others, or which includes the argument values in any error message, is not leakproof. This affects how the system executes queries against views created with the security_barrier option or tables with row level security enabled. The system will enforce conditions from security policies and security barrier views before any user-supplied conditions from the query itself that contain non-leakproof functions, in order to prevent the inadvertent exposure of data. Functions and operators marked as leakproof are assumed to be trustworthy, and may be executed before conditions from security policies and security barrier views.

The docs go on to say,

  The query planner has more flexibility when dealing with functions that have no side effects. Such functions are referred to as LEAKPROOF, and include many simple, commonly used operators, such as many equality operators. The query planner can safely allow such functions to be evaluated at any point in the query execution process, since invoking them on rows invisible to the user will not leak any information about the unseen rows. Further, functions which do not take arguments or which are not passed any arguments from the security barrier view do not have to be marked as LEAKPROOF to be pushed down, as they never receive data from the view. In contrast, a function that might throw an error depending on the values received as arguments (such as one that throws an error in the event of overflow or division by zero) is not leak-proof, and could provide significant information about the unseen rows if applied before the security view's row filters.

Is the following LEAKRPOOF function

CREATE FUNCTION foo_leakproof(bar int)
RETURNS int AS $$
  SELECT bar;
$$
IMMUTABLE LEAKPROOF
LANGUAGE sql;

Ever planned differently or does it run with a different runtime profile than the LEAKPROOF lacking alternative,

CREATE FUNCTION foo_leaky(bar int)
RETURNS int AS $$
  SELECT bar;
$$
IMMUTABLE
LANGUAGE sql;

Seems like there is no difference in the most simple cases between thee two performance profiles,

CREATE TABLE t1 AS
  SELECT x::int FROM generate_series(1,1e6) AS gs(x);

CREATE VIEW v1 AS TABLE t1;

CREATE VIEW v2 WITH (security_barrier)
  AS TABLE t1;

-- BASE TABLE
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM t1;
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM t1;
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM t1;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM t1;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM t1;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM t1;

-- VIEW
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM v1;
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM v1;
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM v1;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM v1;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM v1;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM v1;

-- VIEW with SECURITY_BARRIER
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM v2;
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM v2;
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM v2;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM v2;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM v2;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM v2;

These were all 100-110ms. No difference in performance.

However changing the functions to use

CREATE FUNCTION foo_leakproof(bar int)
RETURNS int AS $$
  SELECT bar+1;
$$
IMMUTABLE LEAKPROOF
LANGUAGE sql;

CREATE FUNCTION foo_leaky(bar int)
RETURNS int AS $$
  SELECT bar+1;
$$
IMMUTABLE
LANGUAGE sql;

Will make all the calls slower, but the calls on v2 (our view with a security_barrier) massively slower (2x slower) on the version with and without LEAKPROOF. This is also in agreement with the docs on security_barrier,

  Views created with the security_barrier may perform far worse than views created without this option. In general, there is no way to avoid this: the fastest possible plan must be rejected if it may compromise security. For this reason, this option is not enabled by default.

;tldr LEAKPROOF seems to never make queries slower. However, it's not making them faster either -- in my tests. Perhaps it gives the ability to greater-optimize a query on a view with a security_barrier, but I haven't created that environment. security_barrier massively slows down the view, and should only be used when needed.

Benchmarks done with PostgreSQL 10.4.


MySQL cursor always exits out of loop The cursor query and select value query returns rows if I run it in mysql but when in a cursor it always exits out of loop.

Anything wrong here?

I've added "BEFORE LOOP", "EXIT" and "IN LOOP" so it prints where it is but it always starts with BEFORE LOOP and then ends with EXIT.

CREATE PROCEDURE getTotal()
BEGIN

DECLARE HOSTID INTEGER;
DECLARE cITEMID INT;
declare finished bool default false;
DECLARE Total INT;
declare cur1 cursor for SELECT itemid  FROM items WHERE hostid = 10579;
declare continue handler for not found set finished = true;

open cur1;
   loop_1: loop
        fetch cur1 into cITEMID;
        SELECT "BEFORE LOOP";
        if finished then
            SELECT "EXIT";
            leave loop_1;
        end if;
        SELECT "IN LOOP";
-- Test query
      SELECT value from history_uint WHERE itemid = cITEMID ORDER BY itemid DESC LIMIT 1;

-- Final select query will look like this.
-- SET @Total := @Total + (SELECT value from history_uint WHERE itemid = cITEMID ORDER BY itemid DESC LIMIT 1);
-- SELECT @Total;

  end loop;
close cur1;

END //

DELIMITER ;

Queries:

SELECT itemid  FROM items WHERE hostid = 10579;
| itemid |
| 12345  |
| 12346  |
| 12347  |

SELECT value from history_uint WHERE itemid = 12345 ORDER BY itemid DESC LIMIT 1;
| value |
| 1     |

SELECT * from history_uint;

| itemid | value | clock (unixtimestamp) |
| 12345  | 13     | 4364564654654        |
| 12346  | 1      | 4364564654657        |
| 12347  | 16     | 4364564654654        |
| 12345  | 13     | 4364564654756        |
| 12346  | 2      | 4364564654753        |
| 12347  | 15     | 4364564654756        |

Note: The clock column value is just made up.

Figured out the issue.

Variable should not be the same as any column in the query.

DECLARE HOSTID INT;

declare cur1 cursor for SELECT itemid  FROM items WHERE hostid = 10579;

Since the cur1 query has a column named hostid it will trigger the not found continue handler which sets finished variable to true.

Not only it triggers continue handler but in some cases it can produce syntax error. Source - https://stackoverflow.com/a/60988686/13109839

Solution rename the HOSTID variable to _HOSTID.

DECLARE _HOSTID INT;


Postgresql row-level encryption Looking over the docs, I didn't see any mention of row-level encryption.

Lets assume I have a database of highly classified information that needs to get locked down. How can I implement a table with row-level encryption in postgres? Failing row-level, what's the next best thing I can do, and what are the drawbacks?

Usually, you don't want to do this. Have you given second thoughts to this?

Else, pg_crypto is your friend. But as the above-mentioned slides suggest, it isn't necessarily a panacea.


Handle concurrency when insertion depends on reading [Short]

I have the following situation: user A attempts to insert data DA into the database. To check whether user A is allowed to insert DA, I need to run a query and do some computation. The problem I'm running into is that while I do the computation, another user (B) also attempts to insert data into the database. Now, suppose both users read the information needed for the computation before new data is inserted, then they might both get cleared for insertion whilst data from user A would forbid user B from inserting, thus leaving the database in a inconsistent state.

How can I solve this kind of concurrency in Azure SQL Database V12?

[Detailed]

The data the user is inserting is the beginning and end of a time interval, such as start: 6:00, end: 7:00.
The requirement is that there must be no time interval overlaps. This means that intervals start: 6:00, end: 9:00 and start: 5:00, end: 6:00 can't both exist.
Currently what I'm doing is checking whether there are any rows that overlap the new interval the user is trying to insert using the following query:

SELECT COUNT(*) FROM [Table1] WHERE Start <= attempEnd && End >= attemptStart

Now, the problem is that multiple users might be trying to insert an interval and these new intervals might overlap each other. However, this information might not be available at the time the query above runs, which causes overlapping intervals being inserted.

How can I solve this kind of concurrency in Azure SQL Database V12?

The problem is that there could be values present at the end of A's transaction - the ones inserted by B - that were not present when A first checked. This is known as a phantom

  A phantom is a row that matches the search criteria but is not initially seen.

The way to prevent phantoms is to use the Serializable isolation level. This is the only isolation level which prevents phantoms.

There are other ways to serialize a workload without using that isolation level. One is to obtain an exclusive lock on the whole table using TABLOCKX. When used within an explicit transaction (BEGIN TRANSACTION) this will take an exclusive lock on the table and hold it until the transaction is committed or rolled back. Therefore B will not be able to insert until A has committed. The table lock affects all work, however, including simple queries which would not induce interval overlaps.

A finer-grained approach would be to use sp_getapplock. This allows your code to generate its own lock with its own semantics. It acts as a MUTEX. When workload A starts it acquires this applock. It can then go on to preform the check and insert confident it alone has rights to do so. If workload B were to start concurrently it would attempt to acquire the applock and be blocked because that applock is owned by A. You have to be careful that all work that could be subject to the overlapping interval problem must start by acquiring this custom applock and finish by releasing it. Writes that do not suffer from the interval overlap problem and all reads can proceed without acquiring the applock or being blocked by it.


How should INNER JOIN query look? I am trying to fetch some records using INNER JOIN. The problem is that I am not sure what's the best (recommended) way of doing it.

What "pros" and "cons" have the queries shown below? All of them work correctly and do the same thing.

#This query seems to perform faster than the
#following one but is it ok to select all the columns?
#Needed is only one column (`id`)

SELECT book_name, book_type FROM books b INNER JOIN (
    SELECT * FROM authors WHERE user_id = ? AND male = ?
) a ON b.author_id = a.id;

SELECT book_name, book_type FROM books b INNER JOIN (
    SELECT id FROM authors WHERE user_id = ? AND male = ?
) a ON b.author_id = a.id;

SELECT book_name, book_type FROM books b INNER JOIN authors a
ON b.author_id = a.id WHERE a.user_id = ? AND a.male = ?;

I am also not sure about the functionality and performance. For example, in the first case, the subquery selects some records (there may be let's say five of them), then joins with the table to return the result set. In the last example (I suppose) the full tables will join (a lot more records) and then the records are filtered.

I would opt for the third option purely for readability and simplicity. Have you looked at the execution plans for all three, I expect they may give the same result for each. I would have expected the bracket query with the * would have been slower.


Issue migrating oracle package to postgres using ora2pg I was migrating an oracle(12c) package to postgres(Enterprise db 10.5) using ora2pg.
The migration run successfully for most of the functions. However, one function referenced multiple times in the package is causing errors.
I am giving both versions of the code below:
 oracle

create or replace package body sms_package as
--- Other functions
PROCEDURE LOG_MESSAGE (LogType varchar2, LogDetails varchar2) IS
    BEGIN
        INSERT INTO SMS_TABLE (REF_DATE, LOG_TYPE, LOG_DETAILS)
        VALUES (SYSDATE, LogType, LogDetails);
        COMMIT;
    END;

ora2pg

CREATE OR REPLACE FUNCTION log_message (LogType text, LogDetails text) RETURNS VOID AS $body$
BEGIN
        INSERT INTO SMS_TABLE(REF_DATE, LOG_TYPE, LOG_DETAILS)
        VALUES (clock_timestamp(), LogType, LogDetails);
        COMMIT;
    END;
$body$
LANGUAGE PLPGSQL
;

An example of the references:
oracle

procedure sendtest (dash_type varchar2, sms_message varchar2) IS
        cursor x is
        SELECT ISDN FROM  oracle_store;
    begin
        dbms_output.put_line('1');
            if sms_message is not null then
                for i in x loop
                    insert into s_table(message_text, received_time, DELIVERY_STATUS, retry_attempts) values (..);
                    sms_package.LOG_MESSAGE (dash_type, 'sending to '||i.ISDN||' message =>'||sms_message);
                    dbms_output.put_line('notification_type='||dash_type||' sms='||sms_message);
                end loop;
                commit;
            end if;
    end;

ora2pg

CREATE OR REPLACE FUNCTION sendtest (dash_type text, sms_message text) RETURNS VOID AS $body$
DECLARE
        x CURSOR FOR
        SELECT ISDN FROM  postgres_store;
BEGIN
        RAISE NOTICE '1';
            if sms_message is not null then
                for i in x loop
                    insert into s_table(message_text, received_time, DELIVERY_STATUS, retry_attempts) values ();
                    sms_package.LOG_MESSAGE(dash_type, 'sending to '||i.ISDN||' message =>'||sms_message);
                    RAISE NOTICE 'notification_type=% sms=%', dash_type, sms_message;
                end loop;
                commit;
            end if;
    end;
$body$
LANGUAGE PLPGSQL

There appears to be a problem in calling the function from within another function as am getting this error

[42601] ERROR: syntax error at or near "sms_package"

How can I execute another function from within a function in postgres. How can I call that function from within the another function

Since sms_package.LOG_MESSAGE is a function in PostgreSQL, you have to call it using PERFORM in PL/pgSQL:

PERFORM sms_package.LOG_MESSAGE(...);

But there are other problems in your code. At least the COMMIT in the finction definition will cause an error.

You may have to redesign and rewrite your code.


View for a MANY_MANY_MANY relationship Let's say I have 3 main tables:
category, tag, url

category and tag have a MANY_MANY relationship (category_tag).

tag and url have a MANY_MANY relationship (url_tag).

I would like for a given category to retrieve all the linked urls.

What is the easiest, most efficient way to do this?

Could I maybe build a "VIEW"? What would be the query?

I am using MySQL.

Encapsulating the queries into Views or not, is your choice. For every one of the below queries, you can create a view:

CREATE VIEW category_url AS
  ( Query
  ) ;

If you want only the IDs of the categories and URLs, the simplest (joining only 2 tables) is this - no need for joining the tag table, as long as the foreign key constraints are defined:

    SELECT DISTINCT
        ct.category_id,
        ut.url_id
    FROM
        category_tag AS ct
      JOIN
        url_tag AS ut
          ON ut.tag_id = ct.tag_id

or (the equivalent) which can be easily extended to include the number of tags relating a category with a url:

    SELECT
        ct.category_id,
        ut.url_id,
        COUNT(*) AS number_of_joining_tags         --- optional
    FROM
        category_tag AS ct
      JOIN
        url_tag AS ut
          ON ut.tag_id = ct.tag_id
    GROUP BY
        ct.category_id,
        ut.url_id

If you also want columns from the category and url tables as well (which is very probable), you need to join those tables, too:

    SELECT
        ct.category_id,
        ut.url_id,
        COUNT(*) AS number_of_joining_tags,        --- optional

        c.*,                                       --- the columns from `category`
        u.*                                        --- and `url` that you need
    FROM
        category_tag AS ct
      JOIN
        url_tag AS ut
          ON ut.tag_id = ct.tag_id

      JOIN
        category AS c
          ON c.id = ct.category_id
      JOIN
        url AS u
          ON u.id = ut.url_id

    GROUP BY
        ct.category_id,
        ut.url_id


SQL Server - how transactions and transaction log work (simplified) I have a theory on how transactions work, but I would like if someone could verify it or correct some points if possible. Let's consider we have a database with full recovery model.

Now, everything that happens to the database is logged into the transaction log first, written to disk after a checkpoint is reached.

If I am inside an explicit transaction, all changes to database are written to log first, but during checkpoint uncommitted transactions are not propagated to disk.

If I decide the rollback the transaction, the rollback is simply done by deleting all the transaction log entries.

If I commit, after next checkpoint changes are written to disk.

Transaction log contains instructions on how to redo the operation, not how to undo it, which is why restore to point in time is only possible from baseline backup forwards, not backwards.

Its deducible from some operations and their redo instructions on how to undo it (but not for all). Thats how commercially available tools work to e.g. recover data in full recovery model databases using transaction log.

All are my points at least somewhat valid? Could someone please comment? This is my third and hopefully final question on my quest to discover how transactions and transaction logs work in SQL Server. Thanks.

EDIT:

Simple test seems to disprove my theory, as SQL Server seems to write into primary data file even if inside a transaction that insert 1000000 rows (nothing else goes on with the database). Im confused and sad:)

No your theory is wrong.

Dirty pages can be written to disc even if the transaction is not yet committed. However it is ensured that they cannot be written before the last transaction log entry that modified the page has been written to disc.

The transaction log records do contain sufficient information both for redo and undo (except for in tempdb where only undo is necessary). If you decide to rollback the transaction then nothing is deleted from the log. Instead compensation log records are written to the log indicating this.


MySQL Update: why would rows matched change based on the set clause? I ran an update query joining across a huge number of tables that updated one field, and it matched 29k some-odd rows. Running the same query with only an addition in the Set clause increased the matched rows to 118k some-odd rows. It seems this should only change if the WHERE clause were edited.

I think I just figured it out...

MySQL must count the number of rows that will be updated, not all the ones that will be generated by the join that happens during the update. The difference being that if there are many [B] to one [A], and many [C] to one [B], joining all would create n([C]) rows, but updating [B] would only count n([B]). Adding a set for [C] makes this number n([B]) + n([C]).

Since the fields in the set clause are on different tables, it changes the number updated.


MySQL-load data infile- invalid data validation Is it possible to validate the data and load it in a table using  load data infile?

This is the query

load data local infile 'C:/Users/970685/Desktop/SFcheck.csv'
into table wow_macro.starting_footage_stg
fields terminated by ','
IGNORE 1 LINES
FIELDS ESCAPED BY \N
(store_id,category_id,footage,plan_id,@merch_id) ;

This is data in CSV:

store_id    category-id footage plan_id merch_id
1           1           3       1       fdfd

All the fields are INT in the table but when I execute the query the value 'fdfd' is not validated but saved as 0. (I have also given not null in the table and have not set any default value)

You can validate and have check the values before inserting. Check the example below:

load data infile ‘path/to/file.csv’ into table TABLENAME fields terminated by ‘,’ (col1,col2,@TRUEFALSECOLVAR) set TRUEFALSECOL=case @TRUEFALSECOLVAR when ‘true’ then 0 when ‘false’ then ‘1’ end;

More  load data infile usecases.


Indexes getting highly fragmented during normal usage of system I am using MS SQL Server 2014 Standard Edition in my project and recently I have encountered an issue with index fragmentation getting high during normal usage of the system by the users.

Currently indexes are rebuild once per day during maintenance window, but after certain operations some of the indexes can get quite high (above 30%). Other issue is that in case there is big amount of data to process, the fragmentation occurs during single process, so we end up with process that starts rather quickly, but as soon as fragmentation kicks in its performance drops drastically.

What can I do in such situation? It is rather impossible for me to get additional maintenance window(s).
I was thinking about rebuilding only some of indexes periodically, but I am afraid that it will end up with locks or even deadlocks on the database.

If you can properly confirm that fragmentation really is a problem, for example because read performance drops measurably when read-ahead becomes less effective, or because the system is struggling to keep up with the number of page splits, you might look at gently adjusting the fill factor on the most critical indexes. This will improve write performance, at a cost to reads and the number of pages that can be cached (in the limited buffer pool available with Standard Edition).

Don't go crazy with low fill factors to start with. Pick a manageable number of the most problematic indexes, make an estimate of the amount of free space needed to avoid too many splits during the normal workload between maintenance windows, and test the effects of setting the lower fill factor (normally no lower than about 90% for the first test). Do not just blindly set a lower fill factor for all indexes.

Bear in mind that nine times out of ten, the system has other, bigger, problems than those directly attributable to sub-optimally set fill factors. You should be monitoring the important performance aspects of the system, and be able to make the case for any changes based on a sound technical analysis of real historical data.

Reading material:

Specify Fill Factor for an Index in the product documentation
5 Things About Fillfactor by Kendra Little
Does index fill factor affect fragmentation? by Erik Darling
Stop Worrying About SQL Server Fragmentation by Brent Ozar
What is the Best Value for Fill Factor in SQL Server by Eric Blinn
SQL Server Index and Statistics Maintenance by Ola Hallengren


Oracle RAC: Can two transactions share an SCN? Under what conditions (if any) can two transactions share an SCN?

To put another way, is there a guarantee that if several DML statements share an SCN they are guaranteed to be part of the same transaction?

Two transactions won't share an SCN barring a bug in Oracle.

Exactly how Oracle ensures this varies by version, platform, and configuration settings.  For example, back in the Oracle 9.2 days, there was by default a non-zero max_commit_propagation_delay which meant that a row that was inserted on one node and committed might not be immediately visible to a query on the other node.  In that configuration, Oracle could use a Lamport SCN generation algorithm where SCN's were generated in parallel without a need for internode communication.  In 10.1 and later, a broadcast on commit strategy (link is to a PPT presentation from Julian Dyke which is excellent but may be bigger than you'd expect) is used instead and max_commit_propagation_delay defaults to 0 (no idea what happens if you use 10.1 and set a non-default max_commit_propagation_delay-- that might well flip back to Lamport).


PostgreSQL vs. MySQL: spatial feature comparison We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping spatial polygons.

That being said, our database has many other components that include all the typical things you would find in your general relational database.

We are at the point in our project where we must choose which database solution to use.

All project members are more familiar with the implementation and administration of MySQL, yet all research suggests that PostgreSQL is the better solution - especially in regards to spatial data using postGIS.

We expect (hope) our application will experience a lot of action with a lot of concurrent users.

Does anyone with experience using MySQL as their RDBMS with a spatial data component have any long term advice / experiences?

Are there any disadvantages of using PostGIS with the exception of familiarity?

I can't speak to advantages/disadvantages vis-a-vis MySQL, but the PostGIS code is pretty widely regarded as one of the best (in terms of speed/functionality) and most mature (in terms of testing/real-world exposure) available.

By way of example, there was a talk at PGEast 2010 by some folks from the FAA on their converting their airport database (used by AeroNav and others to compile charts) to Postgres/PostGIS from Oracle.
The avationDB site is also built on top of Postgres (8.0).

If GIS-related queries are at the heart of what you're doing my suggestion would be to go with Postgres.  It can certainly handle everything else you would normally do in a relational database as well.

In terms of making the switch from MySQL, the documentation behind Postgres is first-rate, and there's also a section of the Oostgres Wiki about switching from MySQL to Postgres.
The initial learning curve may be a bit steep and you may need to tweak your database and any stored procedures (if you've written them for MySQL already), but it is not an insurmountable task.

You should be able to pick up enough to make the switch in a couple of weeks time, and if you set up a development database you can probably be well versed in routine tasks within a month, and confident you know where to look in the manual for the not-so-routine ones.


SQL agent job Error while migrating from 2008 R2 to SQL 2014 for openrowset query I need to insert metadata into temp table. It was working fine in 2008 R2. Below is my query.

Insert INTO #Temp
(SELECT * FROM OPENROWSET('SQLNCLI','Server=.;Trusted_Connection=YES;', 'set fmtonly off; EXEC msdb.dbo.sp_help_job') )

Getting this error and unable to resolve.

Msg 11520, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 The metadata could not be determined because statement 'EXECUTE master.dbo.xp_sqlagent_is_starting @retval OUTPUT' in procedure invokes an extended stored procedure.

Try using WITH RESULTSETS as indicated by this connect item

https://connect.microsoft.com/SQLServer/feedback/details/737341/sql-server-2012-openrowset-on-msdb-dbo-sp-help-job-throws-error


Selecting data from multiple rows and insert it into a column My table is like this:

temp1
name   city     phone_no    pincode
-----------------------------------
amit   Delhi     12345       123
Rajiv  Bombay   836536       432

How do I transform the last three columns into a single column, address, like in the following?

name     address
----------------
amit      Delhi
amit      12345
amit        123
rajiv    Bombay
rajiv    836536
rajiv       432

The elegance of the answer will vary with the DBMS of your choice. In it's simplest form:

select name, city as address from temp1
union
select name, phone_no as address from temp1
union
select name, pincode as address from temp1

I believe this should be supported by most DBMSes. If your DBMS supports lateral (cross apply in sqlserver?) you can do something like:

select x.name, y.address
from temp x
join lateral ( values (x.name,x.city)
                    , (x.name,x.phone_no)
                    , (x.name,x.pincode)) y(name,address)
    on x.name = y.name

Edit: The T-SQL (SQL Server) syntax uses CROSS APPLY to perform the same task, i.e.:

SELECT x.name, y.address
FROM temp x
CROSS APPLY ( VALUES (x.city),
                     (x.phone_no),
                     (x.pincode)
            ) AS y(address)

CROSS APPLY in SQL Server also often generates more effective execution plans than a bunch of union operators, so it's definitely worth a try.

Edit: As @ypercube suggests it is not necessary to join, removing the name column and taking the cartesian product gives same result

select x.name, y.address
from temp x
cross join lateral (
    values (x.city),(x.phone_no),(x.pincode)
) y(address)


Automatic failover (Clustering/Always On AG) Let's says we have 2 nodes (N1, N2) clustered (say C1) on-site. And an Always On AG is configured between this cluster C1 and a new SQL server instance N3 (N3 is off-site).

Now, if there is a problem (failure) with N1 then N2 will become active (or vice versa) and continue to serve requests. This is due to clustering concept.

However, if there is a failure of the cluster, then as per SQL 2012 documentation, there cannot be an automatic failover as this is not supported between clustered node and separate server instance. DBA will have to manually do the failover.

Please can you confirm the following:

Is this feature (automatic failover with clustered and separate server instance) supported in newer SQL versions (2016,2017)?
So as to be able to use the automatic failover feature we have only 3 options:

a) Cluster all the server instances into 1 cluster and setup a disk backup strategy (since single shared disk is used in clustering concept)

b) Don't cluster at all and only setup AG between SQL server instances.

c) Cluster all server instances into 1 cluster and setup AG between them - however bit confused in practicality of this scenario - What is the purpose of having AG within cluster?

Any guidance and reference links will be very helpful.

Is this feature (automatic failover with clustered and separate server instance) supported in newer SQL versions (2016,2017)?

No it is not. SQL Server 2019 CTP has also been launched and I am not sure whether the functionality has changed in CTP version.

  So as to be able to use the automatic failover feature we have only 3 options:

For automatic failover to occur you should make sure the replicas are configured for "automatic failover" and the configuration is set to be "synchronous commit". You can select both options when configuring AG with GUI or you can use T-SQL and use options like AVAILABILITY_MODE = SYNCHRONOUS_COMMIT and FAILOVER_MODE = AUTOMATIC
For example( Code Source)

CREATE AVAILABILITY GROUP MyAg
   WITH (
      AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
      FAILURE_CONDITION_LEVEL  =  3,
      HEALTH_CHECK_TIMEOUT = 600000
       )

   FOR
      DATABASE  ThisDatabase, ThatDatabase
   REPLICA ON
      'COMPUTER01' WITH
         (
         ENDPOINT_URL = 'TCP://COMPUTER01:5022',
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  --mmode is set to sync commit
         FAILOVER_MODE = AUTOMATIC,  ----failover mode is set to automatic
         BACKUP_PRIORITY = 30,
         SECONDARY_ROLE (ALLOW_CONNECTIONS = NO,
            READ_ONLY_ROUTING_URL = 'TCP://COMPUTER01:1433' ),
         PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE,
            READ_ONLY_ROUTING_LIST = (COMPUTER03) ),
         SESSION_TIMEOUT = 10
         ),

I would highly recommend you to read Availability Groups:When Is My Secondary Failover ready

As for your question some are vague. You simply cannot have automatic failover in AG if you are deploying AG on FCI. This is because it will conflict with failover mechanism of FCI. As to why the MS has given option to configure AG in FCI this is simply because of complex environment people have and they wanted AG to be set in almost all HA scenarios. Configuring AG in FCI would give you both OS level protection and database level protection just like you used to have mirroring configured for database which was residing on FCI.


Why does Sql Server makes this bad query plan choice? Sql Server gives me a bad query plan and I'm trying to understand why that is.

The query is this:

SELECT TOP (10)
    [Project1].[C1] AS [C1],
    [Project1].[Id] AS [Id],
    [Project1].[SupplierNumber] AS [SupplierNumber],
    [Project1].[ArticleNumber] AS [ArticleNumber],
    [Project1].[ArticleName] AS [ArticleName]
    FROM ( SELECT
        [Extent1].[SupplierNumber] AS [SupplierNumber],
        [Extent1].[ArticleNumber] AS [ArticleNumber],
        [Extent1].[Id] AS [Id],
        [Extent1].[ArticleName] AS [ArticleName],
        1 AS [C1]
        FROM  [dbo].[SalesEntry] AS [Extent1]
        LEFT OUTER LOOP JOIN [dbo].[Article] AS [Extent2]
            ON ([Extent1].[ArticleNumber] = [Extent2].[ArticleNumber])
                AND ([Extent1].[SupplierNumber] = [Extent2].[SupplierNumber])
        WHERE [Extent2].[id] IS NULL
    )  AS [Project1]
    ORDER BY [Project1].[SupplierNumber] ASC, [Project1].[ArticleNumber] ASC
  OPTION (TABLE HINT ([Extent1], INDEX(IX_Main)))

I have already annotated the query with two hints:

the join is forced to be a loop join and
I force an index that fits the order by criteria.

With these hints, I get an efficient query plan that looks like this:

  Scan index IX_Main on SalesEntry and for each 10 entries, look up the respective article entries with IX_Main on Articles.

Both tables have an IX_Main index on (SupplierNumber, ArticleNumber).

That way the query is fast.

Without the hints, however, Sql Server does a clustered index scan on SalesEntry, which is not useful at all, and an index scan on IX_Main for Article, and then brings the two streams together with a hash match.

That's not so fast, particularly because all the rows of SalesEntry now need to be scanned although we're only interested in the top 10 regarding IX_Main.

I'm confused as to why Sql Server would make that decision.

There's a TOP 10 specifier. That should tell Sql Server that it can get enough rows for the result super-fast with the index it chooses to ignore (IX_Main). It then would need to do only a lousy ten lookups with the index IX_Main on Article.

I already tried and failed to reduce this to a simple example that can be reproduced, so I'm putting this with as much information out there as I think it relevant.

Does anyone have an idea about what Sql Server's thought process might be?

(The query looks a bit weird as it is based on what my ORM, Entity Framework produces.)

EDIT: Here's the problematic plan as xml in a gist.

That top being kind of removed from the order by makes is hard for the query optimizer
It does not need to just do a lousy 10 lookups as it need top 10  WHERE [Extent2].[id] IS NULL
With more statistics the query optimizer may get smarter
I know you are using an ORM but give this a try

SELECT  Top(10) 1 AS [C1],
        [Extent1].[SupplierNumber] AS [SupplierNumber],
        [Extent1].[ArticleNumber]  AS [ArticleNumber],
        [Extent1].[Id] AS [Id],
        [Extent1].[ArticleName] AS [ArticleName]
        FROM [dbo].[SalesEntry] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Article] AS [Extent2]
            ON [Extent1].[ArticleNumber]  = [Extent2].[ArticleNumber]
           AND [Extent1].[SupplierNumber] = [Extent2].[SupplierNumber]
WHERE [Extent2].[id] IS NULL
ORDER BY [Extent1].[SupplierNumber] ASC, [Extent1].[ArticleNumber] ASC


Problem in MySQL cluster when using two clusters I have a master-slave MySQL Cluster 1 which is two way. It is working fine.
I have another MySQL master-slave Cluster 2 where data is coming from Cluster 1 master to Cluster 2 master. It is also working fine except the part where data is not replicated to slave in Cluster 2.

This setup is shown in diagram below. The problem part is shown in RED color i.e. it is not working.

Is this kind of setup even possible and if yes then how can I make it work?

I'm assuming from the diagram that this is not in fact MySQL Cluster (NDB) but rather the informal sense of the word "cluster."

This should be fine as long as you don't intend to write to M2 or S2, or if you do, that the writes are exclusively to schemas and tables that do not exist on M1.

There are three probable causes:

You didn't enable log_slave_updates on both M1 and M2.  Without this option, incoming events don't propagate outward.
You haven't given each server a unique server_id.
You're doing replication filtering (rarely a good idea) and its configuration is being interpreted differently than you expect.


How to track IOPS statistics? Is there a way to access statistics about IO operations per second in DB2 9.7? I am not interested in the buffer pool hit ratio, just IOPS.

Storage managers reported us that a few days ago average IOPS is over 5000  (previously it was 300 they said). I just want to track those numbers which storage managers reported us because I do not want to this problem re-occur.

This is a matter of looking at physical writes per buffer pool or tablespace over time.  db2top will tell you this if you are in Delta mode and look at the "Delta p_reads/s" column.


Refresh materialize View fast on commit multiple table In a  materialized view with refresh fast on commit i have the below query

select pp.ROWID AS PP_ROWID,aup.rowid AS AUP_ROWID,
       pp.hierarchicode,pp.id, aup.userid
       from app_useracess aup, core_organization pp;

when the app_useracess changes the materialize view change the data with fast method but when a commit occure on core_organization the materialize view does not refresh , unfortunately even i refresh it with this statement :

DBMS_SNAPSHOT.REFRESH( 'CORE_POWER_AUTHORIZE_ALLOW','F');

it does not refresh , and when i refresh it completely with C as a secound parameter it sense the data changes .
is it possible to refresh the materialize view with fast method and on commit on the core_organization  table ??

update :
the MV DDL is :

create materialized view CORE_POWER_AUTHORIZE_ALLOW
refresh fast on commit
as
select pp.ROWID AS PP_ROWID,aup.rowid AS AUP_ROWID,P.ROWID AS P_ROWID ,
pp.hierarchicode,pp.id, aup.userid,p.id as pID
  from core_power p ,app_userspower aup, core_power pp
 where p.id = aup.powerid
 and pp.hierarchicode like p.hierarchicode || '%';

i also use  dbms_mview.explain_mview and the result is :

REFRESH_FAST_AFTER_INSERT       Y                   REFRESH_FAST_AFTER_ONETAB_DML   Y                   REFRESH_FAST_AFTER_ANY_DML      Y
REFRESH_FAST_PCT                N           PCT is not possible on any of the detail tables in the materialized view

and there is so funny that the materialize refreshed base on this query :

select e.LAST_REFRESH_TYPE,e.LAST_REFRESH_DATE from dba_mviews e where e.mview_name = 'MV_NAME';

but the count(*) from my MV does not changes .

Just use DBMS_MVIEW.EXPLAIN_MVIEW + MV_CAPABILITIES_TABLE to find why fast refresh is not possible on your mview.

Fast refresh is possible without a join condition.

Create the table for storing explain results:

SQL> @?/rdbms/admin/utlxmv

Table created.

Create tables + mview:

create table t1 (c1 number primary key, c2 varchar2(20 char));
create table t2 (c3 number primary key, c4 varchar2(20 char));
create materialized view log on t1 with rowid;
create materialized view log on t2 with rowid;

create materialized view mv1
refresh fast on commit
as select t1.c1, t1.c2, t2.c3, t2.c4,
t1.rowid as rt1, t2.rowid as rt2 from t1, t2;

Explain mview and see fast refresh capabilities:

SQL> exec dbms_mview.explain_mview('MV1');

PL/SQL procedure successfully completed.

SQL> select capability_name, possible, msgtxt from mv_capabilities_table
     where capability_name like 'REFRESH_FAST%'

CAPABILITY_NAME                          P MSGTXT
---------------------------------------- - ----------------------------------------
REFRESH_FAST                             Y
REFRESH_FAST_AFTER_INSERT                Y
REFRESH_FAST_AFTER_ONETAB_DML            Y
REFRESH_FAST_AFTER_ANY_DML               Y
REFRESH_FAST_PCT                         N PCT is not possible on any of the detail
                                            tables in the materialized view

PCT fast refresh is not possible because there are no partitions in this example.

Verify fast refresh:

SQL> select * from mv1;

no rows selected

SQL> insert into t1 select rownum, 'A' from dual connect by level <= 2;

2 rows created.

SQL> insert into t2 select rownum, 'A' from dual connect by level <= 2;

2 rows created.

SQL> commit;

Commit complete.

SQL> select * from mv1;

        C1 C2                           C3 C4                   RT1                RT2
---------- -------------------- ---------- -------------------- ------------------ ------------------
         1 A                             1 A                    AAAOOLAAEAAAACPAAA AAAOONAAEAAAACvAAA
         1 A                             2 A                    AAAOOLAAEAAAACPAAA AAAOONAAEAAAACvAAB
         2 A                             1 A                    AAAOOLAAEAAAACPAAB AAAOONAAEAAAACvAAA
         2 A                             2 A                    AAAOOLAAEAAAACPAAB AAAOONAAEAAAACvAAB

SQL> select last_refresh_type from dba_mviews where mview_name = 'MV1';

LAST_REF
--------
FAST


How Long Would Take to Upgrade an 1T Oracle DB from 10g to 11g? How long would take to upgrade an Oracle DB which has 1T data from 10g to 11g usually/roughly? I need to estimate the down time for it since it is a prod db. Thanks much!

30-90 minutes according to Oracle's Best Practices for Upgrading.   This is about the closest estimate you will get given all the unknowns in this situation.

The size of the database really matters very little in determining how long the upgrade will take.  Here are the main factors effecting the duration (from the Oracle.com upgrade blog):

Number of installed database components and options - the more components/option gotten installed, the more upgrade scripts will have to be run, the longer it'll take
Valid and non-stale dictionary statistics - even though creating dictionary statistics in some older releases of Oracle wasn't a brilliant idea since desupport of the rule based optimizer the data dictionary should be analyzed. Especially right before an upgrade. Otherwise this will happen during the upgrade while the database is started in a restricted upgrade mode causing additional downtime.
Number of rows in AUD$ if audit_trail is set to DB
Number of synonyms when upgrading from Oracle 9i - synonyms will be touched and get a new dependency in the dictionary in DEPENDENCY$ - if there's a high number (such as 100,000) this can eat up some time
Number of objects in XDB
At a very low rate if COMPATIBLE will be increased: Number of datafiles and the size of the redologs

Here are some additional factors you may want to consider that aren't related to the core of the upgrade itself:

Whether the pre-upgrade script has been run and issues resolved.
How many invalid objects there are.
If the upgrade is being done in place (vs. Import/Export, Streams, Data Guard, etc.)
Whether the DBUA or scripts are used to do the upgrade.
If new Oracle home pre-installed.
Disk speed and throughput.
Other CPU/Disk activity occurring at the same time.
Archive Log Mode.
Other changes being made with the upgrade.
If a cold backup is necessary before and/or after the upgrade.
Any patch sets or one off patches that will also be applied.
How much verification of the upgrade needs to be done before it can be made available.

Probably the biggest factor effecting the upgrade is the unknown factor.  Even when the upgrade is practiced in advance on similar hardware with similar data sets, etc. things can still occur that were unanticipated and can drastically effect the duration.  With that in mind you should mimic the production environment as closely as you can for the test upgrades.  That is, as close as your budget will allow.

If space is the issue preventing you from testing the upgrade, consider restoring the database to a test box excluding some of the larger user tablespaces.  This won't give you an exact feel for the time, but it should give you a closer ballpark and allow you to work through more of the unknowns.


Query takes too much time (2 stages. each stage fast, together - nightmare) MS-SQL 2008 R2 (Dell, 2 CPU)

This query takes two minutes to run, which is too slow.

SELECT *
FROM t_events WITH (NOLOCK)
WHERE order_no = 2783190
and event_no <= ( SELECT MIN(event_no)
                  FROM t_events WITH (NOLOCK)
                  WHERE order_no = 2783190 and complaint_id = 80898
                )

However, If I run:

SELECT MIN(event_no) FROM t_events WITH (NOLOCK)
WHERE order_no = 2783190 and complaint_id = 80898

it returns the result, 18880040, in less than a second.

If I run:

SELECT * FROM t_events WITH (NOLOCK)
WHERE order_no = 2783190
and event_no <= 18880040

it returns about 17 lines in 1 sec.

However, together the query takes 2 minutes to return the same 17 lines.

Why is this happening?

You could insert the rows from the subquery into a temporary table, then JOIN against that table.  Something like:

SELECT min_event_no = MIN(event_no)
INTO #events
FROM t_events WITH (NOLOCK)
WHERE order_no = 2783190
    and complaint_id = 80898;

SELECT *
FROM t_events WITH (NOLOCK)
INNER JOIN #events e ON t_events.event_no = e.min_event_no
WHERE order_no = 2783190

This makes SQL Server perform the two operations independently, which may make this query faster.  Potentially you could add appropriate indexes, but without the table definition in your question it's impossible to say.

Be aware of potential issues arising from the use of WITH (NOLOCK)


How to uninstall SQL server 2012 not showing in control panel's "Programs and features" I am frustrated to the core. While fixing my Sql Server I've  made the situation worse.
Here is the problem: initially my master database was corrupted, so the MSSql service was not starting.

I tried to fix it following some instructions from the MSDN site, but I just could not, so I decided to uninstall Sql Server through Control Panel.
Uninstalled every bit of sql from Control Panel, but the Sql Server still exists in my system.

Then I read somewhere to delete the Sql Server registry keys, I did the same. And now I cannot uninstall or even reinstall Sql Server on my machine.

I don't want to reinstall my Windows, can anyone help in here with a sure shot method of how to uninstall Sql Server and then reinstall it. Note that I have tried using the installation media also, but just couldn't install.

My guess is since you have remamants of failed installation still present in your system that is why you cannot install the new instance. This can be fixed by deleting few registry keys related to SQL server. Follow below method , this method was given by Microsoft forum team and is recommended way to remove have helped me and forum users many time . If you have multiple instances below suggesstion  would not work so dont use it.

First please refer to below Microsoft support link for what actions to follow before removing SQL Server instance. Its is for 2008 r2 but would be applicable to 2012 as well.

http://support.microsoft.com/kb/955499/en-gb

If still you face problem you might as well try below suggesstion which involves removing registry entries.

Note: Removing necessary registry entries might cause inconsistency so backup registry before doing changes.

•1. Uninstall the existing SQL Server  and all the components from the add remove program.

•2. Backup the registry  . Please read in details the MS link before proceeding.

•3. Delete the following keys using regedit:

◦--HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server

◦--HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer

•4. Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall and delete all the sub-keys referencing SQL Server.

•5. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services and delete all the keys referencing SQL Server.

•6. Rename all the SQL Server folders in the computer.

•7. Reboot the machine.

If you dont find this useful or have confusion dont proceed. Open a case with Microsoft and allow them to have a look at your system. with my experience with many failed installation registry keys are left in corrupted state and only solution would be to remove them.But you need expert to fiddle with registry

PS: Above method was suggested in SQL server forum and works well.

Of course if you can please revert it would be helpful


What are the advantages of Always Encrypted 2016 vs Encrypted Columns? I was reading about SQL Encryption methods, I have a good understanding of TDE, then I read about Encrypted Columns and then I read about Always Encrypted. The description of Always Encrypted is very lengthy and detailed, but the text with Encrypted Columns is less in depth but seems to me like it does the same thing. I want to understand the difference between the 2 and if there is any advantage with the new version?

The major difference I know is

Normal Column Encryption

The normal Column encryption which was introduced from SQL Server 2005 uses function Encryptbycert to encrypt the columns. It is not as secured as compared to Always Encrypted in terms that with this method data is submitted as clear text in SQL Server and this data can be seen from traces. A DBA, who has admin access to SQL Server, can see the data so this is actually not fully secured.

Always Encrypted

With always encrypted the encryption is done at clients app by API, like  ADO.net,ODBC. Drivers are installed at clients end to do this encryption. This will not allow SQL Server to see text data hence not revealing it to DBA's and one who have admin access on SQL Server. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine.

Quoting from Docs.microsoft

  Always Encrypted makes encryption transparent to applications. An
  Always Encrypted-enabled driver installed on the client computer
  achieves this by automatically encrypting and decrypting sensitive
  data in the client application. The driver encrypts the data in
  sensitive columns before passing the data to the Database Engine, and
  automatically rewrites queries so that the semantics to the
  application are preserved. Similarly, the driver transparently
  decrypts data, stored in encrypted database columns, contained in
  query results.

I hope I am able to give you some insight into both technologies.