Oracle DB occasionally slows down when "Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN" After midnight Oracle Database 12c turned to "slow mode". DML statements became about 10 times slower, though "selects" feel good. CPU load as usual, disk I/O is perfect (tested by copying 500m file), there are lots of free RAM and free disk space.

Restart of Oracle didn't help. All of the user sessions were killed if there were any.

In about one hour "slow mode" went away. Everything became as fast as before.

I've seen similar behavior on 11g as well.

Logs show:

    Thu Aug 07 00:38:13 2014
    Setting Resource Manager plan SCHEDULER[0x420E]:DEFAULT_MAINTENANCE_PLAN via scheduler window
    Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

and when it's gone I see:

    Thu Aug 07 02:00:03 2014
    Closing scheduler window
    Closing Resource Manager plan via scheduler window
    Clearing Resource Manager plan via parameter

Nothing else that can be related to the above bug/feature I can see in the logs.

This trouble happens time after time.

How to avoid this?

Seems like there are some Database Resource Manager (DRM) constraints put on user sessions when the database switches to maintenance plan via Scheduler window each night (as evident by your log portion).

There are at least two predefined DRM plans when a database is created.

DEFAULT_PLAN
DEFAULT_MAINTENANCE_PLAN

(See the complete list.)

When a window activates, it can switch the current resource plan to one specified in the window. You can query ALL_SCHEDULER_WINDOWS view to see which plan each window is assigned.

You can also switch between plans in Enterprise Manager or manually changing RESOURCE_MANAGER_PLAN initialization parameter value.

You can use Enterprise Manager or DBA_RSRC_* views to see

which plans are active in database – DBA_RSRC_PLANS
which resource consumer groups are defined – DBA_RSRC_CONSUMER_GROUPS
which group mappings are defined – DBA_RSRC_GROUP_MAPPINGS
which resource plan directives are defined – DBA_RSRC_PLAN_DIRECTIVES
and so on

Resource plan directives is what defines resource usage by the database sessions. The sessions map to consumer groups via group mappings.

When your database switches to another plan (using window or manually), the different set of directives is enabled which controls the resource usage by sessions. Thus when your database switches from DEFAULT_PLAN to DEFAULT_MAINTENANCE_PLAN and vice versa, your user sessions notice the difference – their performance dynamically changes according to the resource directives defined in the enabled plan.

For more details on implementing and using resource plans in DRM, consult the Managing Resources with Oracle Database Resource Manager chapter in Database Administrator's Guide.

UPDATE

You can check which directives are defined for plans in your database.

select plan, group_or_subplan, mgmt_p1, mgmt_p2, mgmt_p3
from DBA_RSRC_PLAN_DIRECTIVES
where plan like ('DEFAULT_%')
order by plan, mgmt_p1 desc, mgmt_p2 desc, mgmt_p3 desc;

PLAN                        GROUP_OR_SUBPLAN    MGMT_P1 MGMT_P2 MGMT_P3
------------------------    ----------------    ------- ------- -------
DEFAULT_MAINTENANCE_PLAN    SYS_GROUP                75       0       0
DEFAULT_MAINTENANCE_PLAN    OTHER_GROUPS              0      70       0
DEFAULT_MAINTENANCE_PLAN    ORA$AUTOTASK_SUB_PLAN     0      25       0
DEFAULT_MAINTENANCE_PLAN    ORA$DIAGNOSTICS           0       5       0
DEFAULT_PLAN                SYS_GROUP                75       0       0
DEFAULT_PLAN                OTHER_GROUPS              0      90       0
DEFAULT_PLAN                ORA$AUTOTASK_SUB_PLAN     0       5       0
DEFAULT_PLAN                ORA$DIAGNOSTICS           0       5       0

Notice how resources are allocated between different consumer groups. Here OTHER_GROUPS and ORA$AUTOTASK_SUB_PLAN are allowed to use 70% and 25% of CPU respectively in DEFAULT_MAINTENANCE_PLAN plan, while in DEFAULT_PLAN they are allowed 90% and 5% respectively.

It means that these directives might be one of the reasons your users' sessions run slower when maintenance windows are open, because users are allocated less resources than what they are during work hours.


Oracle union default sort is weird As you know union removes duplicated rows. I thought it removes from the latter ones, but it doesn't.

I found out that if there's no order by clause, Oracle sorts the merged dataset by the first column and removes the duplicates.

select 4,5,6 from dual
union
select 1,2,3 from dual
union
select 4,5,6 from dual

It results :

1   2   3
4   5   6

Whereas I expect :

4   5   6
1   2   3

I failed many times with this, and make the problem simple like the example above.

How do I keep the union sequence without order by?

In my real case, the first select is full match(=) result and the second is partial match(like) result.

If I search for roma for example.

Full match result : roma, partial match result : aroma.

And of course full match result should come first. As I said, it seems order by the first column of union result.

But, as you see:

select 'roma' from dual -- result comes out anyway..
union select 'aroma' from dual;

Result:

aroma
roma

aroma is alphabetically ahead of roma, it comes first.

Maybe I should try union all and remove the duplicates?

The simple answer is....

To enforce an order in ANY resultset, not just your specific case, you have to use an order by clause.

The Oracle documentation spells it out clearly:

  Use the ORDER BY clause to order rows returned by the statement.
  Without an order_by_clause, no guarantee exists that the same query
  executed more than once will retrieve rows in the same order.


SQL Replication Automatically Add table to Publisher I am using SQL 2012 transactional replication for an entire database. Everytime I add a new table, say Table A, I have to manually go to the SSMS GUI, and add the table to be publisher, then I resync to import data.

Is there a way to automatically add tables with data into replication, or do I have to write a dynamic/t-sql/powershell script, which checks if sys.tables is replicated and then automatically adds to replication sp_addarticle ?

There is no inbuilt way of automatically adding new table to replication. This means, you have to carve out your own method suited to your environment.

Couple of thing that you should do ..

Poll sys.tables or have a trigger to fire on CREATE_TABLE
Check if table has primary key (must for replication)
modify publication property - immediate_sync to 0 --> should be done once to avoid generating snapshot for all articles.
Call sp_addarticle and sp_addsubscription to add article to publication and subscription.
Kick off snapshot .. this will generate ONLY shapshot for the article that is newly added.


how to update new parent node in the middle of the postgresql ltree by some simple update SQL statement? I follow this URL to make some SOP for my colleague.
How to update the parent/child of all rows in the tree (ltree)?

However, my colleague hope to use some simple statement about "UPDATE" for adding or deleting some parent nodes without complex Regular Expression statements.

Is there any better statement by using UPDATE? Thanks for advance.

I just try another stupid solution:

update product_sections
  set section_path = text2ltree('Automation_Solutionz.Test_Case_Execution.treetype') ||
                     subltree(section_path,1,nlevel(section_path)-1)
where section_path ~ 'Automation_Solutionz.Test_Case_Execution.*'::lquery


Clustering MySQL with InnoDB engine I have a MySQL single server setup with all tables using the InnoDB engine (because I need support for foreign keys). As response times are getting longer, I now believe I need to create a cluster of servers to be able to cope with an ever-growing database load.

Is it possible to create a cluster of MySQL servers using my existing databases which use InnoDB? If not, what are my options?

Moving to Galera would be an option. Then your application has to be aware of 'deadlock detected try restarting transaction' errors.
Another possibility is to set up a read slave and configure your application to use this slave for read_only queries. If one slave isn't enough you can add more and use them with a loadbalancer of any kind. Examples are maxScale, ha-proxy on application layer or ipvs on transport layer.


Can we use alias name in LIKE statement? I have a query to search strings from database tables. The query is

