New answers tagged

3

Now when there is an insert of value 9, then 9 added either to page2 (page split) or to a new page? One or the other. It doesn't really matter. Usually the splitting value isn't right in the middle and it must go on the old page or must go on the new page. Page2: 6, 7, 8, Page4: 9, 10, 11 Page3: 12, 13, 14 How does this result in fragmentation? Is it ...


0

There is actually no big difference at all. A direct pointer link shortcut is completely optional. If you don't use any shortcuts, the jump to the next link takes some 3 or 4 pointers. If the algorithm uses a lot of horizonal jumps, it saves time; if not, you waste some extra resource.


0

Simplest solution: the queries are actually not the same. Not so obvious solution: the sessions from the application server use different optimizer or NLS parameters and this affects the optimizer in a way it choses another execution plan due to different features enabled, different estimated query cost, NLS specific indexes. You can find some of these ...


2

For wide character columns your best bet is to calculate a checksum and add an index. Then calculate the checksum of what you are searching for and add that to your query, it looks like this: -- add a persisted computed column, we will calculate a checksum over the body column which is of type nvarchar(max) alter table dbo.posts add chksum_body as checksum(...


3

Goal is to put together both columns with a length of MAX and an index That is ambiguous. If you mean that the table structure changes such that there are only two colums (ID, textvalue+longtextvalue) - then the index could only be keyed on ID and include textvalue+longtextvalue. If the table structure isn't changing then same concept but index could be ...


2

SELECT * is generally bad even if you really need all or most of the columns, it is even worse if you only need some small number that could be covered by the index Depending on the optimization the subquery itself might be evaluated many times - once per main table row. But it might be optimized by joining or by materializing too. Execution plan can show ...


2

If I get this right, You start with a set of contract id's to collect the set of group id's that correspond to them. Then you want to collect all the rows that have a group id that is in that set. By definition, this means the DBMS must do a table scan. Your best chances are to create a second index in which group id is the first attribute (e.g. group id, ...


5

Columns would be listed in the inequality_columns if the query prompting the missing index request has a range predicate, such as > or NOT. Microsoft Docs says this about the inequality_columns column in sys.dm_db_missing_index_details: Comma-separated list of columns that contribute to inequality predicates, for example, predicates of the form: table....


3

predicates Equality columns: = IS NULL INTERSECT EXCEPT Inequality columns: >= <= > < != and <> IS NOT NULL BETWEEN LIKE


1

If there is something else in the WHERE clause, such as WHERE state = 2 AND foo = "blah" Then add a composite index INDEX(state, foo) Then the "cardinality" question applies to the combination of those two columns; the index is more likely to be used. At the same time, drop the existing INDEX(state) as being useless; even 'in the way'.


0

Your winning query is running against diet.barcodes and is doing a COLLSCAN because it doesn't have an index. Create an index of that collection.


0

You can use $indexStats to debug if all indexes are in use to delete some og them and speed up the inserts: db.collection.aggregate( [ { $indexStats : { } } ] ).pretty() More info: indexStats


3

In addition to MBuschi's answer, if it isn't also clear, but a Scan operation literally iterates through every item in the data structure (e.g. if it's an Index Scan then every item in the underlying B-Tree is scanned), so ordering in the index (for the most part) doesn't matter anymore. Scanning the entire B-Tree of the index is essentially the same (or ...


13

It's a matter of giving results using the minimum IO operations. The index is much smaller than the whole table and based on statistics SQL Server knows the average cardinality of a single name ('xyz'). So, counting number of pages to read that index plus the number of pages of the lookups (number of occurrance * index depth) to retrive others fields of the ...


1

NULL values are stored in an index just like all other values. If you want an index to speed up queries on the columns where the value is not NULL, you can use a partial index that will only index the rows that satisfy a condition: CREATE INDEX ON tab (...) WHERE somecol IS NOT NULL; The advantages are: this will work with queries that have somecol IS NOT ...


1

It depends on if and how you defragment this index. (Which might not be something you want to do in the first place, but that is a different discussion.) The FF value doesn't affect the modifications in any way, the pages will fill up as you do inserts and this will not be limited by the specified FF value (that would make FF useless). ALTER INDEX REORAGINZE ...


4

FYI, here is Brent's support page for sp_blitzIndex. Scripts like Brent's are based on SQL's built-in "dynamic management views" (DMVs), such as sys.dm_db_index_usage_stats, so let's look at that documentation page: The counters are initialized to empty whenever the database engine is started. Use the sqlserver_start_time column in sys....


1

This "composite" index should help: replies: INDEX(repliable_type, repliable_id, created_at) (And DROP INDEX repliable_type_repliable_id since it is now in the way. This may be why you are not seeing an improvement.) Since that one seems to be giving you trouble, DROP any indexes starting with reliable_id, then ADD INDEX(reliable_id, ...


7

if I have a clustered index on [column_a], [column_b], and [column_c] and run the same query from above, will the data ALWAYS come back sorted based on that order since that's the order that the clustered index was created on? No. SQL Server does not guarantee that it will return data in any order unless you specify the order. It is easy to prove things can ...


2

One way: generate all sub-domains, top-level down, and take the first match: WITH cte AS ( SELECT ord, string_agg(label, '.') OVER (ORDER BY ord ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS subdomain FROM unnest(string_to_array('bla.bla.dd.cc.360.cn', '.')) WITH ORDINALITY AS x(label, ord) ORDER BY ord DESC OFFSET 1 -- skip tld ) ...


0

It would be a waste of time using an index when you ask for all rows of a partition. Also, searching for 'LOA' in a varchar2(2) column has little chance of success.


0

Well here I am busting the party.... Your question: Is there typically a mechanism inside of the database to gracefully deal with collisions? How does it work? Yes and no (but not in that order). Let Me Explain Let's generate a table on SQL Server 2019: CREATE TABLE [dbo].[Generated_Data_GUID] ( [ID] [int] IDENTITY(1, 1) NOT NULL, [GUID] ...


8

No, querying things does not write data to the transaction log. The transaction log is the log of changes to your database. When you update an indexed column, the index is updated. The changes this makes to the index are logged in the transaction log the same way that the changes made to the table are logged. This is because if your database was to crash, ...


8

The filtered index isolates the set of rows where L2 = ''. Your query is asking to take that entire subset of rows (the matching rows are subset of the table but also all the rows in the index) and aggregate them with the GROUP BY clause. You would do so by looking at the entire filtered index, or in other words scanning it, especially so since it's covering ...


2

Uniqueness over a lot of tables. This can be solved by additional table + triggers. Like CREATE TABLE tableABC (id VARCHAR(255) UNIQUE); CREATE TRIGGER tr_bi_a BEFORE INSERT ON tableA FOR EACH ROW INSERT INTO tableABC VALUES (NEW.id); DEMO If this column value can be altered then you must create according BEFORE UPDATE triggers (which removes OLD.id and ...


2

I think you should try storing the @shape variable in another base table (temp table ?) and add a spatial index to that too Example CREATE PROCEDURE [dbo].[getUsersByArea] @start DATETIME, @end DATETIME, @shape GEOGRAPHY AS BEGIN SET NOCOUNT ON; CREATE TABLE #Shapes ( [Id] [uniqueidentifier] NOT NULL, Shape GEOGRAPHY ...


1

Please provide SHOW CREATE TABLE so we can see the datatypes and new_index. For that query, I suggest INDEX(col_3, col_1, col_2, col_4, insert_time) I put col_3 first because of the "=" test: col_3 = 0 The rest of the columns are there for "covering" (cf "Using index"), and the ordering may not matter. That ordering may ...


1

It sounds like col_1 is not the first (or only) column in index ind5. Or, many rows have col_1 = 10. Please provide output from SELECT col_1, COUNT(*) FROM mytable GROUP BY col_1; Meanwhile, INDEX(col_1, status) Is optimal for all three queries. That IN and that OR are optimized the same. Tacking status onto the index makes it "covering", hence ...


0

I think you might be confusing a few different concepts which makes it a little difficult to provide a satisfactory answer, but I'll try my best. Additionally, your mileage may vary on what options are available, or what the "usual case" is, depending on which specific database system you're talking about. My answer is based on my general database ...


0

The answer is "no". It's the application responsibility to generate unique IDs using a method appropriate for its requirements and handle duplicate keys as it sees fit. The database cannot decide for you whether you want to generate a new ID or update an existing records (or do something entirely different, like raise an alert). Even things like ...


0

alter system set max_parallel_workers_per_gather=0; select pg_reload_conf();


Top 50 recent answers are included