Tag Info

Hot answers tagged

12

Not allowing customers to have a less than 0 balance is a business rule (which would change quickly as fees for things like over draft are how banks make most of their money). You'll want to handle this in the application processing when rows are inserted into the transaction history. Especially as you may end up with some customers having overdraft ...


12

The following explanation is given in this Microsoft Technical Article: Why does the first index on a view have to be CLUSTERED and UNIQUE? It must be UNIQUE to allow easy lookup of records in the view by key value during indexed view maintenance, and to prevent creation of views with duplicates, which would require special logic to maintain. It must ...


11

All three columns are persisted to disk in the clustered index on the indexed view (no different, really, from a clustered index on a regular table). You can validate this using DBCC PAGE. I created the following structure in tempdb: USE tempdb; GO CREATE TABLE dbo.a(a INT); CREATE TABLE dbo.b(a INT, b DATETIME); CREATE TABLE dbo.c(a INT, c CHAR(32)); ...


10

An indexed view is physically stored ("materialised") on disk = requires memory A standard view is simply an expandable macro: there is no persistence of the data and the base tables are always used Other notes: Both will return the correct data from the base tables SQL Server will consider whether to use the indexed view or just expand it like a macro ...


10

The query processor can produce an invalid execution plan for the (correct) query generated by DBCC to check that the view index produces the same rows as the underlying view query. The plan produced by the query processor incorrectly handles NULLs for the ImageObjectID column. It incorrectly reasons that the view query rejects NULLs for this column, when ...


8

Matching indexed views is a relatively expensive operation, so the optimizer tries other quick and easy transformations first. If those happen to produce a cheap plan (0.05 units in your case) optimization ends early. The bet is that continued optimization would consume more time than it saved. Remember the optimizer's primary goal is a 'good enough' plan ...


8

So SET ARITHABORT ON basically says "if a divide by zero error happens or an arithmetic overflow happens abort the query" This is usually desirable behavior and is the default instance wide setting. If this causes issues with your vendor's queries, I would say that they may have been suffering from some coding issues to begin with. I would ask them for more ...


7

After reading these two discussions, I decided on option 2 Having read those discussions too, I am not sure why you decided on the DRI solution over the most sensible of the other options you outline: Apply transactions to both the transactions and balances tables. Use TRANSACTION logic in my stored procedure layer to ensure that balances and ...


7

Resolving indexes on Views: In SQL Server Enterprise, the query optimizer automatically considers the indexed view. To use an indexed view in the Standard edition or the Datacenter edition, the NOEXPAND table hint must be used. I'm guessing that you're on Standard Edition.


7

The obvious risk from making this change is that vendor queries that previously ran correctly could start to throw errors, or return incorrect results. The ARITHABORT setting partly controls whether arithmetic overflow and divide-by-zero errors return a NULL result, terminate the statement with an error, or terminate the batch with an error. How the vendor ...


6

A slightly different approach (similar to your 2nd option) to consider is to have just the transaction table, with a definition of: CREATE TABLE Transaction ( UserID INT , CurrencyID INT , TransactionDate DATETIME , OpeningBalance MONEY , TransactionAmount MONEY ); You may also want a transaction ...


6

NEXT SYSDATE +1 means the materialized view will be automatically refreshed every day at the same time when the view is created. In order to refresh the materialized view every 6 hours use NEXT SYSDATE +6/24 DISABLE QUERY REWRITE indicates that the materialized view is not eligible to be used by the query optimizer to rewrite sql queries on the base ...


6

A fast-refreshable materialized view cannot contain a non-deterministic function like current_timestamp. So if you want to materialize the data from the last 24 hours in a materialized view, the materialized view would need to do a complete refresh every time. Do you need a materialized view? Could you maintain your own staging table and create a custom ...


5

It appears to do it in sequence in your plan. Not in parallel. The insert happens into the heap. The inserted rows are inserted into the eager spool which is a blocking operator. When all rows are inserted into the base table the rows are inserted from the spool to the view. As for the percent issue it appears that the spool confuses SSMS. Despite showing ...


5