SELECT  id
      , (SELECT office_name
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS ofname
      , (SELECT City_Name
         FROM   City_Master
         WHERE  City_Id = wan_reports.location
        ) AS lname
      , wan_ip
      , (SELECT office_class
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS class
      , last_online
      , last_check
      , attempts
FROM    wan_reports
WHERE   1 = 1
        AND (id LIKE 'B%'
             OR ofname LIKE 'B%'
             OR lname LIKE 'B%'
            );

Now here ofname in LIKE statement says unknown column. Is there any way to use alias in LIKE statement?

There are at least 3 different ways to do that. I will show all of them and add a link to sqlfiddle for each, but I strongly suggest you to adopt the last one.

You can wrap the query in another query and do the filtering "one level higher":

select * from (
  SELECT  id
      , (SELECT office_name
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS ofname
      , (SELECT City_Name
         FROM   City_Master
         WHERE  City_Id = wan_reports.location
        ) AS lname
      , wan_ip
      , (SELECT office_class
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS class
      , last_online
      , last_check
      , attempts
  FROM    wan_reports
) tmp
WHERE   1 = 1
        AND (id LIKE 'N%'
             OR ofname LIKE 'N%'
             OR lname LIKE 'N%'
            );

http://sqlfiddle.com/#!9/8c2a3/2

You can use HAVING (it is officially not supported as HAVING is supposed to work only on aggregated values, but it works really only as late WHERE in MySQL:

SELECT  id
      , (SELECT office_name
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS ofname
      , (SELECT City_Name
         FROM   City_Master
         WHERE  City_Id = wan_reports.location
        ) AS lname
      , wan_ip
      , (SELECT office_class
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS class
      , last_online
      , last_check
      , attempts
FROM    wan_reports
HAVING   1 = 1
        AND (id LIKE 'N%'
             OR ofname LIKE 'N%'
             OR lname LIKE 'N%'
            );

http://sqlfiddle.com/#!9/8c2a3/6

Usually the best is using joins instead of subqueries:

SELECT  id
      , office_name AS ofname
      , City_Name AS lname
      , wan_ip
      , office_class AS class
      , last_online
      , last_check
      , attempts
FROM    wan_reports
JOIN  office_master on (office_id = wan_reports.name)
JOIN  City_Master on (City_Id = wan_reports.location)
WHERE   1 = 1
        AND (id LIKE 'N%'
             OR office_name LIKE 'N%'
             OR City_Name LIKE 'N%'
            );

If there is a possibility that the matching office/city will not exist, then you need to change the corresponding inner join to a left join. That would return NULL in joined columns in case no match was found.

http://sqlfiddle.com/#!9/8c2a3/5

Subqueries have a history of poor performance in MySQL. It is becoming better witch each new version but in the optimizer can still handle joins much better. And as you can see the join version is actually shorter for writting. You only have to use "original" columns names in WHERE even if you assign them aliases for output.


Is it possible to remove useless GRANTS in MySQL I'm using pt-show-grants to create all my GRANTS on a new server but there's lots of old GRANTS that refer to tables that doesn't exist anymore so I get errors when I try to apply the SQL file on the new server.

Is there a way to remove GRANTS that refer to tables that doesn't exist anymore?

I don't see any option in pt-show-grants to do this kind of restriction. There are options for --only and --ignore specific users, but not tables. Nor is there an automatic option to ignore grants on nonexistant tables.

I also glanced in the code, and it doesn't seem to break out the tables to scan through them for any reason.

For what it's worth, I don't see any error when I try to grant to a non-existant table. I tested on Percona Server 5.6.16:

mysql> grant all on test.nonexist to 'user'@'%';
Query OK, 0 rows affected (0.00 sec)

$ pt-show-grants
. . .
GRANT ALL PRIVILEGES ON `test`.`nonexist` TO 'user'@'%';

Can you tell me anything else about your version of MySQL and the specific error that it produces when you try to run that grant script?

Re your comment:

The difference between ALL PRIVILEGES and specific privileges seems to be deliberate, according to Bug #10406 Grant all command does not give error though table does not exist.

If you use GRANT CREATE, SELECT ... ON test.notexist TO 'user'@'%' then this works. As long as you include CREATE privilege, then it permits the grant to the non-existant table.

Anyway, that's interesting trivia, but it doesn't address your original question.

This has been requested before, but never implemented. It was recorded as a "blueprint" (like a feature request) here: https://blueprints.launchpad.net/percona-toolkit/+spec/pt-show-grants-for-nonexistent-tables

Perhaps someone would like to implement a patch and contribute it to Percona Toolkit?


Join condition on complex views If i join two/more complex views (views that are made up by joining two/more tables); would it result in multiple iteration of reads on the base tables?

Would it not be more efficient just to have the join conditions on the base tables itself?

This is a problem i've analysed in our Production Oracle 10g R2 (ASM disk groups) environment, the sql queries are dynamically generated on the fly by the application itself and instead of using Tables to extract results, composite views are used.Moreover, conditional joins are frequently implemented on these composite views.

Can such joins on the complex views unnecessarily cause high cpu utilization and also eat up the precious buffer cache?

Never assume or "belief" the cause for something. Test it.

In your case run an EXPLAIN PLAN on the statements in question and check if the plan with views is more expensive than the corresponding SELECT using the base tables.

You can also use SQL*Plus' autotrace feature to check out logical IO done by both queries.

If that still doesn't give enough information you can turn on tracing and examine the output of the TKPROF formatting utility of the trace file.


Login mapping only works for the first query in a window I have a really odd login mapping error for one DB that I can't seem to figure out.

When running a simple select statement while impersonating a login (A) on DB(1), it appears to work fine. However it only works the first time the query is run on each query window.

After that I repeatedly get an error stating

  Access to the remote server is denied because no login-mapping exists.

Opening a new query window and running exactly the same statement while again impersonation login (A) works every time - but again only the first time it is run before the same mapping error returns.
This is also repeatable on the same DB(1) for any other logins on the instance that are not sysadmin.

Nothing is being changed on the mapping settings in-between running the queries. I've even gone through and checked no settings have dropped off. No other DBs on the instance experience this issue. The logins use Windows Authentication. The required 'User mapping' has been set at instance level with a tick against
the DB, and the login is designated as DB_Owner. This has been used to create the DB login.

The login only has the server role 'Public' to avoid them accessing other DBs.
SysAdmins have no issues on this DB. This DB(1) has previously experienced issues with login mappings randomly dropping off. Re-creating the logins has always solved any issues. Obviously this shouldn't be required but I've found no reason why this happens either. I've re-created this login several times but on this ocassion it is not solving the issue.

To me it's a pretty standard setup and seems to work fine everywhere else, but I can't see why this DB is saying no login-mapping exists, especially when it works the first time on each query window.

For security reasons I'd rather not give the account sysadmin rights just to resolve this. I've never seen anything like this before though and can't find any google results suggesting this is a known bug.

Anyone seen this before and got any ideas on the cause?

I'm Using SQL Server 2008.

Considering that you are using the following SQL:

SELECT * FROM view EXECUTE AS LOGIN = 'loginname'

This is actually 2 different statements:

SELECT * FROM view;
EXECUTE AS LOGIN = 'loginname';

The SELECT statement doesn't have a clause to impersonate, you have to impersonate before issuing the SELECT statement and the impersonation remains on the current session until you execute a REVERT. So the proper SQL should have been:

EXECUTE AS LOGIN = 'loginname';
SELECT * FROM view;
REVERT; -- (if you want to revert to your original login)

As you mentioned in a comment, the error message was being generated by the view that you were querying, as it was using a linked server that wasn't correctly configured with a login against the remote server in which the impersonated login had access. If the current login is sysadmin, the linked server won't require a remote login-mapping mechanic (this can be configured), so the query always succeeded the first time.


Relationship that are optionally more specific Forgive my title, I couldn't think of anything that accurately describes what I'm talking about.

I currently have the following relationship.

CREATE TABLE Events
(
    ID INT IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(64) NOT NULL,
    PRIMARY KEY(ID)
)

CREATE TABLE Locations
(
    ID INT IDENTITY(1,1) NOT NULL,
    EventID INT NOT NULL,
    Name NVARCHAR(64) NOT NULL,
    PRIMARY KEY(ID)
)

ALTER TABLE Locations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)

Basically, an event can have zero or more locations.  What I want now is to associate a donation with either an event, or be more specific and associate it with a location.

CREATE TABLE Donations
(
    ID INT IDENTITY(1,1) NOT NULL,
    EventID INT NOT NULL,
    LocationID INT NULL, -- this is optional
    PRIMARY KEY(ID)
)

ALTER TABLE Donations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)
ALTER TABLE Donations ADD FOREIGN KEY(LocationID) REFERENCES Locations(ID)

However, if the LocationID is specified, then that LocationID should be a Location that belongs to the specified EventID.  In cases where LocationID is specified, it makes EventID redundant, and therefore it is no longer normalized.

Is the schema I proposed considered good form, or should I make both EventID and LocationID nullable, then enforce a check constraint that either exclusively EventID or LocationID should be set?

Down the road, we will then have registration.  Which I am proposing will look similar to this:

CREATE TABLE Users
(
    ID INT IDENTITY(1,1) NOT NULL,
    Username NVARCHAR(64) NOT NULL,
    PRIMARY KEY(ID)
)

CREATE TABLE Registrations
(
    UserID INT NOT NULL,
    EventID INT NOT NULL,
    LocationID INT NULL,
    PRIMARY KEY(UserID, EventID)
)

ALTER TABLE Registrations ADD FOREIGN KEY(UserID) REFERENCES Users(ID)
ALTER TABLE Registrations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)
ALTER TABLE Registrations ADD FOREIGN KEY(LocationID) REFERENCES Locations(ID)

Essentially, a user can register to an event once (regardless of whether they picked an location or not). The primary key I have is problematic if I make EventID nullable in order to prevent data redundancy with EventID and LocationID.

The current solution we are using to this problem is we do not have a Locations table.

CREATE TABLE Events
(
    ID INT IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(64) NOT NULL,
    ParentEventID INT NULL, -- when null it's an event, otherwise it's a location for the eventid it references
    PRIMARY KEY(ID)
)

ALTER TABLE Events ADD FOREIGN KEY(EventID) REFERENCES Events(ID)

Which solves the EventID and LocationID data redundancy problem.  However I find this structure odd as events and locations are conceptually different things.  Down the road we may have fields that are specific to events and locations.

To determine whether records in this Events table are an event or location, if the PrimaryEventID field is NULL, then it is an event, otherwise it's a location for that EventID.

Please let me know a good schema for these business requirements.

Instead of:

ALTER TABLE Donations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)
ALTER TABLE Donations ADD FOREIGN KEY(LocationID) REFERENCES Locations(ID)

I would do this:

ALTER TABLE Donations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)
ALTER TABLE Donations ADD FOREIGN KEY(EventID, LocationID) REFERENCES Locations(EventID, ID)

That would guarantee that "if the LocationID is specified, then that LocationID should be a Location that belongs to the specified EventID".

That done, your approach makes sense.


Postgres - how to find contents of one field within another field How do I find where the contents of one field are within another field?

I have a database table with a auto-generated sequence number in one field, and another character based field which should contain a string which describes a path to a file. The path should be based on the sequence in the other field, sometimes it isn't and I want to identify these rows.

This is query I've been trying (among others) but it doesn't work, position() is obviously designed to find a hard-coded string within a field, not another field

SELECT recordId, path
FROM TABLEA
WHERE position(recordID IN path) = 0;

RecordId is defined as:

recordId integer not nulll default nextval('tablea_recordid_seq'::regclass)

And path is:

character varying(80)

I can't help feeling that the correct solution is probably very simple, but damned if I can find it!

Ok, this works ...

SELECT recordId, path
FROM TABLEA
WHERE position(CAST(recordID AS CHAR) IN path) = 0;

The path should be based on the sequence in the other field, sometimes it isn't and I want to identify these rows

So the absence of any actual position is what you are looking for, which is simpler:

SELECT recordId, path
FROM   table_a
WHERE  path !~ recordId::text;

Note the cast to text (or varchar, all the same).

Do NOT cast to char like you have it in your added (broken) solution. char is a synonym of character (or bpchar) and defaults to character(1) (!), so effectively only takes the first digit of your number and produces many false positives. Related:

Best way to check for “empty or null value”

!~ is the operator for negated regular expressions

NOT LIKE would do the job, too:

...
WHERE path NOT LIKE ('%' || recordId::text || '%');


How to use trigger to perform addition from one table columns into another table column where id matches Sqlite Below is my SQLite Database with two tables.
import sqlite3

conn=sqlite3.connect('calc.db')
conn.execute("""CREATE TABLE IF NOT EXISTS sum
        (id TEXT UNIQUE,
    name    TEXT,
    total   TEXT
    )""")

conn.execute("""CREATE TABLE IF NOT EXISTS newtable
        (id TEXT,
    name    TEXT,
    num TEXT
    )""")

conn.execute("""INSERT INTO sum(id, name, total) \
    VALUES('001', 'name1', '')""")
conn.execute("""INSERT INTO sum(id, name, total) \
    VALUES('002', 'name2', '')""")
#
conn.execute("""INSERT INTO newtable(id, name, num) \
    VALUES('001', 'name1', '1000')""")
conn.execute("""INSERT INTO newtable(id, name, num) \
    VALUES('002', 'name2', '2000')""")
conn.execute("""INSERT INTO newtable(id, name, num) \
    VALUES('001', 'name1', '4000')""")

conn.commit()
conn.close()

Sum table has unique id and newtable id is not unique. I want to perform addition in sum table in total column that should be taken the addition value from newtable column when inserting and if id matches. Hoe to do. My columns are text type. If not posible in TEXT type i can change it into integer but it is better to continue in TEXT type if possible.  How to do.

You can add a trigger to your python script

CREATE TABLE IF NOT EXISTS sum
        (id TEXT UNIQUE,
    name    TEXT,
    total   TEXT
    )

CREATE TABLE IF NOT EXISTS newtable
        (id TEXT,
    name    TEXT,
    num TEXT
    )

INSERT INTO sum(id, name, total)
    VALUES('001', 'name1', '');

INSERT INTO sum(id, name, total)
    VALUES('002', 'name2', '');

CREATE TRIGGER sum_after_insert
   AFTER INSERT ON newtable
BEGIN
  UPDATE sum SET total = total + NEW.num WHERE id = NEW.id;
END;

INSERT INTO newtable(id, name, num)
    VALUES('001', 'name1', '1000');

INSERT INTO newtable(id, name, num)
    VALUES('002', 'name2', '2000');

INSERT INTO newtable(id, name, num)
    VALUES('001', 'name1', '4000');

SELECT * FROM sum;

id  | name  | total
:-- | :---- | :----
001 | name1 | 5000
002 | name2 | 2000

db<>fiddle here


For each field name in a list of fields, get the unique values The Question:

I have a view that is a list of fields in tables:

FIELD_LIST_VW
+-------------+------------+
| TABLE_NAME  | FIELD_NAME |
+-------------+------------+
| ENG.TABLE_1 | FIELD_1    |
| ENG.TABLE_1 | FIELD_2    |
| ENG.TABLE_2 | FIELD_A    |
+-------------+------------+

The underlying tables look like this:

TABLE_1
+---------+---------+
| FIELD_1 | FIELD_2 |
+---------+---------+
| A       | X       |
| A       | Y       |
| B       | Y       |
| B       | Y       |
| B       |         |
| C       |         |
+---------+---------+

TABLE_2
+---------+
| FIELD_A |
+---------+
|       1 |
|       1 |
|       1 |
|       2 |
|       3 |
+---------+

TABLE_3, TABLE_4, etc...

I want to expand the list of fields from FIELD_LIST_VW, so that there is a row for each unique value, per field:

+-------------+------------+--------------+
| TABLE_NAME  | FIELD_NAME | UNIQUE_VALUE |
+-------------+------------+--------------+
| ENG.TABLE_1 | FIELD_1    | A            |
| ENG.TABLE_1 | FIELD_1    | B            |
| ENG.TABLE_1 | FIELD_1    | C            |
+-------------+------------+--------------+
| ENG.TABLE_1 | FIELD_2    | X            |
| ENG.TABLE_1 | FIELD_2    | Y            |
+-------------+------------+--------------+
| ENG.TABLE_2 | FIELD_A    | 1            |
| ENG.TABLE_2 | FIELD_A    | 2            |
| ENG.TABLE_2 | FIELD_A    | 3            |
+-------------+------------+--------------+

What I've tried:

I figure a good place to start would be a cross join sub-select:

SELECT
    a.table_name
    ,a.field_name
    ,b.unique_value
FROM
    eng.field_list_vw a
CROSS JOIN (
    SELECT
        'FIELD_1' AS field_name    --<-- Hardcoded
         ,field_1 AS unique_value  --<-- Hardcoded
    FROM
        eng.table_1                --<-- Hardcoded
    GROUP BY
        field_1                    --<-- Hardcoded
    ) b
WHERE a.field_name = b.field_name

But of course, the cross join sub-select is full of hardcoded table & field names. The query joins TABLE_1, FIELD_1, but not any of the other fields in FIELD_LIST_VW. This is not what I want:

+-------------+------------+--------------+
| TABLE_NAME  | FIELD_NAME | UNIQUE_VALUE |
+-------------+------------+--------------+
| ENG.TABLE_1 | FIELD_1    | A            |
| ENG.TABLE_1 | FIELD_1    | B            |
| ENG.TABLE_1 | FIELD_1    | C            |
+-------------+------------+--------------+
(rows are missing)

Instead of hardcoding, is there a way to dynamically cross join to the unique values of each field in FIELD_LIST_VW?

I do not have CREATE TYPE privileges.

One approach for solving this problem is to use dynamic sql. In fact, your question is one of the example use cases:

For example, a SELECT statement that includes an identifier that is unknown at compile time (such as a table name) or a WHERE clause in which the number of subclauses is unknown at compile time.

At compile time, you don't know the table names, column names, or the number of subclauses for your query. As far as I know, you cannot write this as a simple SQL query. You'll need to have some intermediate object create the query text for you.
First let's write out a full query that does what you need for your three example rows. I propose the following:
SELECT 'ENG.TABLE_1', 'FIELD_1', FIELD_1 FROM ENG.TABLE_1
UNION
SELECT 'ENG.TABLE_1', 'FIELD_2', FIELD_2 FROM ENG.TABLE_1
UNION
SELECT 'ENG.TABLE_2', 'FIELD_A', FIELD_A FROM ENG.TABLE_2

The UNION operator will only return the distinct result set from the query so this should return the correct results. One way to generate the above query using a query against the view is to take advantage of the LISTAGG operator.

For a specified measure, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column.
As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.

For the measure expression I propose building a string that adds quotes around the column names as needed. Because this is a string it gets a bit messy:
'SELECT ''' || CAST(FIELD_LIST_VW.TABLE_NAME AS VARCHAR2(30))
    || ''', ''' || CAST(FIELD_LIST_VW.FIELD_NAME AS VARCHAR2(30))
    || ''', ' || CAST(FIELD_LIST_VW.FIELD_NAME AS VARCHAR2(30))
    || ' FROM ' || CAST(FIELD_LIST_VW.TABLE_NAME AS VARCHAR2(30))

For the delimiter I propose the following:
' UNION '

Here is what the query looks like when you put it all together and use the view:
SELECT LISTAGG(
    'SELECT ''' || CAST(FIELD_LIST_VW.TABLE_NAME AS VARCHAR2(30))
    || ''', ''' || CAST(FIELD_LIST_VW.FIELD_NAME AS VARCHAR2(30))
    || ''', ' || CAST(FIELD_LIST_VW.FIELD_NAME AS VARCHAR2(30))
    || ' FROM ' || CAST(FIELD_LIST_VW.TABLE_NAME AS VARCHAR2(30))
    , ' UNION ')
WITHIN GROUP (ORDER BY NULL) into varStringToExecute
FROM FIELD_LIST_VW;

What the above query does is generates the select query that you need for each table and column from the view as a string, adds UNION between all of the queries, and concatenates the entire string together. If you need the query to build in a specific order you can use the ORDER BY clause in LISTAGG.
You can execute the query using dynamic SQL:
EXECUTE IMMEDIATE varStringToExecute;
You didn't say what you need to do with this data. If you need to just return it to the client you may want to use a cursor. Here is one set of ideas on how to accomplish that.
Note the return value of LISTAGG is VARCHAR2, which means that your query text is limited to 4000 characters with default settings. It is possible to increase that limit up to 32767 by setting the MAX_STRING_SIZE parameter, but I don't know if that's appropriate for your database. If you can't do that, there are various workarounds for creating a string over 4k characters.
Apologies if some of the code in this answer doesn't not compile. I don't have access to Oracle at the moment.


Outer join on a view with calculated columns performs calculation for missing ("outer") records I have a view in MySQL, which has some calculated fields — COALESCE and the like. If I do a left outer join on that view (so records from that view may be missing), then the calculated fields are still calculated for the missing records, instead of showing up as NULL.

Example

I've tested the following example on MySQL versions 5.5.40-36.1-log and 5.5.53-38.5 (because that's what I've got on hand) as well as on SQL Fiddle‡ versions 5.5 and 5.6.

Setup

-- A simple view
CREATE OR REPLACE VIEW foo
AS
      SELECT 1 AS x
UNION SELECT 2
UNION SELECT 3;

-- Another simple view
CREATE OR REPLACE VIEW bar
AS
      SELECT 1 AS y
UNION SELECT 2
UNION SELECT 3;

-- A (contrived) view with a calculated column
CREATE OR REPLACE VIEW baz
AS
SELECT
   f.x,
   b.y,
   SIGN(COALESCE(b.y, 0)) AS z
FROM foo f
LEFT JOIN bar b ON f.x = 2 * b.y;

Query

-- A query that does a left outer join
-- on the view with the calculated column
SELECT *
FROM foo f
LEFT JOIN baz b ON f.x = b.y;

Expected result

I would expect the following result, where we see one row with records from baz and two rows without.

| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | - |
| 3 | - | - | - |

Actual result

But instead I get the following, with indeed one row with records from baz, but two rows where there are no records from baz except for the calculated column, which is calculated for all three rows!

| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | 0 |
| 3 | - | - | 0 |

So z is calculated, even where baz returns no records (as evidenced by the NULLs for x and y).

My actual case involves a join between the view and a table the view is based on. I've replicated this in the example, using views only. I've also tested it without the "self-join"; this makes no difference.

Workaround

What does work for me, is hiding baz in a sub-select, like so.

SELECT *
FROM foo f
LEFT JOIN (
   SELECT *
   FROM baz
) b ON f.x = b.y;

This yields the expected result:

| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | - |
| 3 | - | - | - |

Is this expected behaviour? Why?

‡: worked for me yesterday, can't get it to work today.

If the two queries:

SELECT *
FROM foo f
  LEFT JOIN baz b
  ON f.x = b.y;

and

SELECT *
FROM foo f
  LEFT JOIN (SELECT * FROM baz) b
  ON f.x = b.y;

return different results, then it's a bug and the table and view definitions are irrelevant. That should never happen.

Before you go and post a bug report, check your view and queries in the version (5.5.54). Your 5.5.40 is 14 minor versions and about 2.5 years older than the latest 5.5 version.

If the bug still persists, then file a bug report to MySQL with this example. A similar bug has already been reported: Return inconsistent result when using inline SELECT vs VIEW for left join, so if you report I suggest you add your examples there.

MySQL developers have comfirmed that one and suggested a few workarounds (until it is fixed). You can try the suggestion to create the views with ALGORITHM = TEMPTABLE:

  There are a few workarounds:

  In all version, the query using the view will give correct result if view is created with "CREATE ALGORITHM=TEMPTABLE VIEW ..."

  For 5.7/8.0, both queries will give the correct result by setting optimizer_switch='derived_merge=off'

  In 8.0 the NO_MERGE hint can be used to get correct behavior in both cases:
  SELECT /*+ NO_MERGE(t2) */ ...

Having said that, the query is more complex than needed. You could have used something simpler (although I don't like having a result with columns identically named):

CREATE VIEW baz2
AS
SELECT
   f2.x AS x
   f1.x AS x,
   CASE WHEN f1.x IS NOT NULL THEN f2.x END AS y,
   SIGN(f1.x) AS z
FROM foo f2                   -- changed:   f2 LEFT JOIN f1
  LEFT JOIN bar f1
  ON f1.x = 2 * f2.x;

and

SELECT * FROM baz2 ;


Adding a db for replication into mysql Currently I have these settings. I am adding a new db called mydb3. How to enable the replication for this new db? Currently I have copied the table schema into both master and slave. What other changes must I do ? Can I just add binlog_do_db=mydb3 and replicate-do-db=mydb3 on both master and slave?

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

innodb_file_per_table
innodb_lock_wait_timeout=120
innodb_buffer_pool=5G
innodb_log_file_size=512M

server-id=1
log-bin=mysql-bin
log-error=mysql-bin.err
binlog_do_db=mydb1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

For the slave here is the settings.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

innodb_file_per_table
server-id=1339951115
master-host = ***
master-user = ***
master-password =***
master-port = 3306
replicate-do-db=mydb1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Short answer to what I think you are asking... Yes, you can have multiple binlog_do_db statements.

Long answer...

The binlog and replicate commands can be a bit confusing.  Roughly this is what happens:

If the Master has one or more binlog_do_... statements, they limit what is written to the binlog.  See Bug below.
If the Master has one or more binlog_ignore_... statements, they list what is not written to the binlog; everything else is.
If you have a mixture of the two, I don't know what happens.

For Replication, the binlog is copied to the Slave.  (The binlog could be just sitting there for a form of backup -- No slave involved.  In particular, a Slave can have its own binlog.)

On a Slave, replicate_do_... allows only those things in; replicate_ignore_ tosses the listed things.  A mixture is a mystery.

What you listed (binlog_do_db=mydb3 and replicate-do-db=mydb3) is redundant in that the replicate_do does nothing that the binlog_do didn't already do.  (Namely replicate only mydb3.)  It is more efficient to filter on the Master than on the Slave.  (There are multiple use cases where this is not practical.)  That is, you should have several binlog_do on the master, and nothing on the Slave.

A server that is not being used as a Slave has no use for replicate_*.  (So you should not add it to the Master.)

I am trying to phrase everything carefully because of various topologies that you might not be interested in.  For example...

A "dual-Master" topology has two Masters replicating from each other.  That is, each is both the Master to the other, and the Slave to the other.  I that case each is both a Master and a Slave.  But I digress.

Bug

If the USE command in effect matches any of the binlog_do*, the command will be written.  Note:  USE x; INSERT INTO y.z ... depends on x, not y!  This "bug" (aka 'feature') has tripped up many a DBA.

Side note:

For security, do not include the master-password in my.cnf; do it only when you execute CHANGE MASTER to establish the Master-Slave connection.  (Future versions may disallow it anyway.)


Has some command that lists the privileges of a user in db2? I'm using db2 9.7 on linux and i want to list my privileges on database...
Ex: SYSADM, SECADM, DBADM, etc.
So, has some command in db2 that lists the privileges of a user in db2?

You can use the system catalog views.

Generally speaking any view SYSCAT.xxxxxAUTH will show the authorizations for that particular area.

Examples include SYSCAT.DBAUTH is authorizations at the database level. SYSCAT.ROLEAUTH is authorizations to roles. SYSCAT.INDEXAUTH is authorizations for indexes, etc.


Which is faster in process time? I have a script that uses aggregate functions many times, and that might cause the slowdown of my script. And I thought to simplify my script and do all the summation/addition/concatenation on the SSRS side. is it okay? What do you prefer? TIA. Happy coding! :)

I don't think there's a general best option in this matter. It depends on many things;

For instance, if you are dealing with high activity on the database, or even have memory issues (not you, but the database jk) you might want to grab the data and leave asap, that means, move most of the computations on SSRS;

Leave the simple aggregations (like totals, avgs etc) to the SSRS, because it is already optimised to do such tasks on Tablix. If you have more complex aggregations (eg. partitions) let the SQL server do them. If the database and SSRS are on different machines, watch the activity (load), and based on that do the judgement.

These are all performance issues, but there is also flexibility to be taken into account. Try to be proactive, and find the elements prone to modifications, and see where it would be easier to apply them (on the SQL, or the report).

The idea is that it really depends on each individual report that you are trying to develop. My advise would be to do the computations on SQL, because it is easier.

Lastly, as a rule of thumb, do not try to optimise something that is not having any performance issues. Most of the times the improvement rate it just not worth the time invested. When your server starts to look tired, check the most expensive queries, and if this one is in top, then you might consider moving some of the computations on SSRS.

Hope my advice is useful to you.
Cheers!


What is the alternate to SA account in SQL Server? My client is currently using SQL Server for their app. At this time they are using SA account everywhere. For security reasons we need to disable the SA account. The solution I've is create another power user (not admin), like standard user and root user. The development only needs access for CRUD operations. So, what should be the rights/roles/privileges for this new account? We need to replicate the same model to the internet server where only read rights are required (from viewer's end). How do I go about it?

Two different versions, 2008 R2 and 2012.

On your development server, you can grant the standard users the db_datawriter and db_datareader roles. You probably want to grant the db_owner role to the power user. As these are database level roles, you have to repeat this for each of your databases. Regarding the web server, you should create a limited user that only has been granted the db_datareader role.

As the sa login is hardcoded to be a member of the sysadmin server, you can't limit the permission of this account. The best way to migrate is to replace all the connection string you can find and then finally disable the sa account.


postgres create limited user that can select tables and columns of those tables I have an app that automates the making of a PostgreSQL database, and also automates the creation of a database user. This database user is given limited access for security purposes, as it will be handed off to a client to use.
The version I am using is Postgres 9.6

CREATE USER %USERSETUP% WITH
    LOGIN PASSWORD '%USERSETUPPASS%'
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1;

ALTER DEFAULT PRIVILEGES IN SCHEMA %SCHEMA% GRANT INSERT, SELECT, UPDATE, DELETE, REFERENCES ON TABLES TO %USERSETUP%;

Now, my test also goes into the created database, and verifies that it was created correctly, using Query Scripts to grab the Tables names, and then grab the column names inside each table (As a bonus, also grab the column data type)

Because of these permissions, user cannot use information_schema.
Also, I am only requesting query scripts with SYSTEM CATALOG please

The script I have for the table names works:

SELECT tablename FROM pg_catalog.pg_tables where schemaname = '{schema}'

And this returns the table names as a list:

  business, location, person, customer, etc...

However, when I run the following two scripts to get column name, I get a return of 0, as if this user was trying to Query information_schema. I'm guessing it's because of the privileges:

SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ 'schemaaryzdhyqoi'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

also

SELECT c.oid,
  n.nspname,
  c.relname, t.*
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL (
  SELECT a.attname,
    pg_catalog.format_type(a.atttypid, a.atttypmod),
    (
      SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
      FROM pg_catalog.pg_attrdef d
      WHERE d.adrelid = a.attrelid
        AND d.adnum = a.attnum
        AND a.atthasdef
    ),
    a.attnotnull, a.attnum,
    (
      SELECT c.collname
      FROM
        pg_catalog.pg_collation c,
        pg_catalog.pg_type t
      WHERE c.oid = a.attcollation
        AND t.oid = a.atttypid
        AND a.attcollation <> t.typcollation
    ) AS attcollation
  FROM pg_catalog.pg_attribute a
  WHERE a.attrelid = c.oid
    AND a.attnum > 0
    AND NOT a.attisdropped
) AS t
WHERE n.nspname ~ '^(schemaaryzdhyqoi)$'  -- YOUR SCHEMA HERE
AND pg_catalog.pg_table_is_visible(c.oid);

Question is: How do I add the permission for the limited user to also be able to access just the name (and maybe datatype) of the columns inside each table in the schema?

You can set column level privileges to very specifically (and narrowly) allow otherwise unprivileged users to access data.  In your case, it is two catalogs: pg_attribute and pg_type.

I have a user called alice who is similarly restricted as your user:

alice@test=> > SELECT * FROM pg_class LIMIT 1;
ERROR:  permission denied for relation pg_class

Issue the following GRANT statements:

GRANT SELECT (oid, typname) ON pg_type TO alice;
GRANT SELECT (attrelid, attname, atttypid, attnum) ON TABLE pg_attribute TO alice;

After these, alice can do the following, for example:

SELECT attname, typname
  FROM pg_attribute JOIN pg_type t ON t.oid = atttypid
 WHERE attrelid = 't2'::regclass AND attnum > 0;

 attname │ typname
─────────┼─────────
 key     │ jsonb
 value   │ jsonb

You can trivially extend the privileges to match your needs.


Grant Admin to an Active Directory account in SQL Server I have a SQL Server 2012 instance, and an Active Directory group, which I can add Active Directory users to. I have my personal AD account (MyAccount) and a service account (ServiceAccount) in that group.

I need to grant admin access to this AD group, for the whole instance. This means that I won't need to execute grant commands to every new table and database created. Any user on that group will automatically be able to create, drop, insert, select, etc.

Even trickier. I must be able to use Windows Authentication to login with MyAccount (I login on Windows and then login to SQL Server without needing to type username-password again), and login using ServiceAccount by typing username and password. I can't be forced to login on Windows with ServiceAccount (or run SSMS/SSDT/etc with that account) to be able to login on MSSQL.

What's the best practice for configuring that?

Some of what you are asking for is actually pretty simple.

Permissions for your AD Group

You create a login for your AD group

CREATE LOGIN [domain\AD Group] FROM WINDOWS

Then grant it the access you want.  You say you want to grant it Admin access but unless this is your DBA team I wouldn't add the AD Group to something like sysadmin.  Instead grant them the permissions you actually want them to have.  It sounds like you want

db_ddladmin - Role that grants permissions to create/modify objects within a DB.
db_datareader - Role that grants permissions to run a SELECT against any table/view within the DB
db_datawriter - Role that grants permissions to run UPDATE, INSERT or DELETE statements against any table/view in the DB.
EXECUTE - Permission that grants the ability to EXECUTE any stored procedure or function within the database.

To add this run the following on each user database.

CREATE USER [domain\AD Group] LOGIN [domain\AD Group]
ALTER ROLE db_ddladmin ADD MEMBER [domain\AD Group]
ALTER ROLE db_datareader ADD MEMBER [domain\AD Group]
ALTER ROLE db_datawriter ADD MEMBER [domain\AD Group]
GRANT EXECUTE TO [domain\AD Group]

If you want these permissions added to new user databases then run the script in the model database as well.

Connecting using your service account

If you have an application running under your service account then when that application connects using trusted authentication it will connect to SQL using that service account.  If however you want to connect to SQL using something like SSMS then you have two choices.  You can either log into a machine using the service account or run SSMS as a different user.

To run SSMS under a different user hold down the SHIFT key and right click on your shortcut to SSMS and select Run as different user

You'll then get a login/password prompt for an AD login.  Type in your service account name & password.  Once SSMS opens any trusted (windows auth) connections will be made using the AD login you connected under (your serviced account in this case).


Count of range on datetime in MySql I want to group table entries for each userId only if they are consecutive entries in the table.
I have a table like this.

id userId      transactionTime

1  1           2016-04-13 22:15:44
2  1           2016-04-13 22:40:22
3  1           2016-04-13 22:41:34
4  2           2016-04-13 22:41:34
5  2           2016-04-13 22:42:25
6  1           2016-04-13 23:43:55

Expected Result:

userId    count
1         3
2         2
1         1

Thanks :)

Update:
The transactionTime is ordered in asc. Considering this, we can check the consecutive occurence of same userId and make it a single entry with number of counts.

Consider this, userId=1 occurs three consecutive times from id=1,2,3 this will be first result with userId=1 and count=3. Similarly at id=4 and 5 userId is 2 which will be second row in the result with userId=1 and count=2.

You can use variables for this kind of thing:

select userId, count(*)
from (
    select @g:=case when userId=@lastuserId then @g else @g+1 end AS grp,
           @lastuserId:=userId AS userId
    from test1, (select@lastuserId:=0, @g:=0) vars
    order by transactionTime
) groups
group by grp, userId
order by grp


Sql Server Agent notification email  with custom message I have set email notification on the completition of a scheduled job.

Is it possible to customize the email content?

The way I've done this in the past is added a final step to the job, then I can use Database Mail to customize everything about the message (subject, body, recipients, from, etc) based on the server, the status/outcome of the job, and even the duration.

It is cumbersome if you have a lot of jobs, but I did this for a variety of other reasons as well (for example, other actions to perform in addition to sending an e-mail).

You may also consider a management tool like SQL Sentry which provides lots of flexibility over how to handle successful/failed jobs, and has comprehensive options for subsequent actions.


Optimising views that utilise functions One of our developers created a view whereby one of the select columns was wrapped in an RTRIM function. That same column on the underlying table has a non-clustered index on it.

Now queries using this view and a where statement on this column are failing to use the index due to the RTRIM. I need the view's output to be consistent as unfortunately this developer is no longer with us and I can't read his code.

What are some options for optimisation? I've got one, but I'm hoping there is better.

Create a new computed column on the underlying table that is an RTRIM of the column in question. Add an index on this column. Adjust the view to use this computed column.


What are the advantages of a database design with single table for MtM relationships? The database that backs our software product has a table design like the following:

Positions
ID    Name    Parts
1     One     12345
2     Two     12346
3     Three   12347

Collections
ID      TableID  Collection
12345   1;1      1;2
12346   1;1      3;4
12347   1;2;2    5;1;2

Parts
ID     Name
1      TestOne
2      TestTwo
3      TestThree
4      TestFour
5      TestFive

SubParts
1      SubPartOne
2      SubPartOne

From the above example, each Position has a collection of Parts, but these are mapped generically (without foreign key constraints) into the Collections table. The Collections table keeps track of all relationships between all objects, not just between the example tables shown above, and will be used any time a collection is used.

That means that if I want to get the Position with ID 1, and all of its parts. I have to do three queries:

SELECT * FROM Positions WHERE ID = 1
SELECT * FROM Collections WHERE ID = 12345

Split the string by the semicolons

SELECT * FROM Parts WHERE ID = 1 OR ID = 2

The advantages of the current approach are:

A collection of something can actually be selected from more than table. For example, if SubPart inherits from Part, and position contains a list of Parts, this will be handled OK.

The disadvantages of the current approach are:

Speed? We need to do many queries to load data.
The Collections table is the largest or second largest table in our database.
No support from ORM frameworks? We are looking at switching our persistency layer to use EF or NHibernate etc. but I don't believe our current design will be supported by any of these frameworks.

My questions are:

Is there advantages or disadvantages to this approach that I haven't listed?
Is this design familiar to people and if so, does it have a name?
If the design is familiar, is it supported by out of the box ORM frameworks?

I don't know if this design has a name, but it is:

horrible to retrieve data with,
horrible to modify,
impossible to guarantee referential integrity.

Can't think of any advantage, but if I could, I'm sure none would trump these three.

Multiple many-to-many tables would be much better compared to this design, although I won't insist that would be the only alternative.


Permission hierarchy vs. WITH GRANT OPTION I have an Azure SQL database with this security setup:

SchemaUpdater user with db_ddladmin, db_datawriter, and db_datareader roles. The user is used to execute database change scripts during application deployment.
App user with db_datawriter, and db_datareader roles. The user is used by the application to work with the data.

I wanted the app user to also execute a stored procedure, say dbo.sp_MyProc. I did following:

grant execute to [SchemaUpdater] with grant option using an admin account, so that change scripts can grant permissions for any SPs.
Created the stored procedure in a change script.
Tried to grant execute on [dbo].[sp_MyProc] to [App] in the same change script. That didn't work.

The step failed with

Cannot find the object 'sp_MyProc', because it does not exist or you do not have permission.

What's interesting, that if the SchemaUpdater does grant execute to [App] instead, with no individual SP mentioned, it works.

My questions are:

Why can't SchemaUpdater grant a permission on a single SP, but can grant it on all SPs?
Is SchemaUpdater missing some other permission, so that it could grant permissions to individual SPs?

Unfortunately the WITH GRANT permission is not inherited in some, or perhaps all, cases.  I couldn't find anything in the documentation or other articles on the [cue thunder] World Wide Web [stop thunder] stating that, though.  I am with you in that I would expect it to be inherited.

I tested this on SQL Server 2016 and got the same results as you did.  There are several options to work around it, but the most fine-grained option would be to assign EXECUTE WITH GRANT to SchemaUpdater on each stored procedure.

GRANT EXECUTE ON [dbo].[sp_MyProc] TO [SchemaUpdater] WITH GRANT OPTION

I have confirmed that WITH GRANT is not inherited on other permissions and objects as well.  I tested the same scenario with VIEW DEFINITION on a table, and had to explicitly GRANT WITH GRANT on the table before SchemaUpdater could grant that permission to another user.

So the specific answers to your questions:

Since WITH GRANT is not inherited, a user must have explicit WITH GRANT permission on an object in order to grant permissions.  Since SchemaUpdater has EXECUTE WITH GRANT on the database, it can grant EXECUTE on the database.  Since it doesn't have an explicit EXECUTE WITH GRANT on the stored procedure, it cannot grant explicit permissions on that stored procedure.
As mentioned, SchemaUpdater will need EXECUTE WITH GRANT explicitly on each stored procedure, or a higher privilege that includes that permission (such as db_owner).


Create Trigger that updates the same table before (or after ??) the Insert I have just started using triggers and one thing that I would need to do is to create a trigger that updates the same table that's inserting the row.

TABLE:

CREATE TABLE `city` (
  `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `country_id` smallint(5) unsigned DEFAULT '0',
  `country` varchar(150) DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`city_id`),
  KEY `idx_fk_country_id` (`country_id`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6009 DEFAULT CHARSET=utf8;

Trigger:

DELIMITER $$
CREATE TRIGGER `populate_country_id` BEFORE INSERT ON `city`
FOR EACH ROW
BEGIN

IF NEW.country_id = 0

THEN
    UPDATE city
    INNER JOIN country B ON city.country = B.country
    SET NEW.country_id = B.country_id;

END IF;

END $$
DELIMITER ;

MySQL is just returning: Can't update table 'city' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Is there any way I can make this work?

Thanks everyone!

CREATE TRIGGER `populate_country_id` BEFORE INSERT ON `city`
FOR EACH ROW
SET NEW.country_id = (SELECT CASE WHEN NEW.country_id = 0
                                  THEN country_id
                                  ELSE NEW.country_id END
                      FROM country
                      WHERE country = NEW.country
                      LIMIT 1); -- as a precaution

BEGIN-END and DELIMITER reassign are excess.

PS. If NEW.country value is absent in country table then ... anycase inserted data is wrong.


mysql query gets slower with increasing page number I use laravel 5.2 with mariadb (ARIA engine) and found that my query for creating a gallery gets slower with increasing page number.

select `pics`.`id`,
       `pics`.`title`,
       `pics`.`hits`,
       `pics`.`created_at`,
       `users`.`username`,
       `users`.`displayname`
from `pics`
left join `users` on `pics`.`user_id` = `users`.`id`
order by `pics`.`id` desc
limit 48
offset 7776

This is the query for the last page (163) (offset 7776) which takes about 20ms while page 1 (offset 0) needs only 700µs. This is possibly still fast (?), but also  a difference.

Why does it get slower and what could I do to improve it?
Both tables have gaps and get UPDATE, INSERT and DELETE.

pics table:

id  int(11) unsigned Auto-Inkrement
url varchar(100)
title   varchar(200)
text    text
jahrprod    year(4) NULL
cat varchar(50)
hits    int(11) [0]
username    varchar(30)
user_id smallint(5) unsigned
location_id int(11)
created_at  datetime
updated_at  datetime

pics.username is old and will be removed in the future.

when you're using the "limit 48 offset 7776" mysql need to scan all rows(48+7776), along with offset number increase, mysql need to scan more and more rows for "Next page"
To improve this, try:

select ... from ... where id > 7776 limit 48

or try:

select ... from ... where id between 7776 and 7824


Select rows from table 1, table 2, then selecting based on ID I am dealing with a large dataset, and have run into some issues because of this.

I have a data set like:

Table1

    State    ID  Conditional_Value
     AZ       1         Yes
     AZ       2         Yes
     AZ       5         Yes

Table 2

    State    ID  Conditional_Value
     AZ       1         No
     AZ       3         No
     AZ       7         No

What I need this to return is

   State    ID  Conditional_Value
     AZ       1         Yes
     AZ       2         Yes
     AZ       3         No
     AZ       5         Yes
     AZ       7         No

If there is a duplicate ID in table 2 take that ID's row from table 1 and select it.

I have tried

    Select State, ID, Conditional_Value
    From Table1
    Union
    Select State, ID, Conditional_Value
    From Table2
    Where Table2.ID not in (select Table1.ID from Table1)

But with the large dataset I have, it is too expensive to run.

I have looked at questions about finding duplicates and deleting them and then inserting the non-duplicates into another table, but I need to compare the 2 separate tables as well.

If there is any more info needed, please let me know.
Thanks!

While you already seem to have resolved the problem to your satisfaction, the classical solution to this is to use a FULL OUTER JOIN:

SELECT COALESCE(t1.State, t2.State) as State
      ,COALESCE(t1.ID, t2.ID) as ID
      ,COALESCE(t1.Conditional_Value, t2.Conditional_Value) as Conditional_Value
  FROM Table1 t1
         FULL OUTER JOIN Table2 t2 ON (    t1.State = t2.State
                                       AND t1.ID = t2.ID)

Note: code untested, and I haven't worked with teradata specifically, so there may be some syntax issues I can't predict. However, the basic commands and functions should be correct, from what I can see.

The code merges the two tables based on the State and ID values. The COALESCE functions ensure that whichever table has a value for a particular row, that's the value that will be used. Since we look at Table1 first, if both Table1 and Table2 have a value for Conditional_Value, the value from Table1 will be used.


SQL Server memory usage I'm trying to understand more about the memory usage for SQL Server. I can't seem to figure out what's exactly stored in the memory buffer. My setup is a Windows Server 2008 x64, 8 GB RAM, SQL Server 2008 standard R2, maximum SQL Server memory 6000 MB.

After running SQL Server a couple of hours, the resource monitor shows sqlservr.exe has used about 6000 MB of memory, but when I check the usage for data and plan caches I get less than 6000 MB:

Cached plans          2541 MB
Database data cached  2706 MB
Total                 5247 MB

I used sys.dm_exec_cached_plan and sys.dm_os_buffer_descriptors to find these values, so from these values I don't know what the 750 MB are used for... I don't think SQL Server is reserving memory since this is a x64 machine... Am I missing something here?

There are other memory consumers outside of the buffer pool.  For instance, SQL Server will use memory for worker threads, multi-page allocations, linked servers, extended stored procedures, CLR, etc.

The buffer pool is never going to equate to 100% of memory consumed by SQL Server.  It's a common misconception, as well as setting max memory will be the actual max memory SQL Server uses (not true).

EDIT:  Here's how I think you could get non-buffer pool memory size.  Note: wait for the input of other SQL Server professionals before taking this query as fact.  I cannot guarantee that it is absolutely correct.

declare
    @data_cache_size_mb decimal(12, 2),
    @plan_cache_size_mb decimal(12, 2),
    @total_mem_mb decimal(12, 2)

select
    @data_cache_size_mb = count(*) * 8 / 1024.
from sys.dm_os_buffer_descriptors

select
    @plan_cache_size_mb = sum(size_in_bytes) / 1024. / 1024.
from sys.dm_exec_cached_plans

select
    @total_mem_mb = sum(pages_allocated_count) * 8 / 1024.
from sys.dm_os_memory_objects

select
    @data_cache_size_mb as data_cache_size_mb,
    @plan_cache_size_mb as plan_cache_size_mb,
    @data_cache_size_mb + @plan_cache_size_mb as buffer_pool_size_mb,
    @total_mem_mb as total_instance_mem_mb,
    @total_mem_mb - (@data_cache_size_mb + @plan_cache_size_mb) as non_buffer_pool_size_mb


SQL Server Service Account - Things to test post change (from Local System to Network Service) We needed to change the SQL Service Account for the SQL Server Database Engine & Agent Services from Local System as it has extensive predigests.

What are the things that we should do to test within the SQL Server database itself to ensure that there are no issues / permission problems with this change?

Note the service has been changed, and seems to be running fine Both Agent Services / SQL Server Engine.

The two versions we are implementing this in are SQL Server 2008 R2 and SQL Server 2012.

In Short: Everything and Anything. And Nothing.

But seriously: A few thoughts about changing the SQL Server service account:

ALWAYS use the SQL Server configuration manager. This tool is smart. It knows what permissions need to be assigned to the service account. And generally does it. If you change it from windows services control panel - GOOD LUCK.
The service account is how SQL Server interacts locally and to the network. So moving from local system - you shouldn't care about breaking networked connections - since they wouldn't work anyway. But you would generally want to make sure things like "Will SQL Server start?" happen (that means it can at least read the drives that master/model/msdb/tempdb and the error logs are on). And that verifies the registry.
If you assigned Perform Volume Maintenance Tasks to a SQL Server service account before - that will likely need to be assigned again.
Generally this is a safe move. As long as you do it through the SQL Server configuration manager. Often we'll see folks do this through windows services control panel, something breaks, so they quickly grant their service account local admin rights - and "poof!" everything works.

In your particular case though - you are moving from one special privileged account to another. As you have seen - you were safe and things worked. I would also take this time to question the choice of the service account.

Are you on a domain? Why not use a domain user account per the best practices? The local accounts with special privileges are not the most secure or reliable service account choices. This article from Microsoft explains more there.


Allow user to GRANT certain permissions to others I have tried googling this but may well be using the wrong terms... permissions and the like aren't my strong point.
What I want to do is first create a role - eg 'Snr_Analyst' - with certain permissions to read everything, and alter particular schemas in a database. That bit I can do.
I also want that role to be able to grant read permissions on everything in that database to any other user, but not to grant any higher permissions.
Is this possible?
I'm currently using SQL Server 2014.

you can use GRANT ... WITH GRANT OPTION.
https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql?view=sql-server-ver15
this option specifies that the security principal receiving the permission is given the ability to grant the specified permission to other security accounts.


Avoid output disappearing in psql I open psql and write:

mydb=> \dt

and this list of tables is shown:

                   List of relations
 Schema |            Name            | Type  |  Owner
--------+----------------------------+-------+---------
 public | auth_group                 | table | foouser
 ...

The I have to press Esc to return to the prompt, and the output disappears like it was a less session.

I want to keep the output around so that I can use the information in it to write queries, but googling does not find an option to do that.

Assuming your pager is less, try LESS=-FX; export LESS in your shell environment.

From man less:

      -F or --quit-if-one-screen
              Causes less to automatically exit if the entire file can be dis‐
              played on the first screen.

      -X or --no-init
              Disables sending the termcap initialization and deinitialization
              strings to the terminal.  This is  sometimes  desirable  if  the
              deinitialization  string does something unnecessary, like clear‐
              ing the screen.

To have this only in psql, set it with a meta-command in .psqlrc:

\setenv LESS -FX


Mirroring - Server network address cannot be reached I have SQL Server 2008 R2 Installed. It contains three instances.

Default (MSSQLServer)
First Instance
Second Instance

All these are Log On as Network Service..

Default Instance is Principal server
First Instance is Mirror
Second Instance is Witness Server

I initially took the Full Backup and Transactional Log Backup of my Principal Database.
Restored it to the First Instance by keeping the same Database Name and Recovery State is No-Recovery

Finally, I started the Mirroring and I am getting two error messages shown below.

Try some basic connectivity tests.

Verify that 5022, 5023 and 5024 are listening.
Verify that the server name you are using is correct.

From the command line :

netstat -an

On my server, you can see that 5022 is listening.

Next make sure that you can connect to those ports via telnet

telnet fully-qualified-server-name 5022

As it mentions in the Note section of the Mirroring Properties GUI, just below the witness field, the server names have to be fully qualified tcp addresses.

You should just see a black screen.  In this example I chose a name that would cause a connection failure.  If you see "Could not open connection", then the server(s) defined as mirror, principal and witness aren't reachable or you are not using the right name.

The telnet client can be added under Features in Windows 2008.

In Windows 2008, when you right click on Computer, you can see the full computer name.
You should be able to ping it as well from the command line.  ex: ping myservername

Update

Please run the following queries on each SQL Server instance and put the results in your question.  Many of these troubleshooting tips come from: http://msdn.microsoft.com/en-us/library/ms189127.aspx

Show tcp endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;

Display status of mirroring endpoints

SELECT state_desc FROM sys.database_mirroring_endpoints;

Check that the ROLE is correct

SELECT role FROM sys.database_mirroring_endpoints;

Display permissions to endpoints

SELECT EP.name, SP.STATE,
   CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
      AS GRANTOR,
   SP.TYPE AS PERMISSION,
   CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
      AS GRANTEE
   FROM sys.server_permissions SP , sys.endpoints EP
   WHERE SP.major_id = EP.endpoint_id
   ORDER BY Permission,grantor, grantee;
GO

The login for the service account from the other server instance requires CONNECT permission. Make sure that the login from the other server has CONNECT permission. To determine who has CONNECT permission for an endpoint, on each server instance use the following Transact-SQL statement.

Example output:

name    STATE   GRANTOR PERMISSION  GRANTEE
TSQL Local Machine  G   sqladmin    CO      public
TSQL Named Pipes    G   sqladmin    CO      public
TSQL Default TCP    G   sqladmin    CO      public
TSQL Default VIA    G   sqladmin    CO      public
Mirroring   G   SERVERNAME\Grantor  CO      SERVERNAME\Grantee

Grantor is the account that assigned (CO) connect permission, Grantee is the account that has connect permission

From the command line run ipconfig /all and note what Host Name returns.


How could I connect to MySQL database running on WAMP server from other computers in same LAN? I'm new in web development. I'm requesting help from all of you. My problem is:

I installed WAMP server in my desktop computer. My desktop computer has a static LAN IP (192.168.0.101).

I'm able to access WAMP server homepage from my other computers connected to the same network by using the LAN IP (192.168.0.101).
But while I was trying to access phpMyAdmin from other computer it says:

  Forbidden. You don't have permission to access/phpmyadmin/ on this server. Apache/2.4.9(Win64) PHP/5.5.12 Server at 192.168.0.101 Port 80.

I also tried to connect to the MySQL database running on my desktop computer using cmd in other computer by the following code, mysql -h 192.168.0.101 -u root -p (there is no password for root user).

Please help me how could I be able to access phpMyAdmin and MySQL database from other computer connected to the same network.

WAMPServer is primarily designed for use on a single machine i.e. a developer writing and testing code locally on that PC.

So to protect the beginner from his/her self the Apache & MySQL configuration by default is setup so no access can be made to the Apache or MySQL server form anywhere but the PC running Apache and MySQL.

When changing this configuration great care should be taken to only allow access that you intend and not global access. So be VERY CAREFUL with any help/tutorial that suggest adding Allow from all or Require all granted as they either dont really know what they are doing or the help is based on giving access to the site to the whole internet.

To allow access to phpMyAdmin you need to change the Alias configuration for phpMyAdmin, so edit wamp\alias\phpmyadmin.conf and add an instruction to allow access from any ip address on your internal network like so :-

Alias /phpmyadmin "d:/wamp/apps/phpmyadmin4.1.14/"

<Directory "d:/wamp/apps/phpmyadmin4.1.14/">
   Options Indexes FollowSymLinks MultiViews
   AllowOverride all
  <IfDefine APACHE24>
    Require local
-->     Require ip 192.168.0
  </IfDefine>
  <IfDefine !APACHE24>
    Order Deny,Allow
      Deny from all
      Allow from localhost ::1 127.0.0.1
-->     Allow from 192.168.0
    </IfDefine>
  php_admin_value upload_max_filesize 128M
  php_admin_value post_max_size 128M
  php_admin_value max_execution_time 360
  php_admin_value max_input_time 360
</Directory>

If you actually want to access MySQL via the mysql.exe processor and other tools like it from another IP address you have basically the same problem.

MySQL user account are made up of 2 parts, a UserId and a Host. The Host part tells MySQL where this userid is allowed to connect from. If you look using phpMyAdmin at the user accounts that are pre-created in MySQL you will see that the Host part of all the account is either 127.0.0.1 or localhost or ::1 all of which equate to The PC running MySQL. Again for the security of the beginner no access allowed from outside the PC running MySQL.

So to use mysql.exe etc, I suggest you create a new MySQL User Account, lets say MAKhan with a Host of 192.168.0 AND OF COURSE A PASSWORD. That account will them be allowed to login to MySQL from any ip on your local network. You will of course have to give rights to this new account so that it can access some or all of the databases on the MySQL server.


Performing join on 3 tables & retrieving selected columns I need to write a query which fetches data from 3 tables & retrieve selected columns out of it.
The query below works fine. I am interested to include 1 more column in my SELECT statement which is available in 'PI_PUBLICATION' table.
Since I am using multiple column subquery here, can you please help me out?

SELECT pirhd.GTIN, pirhd.INFORMATION_PROVIDER, pirhd.RETAILER, pirhd.TARGET_MARKET, MESSAGE_XML
FROM PI_RESUBMIT_HIERARCHY_DETAIL pirhd
WHERE (pirhd.GTIN, pirhd.INFORMATION_PROVIDER, pirhd.RETAILER, pirhd.TARGET_MARKET)
IN (
SELECT item.GTIN, item.INFORMATION_PROVIDER, item.RETAILER, item.TARGET_MARKET
FROM Pi_Item Item
WHERE item.GTIN = pirhd.GTIN and item.INFORMATION_PROVIDER = pirhd.INFORMATION_PROVIDER AND item.TARGET_MARKET = pirhd.TARGET_MARKET
AND item.RETAILER = pirhd.RETAILER
AND (item.obj_id IN (SELECT PUB.OBJ_ID from PI_PUBLICATION pub))
) AND RESUBMIT_ITEM_DETAIL_ID='88956457' ORDER BY pirhd.GTIN;

You could try the following restructured query which uses joins instead of the sub-selects;

SELECT pirhd.GTIN,
       pirhd.INFORMATION_PROVIDER,
       pirhd.RETAILER,
       pirhd.TARGET_MARKET,
       MESSAGE_XML,
       pub.another_column
FROM   PI_RESUBMIT_HIERARCHY_DETAIL pirhd
       INNER JOIN Pi_Item Item
          ON     item.GTIN = pirhd.GTIN
             AND item.INFORMATION_PROVIDER = pirhd.INFORMATION_PROVIDER
             AND item.TARGET_MARKET = pirhd.TARGET_MARKET
             AND item.RETAILER = pirhd.RETAILER
       INNER JOIN PI_PUBLICATION pub ON item.obj_id = PUB.OBJ_ID
WHERE  RESUBMIT_ITEM_DETAIL_ID = '88956457'
ORDER BY pirhd.GTIN

Another couple of possibilities;

WITH bi_pub AS
(SELECT DISTINCT obj_id , another_column
   FROM PI_PUBLICATION )
SELECT pirhd.GTIN,
       pirhd.INFORMATION_PROVIDER,
       pirhd.RETAILER,
       pirhd.TARGET_MARKET,
       MESSAGE_XML,
       pub.another_column
FROM   PI_RESUBMIT_HIERARCHY_DETAIL pirhd
       INNER JOIN Pi_Item Item
          ON     item.GTIN = pirhd.GTIN
             AND item.INFORMATION_PROVIDER = pirhd.INFORMATION_PROVIDER
             AND item.TARGET_MARKET = pirhd.TARGET_MARKET
             AND item.RETAILER = pirhd.RETAILER
       INNER JOIN BI_PUB pub
          ON item.obj_id = PUB.OBJ_ID
WHERE  RESUBMIT_ITEM_DETAIL_ID = '88956457'
ORDER BY pirhd.GTIN

OR

SELECT pirhd.GTIN,
       pirhd.INFORMATION_PROVIDER,
       pirhd.RETAILER,
       pirhd.TARGET_MARKET,
       MESSAGE_XML,
       (SELECT pub.another_column
          FROM PI_PUBLICATION pub
         WHERE pub.obj_id = item.obj_id
           AND ROWNUM = 1)
FROM   PI_RESUBMIT_HIERARCHY_DETAIL pirhd
       INNER JOIN Pi_Item Item
          ON     item.GTIN = pirhd.GTIN
             AND item.INFORMATION_PROVIDER = pirhd.INFORMATION_PROVIDER
             AND item.TARGET_MARKET = pirhd.TARGET_MARKET
             AND item.RETAILER = pirhd.RETAILER
       INNER JOIN PI_PUBLICATION pub ON item.obj_id = PUB.OBJ_ID
WHERE  RESUBMIT_ITEM_DETAIL_ID = '88956457'
ORDER BY pirhd.GTIN

This really depends on your data and requirements and only you can answer that.


understanding ORA-01792 maximum number of columns in a table or view is 1000 We've recently migrated a server from Oracle 11 to Oracle 12c and ran into a few annoying issues. One of them is that a few a reports give the following error:

  ORA-01792: maximum number of columns in a table or view is 1000

OK, fine, I understand there have to be some limits. The problem is that the query worked before and that I see no reason for the DB to load all columns. None of tables in the query have more than 1000 columns. Combined they could have. So if I did a select * from everything; I understand that the output runs into limits because a result set is virtualy the same thing as a view/table (is it? I'm just guessing here?)

The "problem query" joins a bunch of big tables and a few smaller ones multiple times, so yes, the total column count could be more than 1000.

However, the query only selects 30 or so columns. Add the fields used for joining and maybe even some additional fields used by indices in the background it might add up to 50~70 columns that are needed to preform the query. All of that information is well defined within the query and table information. So why on earth would the DB even care about all the additional columns?

The solutions I've read so far are to change the query or to disable this warning/error. Both of which are non-solutions to me. The only reason I can think of for disabling the warning is that Oracle decided to inform users earlier and that this "problem" existed in the background in both versions.

Any ideas about this? Or my understanding of how 'big' queries are handled?

According to the My Oracle Support, ORA-01792 is caused due to an unpublished bug.

Oracle suggests to alter the query and/or view definitions to avoid the error. However in cases where the SQL cannot be adjusted then the
 checking can be disabled by:

SQL> alter system set "_fix_control"='17376322:OFF';

Alternatively, an interim patch(patch 19509982) can be applied to disable the error by default.

References:

Bug 19509982  Disable raising of ORA-1792 by default
Select Statement Throws ORA-01792 Error


Why do SQL Server row estimates change when I add a join hint? I have a query that joins a few tables and performs pretty badly - row estimates are way (a 1000 times) off and Nested Loops join is chosen, resulting in multiple table scans. The shape of the query is fairly straightforward, looking something like this:

SELECT t1.id
FROM t1
INNER JOIN t2 ON t1.id = t2.t1_id
LEFT OUTER JOIN t3 ON t2.id = t3.t2_id
LEFT OUTER JOIN t4 ON t3.t4_id = t4.id
WHERE t4.id = some_GUID

Playing around with the query, I noticed that when I hint it to use a Merge join for one of the joins, it runs many times faster. This I can understand - Merge join is a better option for the data that is joined, but SQL Server just doesn't estimate it right choosing the Nested Loops.

What I don't fully understand is why does this join hint changes all the estimates for all the plan operators? From reading different articles and books, I assumed that the cardinality estimations are performed before the plan is built, so using a hint would not have changed the estimations, but rather explicitly tell SQL Server to use a particular physical join implementation.

What I see, however, is that Merge hint causes all estimations to become pretty much perfect. Why does this happen and are there any common techniques to make query optimizer make a better estimate without a hint - considering that statistics obviously allow for this?

UPD:
anonymized execution plans can be found here:
https://www.dropbox.com/s/hchfuru35qqj89s/merge_join.sqlplan?dl=0 https://www.dropbox.com/s/38sjtv0t7vjjfdp/no_hints_join.sqlplan?dl=0

I checked the stats used by both queries using TF 3604, 9292 and 9204, and those are identical. However indexes which are scanned/seeked differ between the queries.

Besides that, I tried running the query with OPTION (FORCE ORDER) - it runs even faster than using merge join, choosing HASH MATCH for every join.

From reading different articles and books, I assumed that the cardinality estimations are performed before the plan is built.

Not exactly. An initial cardinality estimate is derived (after simplifications and other work), which influences the initial join order chosen by the optimizer.

However, subsequent explorations (during cost-based optimization) can, and often do, result in new cardinality estimations being calculated. These later CEs may be more or less 'accurate'. If an under-estimate results, the optimizer may choose a plan that looks cheaper, but in fact runs for much longer.

In general, there is no guarantee that cardinality estimations for semantically identical subtrees will produce the same results. It is a statistical process, after all, and some operations have deeper CE support than others.

In your case, there appears to be another factor - the optimizer introduces (or moves around) a Top, which sets a row goal on the subtree beneath it:

If you were to enable trace flag 4138 (on 2008 R2 or later), you may find the estimates more in-line with expectations, or perhaps even that the optimizer would no longer choose nested loops.

  What I see, however, is that Merge hint causes all estimations to become pretty much perfect.

There is an element of luck involved here. People tend to write queries, or at least the joins, in the order they expect them to be performed physically. Using a join hint comes with an implied FORCE ORDER, thereby fixing the join order to match the textual form, and switching off many optimizer exploration rules that can lead to cardinality re-estimation.

  Besides that, I tried running the query with OPTION (FORCE ORDER) - it runs even faster than using merge join, choosing HASH MATCH for every join.

This is the same as hinting a join, but does not restrict the choice of physical join operator. Again, if you happened to write the query join order in a logical way, it is quite likely you will get a reasonable plan. Of course, you miss out on much of the optimizer's abilities this way, which may not produce optimal results in more general situations.

You will probably not want to use FORCE ORDER very often because it is an extremely powerful hint (directive) that has wider effects than simple forcing the order of joins; for example, it prevents the optimizer moving aggregates around and introducing partial aggregations. I very much advise against using this hint except in very exceptional circumstances, and by truly expert tuners.

A detailed analysis would require more time than I have right now, and access to a statistics-only copy of the database.


best practice for 'staging' relation in stored procedure: use temp table, or view? When I write stored procedures that take relation A and output relation Z, I like to create 'staging' relations along the way, so I can inspect the state of the data after each step in the programming logic is applied.

So far, I have been using temp tables to instantiate these staging relations.

Should I be using views instead? If this is the case, then what happens when different views are being joined in the same query, and these different views are hitting the same base tables? Does performance suffer?

Overall, which approach leads to best overall performance? Is there a way to give a general answer that applies to most scenarios, without having to run traces on specific implementations?

If you are doing this to aid debugging, rather than to facilitate restart after a failure.  I would suggest temp tables.

Temp tables are visible only to the scope in which they are created.  In other words, if two users execute the same stored procedure (SP) simultaneously they will be given different temp tables and most importantly their data will be completely isolated from each other's.  Views are globally visible (permissions not withstanding) and data written by one can be seen by all, with the risk of leakage.  If you define a view and write your working values to it you have to add extra columns to separate your values from other users'.

Temp tables are dropped automatically by the system when they go out of scope.  This makes your debug code simpler since you don't have to program around dirty restarts after crashes.

Temp tables can be created on-the-fly with the SELECT..INTO syntax.  Thus their schema will automatically keep up with changes to the surrounding schema.  Views do not have this advantage.

If you are processing many rows you can index Temp tables with only the same drawbacks that indexing normal tables incurr.  In SQL Server at least - which is my speciality - there are many restraints on a view's defintion before it can be indexed, which could affect their perormance or applicability in your case.

SQL Server makes table-valued variables available as a lighter-weight alternative to temp tables.  See this SO question for the differences.

If you do happen to be on SQL Server, and are lucky enough to have 2014 available, and your row counts are modest, these working tables are an excellent candidate for non-persisted, in-memory tables.


Changing Compatibility level from 90 to 120 in sql 2014 migration I have SQL 2008R2  server to migrate to SQL2014 Server.

A side by side upgrade is what we are thinking for few databases in SQL 2008R2 which are having compatibility level of 90 or 80.

Can I migrate these databases (back& restore) to SQL2014 and sql will allow me to change compatibility level to 120 ? if not how can i deal with this?

thanks

Yes you can migrate, but that totally depends upon the results of testing you have done pre-migration. That is , you can test the application on test-staging server before you make any changes in production. As per the results you may get a go-head.

Also, please refer to this MSDN article, to understand the difference between various compatibility levels in SQL server.Therefore you will need to go what has been changed since OLD Compatibility versions till now and how will it behave when you change to new.

As mentioned :

  When a stored procedure executes, it uses the current compatibility
  level of the database in which it is defined. When the compatibility
  setting of a database is changed, all of its stored procedures are
  automatically recompiled accordingly.

Also, running upgrade advisor will be helpful during you're migration strategy which shall let you know for any issues, so that they can be looked after accordingly.

In addition you can read Bad habits : Clinging to old compatibility levels and What is the actual behavior of compatibility level 80? from Aaron.


Speed up SELECT with WINDOW, (PARTITION ... ORDER) with compound index I have the following table:

CREATE TABLE ranking_points  (
  id serial NOT NULL,
  store_id integer,
  book_id integer,
  list_name character varying(255),
  rank integer,
  created_at timestamp without time zone,
  updated_at timestamp without time zone,
  storewide boolean,
  yesterday_delta integer,
  best_ever_rank integer,
  best_ever_date date,
  CONSTRAINT ranking_points_pkey PRIMARY KEY (id)
);

CREATE INDEX index_ranking_points_on_book_id_and_created_at
  ON ranking_points (book_id, created_at);

CREATE INDEX index_ranking_points_on_book_id_list_name_and_created_at_desc
  ON ranking_points (book_id, list_name, created_at DESC);

CREATE INDEX index_ranking_points_on_store_id_and_created_at
  ON ranking_points (store_id, created_at);`

I am executing the following SELECT statement:

select distinct book_id, list_name,
                first_value(created_at) over w as created_at,
                first_value(rank) over w as rank,
                first_value(id) over w as id
from ranking_points where book_id in (61,62,63)
window w as (partition by book_id, list_name order by 3 desc)

I would expect Postgres to use the index_ranking_points_on_book_id_list_name_and_created_at_desc index since it perfectly matches the partition by and order by, but instead it uses the index_ranking_points_on_book_id_and_created_at index. Here is the explain analyze:

HashAggregate  (cost=895.70..901.41 rows=571 width=42) (actual time=0.740..0.740 rows=0 loops=1)
  ->  WindowAgg  (cost=874.28..888.56 rows=571 width=42) (actual time=0.737..0.737 rows=0 loops=1)
        ->  Sort  (cost=874.28..875.71 rows=571 width=42) (actual time=0.737..0.737 rows=0 loops=1)
              Sort Key: book_id, list_name
              Sort Method: quicksort  Memory: 25kB
              ->  Bitmap Heap Scan on ranking_points  (cost=17.25..848.14 rows=571 width=42) (actual time=0.728..0.728 rows=0 loops=1)
                    Recheck Cond: (book_id = ANY ('{61,62,63}'::integer[]))
                    ->  Bitmap Index Scan on index_ranking_points_on_book_id_and_created_at  (cost=0.00..17.11 rows=571 width=0) (actual time=0.726..0.726 rows=0 loops=1)
                          Index Cond: (book_id = ANY ('{61,62,63}'::integer[]))
Total runtime: 0.811 ms

You can see that what I'm trying to do is to build a result set consisting of the grouping values and the first_value of several fields. My hope was that Postgres could do all of the selection using only the index and then read one record from each group to obtain the first_value values. Is that achievable? I have other solutions, but they generate a sequential scan of the table, so that's not very good.


pg_hba.conf and postgresql.conf in old installation directory I have installed the PostgreSQL 9.4 on my MacBook via MacPorts, and I am trying to edit pg_hba.conf, but I don't see it in my current installation. Furthermore, psql is reporting that config_file is in my old 9.3 directory! How should I fix this?

$ port select --list postgresql
Available versions for postgresql:
    none
    postgresql93
    postgresql94 (active)

$ psql --version
psql (PostgreSQL) 9.4.1

$ psql -U postgres -c 'SHOW config_file'
                 config_file
----------------------------------------------
 /Library/PostgreSQL/9.3/data/postgresql.conf
(1 row)

$ ls /Library/PostgreSQL/9.3/data
PG_VERSION      pg_hba.conf     pg_notify       pg_stat_tmp     pg_xlog
base            pg_ident.conf   pg_serial       pg_subtrans     postgresql.conf
global          pg_log          pg_snapshots    pg_tblspc       postmaster.opts
pg_clog         pg_multixact    pg_stat         pg_twophase     postmaster.pid

$ ls /Library/PostgreSQL/9.4/data
ls: /Library/PostgreSQL/9.4/data: No such file or directory

$ ls /Library/PostgreSQL/9.4
ls: /Library/PostgreSQL/9.4: No such file or directory

$ psql -U postgres -c 'SHOW data_directory'
    data_directory
------------------------------
 /Library/PostgreSQL/9.3/data
(1 row)

$ locate pg_hba.conf
/Library/PostgreSQL/9.3/share/postgresql/pg_hba.conf.sample
/opt/local/share/postgresql93/pg_hba.conf.sample
/opt/local/share/postgresql94/pg_hba.conf.sample

$ ls /opt/local/share/postgresql94
conversion_create.sql  postgres.bki           snowball_create.sql
extension              postgres.description   sql_features.txt
information_schema.sql postgres.shdescription system_views.sql
pg_hba.conf.sample     postgresql.conf.sample timezone
pg_ident.conf.sample   psqlrc.sample          timezonesets
pg_service.conf.sample recovery.conf.sample   tsearch_data

Until postgres version 9.5 the upgrade procedure was to

create a new fresh database cluster using the new version
make a full dump of the old cluster
read the dump into the new cluster.

the selection of the cluster depends on the PGDATA environment variable.
the version of the cluster software version depends on the PATH environment variable.
Make sure they match your needs. The setting is done in the startup scripts and can be copied for the client.


Should I created a different table to hold a big text / varchar field in MySQL 5.5.61 with Antelope file format? I read that newer version of MySQL save the big text fields apart from the rest of the row, so we don't have to bother about it.

However, how about an older version, specifically MySQL 5.5? Is it good for speed and performance purposes to save my text field in a different table?

The non-inline version of text storage a slower access because its in a different page in the tablespace, however in a separate table it has to access the PK of a separate table, and then take the slower access to access the text column in a different page.

So a separate table is worse for performance.


designing data model similar to Power Point Presentation slides I am trying to come up with a data model that would adequately describe business rules, similar to power point presentations with one exception. The structure of the slides is predefined - each slide has a slide style. I need to store the contents the user inputed for that style (which image he/she chose, what is the String in heading 1 or 2...)

By slide style I mean that some slides have 1 heading, some 2, some 3 others, some have 1 image in it, others 8...some have a background video, some solid color, others backgorund image..

The order of slides in a presentation is also important. Users can add slides to presentation, remove them, replace them. One slide style can be repeated multiple times in a project. If you want to print the presentation, it gets printed in the correct order.

How would you construct such data model?

I've created users and projects table, but I am having problems with how to represent and slide_style. slide_style has so many different options and I see no elegant way of doing this. How would you do this?

slide_style and project are in M:N relationship, so I will create another table project_slides and link them together, but how to represent so many different styles is the real issue here.

CREATE TABLE `users` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(256) DEFAULT NULL,
  `password` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

CREATE TABLE `projects` (
  `project_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`project_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `projects_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

It sounds like you are trying to store a whole lot of information, as if you are creating an entire Powerpoint application. Not a bad thing, but as you have indicated you do not want to just use Slide Templates in files, you have to store everything about the Slide (both Templates and user Slides) in the database. This would enable you to reproduce the template on the fly based on the data saved once the user creates their own Presentation, based on a template.

I would approach this with a data model that uses an Entity-Attribute-Value method to store (what could be) a large number of Elements on your slides. The following is the data model I have come up with for you:

The interesting part, that you are probably struggling with, is on the right hand side of the model. SlideElement is an Element object that appears on your Slide. Each Element has a defined set of Attributes as described by the type of element you are adding.

This may not make much sense to you unless you see some example data as shown below:

So each Attribute defines the Attributes of an Element - an Element (could also be called "Control" or something similar) being a text box/picture/video, in the above data.  Elements are placed on Slides at particular locations, defined by the X/Y Position as described on the Element table and with a particular size (Length/Width). The Element entity, along with all the Attributes, is an instance of an Element, with its value described by the "Value" field. For a text box, the value would be its text/text size/colour/back colour etc, for videos/photos, their location/format/encoding/type etc.

Note the "PresentationType" field on the Presentation. I have put this as the identifier to decide if the file is a "Template" or "User" presentation. If the user chooses one of the Template files, the Presentation/Slide/SlideElement/Element rows would all be copied from the "Template" tables and re-inserted for the User tables. The default Element values would therefore be used to start with, with the user overwriting these as they work on their Slide design. The user would be able to move the elements around their slide as well, this would be reflected in the "SlideElement" table XYPos values as well as the size of the Element. The user could also add Elements, like a new text box or photo, which would create a new set of Element rows based on what was added. You would need a default list of Elements to add for each type, that step I haven't modelled but could be done easily.