Tag Info

Hot answers tagged

12

The performance will be the same. The optimizer will recognize this and create the same plan. On the other hand I wouldn't say they are equal. The first form is far more readable and generally expected. For an example using some tables I have at hand you can see the execution plan is exactly the same no matter how I write the query. You should be able to ...


9

They are semantically identical and the optimiser should have no trouble recognising this fact and generating identical plans. I tend to put conditions referencing both tables in the ON and conditions referencing just one table in the WHERE. For OUTER JOINS moving the conditions can affect the semantics however.


7

You are correct in your description but the conclusion is not correct. It may still be faster to use the index anyway even if it has to go off to the table for the rest of it. The data pages contain many fewer rows than the index so there's lots of extra I/O when scanning. Generally the index will tend to give you benefits if it selects few rows even if ...


5

This is a known issue regarding Postgres optimization. If the distinct values are few - like in your case - and you are in 8.4+ version, a very fast workaround using a recursive query is described here: Loose Indexscan. Your query could be rewritten (the LATERAL needs 9.3+ version): WITH RECURSIVE pa AS ( ( SELECT labelDate FROM pages ORDER BY labelDate ...


4

Use KEEP INDEX. drop table demo purge; create table demo (id number, constraint demo_pk primary key (id)); alter table demo drop constraint demo_pk keep index; select index_name from user_indexes where table_name = 'DEMO'; INDEX_NAME ------------------------------ DEMO_PK


4

Try to use this. ;-) use DB; EXEC sp_MSforeachtable @command1=N'print ''?'' DBCC DBREINDEX (''?'', '' '', 80) '; Double quotation marks will escape the following quotation mark. Here an example of sqlcmd: sqlcmd -S YOURSERVER -Q "use net_temp_test; EXEC sp_MSforeachtable @command1=N'print ''?''';"


4

why is the query optimizer not able to use the index for deleting when the subquery version, while it is while using the join version? Because the optimizer is/was a bit dumb in that regard. Not only for DELETE and UPDATE but for SELECT statements as well, anything like WHERE column IN (SELECT ...) was not fully optimized. The execution plan usually ...


4

If you have a simple b-tree index on mycolumn, then yes, you would need to avoid calling functions on that column in order to be able to use the index to filter rows. In this case, it would seem to make much more sense to convert your numeric literals to timestamps than to do the reverse SELECT * FROM MYTABLE WHERE my_column > to_timestamp( ...


3

If the USING INDEX clause is omitted, the index will be still created automatically with the same name as the constraint, in the same tablespace where table is created. This will not cause an error. http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#i2095331 If you specify schema.index, then Oracle attempts to enforce the constraint ...


3

Index usage with text_pattern_ops (as well as with the default operator class when using the C locale) depends on the binary representation of character data. citext stores original values with the case preserved, so there must be a problem with that ... Like you commented, the actual reason is burried in collation support. Either way, citext or text, you ...


3

The old index stays in place, is maintained and being used by queries. Only at the end of the index build does SQL Server alter the metadata in such a way that the old index disappears and the new one is set in. There is not a single millisecond without an index. (But there is a short-lived Sch-M lock twice during the online build.)


3

You forgot to mention that you installed the additional module pg_trgm, which provides the similarity() function. Similarity operator % First of all, whatever else you do, use the similarity operator % instead of the expression (similarity(job_title, 'sales executive') > 0.6). Much cheaper. And index support is bound to operators in Postgres, not to ...


3

Determine the largest current ID value: select max(TIMESHEET_ID) from PROJECTMGMT.TIMESHEET then reset the identity value: alter table PROJECTMGMT.TIMESHEET alter column TIMESHEET_ID restart with <whatever the max value is + 1>


3

My understanding of a rebuild index online operation is that the index will have a snapshot taken and the rebuild is started on the snapshot index. Incorrect. An unfortunate overload of the term 'snapshot'... A snapshot read of the index is used, which means row-versioning see How Online Index Operations Work: A snapshot of the table is defined. ...


3

Constraints and Indexes serve two different purposes in the database. Constraints prevent data that violates the constraints from being stored in the database. This protects future users of the data, but it limits flexibility and adds overhead. For foreign key constraints such as the ones you have presented, the overhead is usually small, compared with ...


2

processing_date is not in the index, so it cannot use an index only scan. Create the index on (presentation_code, pct_id, processing_date, actual_cost) so that it can use an index only scan, and in that order so that it can efficiently use the sort order of the index to do the grouping.


2

Indexing Expressions I believe that the solution you are looking for pertains to building an index on an expression, rather than on the original data itself. For outside references, you can consult Wikipedia or the Oracle documentation, where the link here contains more info and subsequent links in the 'Function-Based Indexes' section. There are associated ...


2

here is the answers for two of your questions Optimizer is not able to use index because the where clause changes for every row. The delete statement will look something like this after it pass the optimizer delete from VARIABLE_SUBSTITUTION where EXISTS ( select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY where BUILDRESULTSUMMARY.BUILD_KEY = ...


2

Here are a few quick tips which can help improve your performance. I'll start with the easiest tip, which is almost effortless on your part, and move on to the more difficult tip after the first. 1. work_mem So, I see right off-hand that a sort reported in your explain plan Sort Method: external merge Disk: 5696kB is consuming less that 6 MB, but is ...


1

The best query very much depends on data distribution. You have many rows per date, that's been established. Since your case burns down to only 26 values in the result, all of these solutions will be blazingly fast as soon as the index is used. The partial index below will be a bit faster if you have many NULL values. For more distinct values it would get ...


1

Index is case insensitive in your case, but optimizer is clever enough to use it efectively anyway. In first explain, mysql uses ref access method - that means that it is able to directly fetch rows with given value. In second explain, it says "using where" and uses range access - it takes the string, fetches all values from index searching for that string ...


1

Note the link you gave on the BINARY operator where is it says the following Posted by Yann Neuhaus on July 20 2005 12:34pm Per default the search operation in not case sensitive, example, looking for 'HYPE BEAU' returns 'Hype Beau' in the table product : mysql> select prod_name, prod_id from products where prod_name = 'HYPE BEAU'; ...


1

The optimizer seems to think that your index is not selective enough. I think that you can do one or more of the following things: ANALYZE TABLE bets; ALTER TABLE bets ADD INDEX bets_time_amount (time, amount); /* this is a covering index */ add a FORCE INDEX to your SELECT


1

You can use SolrMongoImporter Download solr-mongo-importer jar file Download mongo-java-driver jar file Place both jar's files in your Solr libraries folder Include the libraries in your solrconfig.xml Example of usage: <lib path="../../dist/solr-mongo-importer.jar" /> <lib path="../../dist/mongo.jar" /> <?xml version="1.0" ...


1

My understanding is the snapshot should reside on the index file drive. Is this correct? It resides on same file where Index file resides unless you use Sort_in_tempdb option during rebuild. From BOL When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the destination filegroup. If the SORT_IN_TEMPDB option is set to ...



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