I am not familiar with accounting, but I solved some similar problems in inventory-type environments. I store running totals in the same row with the transaction. I am using constraints, so that my data is never wrong even under high concurrency. I have written the following solution back then in 2009:: Calculating running totals is notoriously slow, ...


5

Connect as sysdba and check if there is any entires in dba_summaries for the MV. If there is run, drop summary <OWNER>.<MV_NAME>;`


5

Good question. I "solved" this problem for my situation a while back by making the MV's and any indexes on them NOLOGGING. There was no point to it my situation - I was doing a full refresh of the view anyway, why would I need redo?


5

A basic version of materialized view are being delivered in 9.3, see depesz's article. However, it's a very limited version as you have to call REFRESH MATERIALIZED VIEW to get the database to update the view. This will hopefully improve to become automatic in later versions soon.


4

I can't understand why you can't aggregate on the fly and why it's so slow. Is the "Valid" idea a workaround to deal with the lag of OrderTotals or some business process Both of these ideas discard the InvalidOrder table which is a workaround for poor indexing. Suggestion 1: Create a computed column ALTER TABLE dbo.Order ADD PriceXQuantity AS Price * ...


4

240 msec isn't that bad for a complex view involving unions and subqueries. It's not great, but I've seen a lot worse. Beyond this, I'm afraid I have more questions than answers. It's not clear from your question whether this is currently an indexed view. Is it? It sounds like the first user to hit stale data will have to wait at least a full second for ...


4

Typically you index views if you are often running aggregates, not to magically speed up joins. Also if you are not using table partitioning there is no good reason to investigate partition-aligned indexed views. You should be focusing on optimizing the query, irrespective of the view, IMHO. An indexed view is not a magic turbo button (though a lot of ...


4

I believe that the cast to XML is killing performance for you and what is happening is described by Paul White Compute Scalars, Expressions and Execution Plan Performance The cast to XML is deferred to where you actually use the XML column so in your query the cast happens three times for each row returned.


4

I think there's some misunderstanding about what you're attempting to do here. Since your current design is to return all 24 rows from the base table, presumably all the supplementary fields are returned as well (to display in a grid, or something). In order to fully aggregate the Value column, all the supplementary columns cannot be included in the SELECT ...


4

Further investigation shows that this is a bug in DBCC CHECKDB. A Microsoft Connect bug has been opened: Unfixable DBCC CHECKDB error (that is also a false positive and otherwise strange). Fortunately, I was able to produce a repro so that the bug can be found and fixed. The bug can be hidden by playing with the database schema. Deleting an unrelated ...


4

It seems that Oracle doesn't like ANSI-style joins in the materialized view definition when refereshing... Changing the definition to create materialized view dept_emp_mv as select dname, job, hiredate, count(*) as nbr from emp e, dept d where e.deptno = d.deptno group by dname,hiredate,job; makes it ...


4

SQL Server will always be able to automatically update the view's index(es) as the source table(s) change; there's nothing extra you need to do. You can inspect a query plan that modifies one of the source tables, you'll see there are extra operators to update the view's index(es), the same way it has to update all the applicable nonclustered indexes on the ...


4

A view is just a "saved query". The indexes on the base table are still used whenever you access the view. You don't need to use an indexed view, unless the view contains an expensive logic (aggregations or joins) that you don't want to perform each time you query the view. Please note that even when the view is "materialized", the optimizer is free to ...


3

No, I don't think defining an index for a common table expression is possible. You can experiment with the materialize hint, but I don't think you'll get anything faster than the solution with (indexed) temporary tables.


3

Your materialized is not defined with a NEXT clause, therefore it will only refresh when you ask for it explicitely. You can use either DBMS_MVIEW.REFRESH directly or create a refresh group with DBMS_REFRESH. In order to automate the refresh, you could program a job with DBMS_SCHEDULER or DBMS_JOB (dbms_job is deprecated in 11g). You could also define your ...


3

If drop materialized view is leaving an entry in dba_summaries, then you are hitting a bug - perhaps the one described here As you do not have a support contract, you can't raise a TAR. However the first question Oracle Support would ask you is whether you are running the latest patchset, 11.2.0.3 - I suggest you consider this option first as the root ...



Only top voted, non community-wiki answers of a minimum length are eligible