Commented by: Ann Harrison (awharrison)

I would want a more specific use case than "some queries run to[o] long without index before initiating an operation as expensive as index creation for a temporary object.

Commented by: Sean Leyne (seanleyne)

Given that an index requires a full scan of a table, an expensive operation, I don't see any benefit of temporary indexes. The cost of the full scan will likely be more expensive then the base query.

Commented by: @livius2

//Given that an index requires a full scan of a table, an expensive operation, I don't see any benefit of temporary indexes. The cost of the full scan will likely be more expensive then the base query.
1. For running some query once this is expensive
but for running query many times benefit is very big as big as table is bigger and count of queries are bigger

2. Simple scenario - this is only simplification but this show some case
You have sales system and some external system and you must test data corelation in both systems

You have table like
create table test
(ID Integer NOT NULL PRIMARY KEY,

DEP_ID Integer,
CUST_ID INTEGER,
YEAR INTEGER,
NR Integer,
EXPORT_PACK_NR VARCHAR(10)
)

CREATE INDEX IXA_TEST__XXX ON TEST(DEP_ID, YEAR, NR);

normally you run query like
SELECT * FROM TEST T WHERE T.DEP_ID=1 AND YEAR=2012 AND NR=17
but e.g in external system you can have Year changed in data by some person

and to check if data in both systems are corelated you run query
SELECT * FROM TEST T WHERE T.DEP_ID=1 AND NR=17 AND EXPORT_PACK_NR='123.78.14'

if you run this query many times then in all query you got table scan {really partial index scan} very expensive
but if you create temporary index
CREATE TEMPORARY INDEX TMP_TEST__YYY ON TEST(DEP_ID, NR, EXPORT_PACK_NR)
you got query optimized

and in this scenario e.g. 1 query with table scan run in 1 minute
index creation take e.g. 15 minutes
you run query 100 times then
100*1 minute = 100 minutes - 15 minutes = 85 minutes benefit

3. Think also about queries with ocassional join
4. Think about expression index

Commented by: Sean Leyne (seanleyne)

1 - But why is it necessary to define a *temporary* index? create a permanent index. The cost of maintaining the index is not that significant.

2 - What would be the context/visibility of the *temporary* index?

3 - You can do what you want, today, by defining an index, using it and then deleting it.

4 - If the order of your index was DEP_ID, NR and then YEAR, your sample query would take signigicantly less time. The optimizer would use the first 2 part of the index for narrowing/reducing the scope of the "partial scan".

Commented by: @livius2

1 - But why is it necessary to define a *temporary* index? create a permanent index. The cost of maintaining the index is not that significant.
Cost of maintaining index in high load system is to big to have it permanent i test this

2 - What would be the context/visibility of the *temporary* index?
as in first post transaction/connection

3 - You can do what you want, today, by defining an index, using it and then deleting it.
is this safe way to create it and deleting when system is high loaded with many users or i must be a single user?
If this is safe way then i see that all indexes have "temporary" feature already

4 - If the order of your index was DEP_ID, NR and then YEAR, your sample query would take signigicantly less time. The optimizer would use the first 2 part of the index for narrowing/reducing the scope of the "partial scan".
Yes i know that selectivity is bigger on Nr then on Year - this was only sample but if your initial view is Year then this index can be better if you have many deps