full-table-scan

Why would Oracle ignore a "perfect" index?

I have this table: create table demo ( key number(10) not null, type varchar2(3) not null, state varchar2(16) not null, ... lots more columns ... ) and this index: create index demo_x04 on demo(key, type, state); When I run this query select * from demo where key = 1 and type = '003' and state = 'NEW' EXPLAIN PLA...

Two sets of indentical tables with joins using indexes in MySQL; one requires full-table scan

Hi, Im getting very confused with indexes in MySQL. I have two tables: TableA1 and TableA2. I created indexes on these for the joins between them and queries run really fast. I have another 2 tables with an identical setup e.g. TableB1 and TableB2. The only difference is that these tables have a few null values. For some reason the...

Does postgresql keep track of full table scans it makes?

I'd like to do something similar to what's described in http://www.bestbrains.dk/Blog/2010/03/25/HowToAssertThatYourSQLDoesNotDoFullTableScans.aspx but for that I'd need postgres to keep track of any full table scans it does. Is there such a thing for postgres? ...

Optimize date query for large child tables: GiST or GIN?

Problem 72 child tables, each having a year index and a station index, are defined as follows: CREATE TABLE climate.measurement_12_013 ( -- Inherited from table climate.measurement_12_013: id bigint NOT NULL DEFAULT nextval('climate.measurement_id_seq'::regclass), -- Inherited from table climate.measurement_12_013: station_id integer...