Tag Info

Hot answers tagged

8

The decisive factor is whether there is a unique constraint/index on b (key_column). If there is no unique constraint, there may be duplicate values in b.key_column, so the optimizer has to always make a plan that reads from the table (or an index if there is one on that column). If on the opposite, there is a unique constraint, the query is equivalent to ...


3

When you do a SELECT ... FROM some_view, the first thing Oracle will do is substitute some_view with SELECT statement that defines the view. So in your case, SELECT some_column FROM dummy is, both in logical and optimizer terms, the same as SELECT some_column FROM ( SELECT a.some_column, b.other_column FROM a LEFT JOIN b USING (key_column) ); ...


3

Assuming that card_no and log_entry have VARCHAR or CHAR type, I would first add an index on (card_no, date, last_entry): ALTER TABLE entry_log ADD INDEX card_no__date__last_entry__ix (card_no, date, last_entry) ; and then use this query: SELECT CONCAT(date, ' ', last_entry) AS LAST_LOG FROM entry_log WHERE card_no = LPAD('2948', 32, '0') ORDER ...


3

Cleanse your data before storing it. Otherwise, INDEXes may be useless. I this particular case, the TRIM function is hiding card_no, making the INDEX on card_no useless. This SELECT would run a lot faster because of the index: SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG FROM entry_log WHERE card_no = '2948' OK, you don't like the ...


2

Two things that are very odd here: The query select 300k rows from a table with 1M+ rows. For 30 % (or anything over 5 % - depends on row size and other factors) it doesn't typically pay to use an index at all. We should see a sequential scan. The exception would be index-only scans, which I don't see here. The multicolumn index @Craig suggested would be ...


1

Multiple ranges lookups cannot be optimized with normal (B-tree) indexes. You have to create an R-tree index for your coordinates.



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